IF OBJECT_ID('V_SPED_EST_NF_SAI') IS NOT NULL
	BEGIN
			DROP VIEW V_SPED_EST_NF_SAI
	END
GO
 

SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW V_SPED_EST_NF_SAI

WITH ENCRYPTION

AS
 	
		SELECT
		 DBO.EST_NF_SAI.CD_EMP,
		 DBO.EST_NF_SAI.CD_FILIAL AS CD_FILIAL ,
		 DBO.EST_NF_SAI_IT.CD_NF AS CD_DOC ,
		 B.QTD_CST,
		 C.QTD_CFOP,
		 D.QTD_ALIQ_ICMS,
		 CASE WHEN (B.QTD_CST + C.QTD_CFOP > 2 ) OR D.QTD_ALIQ_ICMS > 1 THEN 1 
		 ELSE 0 END AS BUSCAR_C190 ,
		 1 AS IND_OPER ,
		 0 AS IND_EMIT ,
		 CASE
			 WHEN GLB_MOV_OP.DEST_ORIG = 0 THEN 'CLI'
			 WHEN  GLB_MOV_OP.DEST_ORIG = 1 THEN 'FOR'
			 ELSE 'FIL'
		 END AS TIPO_PART ,
		 EST_NF_SAI.CD_CLI AS CD_ORIG ,
		 '01' AS COD_MOD ,
		 EST_NF_SAI.STS_NF ,
		 DBO.EST_NF_SERIE.SERIE ,
		 DBO.EST_NF_SAI.NF_NF AS NR_DOC ,
		 ISNULL(EST_NF_SAI_NFE.NR_AUTORIZADOR, '') AS CHAVE ,
		 DBO.EST_NF_SAI_IT.DT_EMI_NF ,
		 DBO.EST_NF_SAI_IT.DT_EMI_NF AS DT_ENT_SAI ,
		 DBO.EST_NF_SAI.VLR_NF ,
		 DBO.EST_NF_SAI.CD_COND_PGTO AS IND_PAGTO ,
		 DBO.EST_NF_SAI.VLR_DESC ,
		 0 AS VLR_ABAT ,
		 CASE
			 WHEN EST_NF_SAI.FLAG_NF_PROD_TOTAL_BRUTO = 0 THEN EST_NF_SAI.VLR_TOT_PROD
			 ELSE EST_NF_SAI.VLR_TOT_PROD - EST_NF_SAI.VLR_DESC
		 END AS VLR_MERC ,
		 EST_NF_SAI.TP_FRETE ,
		 DBO.EST_NF_SAI.VLR_FRETE ,
		 ISNULL(EST_NF_SAI.VLR_OUTROS, 0) AS VLR_OUTRAS_DESP_CAB ,
		 COALESCE(CAB.VLR_BASE_ICMS_PROD, EST_NF_SAI.VLR_BASE_ICMS) AS VLR_BASE_ICMS_CAB ,
		 COALESCE(CAB.VLR_ICMS_PROD, EST_NF_SAI.VLR_ICMS) AS VLR_ICMS_CAB ,
		 COALESCE(CAB.VLR_BASE_SUBS_PROD, DBO.EST_NF_SAI.VLR_BASE_ICMS_SUBS) AS VLR_BASE_ICMS_SUBS_CAB ,
		 COALESCE(CAB.VLR_ICMS_SUBS_PROD, DBO.EST_NF_SAI.VLR_ICMS_SUBS) AS VLR_ICMS_SUBS_CAB ,
		 DBO.EST_NF_SAI.VLR_IPI AS VLR_IPI_CAB ,
		 DBO.EST_NF_SAI_IT.CD_IT AS NUM_ITEM ,
		 DBO.EST_NF_SAI_IT.CD_PROD AS COD_ITEM ,
		 DBO.EST_PROD.DS_PROD AS DS_PROD ,
		 DBO.EST_NF_SAI_IT.QT_IT AS QT_IT ,
		 DBO.EST_NF_SAI_IT.VLR_DESC_IT ,
		 DBO.EST_TRIB.CD_TRIB_FC AS CST_ICMS ,
		 DBO.EST_TRIB.ISENTO,
		 DBO.EST_TRIB.ICMS,
		 DBO.GLB_NT_OP.CD_NT_OP_FC AS CFOP ,
		 DBO.GLB_NT_OP.CD_NT_OP ,
		 DBO.EST_NF_SAI_IT.VLR_BASE_ICMS_IT AS VLR_BASE_ICMS,
		 DBO.EST_NF_SAI_IT.ALIQ_ICMS ,
		 DBO.EST_NF_SAI_IT.VLR_ICMS_IT ,
		 DBO.EST_NF_SAI_IT.VLR_BASE_SUBS ,
		 DBO.EST_NF_SAI_IT.VLR_ICMS_SUBS ,
		 ISNULL(EST_NF_SAI_IT.VLR_ICMS_RET, 0) AS VLR_ICMS_RET,
		 ISNULL(EST_NF_SAI_IT.VLR_PROD_FRETE, 0) AS VLR_PROD_FRETE,
		 ISNULL(EST_NF_SAI_IT.VLR_OUTROS_DESPESAS, 0) AS VLR_OUTRAS_DESPESAS,
		 ISNULL(EST_NF_SAI_IT.VLR_FECOP_ST, 0) AS VLR_FECOP_ST, 
		 ISNULL(EST_NF_SAI_IT.CST_PIS, 0) AS CST_PIS ,
		 ISNULL(EST_NF_SAI_IT.CST_COFINS, 0) AS CST_COFINS ,
		 ISNULL(DBO.EST_NF_SAI_IT.VLR_BASE_PIS, 0) AS VLR_BASE_PIS ,
		 ISNULL(EST_NF_SAI_IT.PERC_ALIQ_PIS, 0) AS PERC_ALIQ_PIS ,
		 ISNULL(DBO.EST_NF_SAI_IT.VLR_PIS, 0) AS VLR_PIS ,
		 ISNULL(DBO.EST_NF_SAI_IT.VLR_BASE_COFINS, 0) AS VLR_BASE_COFINS ,
		 ISNULL(EST_NF_SAI_IT.PERC_ALIQ_COFINS, 0) AS PERC_ALIQ_COFINS ,
		 ISNULL(DBO.EST_NF_SAI_IT.VLR_COFINS, 0) AS VLR_COFINS ,
		 ISNULL(
				  (SELECT SUM(EST_NF_SAI_IT_EST_PROD_CPL_LOTE.QTDE_PROD)QTDE_PROD
				   FROM EST_NF_SAI_IT_EST_PROD_CPL_LOTE, EST_PROD_CPL_LOTE
				   WHERE EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_NF_SAI_IT.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_NF = EST_NF_SAI_IT.CD_NF
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_PROD = EST_NF_SAI_IT.CD_PROD
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE), 0) AS QTDE_PROD ,
		 ISNULL(
				  (SELECT TOP (1) EST_PROD_CPL_LOTE.NR_LOTE
				   FROM EST_NF_SAI_IT_EST_PROD_CPL_LOTE, EST_PROD_CPL_LOTE
				   WHERE EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_NF_SAI_IT.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_NF = EST_NF_SAI_IT.CD_NF
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_PROD = EST_NF_SAI_IT.CD_PROD
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE
					 ORDER  BY EST_PROD_CPL_LOTE.CD_LOTE), 0) AS NR_LOTE ,
		 ISNULL(
				  (SELECT TOP (1) EST_PROD_CPL_LOTE.DT_VALID
				   FROM EST_NF_SAI_IT_EST_PROD_CPL_LOTE, EST_PROD_CPL_LOTE
				   WHERE EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_NF_SAI_IT.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_NF = EST_NF_SAI_IT.CD_NF
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_PROD = EST_NF_SAI_IT.CD_PROD
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE), 0) AS DT_VALID ,
		 ISNULL(
				  (SELECT TOP (1) EST_PROD_CPL_LOTE.DT_FABR
				   FROM EST_NF_SAI_IT_EST_PROD_CPL_LOTE, EST_PROD_CPL_LOTE
				   WHERE EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_NF_SAI_IT.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_NF = EST_NF_SAI_IT.CD_NF
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_PROD = EST_NF_SAI_IT.CD_PROD
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_EMP = EST_PROD_CPL_LOTE.CD_EMP
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
					 AND EST_NF_SAI_IT_EST_PROD_CPL_LOTE.CD_LOTE = EST_PROD_CPL_LOTE.CD_LOTE), 0) AS DT_FABR ,
		 (CASE
			  WHEN DS_SIGLA_LINHA = 'S' THEN 0
			  WHEN DS_SIGLA_LINHA = 'G' THEN 1
			  WHEN DS_SIGLA_LINHA = 'E' THEN 2
			  ELSE 0
		  END) AS TIPO_MEDIC ,
		 EST_PROD_PRECO.VLR_TABELA ,
		 EST_NF_SERIE.TP_SR_NFE ,
		 ISNULL(V_EST_NF_SAI_IMPOSTOS_PIS_COFINS.PIS_NF, 0) PIS_NF ,
		 ISNULL(V_EST_NF_SAI_IMPOSTOS_PIS_COFINS.COFINS_NF, 0) COFINS_NF ,
		 PRC_FILIAL.FLAG_OPERADOR_LOG ,
		 CASE
			 WHEN EST_PROD.CTR_PMC = 1 THEN 1
			 ELSE 0
		 END AS PROD_MEDICAM ,
		 CASE
			 WHEN ISNULL(DBO.EST_NF_SAI.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN DBO.EST_NF_SAI_IT.VLR_TOT_IT
			 ELSE DBO.EST_NF_SAI_IT.VLR_TOT_IT - EST_NF_SAI_IT.VLR_DESC_IT
		 END AS VLR_ITEM ,
		 CASE
			 WHEN GLB_MOV_OP.TP_MOV = 28 THEN 1
			 ELSE 0
		 END AS NOTA_CUPOM ,
		 ISNULL( EST_NF_SAI.CD_PED, 0) AS COD_PED ,
		 DBO.EST_NF_SAI_IT.PERC_IPI ,
		 CASE
			 WHEN ISNULL(EST_NF_SAI_IT.VLR_IPI_IT, 0) = 0 THEN CASE
																   WHEN ISNULL(DBO.EST_NF_SAI.FLAG_NF_PROD_TOTAL_BRUTO, 0) = 0 THEN ROUND(ISNULL((EST_NF_SAI_IT.VLR_TOT_IT + EST_NF_SAI_IT.VLR_OUTROS_DESPESAS) * EST_NF_SAI_IT.PERC_IPI, 0) / 100, 2)
																   ELSE ROUND(ISNULL((EST_NF_SAI_IT.VLR_TOT_IT - DBO.EST_NF_SAI_IT.VLR_DESC_IT + EST_NF_SAI_IT.VLR_OUTROS_DESPESAS) * EST_NF_SAI_IT.PERC_IPI, 0) / 100, 2)
															   END
			 ELSE EST_NF_SAI_IT.VLR_IPI_IT
		 END AS VLR_IPI_IT ,
		 ISNULL(EST_NF_SAI_CANCEL.CANCEL_FORA_PRAZO, 0) CANCEL_FORA_PRAZO ,
		 ISNULL(EST_NF_SAI_NFE_CANCEL.NR_PROTOCOLO, '') AS PROTOCOLO_CANCEL ,
		 GLB_MOV_OP.TP_MOV ,
		 CASE
			 WHEN EST_NF_SAI.STS_NF = 3
				  OR ISNULL(EST_NF_SAI_DENEGADA.CD_EMP, 0) = 1 THEN 1
			 ELSE 0
		 END AS NOTA_DENEGADA ,
		 ISNULL(EST_NF_SAI_DAR_IT.CD_EMP, 0) AS DIFAL ,
		 ISNULL(EST_NF_SAI_DAR_IT.VLR_BASE_ICMS, 0) VLR_BASE_ICMS_DIFAL ,
		 ISNULL(EST_NF_SAI_DAR_IT.PERC_ICMS_DESTINO, 0) ALIQ_ICMS_DIFAL ,
		 ISNULL(EST_NF_SAI_DAR_IT.VLR_ICMS, 0) VLR_ICMS_DIFAL ,
		 ISNULL(
				  (SELECT VLR_DAR_RECOLHER
				   FROM EST_NF_SAI_DAR
				   WHERE EST_NF_SAI.CD_EMP = EST_NF_SAI_DAR.CD_EMP
					AND EST_NF_SAI.CD_FILIAL = EST_NF_SAI_DAR.CD_FILIAL
					AND EST_NF_SAI.CD_NF = EST_NF_SAI_DAR.CD_NF
					AND EST_NF_SAI_DAR.UF_DESTINO = 1), 0) AS VLR_DAR_DEST ,
		 ISNULL(VLR_DAR_RECOLHER, 0) AS VLR_DAR_ORIG ,
		 V_GLB_PESSOA.CONTRIBUINTE,   
		 ISNULL(EST_NF_SAI.VLR_FECOP, 0) AS VLR_FECOP ,
		 ISNULL(EST_NF_SAI_EST_NF_NFE_INU.CD_EMP, 0) INUTILIZADO,
		 ISNULL(EST_NF_SAI_NFE.NR_AUTORIZADOR, '') CHAVE_NFE,
		 ISNULL(V_GLB_PESSOA.UF, '') AS UF,
		
	--	CASE WHEN   EST_NF_SAI.STS_NF = 3  OR ISNULL(EST_NF_SAI_DENEGADA.CD_EMP, 0) = 1 THEN 1 ELSE 0 END AS NOTA_DENEGADA,

		 EST_NF_SAI.CD_CLI                                                 AS COD_PART

		,ISNULL(V_GLB_PESSOA.RAZAO_SOCIAL_PESSOA, '')                                         AS NOME_PART
		,ISNULL(V_GLB_PESSOA.ENDERECO_PESSOA, '')                                             AS ENDERECO
		,ISNULL(V_GLB_PESSOA.BAIRRO_ENDERECO_PESSOA, '')                                      AS BAIRRO
		,ISNULL(V_GLB_PESSOA.NUMERO_ENDERECO_PESSOA, '')                                      AS NUMERO
		,ISNULL(V_GLB_PESSOA.INSC_EST, '') AS INSC_EST
		, ISNULL(V_GLB_PESSOA.CGC_CPF, '') AS CGC_CPF 
		, ISNULL(V_GLB_PESSOA.TELEFONE_PESSOA, '') AS TEL
		, GLB_CID.CD_PAIS
		
		 ,ISNULL(GLB_MUNICIPIO.COD_MUNICIPIO_SINTEGRA, 0)                                     AS COD_IBGE
		 ,ISNULL(V_GLB_PESSOA.CEP_ENDERECO_PESSOA, '')                          AS CEP                                                     
		 ,EST_PROD_CPL.TP_PROD
		 ,ISNULL((SELECT TOP (1)
							 CD_BARRA
						 FROM
							 EST_PROD_CD_BARRA
						 WHERE
							CD_EMP  = EST_NF_SAI_IT.CD_EMP AND
							CD_PROD =  EST_NF_SAI_IT.CD_PROD
						 ORDER  BY
							CD_BARRA), '')                                                AS COD_BARRA
		 ,EST_PROD.NR_NCM                                                                 AS COD_NCM
		 
		 ,DS_NT_OP
		 ,GLB_MOV_OP.CD_MOV_OP
		 ,ISNULL(GLB_MOV_OP.FLAG_NAO_GERAR_ARQ_SPED, 0)                       AS FLAG_NAO_GERAR_ARQ_SPED
 
		 ,ISNULL(EST_PROD.NR_CEST,0)											             AS NR_CEST
		 ,ISNULL(COD_MUNICIPIO_SINTEGRA, 0) AS COD_IBGE_MUNICIPIO_DESTINATARIO
         , ISNULL(EST_NF_SAI.END_DESTINATARIO, '') END_DESTINATARIO
		  , ISNULL(EST_NF_SAI.NR_END_DESTINATARIO, '') NR_END_DESTINATARIO
		  , ISNULL(EST_NF_SAI.PONTO_REF_DESTINATARIO, '') PONTO_REF_DESTINATARIO
		  , ISNULL(EST_NF_SAI.BAIRRO_DESTINATARIO, '') BAIRRO_DESTINATARIO
		  , ISNULL(EST_NF_SAI.CEP_DESTINATARIO, '') CEP_DESTINATARIO
		  , ISNULL(EST_NF_SAI.CIDADE_DESTINATARIO, '') CIDADE_DESTINATARIO
		  , ISNULL(EST_NF_SAI.CNPJ_DESTINATARIO, '') CNPJ_DESTINATARIO
		  , ISNULL(EST_NF_SAI.INSC_ESTADUAL_DESTINATARIO, '') INSC_ESTADUAL_DESTINATARIO
		  , ISNULL(EST_NF_SAI.RZ_DESTINATARIO, '') RZ_DESTINATARIO
		  , ISNULL(EST_NF_SAI.UF_DESTINATARIO, '') UF_DESTINATARIO
		  , ISNULL(EST_NF_SAI.CD_CID_DESTINATARIO, '') CD_CID_DESTINATARIO


		
		FROM DBO.EST_NF_SAI
			INNER JOIN DBO.EST_NF_SAI_IT ON DBO.EST_NF_SAI.CD_EMP = DBO.EST_NF_SAI_IT.CD_EMP
			AND DBO.EST_NF_SAI.CD_FILIAL = DBO.EST_NF_SAI_IT.CD_FILIAL
			AND DBO.EST_NF_SAI.CD_NF = DBO.EST_NF_SAI_IT.CD_NF
			AND DBO.EST_NF_SAI.DT_EMI_NF = DBO.EST_NF_SAI_IT.DT_EMI_NF

		   INNER JOIN EST_PROD_CPL ON
			EST_NF_SAI_IT.CD_EMP = EST_PROD_CPL.CD_EMP AND
			EST_NF_SAI_IT.CD_FILIAL = EST_PROD_CPL.CD_FILIAL AND
			EST_NF_SAI_IT.CD_PROD = EST_PROD_CPL.CD_PROD
			
			INNER JOIN DBO.EST_PROD ON DBO.EST_NF_SAI_IT.CD_EMP = DBO.EST_PROD.CD_EMP
			AND DBO.EST_NF_SAI_IT.CD_PROD = DBO.EST_PROD.CD_PROD
			
			LEFT JOIN DBO.EST_PROD_EST_ARV_MERCADOLOGICA AS ARV ON EST_PROD.CD_EMP = ARV.CD_EMP
			AND EST_PROD.CD_PROD = ARV.CD_PROD
			
			LEFT JOIN DBO.EST_ARV_MERC_LINHA AS L ON ARV.CD_EMP = L.CD_EMP
			    AND ARV.CD_ARV_MERC_LINHA = L.CD_ARV_MERC_LINHA
			
			INNER JOIN DBO.GLB_NT_OP ON DBO.EST_NF_SAI_IT.CD_NT_OP = DBO.GLB_NT_OP.CD_NT_OP
			INNER JOIN DBO.EST_TRIB ON DBO.EST_NF_SAI_IT.CD_TRIB = DBO.EST_TRIB.CD_TRIB
			INNER JOIN DBO.GLB_MOV_OP ON DBO.EST_NF_SAI.CD_NT_MOV = DBO.GLB_MOV_OP.CD_MOV_OP 
			
			OUTER APPLY UFD_GLB_PESSOA(EST_NF_SAI.CD_CLI, GLB_MOV_OP.DEST_ORIG) V_GLB_PESSOA
			INNER JOIN DBO.EST_NF_SERIE ON DBO.EST_NF_SAI.CD_EMP = DBO.EST_NF_SERIE.CD_EMP
			AND DBO.EST_NF_SAI.CD_NF_SERIE = DBO.EST_NF_SERIE.CD_NF_SERIE
			
			INNER JOIN PRC_FILIAL   ON DBO.EST_NF_SAI.CD_EMP = PRC_FILIAL.CD_EMP
			   AND DBO.EST_NF_SAI.CD_FILIAL = PRC_FILIAL.CD_FILIAL
			
			LEFT JOIN EST_PROD_PRECO ON EST_PROD_PRECO.CD_EMP = EST_NF_SAI_IT.CD_EMP
			    AND EST_PROD_PRECO.CD_FILIAL = EST_NF_SAI_IT.CD_FILIAL
			    AND EST_PROD_PRECO.CD_PROD = EST_NF_SAI_IT.CD_PROD

			LEFT JOIN EST_NF_SAI_NFE   ON DBO.EST_NF_SAI.CD_EMP = EST_NF_SAI_NFE.CD_EMP
								AND DBO.EST_NF_SAI.CD_FILIAL = EST_NF_SAI_NFE.CD_FILIAL
								AND DBO.EST_NF_SAI.CD_NF = EST_NF_SAI_NFE.CD_NF

			LEFT JOIN V_EST_NF_SAI_IMPOSTOS_PIS_COFINS ON
			V_EST_NF_SAI_IMPOSTOS_PIS_COFINS.CD_EMP =  EST_NF_SAI.CD_EMP AND
			V_EST_NF_SAI_IMPOSTOS_PIS_COFINS.CD_FILIAL =  EST_NF_SAI.CD_FILIAL AND
			V_EST_NF_SAI_IMPOSTOS_PIS_COFINS.CD_NF =  EST_NF_SAI.CD_NF

			LEFT JOIN EST_NF_SAI_CANCEL ON
			EST_NF_SAI_CANCEL.CD_EMP =  EST_NF_SAI.CD_EMP AND
			EST_NF_SAI_CANCEL.CD_FILIAL =  EST_NF_SAI.CD_FILIAL AND
			EST_NF_SAI_CANCEL.CD_NF =  EST_NF_SAI.CD_NF

		    LEFT JOIN EST_NF_SAI_NFE_CANCEL ON
			EST_NF_SAI_CANCEL.CD_EMP = EST_NF_SAI_NFE_CANCEL.CD_EMP AND
			EST_NF_SAI_CANCEL.CD_FILIAL = EST_NF_SAI_NFE_CANCEL.CD_FILIAL AND
			EST_NF_SAI_CANCEL.CD_NF = EST_NF_SAI_NFE_CANCEL.CD_NF

			LEFT JOIN EST_NF_SAI_DENEGADA ON
				 EST_NF_SAI.CD_EMP = EST_NF_SAI_DENEGADA.CD_EMP AND
				 EST_NF_SAI.CD_FILIAL = EST_NF_SAI_DENEGADA.CD_FILIAL AND
				 EST_NF_SAI.CD_NF = EST_NF_SAI_DENEGADA.CD_NF
			LEFT JOIN EST_NF_SAI_DAR ON
				 EST_NF_SAI.CD_EMP = EST_NF_SAI_DAR.CD_EMP AND
				 EST_NF_SAI.CD_FILIAL = EST_NF_SAI_DAR.CD_FILIAL AND
				 EST_NF_SAI.CD_NF = EST_NF_SAI_DAR.CD_NF AND
				EST_NF_SAI_DAR.UF_DESTINO = 1
			LEFT JOIN EST_NF_SAI_DAR_IT ON
				EST_NF_SAI_DAR.CD_EMP	 = EST_NF_SAI_DAR_IT.CD_EMP AND
				EST_NF_SAI_DAR.CD_FILIAL = EST_NF_SAI_DAR_IT.CD_FILIAL AND
				EST_NF_SAI_DAR.CD_NF	= EST_NF_SAI_DAR_IT.CD_NF AND
				EST_NF_SAI_DAR.CD_DAR	= EST_NF_SAI_DAR_IT.CD_DAR  
				AND	EST_PROD.CD_PROD	= EST_NF_SAI_DAR_IT.CD_PROD

		   LEFT JOIN EST_NF_SAI_EST_NF_NFE_INU ON
	   			 EST_NF_SAI.CD_EMP	 = EST_NF_SAI_EST_NF_NFE_INU.CD_EMP AND
				 EST_NF_SAI.CD_FILIAL = EST_NF_SAI_EST_NF_NFE_INU.CD_FILIAL AND
				 EST_NF_SAI.CD_NF	= EST_NF_SAI_EST_NF_NFE_INU.CD_NF 

		   INNER JOIN GLB_USU ON EST_NF_SAI.CD_USU = GLB_USU.CD_USU
					LEFT JOIN GLB_CID ON EST_NF_SAI.CD_CID_DESTINATARIO = GLB_CID.CD_CID
					LEFT OUTER JOIN (SELECT CD_CID, MAX(COD_MUNICIPIO_SINTEGRA) AS COD_MUNICIPIO_SINTEGRA
					FROM GLB_CID_GLB_MUNICIPIO
					LEFT OUTER JOIN GLB_MUNICIPIO ON GLB_CID_GLB_MUNICIPIO.CD_MUNICIPIO = GLB_MUNICIPIO.CD_MUNICIPIO
					GROUP BY CD_CID) GLB_MUNICIPIO ON GLB_CID.CD_CID = GLB_MUNICIPIO.CD_CID 

				 
			CROSS APPLY (SELECT COUNT(DISTINCT T.CD_TRIB_FC) AS QTD_CST
				 FROM EST_NF_SAI_IT IT2
				 INNER JOIN DBO.EST_TRIB T
						ON IT2.CD_TRIB = T.CD_TRIB
					WHERE  EST_NF_SAI.CD_EMP = IT2.CD_EMP
							AND EST_NF_SAI.CD_FILIAL = IT2.CD_FILIAL
							AND EST_NF_SAI.CD_NF = IT2.CD_NF
				) B
				
				CROSS APPLY (SELECT COUNT(DISTINCT T.CD_NT_OP) AS QTD_CFOP
				 FROM EST_NF_SAI_IT IT3
				 INNER JOIN DBO.GLB_NT_OP T
						ON IT3.CD_NT_OP = T.CD_NT_OP
					WHERE  EST_NF_SAI.CD_EMP = IT3.CD_EMP
							AND EST_NF_SAI.CD_FILIAL = IT3.CD_FILIAL
							AND EST_NF_SAI.CD_NF = IT3.CD_NF
				) C

				CROSS APPLY (SELECT COUNT(DISTINCT IT4.ALIQ_ICMS) AS QTD_ALIQ_ICMS
				 FROM EST_NF_SAI_IT IT4
					WHERE  EST_NF_SAI.CD_EMP = IT4.CD_EMP
							AND EST_NF_SAI.CD_FILIAL = IT4.CD_FILIAL
							AND EST_NF_SAI.CD_NF = IT4.CD_NF
							AND IT4.ALIQ_ICMS > 0 
				) D

			
 
			OUTER APPLY (SELECT SUM(CASE WHEN RIGHT( TRIB2.CD_TRIB_FC, 2) NOT IN ( '10','30','70') THEN NF_SAI2.VLR_BASE_ICMS_IT ELSE 0 END) AS VLR_BASE_ICMS_PROD,
					SUM(CASE WHEN RIGHT( TRIB2.CD_TRIB_FC, 2) NOT IN ( '10','30','70') THEN IT_2.VLR_ICMS_IT ELSE 0 END) AS VLR_ICMS_PROD,
					SUM(CASE WHEN RIGHT( TRIB2.CD_TRIB_FC, 2) NOT IN ( '10','30','70') THEN IT_2.VLR_BASE_SUBS ELSE 0 END) AS VLR_BASE_SUBS_PROD,
					SUM(CASE WHEN RIGHT( TRIB2.CD_TRIB_FC, 2) NOT IN ( '10','30','70') THEN IT_2.VLR_ICMS_SUBS ELSE 0 END) AS VLR_ICMS_SUBS_PROD
				
				FROM DBO.EST_NF_SAI NF_SAI2 

				INNER JOIN EST_NF_SAI_IT IT_2   
				        ON NF_SAI2.CD_EMP = IT_2.CD_EMP
						AND NF_SAI2.CD_FILIAL = IT_2.CD_FILIAL
						AND NF_SAI2.CD_NF =IT_2.CD_NF
						AND NF_SAI2.DT_EMI_NF = IT_2.DT_EMI_NF
						INNER JOIN EST_TRIB TRIB2 ON  TRIB2.CD_TRIB = IT_2.CD_TRIB

				WHERE EST_NF_SAI.CD_EMP = NF_SAI2.CD_EMP
				AND EST_NF_SAI.CD_FILIAL = NF_SAI2.CD_FILIAL
				AND EST_NF_SAI.CD_NF = NF_SAI2.CD_NF
				AND EST_NF_SAI.DT_EMI_NF = NF_SAI2.DT_EMI_NF
				            
				AND EXISTS (SELECT 1 FROM DBO.EST_NF_SAI NF_SAI3
							INNER JOIN EST_NF_SAI_IT IT_3  
									  ON DBO.EST_NF_SAI.CD_EMP = IT_3.CD_EMP
									AND DBO.EST_NF_SAI.CD_FILIAL = IT_3.CD_FILIAL
									AND DBO.EST_NF_SAI.CD_NF =IT_3.CD_NF
									AND DBO.EST_NF_SAI.DT_EMI_NF = IT_3.DT_EMI_NF
									INNER JOIN EST_TRIB TRIB3 ON  TRIB3.CD_TRIB = IT_3.CD_TRIB
								     INNER JOIN GLB_MOV_OP MOV2 ON MOV2.CD_MOV_OP = NF_SAI3.CD_NT_MOV

							WHERE EST_NF_SAI.CD_EMP = NF_SAI3.CD_EMP
							AND EST_NF_SAI.CD_FILIAL = NF_SAI3.CD_FILIAL
							AND EST_NF_SAI.CD_NF = NF_SAI3.CD_NF
							AND EST_NF_SAI.DT_EMI_NF = NF_SAI3.DT_EMI_NF
							AND RIGHT( TRIB3.CD_TRIB_FC, 2) IN ( '10','30','70')
							AND MOV2.TP_MOV = 11
							AND ISNULL((SELECT VALOR 
										FROM PRC_FILIAL_CONFIG PRC
										WHERE CD_CHAVE = 'ZERAR_ICMS_NORMAL_ST_DEV_COMPRAS_SPED_FISCAL'
										AND PRC.CD_FILIAL = EST_NF_SAI.CD_FILIAL),0) = 1
								 
					 )
			) CAB
 
  


 
  