IF  EXISTS (SELECT * FROM SYS.VIEWS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[V_EST_ANALISE_CFOP_DARE]'))
    DROP VIEW [DBO].[V_EST_ANALISE_CFOP_DARE]    
GO

 /*
=======================================================================================
'Nível Classificação.: INFORMAÇÃO RESTRITA
'Versão..............: 202203040019
'Job.................:  
'Nome................: [V_EST_ANALISE_CFOP_DARE]
'Objetivo............: 
'Tabelas.............: 
'Dt Criação..........: Shirlene Prado
'Dt Ult Alteracão....: 03-03-2022
'Alterado por........: Marcos barros
'Criado por..........: 
'=======================================================================================
FARMAGOI-24920 - 202203040019 - Incluído Valor NF - Juan Mello
 */

 --SELECT * FROM V_EST_ANALISE_CFOP_DAREx WHERE CD_EMP= 1 AND CD_FILIAL = 1 AND cd_nf = 2657
CREATE VIEW [DBO].[V_EST_ANALISE_CFOP_DARE] 
WITH ENCRYPTION AS		
		
--Validar composição de notas fiscais  que geraram o Dare/Dae/GNRE
	SELECT 
                V_EST_NF_ENT.CD_EMP, 
                V_EST_NF_ENT.CD_FILIAL, 
				V_EST_NF_ENT.DT_ENT,
                0 AS CD_CX, 
                V_EST_NF_ENT.NR_NF, 
                V_EST_NF_ENT.CD_ORIG, 
                V_EST_NF_ENT.NM_ORIG, 
        
                GLB_NT_OP_SEM_DARE.CD_NT_OP_FC AS CFOP, 
                ISNULL(GLB_NT_OP.CD_NT_OP_FC,'')  AS CFOP_DARE, 
        
                ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) + V_EST_NF_ENT.VLR_OUTRAS_DESPESAS + V_EST_NF_ENT.VLR_ICMS_SUBS_IT  + ISNULL(V_EST_NF_ENT.VLR_IPI_IT,0) - ISNULL(V_EST_NF_ENT.VLR_ICMS_DESONERADO,0)  AS VLR_CONTAB, 
        
                V_EST_NF_ENT.VLR_BASE_ICMS_IT AS VLR_BASE_ICMS, 
        

                CASE WHEN EST_TRIB_SEM_DARE.ICMS = 0 AND EST_TRIB_SEM_DARE.ISENTO = 0 THEN  
                    0 
                ELSE 
                    CASE WHEN EST_TRIB_SEM_DARE.ISENTO = 0 THEN  
						0
                    ELSE 
                        CASE WHEN ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) > 0 THEN  
                            ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) 
                        ELSE  
                            0 
                        END 
                    END 
                END VLR_ISENTO, 
        
                V_EST_NF_ENT.ALIQ_ICMS AS ALIQ_ICMS, 
        
                V_EST_NF_ENT.VLR_ICMS_IT AS VLR_ICMS, 
                0 AS VLR_SERVICO, 
			    0 AS VLR_BASE_SUBS, 
                0 AS VLR_ICMS_SUBS,  
                ISNULL(EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS,0) AS VLR_ICMS_SUBS_DARE, 
        
                'ENTRADA' AS TIPO, 
                CASE WHEN EST_NF_ENT_DAR_IT.CD_EMP IS NULL THEN 'NOTA FISCAL' ELSE 'DARE' END AS TIPO_DOC, 
                V_EST_NF_ENT.UF, 
                EST_TRIB_SEM_DARE.CD_TRIB_FC AS CD_TRIB_FC, 
                ISNULL(EST_TRIB.CD_TRIB_FC,'') AS CD_TRIB_FC_DARE, 
        
                ISNULL(V_EST_NF_ENT.VLR_IPI_IT,0) AS VLR_IPI, 
        
				--'Valor Outros
                CASE WHEN EST_TRIB_SEM_DARE.SUBS_TRIB = 0 THEN  
					0			
                ELSE 
                    CASE WHEN ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) > 0 THEN  
						((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT)  
                    ELSE 
						0 
                    END 
                END +       
				CASE WHEN EST_TRIB_SEM_DARE.ICMS_RD =1 THEN
					((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) - V_EST_NF_ENT.VLR_BASE_ICMS_IT 
				ELSE
					0 
				END +
                CASE WHEN EST_TRIB_SEM_DARE.SUBS_TRIB_ANT = 0 THEN  
					0 
                ELSE 
                    CASE WHEN ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) > 0 THEN  
						((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT)  
                    ELSE 
						0 
                    END 
                END +  
                CASE WHEN EST_TRIB_SEM_DARE.FLAG_OUTROS = 0 THEN  
					0 
                ELSE 
                    CASE WHEN ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) > 0 THEN  
					 ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT)  
                    ELSE 
						0 
                    END 
                END +  
				ISNULL(V_EST_NF_ENT.VLR_OUTRAS_DESPESAS,0) 
				+ 
				SUM(V_EST_NF_ENT.VLR_ICMS_SUBS_IT) AS VLR_OUTRAS_DESPESAS, 
        
                ISNULL(V_EST_NF_ENT.VLR_ICMS_DESONERADO,0) AS VLR_ICMS_DESONERADO,  
  
                V_EST_NF_ENT.CD_PROD, 
                V_EST_NF_ENT.DS_PROD, 
                V_EST_NF_ENT.DS_MOV_OP, 
                ((V_EST_NF_ENT.QT_IT*V_EST_NF_ENT.VLR_IT) - V_EST_NF_ENT.VLR_DESC_IT) +  V_EST_NF_ENT.VLR_IPI_IT + V_EST_NF_ENT.VLR_OUTRAS_DESPESAS + V_EST_NF_ENT.VLR_ICMS_SUBS_IT - ISNULL(V_EST_NF_ENT.VLR_ICMS_DESONERADO,0) AS VLR_COMPRA, 
                V_EST_NF_ENT.NR_NCM, 
                CASE WHEN V_EST_NF_ENT.LISTA_PROD = 0 THEN   
                'POSITIVA' 
                WHEN V_EST_NF_ENT.LISTA_PROD = 1 THEN   
                'NEGATIVA' 
                WHEN V_EST_NF_ENT.LISTA_PROD = 2 THEN   
                'NEUTRA' 
                ELSE 
                'NAO USA LISTA' 
                END AS LISTA_PROD_NAME, 
    
                CASE WHEN V_EST_NF_ENT.TP_SUBS_TRIB = 0 THEN   
                CASE WHEN V_EST_NF_ENT.SUBS_TRIB_FORN = 0 THEN   
                    'NAO GERA SUBSTITUICAO' 
                ELSE 
                    'CORPO DA NOTA FISCAL' 
                END 
                WHEN V_EST_NF_ENT.TP_SUBS_TRIB = 1 THEN   
                'DARE 2.1' 
                WHEN V_EST_NF_ENT.TP_SUBS_TRIB = 2 THEN   
                'CONSUMIDOR FINAL' 
                WHEN V_EST_NF_ENT.TP_SUBS_TRIB = 3 THEN   
                'CONVENIO ICMS REA - CE' 
                ELSE 
                'NAO DEFINIDO' 
                END AS TP_SUBS_TRIB, 
        
                CASE WHEN V_EST_NF_ENT.SUBS_TRIB_FORN = 0 THEN   
                'NAO' 
                WHEN V_EST_NF_ENT.SUBS_TRIB_FORN = 1 THEN   
                'SIM' 
                END AS SUBS_TRIB_FORN, 
                CASE WHEN FLAG_N_GERAR_DAR = 0 THEN 'SIM' ELSE 'NAO' END AS FLAG_N_GERAR_DAR, 
                CASE WHEN FLAG_N_GERAR_DAR_IT = 0 THEN 'SIM' ELSE 'NAO' END AS FLAG_N_GERAR_DAR_IT, 
                V_EST_NF_ENT.UF AS UF_DEST,   
                GLB_CID.UF AS UF_FILIAL, 
                V_EST_NF_ENT.PERC_IVA, 
                ISNULL(EST_NF_ENT_DAR_IT.PERC_IVA,0) AS PERC_IVA_DARE, 
                ISNULL(EST_TRIB.ICMS,0) AS ICMS_CRED_DEB, 
                'Não' as SERVICO, 
                ISNULL(EST_NF_ENT_DAR_IT.VLR_PMC,0)AS VLR_PMC, 
                ISNULL(V_EST_NF_ENT.QT_IT,0)AS QT_IT, 
                V_EST_NF_ENT.GERA_MOV_ICMS_SUBS, 
                V_EST_NF_ENT.GERA_MOV_ICMS, 
                ISNULL(SUM(V_EST_NF_ENT.VLR_PROD_FRETE),0) AS VLR_PROD_FRETE, 
                CASE WHEN V_EST_NF_ENT.FLAG_MOV_OP_NFE_CPL = 1 THEN  
                'Sim' 
                ELSE  
                'Não' 
                END AS NOTA_CPL,
				ISNULL(SUM(EST_NF_ENT_DAR_IT.VLR_BASE_FECOP_ST),0)	AS VLR_BASE_FECOP_ST_IT,
				ISNULL(EST_NF_ENT_DAR_IT.PERC_FECOP_ST,0)			AS PERC_FECOP_ST,
				ISNULL(SUM(EST_NF_ENT_DAR_IT.VLR_FECOP_ST),0)		AS VLR_FECOP_ST_IT,
				ISNULL(SUM(V_EST_NF_ENT.VLR_BASE_FECOP_IT),0)															AS VLR_BASE_FECOP_IT,
				ISNULL(V_EST_NF_ENT.PERC_FECOP,0)																		AS PERC_FECOP,
				ISNULL(SUM(V_EST_NF_ENT.VLR_FECOP_IT),0)																AS VLR_FECOP_IT,
				V_EST_NF_ENT.CD_NF,
				V_EST_NF_ENT.VLR_NF
        FROM  
                V_EST_NF_ENT LEFT OUTER JOIN EST_NF_ENT_DAR_IT  ON 
                V_EST_NF_ENT.CD_EMP = EST_NF_ENT_DAR_IT.CD_EMP AND 
                V_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DAR_IT.CD_FILIAL AND  
                V_EST_NF_ENT.CD_NF = EST_NF_ENT_DAR_IT.CD_NF AND  
                V_EST_NF_ENT.CD_PROD = EST_NF_ENT_DAR_IT.CD_PROD  
        
                LEFT OUTER JOIN GLB_NT_OP ON  
                EST_NF_ENT_DAR_IT.CD_NT_OP = GLB_NT_OP.CD_NT_OP 
        
                INNER JOIN GLB_NT_OP GLB_NT_OP_SEM_DARE ON  
                V_EST_NF_ENT.CD_NT_OP = GLB_NT_OP_SEM_DARE.CD_NT_OP 
        
                LEFT OUTER JOIN  EST_TRIB ON 
                EST_NF_ENT_DAR_IT.CD_TRIB = EST_TRIB.CD_TRIB 
        
                INNER JOIN  EST_TRIB EST_TRIB_SEM_DARE ON 
                V_EST_NF_ENT.CD_TRIB = EST_TRIB_SEM_DARE.CD_TRIB 
        
                INNER JOIN  PRC_FILIAL ON 
                V_EST_NF_ENT.CD_EMP = PRC_FILIAL.CD_EMP AND  
                V_EST_NF_ENT.CD_FILIAL = PRC_FILIAL.CD_FILIAL 
        
                LEFT JOIN GLB_CID   ON  
                PRC_FILIAL.CD_CID  = GLB_CID.CD_CID 
        
            WHERE  V_EST_NF_ENT.STS_NF = 0 

            GROUP BY 
                V_EST_NF_ENT.CD_EMP, 
                V_EST_NF_ENT.CD_FILIAL, 
                V_EST_NF_ENT.NR_NF, 
                V_EST_NF_ENT.CD_ORIG, 
                V_EST_NF_ENT.NM_ORIG, 
                GLB_NT_OP_SEM_DARE.CD_NT_OP_FC, 
                GLB_NT_OP.CD_NT_OP_FC, 
                V_EST_NF_ENT.QT_IT, 
                V_EST_NF_ENT.VLR_IT, 
                V_EST_NF_ENT.VLR_DESC_IT , 
                V_EST_NF_ENT.VLR_OUTRAS_DESPESAS , 
                V_EST_NF_ENT.VLR_ICMS_SUBS_IT, 
                V_EST_NF_ENT.VLR_IPI_IT, 
                V_EST_NF_ENT.VLR_ICMS_DESONERADO, 
                V_EST_NF_ENT.VLR_BASE_ICMS_IT, 
                EST_TRIB.ICMS, 
                EST_TRIB.ISENTO, 
                EST_TRIB_SEM_DARE.ICMS, 
                EST_TRIB_SEM_DARE.ISENTO, 
                V_EST_NF_ENT.ALIQ_ICMS  , 
                V_EST_NF_ENT.VLR_ICMS_IT , 
                EST_NF_ENT_DAR_IT.VLR_BASE_SUBS   , 
                EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS , 
                EST_NF_ENT_DAR_IT.VLR_ICMS_SUBS, 
                EST_NF_ENT_DAR_IT.CD_EMP, 
                V_EST_NF_ENT.UF   , 
                EST_TRIB_SEM_DARE.CD_TRIB_FC , 
                EST_TRIB.CD_TRIB_FC      , 
                EST_TRIB.SUBS_TRIB, 
                EST_TRIB.SUBS_TRIB_ANT, 
                EST_TRIB.FLAG_OUTROS, 
                EST_TRIB_SEM_DARE.SUBS_TRIB, 
                EST_TRIB_SEM_DARE.SUBS_TRIB_ANT, 
                EST_TRIB_SEM_DARE.FLAG_OUTROS, 
                V_EST_NF_ENT.CD_PROD, 
                V_EST_NF_ENT.DS_PROD, 
                V_EST_NF_ENT.DS_MOV_OP, 
                V_EST_NF_ENT.NR_NCM , 
                V_EST_NF_ENT.LISTA_PROD, 
                V_EST_NF_ENT.TP_SUBS_TRIB, 
                V_EST_NF_ENT.SUBS_TRIB_FORN, 
                FLAG_N_GERAR_DAR, 
                FLAG_N_GERAR_DAR_IT, 
                GLB_CID.UF, 
                V_EST_NF_ENT.PERC_IVA, 
                EST_NF_ENT_DAR_IT.PERC_IVA, 
                V_EST_NF_ENT.DT_ENT, 
                EST_NF_ENT_DAR_IT.VLR_PMC, 
                V_EST_NF_ENT.GERA_MOV_ICMS_SUBS, 
                V_EST_NF_ENT.GERA_MOV_ICMS, 
                V_EST_NF_ENT.FLAG_MOV_OP_NFE_CPL,
				EST_NF_ENT_DAR_IT.PERC_FECOP_ST,
				V_EST_NF_ENT.PERC_FECOP,
				V_EST_NF_ENT.CD_NF,
				EST_TRIB_SEM_DARE.ICMS_RD,
				V_EST_NF_ENT.VLR_NF

