IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_GERA_NFE]') AND type IN (N'P', N'PC'))
    DROP PROCEDURE [dbo].[P_GERA_NFE]
GO 

CREATE  PROCEDURE [dbo].[P_GERA_NFE] (@NR_LOTE BIGINT, @CONSIDERANOTASVALIDADA INT = 0)
AS
	
				   
			SET NOCOUNT ON;
			--CRIANDO TABELAS TEMPORARIAS  
			CREATE TABLE #NF_LOTE
				(
					 CD_NFE_LOTE    BIGINT,
					 TP_LOTE        INT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_EMP         INT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 TP_SAI_ENT     INT,
					 REG_CONTG      INT
				);
           	CREATE TABLE #NF_RESPTECNICO
             (
                    CD_EMP INTEGER,
                    CNPJ_RESPTEC VARCHAR(14),    
                    CONTATO_RESPTEC VARCHAR(200),
                    EMAIL_RESPTEC VARCHAR(200),   
                    FONE_RESPTEC VARCHAR(20)    
            );         
			CREATE TABLE #NF_00
				(
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 TP_SAI_ENT     VARCHAR(1)
				);
			CREATE TABLE #NF_01
				(
					 CD_NFE_LOTE      BIGINT,
					 CD_NFE_LOTE_IT   BIGINT,
					 CD_FILIAL        INT,
					 CD_NF            INT,
					 NF_NF            INT,
					 DS_MOV_OP        VARCHAR(100),
					 FORMA_PAG        VARCHAR(1),
					 MODELO           VARCHAR(2),
					 CD_NF_SERIE      INT,
					 SERIE            VARCHAR(10),
					 DT_EMI_NF        DATETIME,
					 DT_SAIDA         DATETIME,
					 TP_DOC_FISCAL    INT,
					 FOR_IMP          INT,
					 CD_IBGE          INT,
					 UF               VARCHAR(2),
					 NR_AUTORIZADOR   VARCHAR(100),
					 FIN_NFE          INT,
					 NF_REF           VARCHAR(MAX),
					 REG_CONTG        INT,
					 IND_PRES         INT,
					 IND_INTERMED     INT,	   
					 IDE_ECF_NMOD     VARCHAR(3),
					 IDE_ECF_NECF     INT,
					 IDE_ECF_NCOO     INT,
					 IDE_NF_1_MOD     VARCHAR(3),
					 IDE_NF_1_UF      INT,
					 IDE_NF_1_ANO_MES VARCHAR(4),
					 IDE_NF_1_CNPJ    VARCHAR(20),
					 IDE_NF_1_SERIE   INT,
					 IDE_NF_1_NR_NF   INT,
					 AUTXML           VARCHAR(20),
					 TIPO_TRANSACAO	  INT,
					 CD_EMP		    INT,
					 CD_CX            INT,
					 NR_ECF           INT

				);
			CREATE TABLE #DADOS_EMIT
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 RZ_FILIAL      VARCHAR(60),
					 CD_EMIT        INT,
					 NM_FANT        VARCHAR(60),
					 INSC_EST       VARCHAR(20),
					 INSC_MUNI      VARCHAR(20),
					 CNAE_FISCAL    VARCHAR(7),
					 CGC            VARCHAR(20),
					 INSC_SUBS      VARCHAR(20),
					 CRT            INT,
					 ENDE           VARCHAR(60),
					 NUMERO         VARCHAR(60),
					 COMPLEMENTO    VARCHAR(60),
					 BAIRRO         VARCHAR(60),
					 DS_CID         VARCHAR(60),
					 UF             VARCHAR(2),
					 CEP            VARCHAR(10),
					 CD_PAIS        INT,
					 NM_PAIS        VARCHAR(60),
					 TEL            VARCHAR(60),
					 CD_IBGE        INT
				);
			CREATE TABLE #DADOS_DEST
				(
					 CD_NFE_LOTE			BIGINT,
					 CD_NFE_LOTE_IT			BIGINT,
					 CD_FILIAL				INT,
					 CD_NF					INT,
					 NOME					VARCHAR(60),
					 INSC_EST				VARCHAR(20),
					 CGC_CPF				VARCHAR(20),
					 ENVIAR_XML_NFE			VARCHAR(1),
					 EMAIL					VARCHAR(60),
					 ENDE					VARCHAR(60),
					 NUMERO					VARCHAR(60),
					 COMPLEMENTO			VARCHAR(60),
					 BAIRRO					VARCHAR(60),
					 CD_IBGE				VARCHAR(7),
					 DS_CID					VARCHAR(60),
					 UF						VARCHAR(2),
					 CEP					VARCHAR(10),
					 CD_PAIS_SINTEGRA		VARCHAR(10),
					 DS_PAIS				VARCHAR(60),
					 DEST_FONE				VARCHAR(20),
					 CONTRIBUINTE			INT,
					 DEST_XNOME_ENTREGA		VARCHAR(100),
					 DEST_IE_ENTREGA		VARCHAR(20),
					 DEST_XLGR_ENTREGA		VARCHAR(100),
					 DEST_NRO_ENTREGA		VARCHAR(60),
					 DEST_XCPL_ENTREGA		VARCHAR(60),
					 DEST_XBAIRRO_ENTREGA	VARCHAR(60),
					 DEST_CMUN_ENTREGA		VARCHAR(60),
					 DEST_XMUN_ENTREGA		VARCHAR(60),
					 DEST_UF_ENTREGA		VARCHAR(2),
					 DEST_CEP_ENTREGA		VARCHAR(10),
					 DEST_FONE_ENTREGA		VARCHAR(60),
					 DEST_EMAIL_ENTREGA		VARCHAR(60),
					 DEST_CD_IBGE_ENTREGA	VARCHAR(7),
					 DEST_CPF_CNPJ_ENTREGA	VARCHAR(20)
					 
				);
			CREATE TABLE #DADOS_IMP_ITEM
				(
					 CD_NFE_LOTE                BIGINT,
					 CD_NFE_LOTE_IT             BIGINT,
					 CD_FILIAL                  INT,
					 CD_NF                      INT,
					 NITEM                      INT,
					 MOD_TRIB                   VARCHAR(10),
					 ORIGEM                     INT,
					 CD_PROD                    INT,
					 MOD_BC_ICMS                INT,
					 VLR_BASE_ICMS              MONEY,
					 VLR_ICMS                   MONEY,
					 ALIQUOTA                   MONEY,
					 VLR_CRED_BASE_SN           MONEY,
					 VLR_CRED_ICMS_SN           MONEY,
					 PERC_MARG                  MONEY,
					 PERC_REDU                  MONEY,
					 PERC_REDU_ST               MONEY,
					 VLR_BASE_SUBS              MONEY,
					 VLR_ICMS_SUBS              MONEY,
					 MOD_BC_ICMS_ST             INT,
					 ALIQ_ICMS_ST               MONEY,
					 VLR_BASE_ICMS_RET          MONEY,
					 VLR_ICMS_RET               MONEY,
					 VLR_ICMS_DESONERADO		MONEY,
                     ICMS_DESONERADO_MOTIVO     INT,
                     CBENEF                     VARCHAR(30),
					 CD_SIT_OPERA_SIMPLES_NACIO INT,
					 MODALI_BASE_ICMS_SUBST     INT,
					 MODALI_BASE_ICMS           INT,
					 PERC_ALIQ_COFINS           MONEY,
					 PERC_ALIQ_PIS              MONEY,
					 VLR_BASE_COFINS            MONEY,
					 VLR_BASE_PIS               MONEY,
					 VLR_PIS                    MONEY,
					 VLR_COFINS                 MONEY,
					 ENQUADRAMENTO              INT,
					 VLR_IPI                    MONEY,
					 VLR_BC_IPI                 MONEY,
					 ALIQ_IPI                   MONEY,
					 VLR_UND_TRIB               MONEY,
					 QTDE_TOT_UND               MONEY,
					 CST_IPI                    VARCHAR(10),
					 CST_PIS                    VARCHAR(10),
					 CST_COFINS                 VARCHAR(10),
					 PERC_ICMS_INTRA            MONEY,
					 PERC_ICMS_INTER            MONEY,
					 PERC_FECOP                 MONEY,
					 BASE_DIFAL                 MONEY,
					 ICMS_INTER_PART            MONEY,
					 VALOR_FECOP_DEST           MONEY,
					 VALOR_DIFAL_DEST           MONEY,
					 VALOR_DIFAL_ORIG           MONEY,
					 VLR_BASE_ICMS_OP	        MONEY,
					 VLR_ICMS_OP		        MONEY,
					 PERC_ICMS_OP		        MONEY,
					 PERC_DIFERIMENTO	        MONEY,
					 VLR_ICMS_DIFERIMENTO       MONEY,
					 PERC_FECOP_ST              MONEY,
					 VLR_BASE_FECOP_ST          MONEY,
					 VLR_FECOP_ST               MONEY, 
					 VLR_BASE_FECOP             MONEY,    
					 VLR_FECOP                  MONEY,      
					 VLR_BASE_FECOP_RET         MONEY,        
					 VLR_FECOP_RET              MONEY,       
					 PERC_FECOP_RET             MONEY,    
					 NR_ITEM_XPED               VARCHAR(50),  
					 ALIQUOTA_PST               MONEY, 
					 VLR_BC_UF_DEST             MONEY,
					 PERC_ICMS_RET		        MONEY,                     
                     VLR_IPI_DEVOL              MONEY,
                     PERC_IPI_DEVOL             MONEY,
                     VLR_ICMS_SUBSTITUTO        MONEY,
					 ALIQ_EFET_ICMS				MONEY,
					 PERC_EFET_RED_BASE_CALC	MONEY,
					 VLR_EFET_BASE_ICMS			MONEY,
					 VLR_EFET_ICMS				MONEY
					 );
			CREATE TABLE #DADOS_TRANSPORTE
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 MODA_FRETE     INT,
					 CGC_CPF        VARCHAR(20),
					 DS_TRANSP      VARCHAR(60),
					 INSC_EST       VARCHAR(20),
					 ENDE           VARCHAR(60),
					 DS_CID         VARCHAR(60),
					 UF             VARCHAR(2),
					 PLACA          VARCHAR(8),
					 SIGLA_UF       VARCHAR(2),
					 REG_NAC_TRANS  VARCHAR(20),
					 QTDE_VOL       MONEY,
					 ESP_VOL_TRANS  VARCHAR(60),
					 MARCA          VARCHAR(60),
					 NUM_VOL        BIGINT,
					 PESO_LIQ       MONEY,
					 PESO_BRUTO     MONEY,
					 EMAIL          VARCHAR(60)
				);
			CREATE TABLE #DADOS_DUP
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 NR_DP          VARCHAR(60),
					 DT_VENCTO      DATETIME,
					 VLR_DP         MONEY
				);
			CREATE TABLE #DADOS_IMP_NF
				(
					 CD_NFE_LOTE             BIGINT,
					 CD_NFE_LOTE_IT          BIGINT,
					 CD_FILIAL               INT,
					 CD_NF                   INT,
					 BASE_CAL_ICMS           MONEY,
					 VLR_ICMS                MONEY,
					 BASE_CAL_ICMS_ST        MONEY,
					 VLR_TOT_ICMS_ST         MONEY,
					 VLR_TOT_PROD            MONEY,
					 VLR_TOT_FRETE           MONEY,
					 VLR_TOT_SEG             MONEY,
					 VLR_DESC_CORPO          MONEY,
					 VLR_TOT_II              MONEY,
					 VLR_TOT_IPI             MONEY,
					 VLR_PIS                 MONEY,
					 VLR_COFINS              MONEY,
					 VLR_OUTRAS_DES          MONEY,
					 VLR_NF                  MONEY,
					 VLR_ICMS_DESONERADO     MONEY,
					 VLR_TOT_DIFAL_ORIG      MONEY,
					 VLR_TOT_DIFAL_DEST      MONEY,
					 VLR_TOT_FECOP_DEST      MONEY,
					 VLR_FECOP_ST            MONEY,               
					 VLR_FECOP               MONEY,
					 VLR_FECOP_RET           MONEY,
                     VLR_TOT_IPI_DEVOL       MONEY,
					 VLR_BASE_CALC_EFET		 MONEY,
					 VLR_EFET_ICMS			 MONEY
				);
			CREATE TABLE #DADOS_PROD
				(
					 CD_NFE_LOTE            BIGINT,
					 CD_NFE_LOTE_IT         BIGINT,
					 CD_FILIAL              INT,
					 CD_NF                  INT,
					 NR                     VARCHAR(8),
					 VTIPONOTAPROCESSAMENTO VARCHAR(1),
					 FILIAL                 INT,
					 VS                     VARCHAR(10),
					 DET_CPROD              INT,
					 DET_NITEM              INT,
					 DET_CEAN               VARCHAR(20),
					 DET_XPROD              VARCHAR(60),
					 DET_NCM                VARCHAR (8),
					 DET_CFOP               VARCHAR(10),
					 DET_UCOM               VARCHAR(6),
					 DET_QCOM               MONEY,
					 DET_VUNCOM             MONEY,
					 DET_VPROD              MONEY,
					 DET_CEANTRIB           VARCHAR(20),
					 DET_UTRIB              VARCHAR(10),
					 DET_QTRIB              MONEY,
					 DET_VUNTRIB            MONEY,
					 DET_VFRETE             MONEY,
					 DET_VSEG               MONEY,
					 DET_VDESC              MONEY,
					 DET_VOUTRO             MONEY,
					 DET_XPED               VARCHAR(100),
					 DET_NITEMPED           INT,
					 DET_INDTOT             INT,
					 NR_CEST                VARCHAR(20),
					 DET_INFADPROD			VARCHAR(500)	
				);
			CREATE TABLE #DADOS_LOTE_PROD
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 CD_PROD        INT,
					 ITEM           INT,
					 NR_LOTE        VARCHAR(20),
					 QTDE_PROD      MONEY,
					 DT_FABR        DATETIME,
					 DT_VALID       DATETIME,
					 VLR_TABELA     MONEY,                         
					 REGISTRO_MS    VARCHAR(25),
					 CD_DCB         VARCHAR(50),
					 CD_AGREGADOR   VARCHAR(100),
					 MOT_ISENCAO_ANVISA VARCHAR(250),
					 OBS_CONTRIBUINTE 	VARCHAR(60),
					 OBS_FISCO 			VARCHAR(60),
					 DET_MED		INT
									
							  
				);
			CREATE TABLE #DADOS_CONTIGENCIA
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 JUSTIFICATIVA  VARCHAR(MAX),
					 DHCONT         DATETIME
				);
			CREATE TABLE #DADOS_ADICIONAIS
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 OBS            VARCHAR(MAX)collate SQL_Latin1_General_CP1_CI_AS,
					 XPED           VARCHAR(100)
				);
			CREATE TABLE #DADOS_CANCELAMENTO
				(
					 CD_NFE_LOTE    BIGINT,
					 CD_NFE_LOTE_IT BIGINT,
					 CD_FILIAL      INT,
					 CD_NF          INT,
					 CD_MT_CANCEL   INT,
					 DS_MT_CANCEL   VARCHAR(MAX),
					 NR_AUTORIZADOR VARCHAR(MAX),
					 NR_PROTOCOLO   VARCHAR(MAX)
				);
			CREATE TABLE #DADOS_MANIFESTO
				(
					 NR_AUTORIZADOR VARCHAR(MAX),
					 CGC_CPF        VARCHAR(14),
					 DT_ENT         DATETIME,
					 TP_EVENT       INT
				);
			CREATE TABLE #DADOS_CARTA_CORRECAO
				(
					 CD_NFE_LOTE           BIGINT,
					 CD_NFE_LOTE_IT        BIGINT,
					 CD_FILIAL             INT,
					 CD_NF                 INT,
					 TP_SAI_ENT            INT,
					 NF_NF                 INT,
					 DH_EVENTO             VARCHAR(14),
					 NR_AUTORIZADOR        VARCHAR(MAX),
					 CGC_ORIGEM            VARCHAR(60),
					 DS_MOTIVO             VARCHAR(MAX),
					 NR_CARTA              INT,
					 CD_CTR_CARTA_CORRECAO INT,
					 CGC_DESTINO           VARCHAR(60),
					 CD_CLI                INT,
					 NM_DEST               VARCHAR(80)
				);
			CREATE TABLE #DADOS_INUTILIZACAO
				(
					 CD_INU       INT,
					 CGC          VARCHAR(20),
					 ANO          INT,
					 MODELO       VARCHAR(2),
					 CD_NF_SERIE  INT,
					 NR_NF        INT,
					 DS_MT_CANCEL VARCHAR(MAX)
				)
			CREATE TABLE #COD_UF
				(
					 UF    CHAR(2),
					 CD_UF INT
				);

	
			CREATE TABLE #DADOS_INTERMEDIADOR
				(
					CD_FILIAL					INT,
					CD_NF						INT,
					CD_NFE_LOTE					BIGINT,
					CD_NFE_LOTE_IT				BIGINT,
					CNPJ_INTERMEDIADOR			VARCHAR(20),
					NM_VENDEDOR_MARKETPLACE     VARCHAR(80)
				)
				
			CREATE TABLE #ATOS_CONCESSORIOS (
					CD_FILIAL					INT,
					CD_NF						INT,
					N_PROC						VARCHAR(60),
					ORIG_PROC					INT,
					TIPO_ATO_CONCESSORIO		INT
				);
				

			--CARREGANDO TABELA DE TRABALHO  
			PRINT 'INSERINDO #COD_UF';
			INSERT INTO #COD_UF
			SELECT UF,COD 
			FROM V_GLB_UF;
			--CRIANDO VARIAVEIS DE TRABALHO  
			DECLARE @CD_NFE_LOTE BIGINT;
			DECLARE @CD_NFE_LOTE_IT BIGINT;
			DECLARE @CD_FILIAL INT;
			DECLARE @CD_NF INT;
			DECLARE @TP_LOTE INT;
			DECLARE @TP_SAI_ENT INT;
			DECLARE @REG_CONTG INT;
			DECLARE @SERIE_CONTIGENCIA INT;
			DECLARE @UFDEST VARCHAR(2);
			DECLARE @FILIAL_OPTANTE_SIMPLES_NACIONAL INT;
			DECLARE @ACRES_LAB_SUBSTANCIA_DESC_PRODUTO_NFE TINYINT;  
				SET @ACRES_LAB_SUBSTANCIA_DESC_PRODUTO_NFE = ISNULL(( SELECT VALOR FROM PRC_EMP_CONFIG WHERE CD_EMP = 1 AND CD_CHAVE = 'ACRES_LAB_SUBSTANCIA_DESC_PRODUTO_NFE'),0)
   
				SET @SERIE_CONTIGENCIA = ( SELECT TOP 1 CD_NF_SERIE FROM EST_NF_SERIE WHERE CONTIGENCIA = 1);

					
							 
					 
								  
			IF ( @CONSIDERANOTASVALIDADA = 0 )
				BEGIN
						PRINT 'INSERINDO #NF_LOTE';
						INSERT INTO #NF_LOTE
						--SAIDAS  
						SELECT
							LOTE.CD_NFE_LOTE
							,LOTE.TP_LOTE
							,LOTE_SAI.CD_NFE_LOTE_SAI AS CD_NFE_LOTE_IT
							,LOTE_SAI.CD_EMP
							,LOTE_SAI.CD_FILIAL
							,LOTE_SAI.CD_NF
							,1                        AS TP_SAI_ENT
							,CASE WHEN ISNULL(LOTE.REG_CONTG, 0) = 1 THEN 6
							 ELSE 0
							 END                      AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_LOTE_SAI LOTE_SAI ON
								LOTE.CD_NFE_LOTE = LOTE_SAI.CD_NFE_LOTE
							LEFT OUTER JOIN EST_NF_SAI_NFE ON
								LOTE_SAI.CD_NFE_LOTE	 = EST_NF_SAI_NFE.CD_NFE_LOTE
								AND LOTE_SAI.CD_EMP      = EST_NF_SAI_NFE.CD_EMP
								AND LOTE_SAI.CD_FILIAL   = EST_NF_SAI_NFE.CD_FILIAL
								AND LOTE_SAI.CD_NF       = EST_NF_SAI_NFE.CD_NF
						WHERE
							LOTE.CD_NFE_LOTE = @NR_LOTE
						UNION ALL --ENTRADAS  
						SELECT
							LOTE.CD_NFE_LOTE
							,LOTE.TP_LOTE
							,LOTE_ENT.CD_NFE_LOTE_ENT AS CD_NFE_LOTE_IT
							,LOTE_ENT.CD_EMP
							,LOTE_ENT.CD_FILIAL
							,LOTE_ENT.CD_NF
							,0                        AS TP_SAI_ENT
							,CASE WHEN ISNULL(LOTE.REG_CONTG, 0) = 1 THEN 6
							 ELSE 0
							 END                      AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_LOTE_ENT LOTE_ENT ON
								LOTE.CD_NFE_LOTE = LOTE_ENT.CD_NFE_LOTE
							LEFT OUTER JOIN EST_NF_ENT_NFE ON
								LOTE_ENT.CD_NFE_LOTE	 = EST_NF_ENT_NFE.CD_NFE_LOTE
								AND LOTE_ENT.CD_EMP      = EST_NF_ENT_NFE.CD_EMP
								AND LOTE_ENT.CD_FILIAL   = EST_NF_ENT_NFE.CD_FILIAL
								AND LOTE_ENT.CD_NF       = EST_NF_ENT_NFE.CD_NF
						WHERE
							LOTE.CD_NFE_LOTE = @NR_LOTE
						UNION ALL --INUTILIZAO  
						SELECT
							LOTE.CD_NFE_LOTE
							,LOTE.TP_LOTE
							,0   AS CD_NFE_LOTE_IT
							,LOTE.CD_EMP
							,LOTE.CD_FILIAL
							,0   AS CD_NF
							,3   AS TP_SAI_ENT
							,CASE WHEN ISNULL(LOTE.REG_CONTG, 0) = 1 THEN 6
							 ELSE 0
							 END AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_LOTE_EST_NF_NFE_INU LOTE_INU ON
							LOTE.CD_NFE_LOTE = LOTE_INU.CD_NFE_LOTE
							INNER JOIN EST_NF_NFE_INU INU ON
							LOTE_INU.CD_INU = INU.CD_INU
						WHERE
							LOTE.CD_NFE_LOTE = @NR_LOTE
						UNION ALL --CARTA DE CORREO ENTRADA        
						SELECT
							LOTE.CD_NFE_LOTE
							,2             TP_LOTE
							,0             AS CD_NFE_LOTE_IT
							,CDC_ENT.CD_EMP
							,CDC_ENT.CD_FILIAL
							,CDC_ENT.CD_NF CD_NF
							,0             AS TP_SAI_ENT
							,0             AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NFE_LOTE LOTE_CDC ON
							LOTE.CD_NFE_LOTE = LOTE_CDC.CD_NFE_LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT CDC_ENT ON
							LOTE_CDC.CD_CTR_CARTA_CORRECAO = CDC_ENT.CD_CTR_CARTA_CORRECAO
						WHERE
							LOTE.CD_NFE_LOTE = @NR_LOTE
						UNION ALL --CARTA DE CORREO SAIDA        
						SELECT
							LOTE.CD_NFE_LOTE
							,2             TP_LOTE
							,0             AS CD_NFE_LOTE_IT
							,CDC_SAI.CD_EMP
							,CDC_SAI.CD_FILIAL
							,CDC_SAI.CD_NF CD_NF
							,1             AS TP_SAI_ENT
							,0             AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NFE_LOTE LOTE_CDC ON
								LOTE.CD_NFE_LOTE = LOTE_CDC.CD_NFE_LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI CDC_SAI ON
								LOTE_CDC.CD_CTR_CARTA_CORRECAO = CDC_SAI.CD_CTR_CARTA_CORRECAO
						WHERE
							LOTE_CDC.CD_NFE_LOTE = @NR_LOTE
						ORDER  BY
							TP_SAI_ENT
							,CD_NF
				END
			IF ( @CONSIDERANOTASVALIDADA = 1 )
				BEGIN
						PRINT 'INSERINDO #NF_LOTE';
						INSERT INTO #NF_LOTE
						--SAIDAS  
						SELECT
							LOTE.CD_NFE_LOTE
							,LOTE.TP_LOTE
							,LOTE_SAI.CD_NFE_LOTE_SAI AS CD_NFE_LOTE_IT
							,LOTE_SAI.CD_EMP
							,LOTE_SAI.CD_FILIAL
							,LOTE_SAI.CD_NF
							,1                        AS TP_SAI_ENT
							,CASE WHEN ISNULL(LOTE.REG_CONTG, 0) = 1 THEN 6
							 ELSE 0
							 END                      AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_LOTE_SAI LOTE_SAI ON
								LOTE.CD_NFE_LOTE = LOTE_SAI.CD_NFE_LOTE
							LEFT OUTER JOIN EST_NF_SAI_NFE ON
								LOTE_SAI.CD_NFE_LOTE = EST_NF_SAI_NFE.CD_NFE_LOTE
								AND LOTE_SAI.CD_EMP      = EST_NF_SAI_NFE.CD_EMP
								AND LOTE_SAI.CD_FILIAL   = EST_NF_SAI_NFE.CD_FILIAL
								AND LOTE_SAI.CD_NF       = EST_NF_SAI_NFE.CD_NF
						WHERE
									LOTE.CD_NFE_LOTE = @NR_LOTE
							AND EST_NF_SAI_NFE.CD_NFE_LOTE IS NULL
						UNION ALL --ENTRADAS  
						SELECT
							LOTE.CD_NFE_LOTE
							,LOTE.TP_LOTE
							,LOTE_ENT.CD_NFE_LOTE_ENT AS CD_NFE_LOTE_IT
							,LOTE_ENT.CD_EMP
							,LOTE_ENT.CD_FILIAL
							,LOTE_ENT.CD_NF
							,0                        AS TP_SAI_ENT
							,CASE WHEN ISNULL(LOTE.REG_CONTG, 0) = 1 THEN 6
							 ELSE 0
							 END                      AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_LOTE_ENT LOTE_ENT ON
								LOTE.CD_NFE_LOTE = LOTE_ENT.CD_NFE_LOTE
							LEFT OUTER JOIN EST_NF_ENT_NFE ON
										LOTE_ENT.CD_NFE_LOTE = EST_NF_ENT_NFE.CD_NFE_LOTE
								AND LOTE_ENT.CD_EMP      = EST_NF_ENT_NFE.CD_EMP
								AND LOTE_ENT.CD_FILIAL   = EST_NF_ENT_NFE.CD_FILIAL
								AND LOTE_ENT.CD_NF       = EST_NF_ENT_NFE.CD_NF
						WHERE
									LOTE.CD_NFE_LOTE = @NR_LOTE
							AND EST_NF_ENT_NFE.CD_NFE_LOTE IS NULL
						UNION ALL --INUTILIZAO  
						SELECT
							LOTE.CD_NFE_LOTE
							,LOTE.TP_LOTE
							,0   AS CD_NFE_LOTE_IT
							,LOTE.CD_EMP
							,LOTE.CD_FILIAL
							,0   AS CD_NF
							,3   AS TP_SAI_ENT
							,CASE WHEN ISNULL(LOTE.REG_CONTG, 0) = 1 THEN 6
							 ELSE 0
							 END AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_LOTE_EST_NF_NFE_INU LOTE_INU ON
								LOTE.CD_NFE_LOTE = LOTE_INU.CD_NFE_LOTE
							INNER JOIN EST_NF_NFE_INU INU ON
								LOTE_INU.CD_INU = INU.CD_INU
						WHERE
							LOTE.CD_NFE_LOTE = @NR_LOTE
							AND (NOT EXISTS (SELECT
												CD_INU
											 FROM
												EST_NF_NFE_INU_VALIDADA
											WHERE
												CD_INU = INU.CD_INU) 
								 )
						UNION ALL --CARTA DE CORREO ENTRADA        
						SELECT
							LOTE.CD_NFE_LOTE
							,2             TP_LOTE
							,0             AS CD_NFE_LOTE_IT
							,CDC_ENT.CD_EMP
							,CDC_ENT.CD_FILIAL
							,CDC_ENT.CD_NF CD_NF
							,0             AS TP_SAI_ENT
							,0             AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NFE_LOTE LOTE_CDC ON
								LOTE.CD_NFE_LOTE = LOTE_CDC.CD_NFE_LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT CDC_ENT ON
								LOTE_CDC.CD_CTR_CARTA_CORRECAO = CDC_ENT.CD_CTR_CARTA_CORRECAO
						WHERE
							LOTE_CDC.CD_NFE_LOTE = @NR_LOTE
						UNION ALL --CARTA DE CORREO SAIDA        
						SELECT
							LOTE.CD_NFE_LOTE
							,2             TP_LOTE
							,0             AS CD_NFE_LOTE_IT
							,CDC_SAI.CD_EMP
							,CDC_SAI.CD_FILIAL
							,CDC_SAI.CD_NF CD_NF
							,1             AS TP_SAI_ENT
							,0             AS REG_CONTG
						FROM
							EST_NFE_LOTE LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NFE_LOTE LOTE_CDC ON
								LOTE.CD_NFE_LOTE = LOTE_CDC.CD_NFE_LOTE
							INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI CDC_SAI ON
								LOTE_CDC.CD_CTR_CARTA_CORRECAO = CDC_SAI.CD_CTR_CARTA_CORRECAO
						WHERE
							LOTE_CDC.CD_NFE_LOTE = @NR_LOTE
						ORDER  BY
							TP_SAI_ENT
							,CD_NF
				END
			PRINT 'INSERINDO #NF_RESPTECNICO';
			INSERT INTO #NF_RESPTECNICO
			SELECT
				TOP 1
                 CD_EMP
                , CNPJ_RESPTEC
                ,CONTATO_RESPTEC
                ,EMAIL_RESPTEC  
                ,FONE_RESPTEC 
			FROM
				RC_RESP_TEC
			WHERE
			  CD_EMP  =1  ;                
            PRINT 'INSERINDO #NF_00 ' + CONVERT(VARCHAR,@NR_LOTE);
			INSERT INTO #NF_00
			SELECT
				LOTE.CD_FILIAL
				,LOTE.CD_NF
				,LOTE.CD_NFE_LOTE
				,LOTE.CD_NFE_LOTE_IT
				,( CASE WHEN LOTE.TP_SAI_ENT = 0 THEN 'E'
					 WHEN LOTE.TP_SAI_ENT = 1 THEN 'S'
					 ELSE 'I'
					 END ) AS TP_SAI_ENT
			FROM
				#NF_LOTE LOTE
			WHERE
				TP_LOTE NOT IN ( 2, 4, 5 ) -- N? FOR -> 2 CARTA CORRECAO, 4 MANIFESTO, 5 INUTILIZACAO  
			--ALIMENTANDO OS DADOS PARA GERAO DO RETORNO  
			SET NOCOUNT ON
			DECLARE NOTAS CURSOR FAST_FORWARD FOR
				SELECT
					CD_NFE_LOTE
					,CD_NFE_LOTE_IT
					,CD_FILIAL
					,CD_NF
					,TP_LOTE
					,TP_SAI_ENT
					,REG_CONTG
				FROM
					#NF_LOTE
			OPEN NOTAS
			FETCH NEXT FROM NOTAS INTO @CD_NFE_LOTE,
																 @CD_NFE_LOTE_IT,
																 @CD_FILIAL,
																 @CD_NF,
																 @TP_LOTE,
																 @TP_SAI_ENT,
																 @REG_CONTG
			WHILE @@FETCH_STATUS = 0
				BEGIN
						IF ( @TP_LOTE = 0 )
								OR ( @TP_LOTE = 3 ) --NOTAS DE ENTRADA E SAIDA, REPROCESSAMENTO          
							BEGIN
									IF ( @TP_SAI_ENT = 0 )
										BEGIN
												SET @UFDEST = (SELECT TOP 1
																				 UF
																			 FROM
																				 V_EST_NF_ENT_CAB
																			 WHERE
																						CD_EMP    = 1
																				AND CD_FILIAL = @CD_FILIAL
																				AND CD_NF     = @CD_NF);
												SET @FILIAL_OPTANTE_SIMPLES_NACIONAL = (SELECT TOP 1
																																	REGIME_TRIBUTARIO
																																FROM
																																	PRC_FILIAL
																																WHERE
																																		 CD_EMP    = 1
																																 AND CD_FILIAL = @CD_FILIAL
																																);
												
												
												
												IF (COALESCE(@FILIAL_OPTANTE_SIMPLES_NACIONAL,0) = 0)
													BEGIN
														SET @FILIAL_OPTANTE_SIMPLES_NACIONAL = (SELECT TOP 1 ISNULL(VALOR, 0) FROM PRC_FILIAL_CONFIG WHERE CD_EMP    = 1
																																			 AND CD_FILIAL = @CD_FILIAL
																																			 AND CD_CHAVE LIKE 'FILIAL_OPTANTE_SIMPLES_NACIONAL');
													END;
													
												PRINT 'INSERINDO #NF_01 ' + CONVERT(VARCHAR, @CD_NF);
												--UPDATE I
												--SET
												--	I.DT_ENT=E.DT_ENT
												--FROM 
												--	EST_NF_ENT E
												--	INNER JOIN EST_NF_ENT_IT I ON 
												--	E.CD_EMP = I.CD_EMP AND
												--	E.CD_FILIAL = I.CD_FILIAL AND
												--	E.CD_NF = I.CD_NF AND
												--	E.DT_ENT <> isnull(I.DT_ENT,'19000101')
												--WHERE
												--	I.CD_EMP=1 
												--	AND I.CD_FILIAL=@CD_FILIAL
												--	AND I.CD_NF=@CD_NF;


												INSERT INTO #NF_01
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,T1.NR_NF                                                AS NF_NF
													,DS_MOV_OP
													,CASE WHEN T1.TP_MOV = 25 or  T1.TP_MOV = 58 THEN '3'
													 ELSE '1'
													 END                                                     AS FORMA_PAG
													,55                                                      AS MODELO
													,CD_NF_SERIE
													,REPLACE(T1.SERIE, ' ', '')                              AS SERIE
													,( DT_EMI_NF + HR_EMI_NF )                               AS DT_EMI_NF
													,( DT_EMI_NF + HR_EMI_NF )                               AS DT_SAIDA
													,0                                                       AS TP_DOC_FISCAL
													,2                                                       AS FOR_IMP
													,ISNULL(T4.COD_MUNICIPIO_SINTEGRA, 0)                    AS CD_IBGE
													,(SELECT
															CD_UF
														FROM
															#COD_UF
														WHERE
														 UF = GLB_CID.UF COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS) AS UF
													,CONTG.NR_AUTORIZADOR
													,( CASE WHEN T1.FLAG_MOV_OP_NFE_CPL = 1 THEN --Nota Complementar
														 2
														 WHEN T1.TP_MOV IN ( 25, 52,58 ) THEN 3
														 WHEN T1.TP_MOV IN ( 11, 15, 17, 18,19, 20, 31, 41, 57, 59,60) THEN 4
														 ELSE 1
														 END )                                                 AS FIN_NFE
													,ISNULL(REF_NF_DEV.NR_AUTORIZADOR, '')                   AS NF_REF
													,( CASE WHEN @REG_CONTG = 0 THEN 1
														 ELSE 3
														 END )                                                 AS REG_CONTG
				   
													,(CASE
														WHEN T1.TP_MOV IN (11,15,16,24,25,52,58,60) THEN 0
														WHEN T1.TP_MOV IN (0,1,3,28) THEN 1
														WHEN T1.TP_MOV IN (65) THEN 2
														ELSE 9
													  END) 													AS IND_PRES	,
													  0 AS IND_INTERMED,
													  
													  CASE WHEN ISNULL(REF_NF_DEV.NR_AUTORIZADOR, '') = '' THEN
														ISNULL(REF_NF_DEV.MODELO, '')
													ELSE 
														''
													END AS IDE_ECF_NMOD
													,CASE WHEN ISNULL(REF_NF_DEV.NR_AUTORIZADOR, '') = '' THEN
														ISNULL(REF_NF_DEV.CD_CX, '')
													ELSE 
														''
													END AS IDE_ECF_NECF
													,CASE WHEN ISNULL(REF_NF_DEV.NR_AUTORIZADOR, '') = '' THEN
														ISNULL(RIGHT(REF_NF_DEV.NR_ECF,6), '')
													ELSE
														''
													END AS IDE_ECF_NCOO
													,0                                                       AS IDE_NF_1_MOD
													,0                                                       AS IDE_NF_1_UF
													,''                                                      AS IDE_NF_1_ANO_MES
													,''                                                      AS IDE_NF_1_CNPJ
													,0                                                       AS IDE_NF_1_SERIE
													,0                                                       AS IDE_NF_1_NR_NF
													,ISNULL(PRC_FILIAL.CGC_CPF_RESP, '')                     AS AUTXML
													,0
													,REF_NF_DEV.CD_EMP								  AS CD_EMP
													,REF_NF_DEV.CD_CX								  AS CD_CX
													,REF_NF_DEV.NR_ECF								  AS NR_ECF
												FROM
													V_EST_NF_ENT T1
													LEFT JOIN EST_NF_ENT_NFE_CONTG CONTG ON
																T1.CD_EMP    = CONTG.CD_EMP
														AND T1.CD_FILIAL = CONTG.CD_FILIAL
														AND T1.CD_NF     = CONTG.CD_NF
													INNER JOIN PRC_FILIAL ON
																T1.CD_EMP    = PRC_FILIAL.CD_EMP
														AND T1.CD_FILIAL = PRC_FILIAL.CD_FILIAL
													INNER JOIN GLB_CID ON
														PRC_FILIAL.CD_CID = GLB_CID.CD_CID
													INNER JOIN GLB_NT_OP T2 ON
														T1.CD_NT_OP = T2.CD_NT_OP
													LEFT JOIN GLB_CID_GLB_MUNICIPIO T3 ON
														PRC_FILIAL.CD_CID = T3.CD_CID
													LEFT JOIN GLB_MUNICIPIO T4 ON
														T3.CD_MUNICIPIO = T4.CD_MUNICIPIO
													LEFT JOIN EST_NF_ENT_DEVOLUCAO_NFE_CHAVE_REFERENCIA REF_NF_DEV ON
																T1.CD_EMP    = REF_NF_DEV.CD_EMP
														AND T1.CD_FILIAL = REF_NF_DEV.CD_FILIAL
														AND T1.CD_NF     = REF_NF_DEV.CD_NF
												WHERE
															T1.CD_NF     = @CD_NF
													AND T1.CD_EMP    = 1
													AND T1.CD_FILIAL = @CD_FILIAL;
												--DADOS EMITENTE          
												IF ( @UFDEST = '' )
													BEGIN
															PRINT 'INSERINDO #DADOS_EMIT';
															INSERT INTO #DADOS_EMIT
															SELECT DISTINCT
																@CD_NFE_LOTE
																,@CD_NFE_LOTE_IT
																,@CD_FILIAL
																,@CD_NF
																,T2.RZ_FILIAL
																,T2.CD_FILIAL                                                 AS CD_EMIT
																,T2.NM_FANT                                                   AS NM_FANT
																,( CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) = '' THEN 'ISENTO'
																	 ELSE
																		 CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) <> 'ISENTO' THEN
																			 CASE WHEN RTRIM(LTRIM(DBO.F_LIMPA_NUMEROS(T2.INSC_EST))) = '' THEN 'ISENTO'
																			 ELSE DBO.F_LIMPA_NUMEROS(T2.INSC_EST)
																			 END
																		 ELSE RTRIM(LTRIM(ISNULL(T2.INSC_EST, '')))
																		 END
																	 END )                                                      AS INSC_EST
																,ISNULL(T2.INSC_MUNI, 'ISENTO')                               AS INSC_MUNI
																,'0000000'                                                    AS CNAE_FISCAL
																,LTRIM(RTRIM(CGC))                                            AS CGC
																,( CASE WHEN ISNULL(VLR_BASE_ICMS_SUBS, 0) > 0 THEN ( CASE WHEN ( T1.TP_MOV_OP_TRANSA = 1 )
																																											AND ( T1.TP_MOV = 11 ) THEN '0'
																																								 ELSE ( CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) = '' THEN ''
																																												ELSE
																																													CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) <> 'ISENTO' THEN
																																														CASE WHEN RTRIM(LTRIM(DBO.F_LIMPA_NUMEROS(T2.INSC_EST))) = '' THEN ''
																																														ELSE DBO.F_LIMPA_NUMEROS(T2.INSC_EST)
																																														END
																																													ELSE ''
																																													END
																																												END )
																																								 END )
																	 ELSE '0'
																	 END )                                                      INSC_SUBS
																,@FILIAL_OPTANTE_SIMPLES_NACIONAL                             AS CRT
																,T2.END_FILILAL                                               AS ENDE
																,isnull(T1.NUMERO,'')                                         AS NUMERO
																,''                                                           AS COMPLEMENTO
																,T2.BAIRRO                                                    AS BAIRRO
																,T4.DS_CID                                                    AS DS_CID
																,T4.UF                                                        AS UF
																,REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', '') AS CEP
																,1058                                                         AS CD_PAIS
																,'BRASIL'                                                     AS NM_PAIS
																,T2.TEL                                                       AS TEL
																,ISNULL(T6.COD_MUNICIPIO_SINTEGRA, 0)                         AS CD_IBGE
															FROM
																V_EST_NF_ENT T1
																INNER JOIN PRC_FILIAL T2 ON
																			T1.CD_FILIAL = T2.CD_FILIAL
																	AND T1.CD_EMP    = T2.CD_EMP
																LEFT JOIN GLB_CID T4 ON
																	T2.CD_CID = T4.CD_CID
																LEFT JOIN GLB_CID_GLB_MUNICIPIO T5 ON
																	T4.CD_CID = T5.CD_CID
																LEFT JOIN GLB_MUNICIPIO T6 ON
																	T5.CD_MUNICIPIO = T6.CD_MUNICIPIO
															WHERE
																		CD_NF        = @CD_NF
																AND T1.CD_EMP    = 1
																AND T1.CD_FILIAL = @CD_FILIAL
													END
												IF ( @UFDEST <> '' )
													BEGIN
															PRINT 'INSERINDO #DADOS_EMIT';
															INSERT INTO #DADOS_EMIT
															SELECT DISTINCT
																@CD_NFE_LOTE
																,@CD_NFE_LOTE_IT
																,@CD_FILIAL
																,@CD_NF
																,LEFT(T2.RZ_FILIAL, 60)
																,T2.CD_FILIAL                                                                   AS CD_EMIT
																,LEFT(T2.NM_FANT, 60)                                                           AS NM_FANT
																,( CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) = '' THEN 'ISENTO'
																	 ELSE
																		 CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) <> 'ISENTO' THEN
																			 CASE WHEN RTRIM(LTRIM(DBO.F_LIMPA_NUMEROS(T2.INSC_EST))) = '' THEN 'ISENTO'
																			 ELSE DBO.F_LIMPA_NUMEROS(T2.INSC_EST)
																			 END
																		 ELSE RTRIM(LTRIM(ISNULL(T2.INSC_EST, '')))
																		 END
																	 END )                                                                        AS INSC_EST
																,REPLACE(REPLACE(REPLACE(ISNULL(T2.INSC_MUNI, ''), '/', ''), '-', ''), '.', '') AS INSC_MUNI
																,'0000000'                                                                      AS CNAE_FISCAL
																,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CGC)), '/', ''), '-', ''), '.', '')        AS CGC
																,( CASE WHEN ISNULL(VLR_BASE_ICMS_SUBS, 0) > 0 THEN ( CASE WHEN ( T1.TP_MOV_OP_TRANSA = 1 )
																																											AND ( T1.TP_MOV = 11 ) THEN ''
																																								 ELSE ( CASE WHEN RTRIM(LTRIM(ISNULL(T3.INSC_EST_ST, ''))) = '' THEN ''
																																												ELSE
																																													CASE WHEN RTRIM(LTRIM(ISNULL(T3.INSC_EST_ST, ''))) <> 'ISENTO' THEN
																																														CASE WHEN RTRIM(LTRIM(DBO.F_LIMPA_NUMEROS(ISNULL(T3.INSC_EST_ST, '')))) = '' THEN ''
																																														ELSE DBO.F_LIMPA_NUMEROS(ISNULL(T3.INSC_EST_ST, ''))
																																														END
																																													ELSE 'ISENTO'
																																													END
																																												END )
																																								 END )
																	 ELSE ''
																	 END )                                                                        INSC_SUBS
																,@FILIAL_OPTANTE_SIMPLES_NACIONAL
																,T2.END_FILILAL                                                                 AS ENDE
																,isnull(T1.NUMERO,'')                                                                              AS NUMERO
																,''                                                                             AS COMPLEMENTO
																,T2.BAIRRO                                                                      AS BAIRRO
																,T4.DS_CID                                                                      AS DS_CID
																,T4.UF                                                                          AS UF
																,REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', '')                   AS CEP
																,1058                                                                           AS CD_PAIS
																,'BRASIL'                                                                       AS NM_PAIS
																,T2.TEL                                                                         AS TEL
																,ISNULL(T6.COD_MUNICIPIO_SINTEGRA, 0)                                           AS CD_IBGE
															FROM
																V_EST_NF_ENT T1
																INNER JOIN PRC_FILIAL T2 ON
																			T1.CD_FILIAL = T2.CD_FILIAL
																	AND T1.CD_EMP    = T2.CD_EMP
																LEFT JOIN PRC_FILIAL_INSC_EST_ST T3 ON
																			T1.CD_FILIAL = T3.CD_FILIAL
																	AND T1.CD_EMP    = T3.CD_EMP
																	AND T3.UF        = @UFDEST
																LEFT JOIN GLB_CID T4 ON
																	T2.CD_CID = T4.CD_CID
																LEFT JOIN GLB_CID_GLB_MUNICIPIO T5 ON
																	T4.CD_CID = T5.CD_CID
																LEFT JOIN GLB_MUNICIPIO T6 ON
																	T5.CD_MUNICIPIO = T6.CD_MUNICIPIO
															WHERE
																		T1.CD_NF     = @CD_NF
																AND T1.CD_EMP    = 1
																AND T1.CD_FILIAL = @CD_FILIAL
													END
												--DADOS DESTINATARIO  
												PRINT 'INSERINDO #DADOS_DEST';
												INSERT INTO #DADOS_DEST
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,LEFT(ENT.NM_ORIG, 60)	                                                         AS NOME
													,ENT.INSC_EST																	 AS INSC_EST
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(ENT.CGC_CPF)), '/', ''), '-', ''), '.', '') AS CGC_CPF
													,ISNULL(ENT.ENVIAR_XML_NFE, 0)                                                   AS ENVIAR_XML_NFE
													,LEFT(ENT.EMAIL, 60)                                                             AS EMAIL
													,LEFT(ISNULL(ENDE, ''), 60)                                                      AS ENDE
													,LEFT(ENT.NUMERO, 60)															 AS NUMERO
													,''                                                                              AS COMPLEMENTO
													,LEFT(ENT.BAIRRO, 60)															 AS BAIRRO
													,ISNULL(T4.COD_MUNICIPIO_SINTEGRA, 0)                                            AS CD_IBGE
													,LEFT(ENT.DS_CID, 60)															 AS DS_CID
													,ENT.UF																			 AS UF
													,CASE WHEN OP.DEST_ORIG = 0 THEN
														 CASE WHEN ( COALESCE(REPLACE(REPLACE(REPLACE(CLI.CEP, '/', ''), '-', ''), '.', ''), '') = '' ) THEN COALESCE(REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', ''), '')
														 ELSE COALESCE(REPLACE(REPLACE(REPLACE(CLI.CEP, '/', ''), '-', ''), '.', ''), '')
														 END
													 WHEN OP.DEST_ORIG = 1 THEN
														 CASE WHEN ( COALESCE(REPLACE(REPLACE(REPLACE(FRN.CEP, '/', ''), '-', ''), '.', ''), '') = '' ) THEN COALESCE(REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', ''), '')
														 ELSE COALESCE(REPLACE(REPLACE(REPLACE(FRN.CEP, '/', ''), '-', ''), '.', ''), '')
														 END
													 WHEN OP.DEST_ORIG = 2 THEN
														 CASE WHEN ( COALESCE(REPLACE(REPLACE(REPLACE(FL.CEP, '/', ''), '-', ''), '.', ''), '') = '' ) THEN COALESCE(REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', ''), '')
														 ELSE COALESCE(REPLACE(REPLACE(REPLACE(FL.CEP, '/', ''), '-', ''), '.', ''), '')
														 END
													 END AS CEP
													,T5.CD_PAIS_SINTEGRA
													,LEFT(T5.DS_PAIS, 60)
													,'' AS DADOS_FONE
													,ENT.CONTRIBUINTE
													,'' AS DEST_XNOME_ENTREGA		
													,'' AS DEST_IE_ENTREGA		
													,'' AS DEST_XLGR_ENTREGA		
													,'' AS DEST_NRO_ENTREGA		
													,'' AS DEST_XCPL_ENTREGA		
													,'' AS DEST_XBAIRRO_ENTREGA	
													,'' AS DEST_CMUN_ENTREGA		
													,'' AS DEST_XMUN_ENTREGA		
													,'' AS DEST_UF_ENTREGA		
													,'' AS DEST_CEP_ENTREGA		
													,'' AS DEST_FONE_ENTREGA		
													,'' AS DEST_EMAIL_ENTREGA
													,'' AS DEST_CD_IBGE_ENTREGA
													,'' AS DEST_CPF_CNPJ_ENTREGA
													
												FROM
													V_EST_NF_ENT ENT   
													INNER JOIN GLB_MOV_OP OP ON
														OP.CD_MOV_OP = ENT.CD_MOV_OP
													LEFT JOIN GLB_CID T2 ON
														ENT.CD_CID = T2.CD_CID
													LEFT JOIN GLB_CID_GLB_MUNICIPIO T3 ON
														T2.CD_CID = T3.CD_CID
													LEFT JOIN GLB_MUNICIPIO T4 ON
														T3.CD_MUNICIPIO = T4.CD_MUNICIPIO
													LEFT JOIN GLB_PAIS T5 ON
														T2.CD_PAIS = T5.CD_PAIS
													LEFT JOIN RC_CLI CLI ON
																CLI.CD_EMP = ENT.CD_EMP
														AND CLI.CD_CLI = ENT.CD_FORN
													LEFT JOIN PG_FORN FRN ON
																FRN.CD_EMP  = ENT.CD_EMP
														AND FRN.CD_FORN = ENT.CD_FORN
													LEFT JOIN V_PRC_FILIAL FL ON
																FL.CD_EMP    = ENT.CD_EMP
														AND FL.CD_FILIAL = ENT.CD_ORIG
												WHERE
															ENT.CD_NF     = @CD_NF
													AND ENT.CD_EMP    = 1
													AND ENT.CD_FILIAL = @CD_FILIAL;
												--DADOS PRODUTOS  
												PRINT 'INSERINDO #DADOS_PROD';
												INSERT INTO #DADOS_PROD
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,RIGHT('00000000' + CAST(@CD_NF AS VARCHAR(8)), 8)
													,@TP_SAI_ENT
													,@CD_FILIAL
													,'3.0'
													,T1.CD_PROD                                                       AS CD_PROD
													,T1.SEQ_ITEM
													,LEFT(ISNULL((SELECT TOP 1
																					CD_BARRA
																				FROM
																					EST_PROD_CD_BARRA
																				WHERE
																						 EST_PROD_CD_BARRA.CD_EMP                        = T1.CD_EMP
																				 AND EST_PROD_CD_BARRA.CD_PROD                       = T1.CD_PROD
																				 AND EST_PROD_CD_BARRA.NAO_EXPORTAR                  = 0
																				 AND EST_PROD_CD_BARRA.EAN_CAIXA_FECHADA = 0 -- EAN VALIDO
																				 AND EST_PROD_CD_BARRA.FLAG_PRE_VENCIDOS = 0 
																				 AND EST_PROD_CD_BARRA.FLAG_NAO_ENVIA_NFE = 0
																				ORDER  BY
																				 GTIN DESC, CD_BARRA), 'SEM GTIN'), 20)                                AS CD_BARRA
													,RTRIM(LTRIM(LEFT(DBO.F_RETIRA_CARAC_ESPECIAIS(T1.DS_PROD), 60))) AS DS_PROD
													,CASE WHEN T1.TP_MOV = 25 or T1.TP_MOV = 58 THEN LEFT(RIGHT('00000000'
																															 + RTRIM(LTRIM(ISNULL(T2.NR_NCM, 0))), 8), 8)
													 ELSE RIGHT('00000000'
																			+ RTRIM(LTRIM(ISNULL(T2.NR_NCM, 0))), 8)
													 END                                                              AS CD_NCM
													,LEFT(CFOP, 10)                                                   AS CD_FISCAL
													,LEFT(UN, 6)
													,QT_IT
													,--- 4 CASAS DECIMAIS  
													VLR_IT
													,--- 4 CASAS DECIMAIS  
													( CASE WHEN FLAG_NF_PROD_TOTAL_BRUTO = 0 THEN VLR_TOT_IT
														 ELSE VLR_TOT_IT + VLR_DESC_IT
														 END )                                                          AS VLR_TOT_IT
													,LEFT(ISNULL((SELECT TOP 1
																					CD_BARRA
																				FROM
																					EST_PROD_CD_BARRA
																				WHERE
																						 EST_PROD_CD_BARRA.CD_EMP                        = T1.CD_EMP
																				 AND EST_PROD_CD_BARRA.CD_PROD                       = T1.CD_PROD
																				 AND EST_PROD_CD_BARRA.NAO_EXPORTAR                  = 0
																				 AND EST_PROD_CD_BARRA.EAN_CAIXA_FECHADA=0 -- EAN VALIDO
																				 AND EST_PROD_CD_BARRA.FLAG_PRE_VENCIDOS = 0 
																				 AND EST_PROD_CD_BARRA.FLAG_NAO_ENVIA_NFE = 0
																				ORDER  BY
																				 GTIN DESC, CD_BARRA), 'SEM GTIN'), 20)                                AS EAN_TRIB
													,LEFT(UN, 10)                                                     AS UND_TRIB
													,QT_IT                                                            AS QT_TRIB
													,( CASE WHEN QT_IT = 0 THEN 0
														ELSE ( CONVERT(float,( CASE WHEN FLAG_NF_PROD_TOTAL_BRUTO = 0 THEN VLR_TOT_IT
														ELSE VLR_TOT_IT + VLR_DESC_IT
														END )) / QT_IT )
														END ) AS VLR_UNIT_TRIB
													,---4 CASAS DECIMAIS  
													VLR_PROD_FRETE                                                         AS VLR_TOT_FRETE
													,---- 4 CASAS DECIMAIS  
													0                                                                 AS VLR_TOT_SEGURO
													,VLR_DESC_IT
													,VLR_OUTRAS_DESPESAS                                              AS VLR_OUTRAS_DESPESAS
													,''                                                               AS XPED
													,0                                                                AS NITEMPED
													,
													--Valor do item compoe total nf ?
													CASE WHEN T1.TP_MOV = 52 THEN 0 --nf adjudicacao
													 ELSE 1
													 END                                                              AS DET_INDTOT
													,ISNULL(T1.NR_CEST,'') AS NR_CEST
																	   
										   
													,CASE WHEN T2.CTR_PMC = 1 AND @ACRES_LAB_SUBSTANCIA_DESC_PRODUTO_NFE = 1  THEN 'LAB : ' + ISNULL(EST_PROD_FABRIC.NM_FABRIC, '')  + ' - LISTA SUBSTANCIA: ' + ISNULL(EST_PROD_EST_SNGPC.CLASSIFICACAO, '') 
														  ELSE ''
																																								   
				 
				 
				
													  END  															  AS DET_INFADPROD
												FROM
													V_EST_NF_ENT T1
													INNER JOIN EST_PROD T2 ON T1.CD_EMP  = T2.CD_EMP
																		  AND T1.CD_PROD = T2.CD_PROD
													LEFT JOIN EST_PROD_FABRIC ON T2.CD_FABRIC = EST_PROD_FABRIC.CD_FABRIC
													LEFT JOIN EST_PROD_EST_SNGPC ON EST_PROD_EST_SNGPC.CD_EMP  = T2.CD_EMP
																				AND EST_PROD_EST_SNGPC.CD_PROD = T2.CD_PROD
												WHERE
															CD_NF        = @CD_NF
													AND T1.CD_EMP    = 1
													AND T1.CD_FILIAL = @CD_FILIAL
												ORDER  BY
													T1.SEQ_ITEM
												--DADOS DE TRANSPORTE  
												PRINT 'INSERINDO #DADOS_TRANPORTE';
												INSERT INTO #DADOS_TRANSPORTE
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,TP_FRETE                                                                           AS MODA_FRETE
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CGC_CPF_TRANSP)), '/', ''), '-', ''), '.', '') AS CGC_CPF
													,LTRIM(LEFT(DS_TRANSP, 60))                                                         AS DS_TRANSP
													,REPLACE(REPLACE(REPLACE(INSC_EST_TRANSP, '/', ''), '-', ''), '.', '')              AS INSC_EST
													,LTRIM(LEFT(ENDE_TRANSP, 60))                                                       AS ENDE
													,REPLACE(LTRIM(LEFT(DS_CID_TRANSP, 60)), 'NAO INFORMADO', '')                       AS DS_CID
													,REPLACE(LEFT(LTRIM(RTRIM(REPLACE(UF_TRANSP, ' ', ''))), 2), 'SN', '')              AS UF
													,REPLACE(LEFT(PLACA_VEIC, 8), '-', '')                                              AS PLACA
													,PLACA_UF                                                                           AS SIGLA_UF
													,''                                                                                 AS REG_NAC_TRANS
													,QT_VOL                                                                             AS QTDE_VOL
													,LEFT(ESPCIE_VOL, 60)                                                               AS ESP_VOL_TRANS
													,LEFT(MC_VOL, 60)                                                                   AS MARCA
													,ROUND(ISNULL(NR_VOL, 0), 0)                                                        AS NUM_VOL
													,PESO_LIQ                                                                           AS PESO_LIQ
													,---3 CASAS DECIMAIS  
													PESO_BRT                                                                            AS PESO_BRUTO
													,--- 3 CASAS DECIMAIS  
													ISNULL(EMAIL_TRANSP, '')                                                            AS EMAIL_TRANSP
												FROM
													V_EST_NF_ENT_CAB
												WHERE
															CD_NF     = @CD_NF
													AND CD_EMP    = 1
													AND CD_FILIAL = @CD_FILIAL;
												--DADOS IMPOSTOS ITEM  
												PRINT 'INSERINDO #DADOS_IMP_ITEM';
												INSERT INTO #DADOS_IMP_ITEM
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NOTA_FISCAL.SEQ_ITEM
													,NOTA_FISCAL.CD_TRIB_FC                                      AS MOD_TRIB
													,NOTA_FISCAL.ORIGEM_PRODUTO                                  AS ORIGEM
													,NOTA_FISCAL.CD_PROD
													,3                                                           AS MOD_BC_ICMS
													,NOTA_FISCAL.VLR_BASE_ICMS_IT AS VLR_BASE_ICMS
													,NOTA_FISCAL.VLR_ICMS_IT AS VLR_ICMS
													,NOTA_FISCAL.ALIQ_ICMS AS ALIQUOTA
													,NOTA_FISCAL.VLR_CRED_BASE_SN
													,NOTA_FISCAL.VLR_CRED_ICMS_SN
													,PERC_IVA                                                    AS PERC_MARG
													,NOTA_FISCAL.PERC_REDU_BASE_ICMS_IT                          AS PERC_REDU
													,NOTA_FISCAL.PERC_REDU_BASE_SUBS_IT                          AS PERC_REDU_ST
													,NOTA_FISCAL.VLR_BASE_SUBS_IT                                AS VLR_BASE_SUBS
													,NOTA_FISCAL.VLR_ICMS_SUBS_IT                                AS VLR_ICMS_SUBS
													,4                                                           AS MOD_BC_ICMS_ST
													,NOTA_FISCAL.ALIQ_ICMS_INTERNO                               AS ALIQ_ICMS_ST
													,VLR_BASE_ICMS_RET
													,VLR_ICMS_RET
													,NOTA_FISCAL.VLR_ICMS_DESONERADO
													,NOTA_FISCAL.ICMS_DESONERADO_MOTIVO
                                                    ,NOTA_FISCAL.CD_BENEF_IT								     AS CBENEF
                                                    ,NOTA_FISCAL.CD_SIT_OPERA_SIMPLES_NACIO
													,NOTA_FISCAL.MODALI_BASE_ICMS_SUBST
													,NOTA_FISCAL.MODALI_BASE_ICMS
													,NOTA_FISCAL.PERC_ALIQ_COFINS
													,NOTA_FISCAL.PERC_ALIQ_PIS
													,NOTA_FISCAL.VLR_BASE_COFINS
													,NOTA_FISCAL.VLR_BASE_PIS
													,NOTA_FISCAL.VLR_PIS
													,NOTA_FISCAL.VLR_COFINS
													,999																		AS ENQUADRAMENTO
													,VLR_IPI_IT																	AS VLR_IPI
													,( CASE WHEN ISNULL(PERC_IPI, 0) = 0 THEN 0
														 ELSE NOTA_FISCAL.VLR_TOT_IT + NOTA_FISCAL.VLR_OUTRAS_DESPESAS
														 END )																	AS VLR_BC_IPI
													,PERC_IPI																	AS ALIQ_IPI
													,VLR_IT																		AS VLR_UND_TRIB
													,QT_IT																		AS QTDE_TOT_UND
													,99																			AS CST_IPI
							  
													,RIGHT('00000000' + RTRIM(LTRIM(ISNULL(NOTA_FISCAL.CST_PIS, 0))), 2)		AS CST_PIS
							  
													,RIGHT('00000000' + RTRIM(LTRIM(ISNULL(NOTA_FISCAL.CST_COFINS, 0))), 2)		AS CST_COFINS
													,0																			AS PERC_ICMS_INTRA
													,0																			AS PERC_ICMS_INTER
													,NOTA_FISCAL.PERC_FECOP														AS PERC_FECOP
													,0																			AS BASE_DIFAL                                   
													,0																			AS ICMS_INTER_PART
													,0																			AS VALOR_FECOP_DEST
													,0																			AS VALOR_DIFAL_DEST
													,0																			AS VALOR_DIFAL_ORIG
													,NOTA_FISCAL.VLR_BASE_ICMS_OP												AS VLR_BASE_ICMS_OP
													,NOTA_FISCAL.VLR_ICMS_OP													AS VLR_ICMS_OP
													,NOTA_FISCAL.PERC_ICMS_OP													AS PERC_ICMS_OP
													,ISNULL(NOTA_FISCAL.PERC_DIFERIMENTO,0)
													,ISNULL(NOTA_FISCAL.VLR_ICMS_DIFERIMENTO,0)  
												   ,NOTA_FISCAL.PERC_FECOP_ST													AS  PERC_FECOP_ST 
												   ,NOTA_FISCAL.VLR_BASE_FECOP_ST_IT											AS VLR_BASE_FECOP_ST         
												   ,NOTA_FISCAL.VLR_FECOP_ST_IT													AS VLR_FECOP_ST              
												   ,NOTA_FISCAL.VLR_BASE_FECOP_IT												AS VLR_BASE_FECOP                
												   ,NOTA_FISCAL.VLR_FECOP_IT													AS VLR_FECOP                        
												   ,NOTA_FISCAL.VLR_BASE_FECOP_RET_IT											AS VLR_BASE_FECOP_RET                
												   ,NOTA_FISCAL.VLR_FECOP_RET_IT												AS VLR_FECOP_RET                  
												   ,NOTA_FISCAL.PERC_FECOP_RET													AS PERC_FECOP_RET               
												   ,0																			AS NR_ITEM_XPED               
												   ,0																			AS ALIQUOTA_PST               
												   ,0																			AS VLR_BC_UF_DEST
												   ,0																			AS PERC_ICMS_RET
												   ,0																			AS VLR_IPI_DEVOL
                                                   ,0																			AS PERC_IPI_DEVOL
                                                   ,0																			AS VLR_ICMS_SUBSTITUTO
												   ,0																			AS ALIQ_EFET_ICMS
												   ,0																			AS PERC_EFET_RED_BASE_CALC
												   ,0																			AS VLR_EFET_BASE_ICMS
												   ,0																			AS VLR_EFET_ICMS
                                                FROM
													V_EST_NF_ENT NOTA_FISCAL
													INNER JOIN PRC_FILIAL ON
															NOTA_FISCAL.CD_EMP    = PRC_FILIAL.CD_EMP
														AND NOTA_FISCAL.CD_FILIAL = PRC_FILIAL.CD_FILIAL
													INNER JOIN GLB_CID ON
														PRC_FILIAL.CD_CID = GLB_CID.CD_CID
												WHERE
														NOTA_FISCAL.CD_NF     = @CD_NF
													AND NOTA_FISCAL.CD_EMP    = 1
													AND NOTA_FISCAL.CD_FILIAL = @CD_FILIAL
												ORDER  BY
													NOTA_FISCAL.CD_PROD;

												--DADOS IMPOSTOS NF  
												PRINT 'INSERINDO #DADOS_IMP_NF';
												INSERT INTO #DADOS_IMP_NF
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,VLR_BASE_ICMS         AS BASE_CAL_ICMS
													,VLR_ICMS
													,VLR_BASE_ICMS_SUBS    AS BASE_CAL_ICMS_ST
													,VLR_ICMS_SUBS         AS VLR_TOT_ICMS_ST
													,VLR_TOT_PROD
													,VLR_FRETE             AS VLR_TOT_FRETE
													,0                     AS VLR_TOT_SEG
													,VLR_DESC              AS VLR_DESC_CORPO
													,0                     AS VLR_TOT_II
													,VLR_IPI               AS VLR_TOT_IPI
													,B.VLR_PIS             AS VLR_PIS
													,B.VLR_COFINS          AS VLR_COFINS
													,B.VLR_OUTROS_DESPESAS AS VLR_OUTRAS_DES
													,VLR_NF
													,VLR_ICMS_DESONERADO_CAB AS VLR_ICMS_DESONERADO
													,0  AS VLR_TOT_DIFAL_ORIG
													,0  AS VLR_TOT_DIFAL_DEST
													,0  AS VLR_TOT_FECOP_DEST
													,A.VLR_FECOP_ST AS VLR_FECOP_ST                          
													,A.VLR_FECOP  AS VLR_FECOP              
													,A.VLR_FECOP_RET AS VLR_FECOP_RET
                                                    ,0 AS  VLR_TOT_IPI_DEVOL
													,0 AS VLR_BASE_CALC_EFET
													,0 AS VLR_EFET_ICMS
												FROM
													V_EST_NF_ENT_CAB A
													INNER JOIN (SELECT
																				CD_EMP
																				,CD_FILIAL
																				,CD_NF
																				,SUM (ROUND(VLR_PIS,2))             VLR_PIS
																				,SUM (ROUND(VLR_COFINS,2))          VLR_COFINS
																				,SUM (ROUND(VLR_OUTRAS_DESPESAS,2)) VLR_OUTROS_DESPESAS
																			FROM
																				EST_NF_ENT_IT
																			GROUP  BY
																			 CD_EMP
																			 ,CD_FILIAL
																			 ,CD_NF) B ON
																A.CD_EMP    = B.CD_EMP
														AND A.CD_FILIAL = B.CD_FILIAL
														AND A.CD_NF     = B.CD_NF
												WHERE
															A.CD_NF     = @CD_NF
													AND A.CD_EMP    = 1
													AND A.CD_FILIAL = @CD_FILIAL;
												--DADOS ADICIONAIS  
												PRINT 'INSERINDO #DADOS_ADICIONAIS';
												INSERT INTO #DADOS_ADICIONAIS
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS1, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS2, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS3, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS4, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS5, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS6, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(650), RTRIM(ISNULL(REPLACE(OBS7, ';', ''), ''))) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS OBSERVACAO
													,''                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        AS XPED
												FROM
													V_EST_NF_ENT_CAB
												WHERE
															CD_NF     = @CD_NF
													AND CD_EMP    = 1
													AND CD_FILIAL = @CD_FILIAL
												--DADOS LOTE ITEM  
												PRINT 'INSERINDO #DADOS_LOTE_PROD';
												INSERT INTO #DADOS_LOTE_PROD
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NOTAFISCAL.CD_PROD
													,NOTAFISCAL.SEQ_ITEM
													,LEFT(EST_PROD_CPL_LOTE.NR_LOTE, 20)
													,NOTAFISCAL_LOTE.QTDE_PROD
													,EST_PROD_CPL_LOTE.DT_FABR
													,EST_PROD_CPL_LOTE.DT_VALID
													,EST_PROD_PRECO.VLR_TABELA
													,LEFT(COALESCE( LOTE_SNGPC.REGISTRO_MS,PRODUTO.NR_REG_MS), 25) AS  REGISTRO_MS
													,LOTE_SNGPC.CD_DCB
													,CD_AGREGADOR
													, CASE WHEN PRODUTO.NR_REG_MS = 'ISENTO' AND MOT_ISENCAO_ANVISA.DS_MOT_ISENCAO_ANVISA IS NOT NULL THEN MOT_ISENCAO_ANVISA.DS_MOT_ISENCAO_ANVISA ELSE '' END AS MOT_ISENCAO_ANVISA
													, CASE WHEN NOTAFISCAL.OBS_CONTRIBUINTE IS NOT NULL THEN NOTAFISCAL.OBS_CONTRIBUINTE ELSE NULL END AS OBS_CONTRIBUINTE 
													, CASE WHEN NOTAFISCAL.OBS_FISCO IS NOT NULL THEN NOTAFISCAL.OBS_FISCO ELSE NULL END AS OBS_FISCO
													,CASE 
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30010000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30019999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30020000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30029999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30030000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30039999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30040000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30049999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30050000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30059999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30060000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30069999 THEN 1
														WHEN LEFT(EST_PROD_CPL_LOTE.NR_LOTE, 20) IS NOT NULL THEN 1
														ELSE 0
													END	AS DET_MED
												FROM
													EST_NF_ENT_IT NOTAFISCAL
													LEFT JOIN EST_NF_ENT_IT_EST_PROD_CPL_LOTE NOTAFISCAL_LOTE ON
																NOTAFISCAL.CD_EMP    = NOTAFISCAL_LOTE.CD_EMP
														AND NOTAFISCAL.CD_FILIAL = NOTAFISCAL_LOTE.CD_FILIAL
														AND NOTAFISCAL.CD_NF     = NOTAFISCAL_LOTE.CD_NF
														AND NOTAFISCAL.CD_PROD   = NOTAFISCAL_LOTE.CD_PROD
													LEFT JOIN  EST_PROD_EST_SNGPC  LOTE_SNGPC ON
														NOTAFISCAL.CD_EMP        = LOTE_SNGPC.CD_EMP											
														AND NOTAFISCAL.CD_PROD   = LOTE_SNGPC.CD_PROD 
													LEFT JOIN  EST_PROD  PRODUTO ON
													   NOTAFISCAL.CD_EMP        = PRODUTO.CD_EMP											
													  AND NOTAFISCAL.CD_PROD   = PRODUTO.CD_PROD      
													LEFT JOIN EST_PROD_CPL_LOTE ON
																NOTAFISCAL_LOTE.CD_EMP    = EST_PROD_CPL_LOTE.CD_EMP
														AND NOTAFISCAL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
														AND NOTAFISCAL_LOTE.CD_LOTE   = EST_PROD_CPL_LOTE.CD_LOTE
														AND NOTAFISCAL_LOTE.CD_PROD   = EST_PROD_CPL_LOTE.CD_PROD
													INNER JOIN EST_PROD_PRECO ON
																NOTAFISCAL.CD_EMP    = EST_PROD_PRECO.CD_EMP
														AND NOTAFISCAL.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL
														AND NOTAFISCAL.CD_PROD   = EST_PROD_PRECO.CD_PROD
													LEFT JOIN MOT_ISENCAO_ANVISA ON
															PRODUTO.CD_MOT_ISENCAO_ANVISA = MOT_ISENCAO_ANVISA.CD_MOT_ISENCAO_ANVISA
													LEFT JOIN INFORMA_LOTE_NFE ON 
														NOTAFISCAL.CD_PROD = INFORMA_LOTE_NFE.CD_PROD
												
												WHERE
															NOTAFISCAL.CD_EMP    = 1
													AND NOTAFISCAL.CD_FILIAL = @CD_FILIAL
													AND NOTAFISCAL.CD_NF     = @CD_NF
												ORDER  BY
													NOTAFISCAL.CD_PROD
													
												PRINT 'INSERINDO #ATOS_CONCESSORIOS';
												INSERT INTO #ATOS_CONCESSORIOS
												select
													ENT.cd_filial,
													ENT.cd_nf,
													REF.N_PROC,
													cast(LEFT(O.ORIG_PROC,1) as numeric(1)) AS ORIG_PROC,
													cast(LEFT(TIPO.tipo_ato_concessorio, 2) as numeric(2)) as TIPO_ATO_CONCESSORIO
												from PROCESSOS_FISCAIS_REFERENCIADOS REF
												INNER JOIN EST_NF_ENT_IT ENT ON ENT.CD_PROC_FISCAL_REFERENCIADO = REF.CD_PROC_FISCAL_REFERENCIADO
												LEFT JOIN ORIGEM_PROCESSOS_REFERENCIADOS O ON O.CD_ORIG_PROC = REF.CD_ORIG_PROC
												LEFT JOIN TIPO_ATO_CONCESSORIO TIPO ON TIPO.CD_ATO_CONCESSORIO = REF.CD_ATO_CONCESSORIO
												WHERE ENT.cd_filial = @CD_FILIAL
													AND ENT.cd_nf  = @cd_nf;
										END
									IF ( @TP_SAI_ENT = 1 )
										BEGIN
												SET @UFDEST = (	SELECT TOP 1 UF
																FROM
						
																	V_EST_NF_SAI_CAB
																WHERE
																	CD_EMP    = 1
																	AND CD_FILIAL = @CD_FILIAL
																	AND CD_NF     = @CD_NF)
												
												
												SET @FILIAL_OPTANTE_SIMPLES_NACIONAL = (SELECT TOP 1
																																	REGIME_TRIBUTARIO
																																FROM
																																	PRC_FILIAL
																																WHERE
																																		 CD_EMP    = 1
																																 AND CD_FILIAL = @CD_FILIAL
																																);
												
												
												
												IF (COALESCE(@FILIAL_OPTANTE_SIMPLES_NACIONAL,0) = 0)
													BEGIN
														SET @FILIAL_OPTANTE_SIMPLES_NACIONAL = (SELECT TOP 1 ISNULL(VALOR, 0) FROM PRC_FILIAL_CONFIG WHERE CD_EMP    = 1
																																			 AND CD_FILIAL = @CD_FILIAL
																																			 AND CD_CHAVE LIKE 'FILIAL_OPTANTE_SIMPLES_NACIONAL');
													END;
												
												PRINT 'INSERINDO #NF_O1 ' + CONVERT(VARCHAR,@CD_NF);
												UPDATE I
												SET
													I.DT_EMI_NF=S.DT_EMI_NF 
												FROM 
													EST_NF_SAI S
													INNER JOIN EST_NF_SAI_IT I ON 
													S.CD_EMP	= I.CD_EMP AND
													S.CD_FILIAL	= I.CD_FILIAL AND
													S.CD_NF		= I.CD_NF AND
													S.DT_EMI_NF	<> isnull(I.DT_EMI_NF,'19000101')  
												WHERE
													I.CD_EMP=1 
													AND I.CD_FILIAL=@CD_FILIAL 
													AND I.CD_NF=@CD_NF;

						
												INSERT INTO 
													#DADOS_INTERMEDIADOR (CD_FILIAL, CD_NF, CD_NFE_LOTE, CD_NFE_LOTE_IT, CNPJ_INTERMEDIADOR, NM_VENDEDOR_MARKETPLACE)
												SELECT TOP 1
													SAI.CD_FILIAL,
													SAI.CD_NF,
													@CD_NFE_LOTE,
													@CD_NFE_LOTE_IT,
													RC_CLI.CGC_CPF AS CNPJ_INTERMEDIADOR,
													COALESCE(RC_CLI.NM_VENDEDOR_MARKETPLACE, '') AS NM_VENDEDOR_MARKETPLACE
												FROM
													EST_NF_SAI SAI
													INNER JOIN EST_NF_SAI_PED_VD SAI_PED ON
														SAI.CD_EMP = SAI_PED.CD_EMP
														AND SAI.CD_FILIAL = SAI_PED.CD_FILIAL
														AND SAI.CD_NF = SAI_PED.CD_NF
													INNER JOIN EST_PED_VD_PAG PED_PAG ON
														SAI_PED.CD_EMP = PED_PAG.CD_EMP
														AND SAI_PED.CD_FILIAL = PED_PAG.CD_FILIAL
														AND SAI_PED.CD_PED = PED_PAG.CD_PED
													INNER JOIN EST_PED_VD_PAG_DUPLICATA PED_PAG_DUP ON
														PED_PAG.CD_CTR = PED_PAG_DUP.PAG_CD_CTR
													INNER JOIN RC_CLI ON
														PED_PAG_DUP.ADQUIRENTE = RC_CLI.MARKETPLACE_ID COLLATE SQL_Latin1_General_CP1_CI_AS
													INNER JOIN TELE_ORC_PAG ORC_PAG ON
														PED_PAG.CD_ORC_PAG = ORC_PAG.CD_ORC_PAG
													INNER JOIN TELE_TIPO_PAG ON
														ORC_PAG.CD_TIPO_PAG = TELE_TIPO_PAG.CD_TIPO_PAG
												WHERE
													SAI.CD_EMP = 1
													AND SAI.CD_NF = @CD_NF
													AND SAI.CD_FILIAL = @CD_FILIAL
													AND TELE_TIPO_PAG.CD_TIPO_MOEDA = 7;

												PRINT 'INSERINDO #ATOS_CONCESSORIOS';
												INSERT INTO #ATOS_CONCESSORIOS
												select
													SAI.cd_filial,
													SAI.cd_nf,
													REF.N_PROC,
													cast(LEFT(O.ORIG_PROC,1) as numeric(1)) AS ORIG_PROC,
													cast(LEFT(TIPO.tipo_ato_concessorio, 2) as numeric(2)) as TIPO_ATO_CONCESSORIO
												from PROCESSOS_FISCAIS_REFERENCIADOS REF
												INNER JOIN EST_NF_SAI_IT SAI ON SAI.CD_PROC_FISCAL_REFERENCIADO = REF.CD_PROC_FISCAL_REFERENCIADO
												LEFT JOIN ORIGEM_PROCESSOS_REFERENCIADOS O ON O.CD_ORIG_PROC = REF.CD_ORIG_PROC
												LEFT JOIN TIPO_ATO_CONCESSORIO TIPO ON TIPO.CD_ATO_CONCESSORIO = REF.CD_ATO_CONCESSORIO
												WHERE SAI.cd_filial = @CD_FILIAL
													AND SAI.cd_nf  = @cd_nf;


												INSERT INTO #NF_01
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NF_NF
													,DS_MOV_OP
													,CASE WHEN T1.TP_MOV = 25 or T1.TP_MOV = 58 THEN '3'
													 ELSE '1'
													 END                                                                      AS FORMA_PAG
													,55                                                                       AS MODELO
													,CD_NF_SERIE
													,T1.SERIE
													,( DT_EMI_NF + HR_EMI_NF )                                                AS DT_EMI_NF
													,( DT_EMI_NF + HR_EMI_NF )                                                AS DT_SAIDA
													,1                                                                        AS TP_DOC_FISCAL
													,2                                                                        AS FOR_IMP
													,ISNULL(T4.COD_MUNICIPIO_SINTEGRA, 0)                                     AS CD_IBGE
													,(SELECT
															CD_UF
														FROM
															#COD_UF
														WHERE
														 UF = GLB_CID.UF COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS)                  AS UF
													,CONTG.NR_AUTORIZADOR
													,( CASE WHEN T1.FLAG_MOV_OP_NFE_CPL = 1 THEN --Nota Complementar
														 2
														 WHEN T1.TP_MOV IN ( 25, 52,58,68 ) THEN 3
														 WHEN T1.TP_MOV IN ( 11, 15, 17, 18,19, 20, 31, 41, 57, 59,60) THEN 4
														 ELSE 1
														 END )                                                                  AS FIN_NFE
													,(SELECT
															dbo.UFD_BUSCA_NFE_REFERENCIADA(T1.CD_EMP, T1.CD_FILIAL, T1.CD_NF, 1)) AS NF_REF
													,( CASE WHEN @REG_CONTG = 0 THEN 1
														 ELSE 3
														 END )                                                                  AS REG_CONTG
													,(CASE		 
														WHEN T1.TP_MOV IN (10,11,15,16,24,25,33,52,58,60) THEN 0
														WHEN T1.TP_MOV IN (0,1,3,28) THEN 1
														WHEN T1.TP_MOV IN (65) THEN 2
														ELSE 9
													  END)     																  AS IND_PRES	
													 , 0 AS IND_INTERMED
													--,ISNULL(REF_NF_DEV.MODELO, '')                                            AS IDE_ECF_NMOD
													,CASE WHEN ISNULL(REF_NF_DEV.NR_ECF,'0') = '0' THEN '0'
													 ELSE REF_NF_DEV.MODELO END												  AS IDE_NF_1_MOD
													,ISNULL(REF_NF_DEV.CD_CX, '')                                             AS IDE_ECF_NECF
													,ISNULL(RIGHT(REF_NF_DEV.NR_ECF,6), '')                                            AS IDE_ECF_NCOO
													--Se nao ?cupom manda o modelo
													,CASE WHEN ISNULL(REF_NF_DEV.NR_ECF,'0') = '0' THEN ISNULL(REF_NF_DEV.MODELO,0)
													 ELSE '' END															  AS IDE_NF_1_MOD
													,REF_NF_DEV.CD_UF                                                         AS IDE_NF_1_UF
													,RIGHT(LEFT(ANO_MES_EMI, 4), 2)
													 + RIGHT(ANO_MES_EMI, 2)                                                  AS IDE_NF_1_ANO_MES
													,REF_NF_DEV.CNPJ                                                          AS IDE_NF_1_CNPJ
													,REF_NF_DEV.SERIE                                                         AS IDE_NF_1_SERIE
													,REF_NF_DEV.NR_NF                                                         AS IDE_NF_1_NR_NF
													,ISNULL(PRC_FILIAL.CGC_CPF_RESP, '')                                      AS AUTXML
													,T1.TIPO_TRANSACAO
													,REF_NF_DEV.CD_EMP								  AS CD_EMP
													,REF_NF_DEV.CD_CX								  AS CD_CX
													,REF_NF_DEV.NR_ECF								  AS NR_ECF
												FROM
													V_EST_NF_SAI T1
													LEFT JOIN EST_NF_SAI_NFE_CONTG CONTG ON
																T1.CD_EMP    = CONTG.CD_EMP
														AND T1.CD_FILIAL = CONTG.CD_FILIAL
														AND T1.CD_NF     = CONTG.CD_NF
													LEFT JOIN (SELECT
																			 ROW_NUMBER()
																				 OVER (
																					 PARTITION BY NFE_REF.CD_EMP, NFE_REF.CD_FILIAL, NFE_REF.CD_NF
																					 ORDER BY NFE_REF.CD_EMP, NFE_REF.CD_FILIAL, NFE_REF.CD_NF) AS ID_ROW
																			 ,NFE_REF.*
																		 FROM
																			 EST_NF_SAI_DEVOLUCAO_NFE_CHAVE_REFERENCIA NFE_REF
																		 WHERE
																					NFE_REF.CD_EMP    = 1
																			AND NFE_REF.CD_FILIAL = @CD_FILIAL
																			AND NFE_REF.CD_NF     = @CD_NF) AS REF_NF_DEV ON
																T1.CD_EMP         = REF_NF_DEV.CD_EMP
														AND T1.CD_FILIAL      = REF_NF_DEV.CD_FILIAL
														AND T1.CD_NF          = REF_NF_DEV.CD_NF
														AND REF_NF_DEV.ID_ROW = 1
													LEFT JOIN EST_NF_SAI_EST_NF_SAI_CPL NF_CPL ON
																T1.CD_EMP    = NF_CPL.CD_EMP
														AND T1.CD_FILIAL = NF_CPL.CD_FILIAL
														AND T1.CD_NF     = NF_CPL.CD_NF
													LEFT JOIN EST_NF_SAI_NFE NFE ON
																NF_CPL.CD_EMP    = NFE.CD_EMP
														AND NF_CPL.CD_FILIAL = NFE.CD_FILIAL
														AND NF_CPL.CD_NF_REF = NFE.CD_NF
													INNER JOIN PRC_FILIAL ON
																T1.CD_EMP    = PRC_FILIAL.CD_EMP
														AND T1.CD_FILIAL = PRC_FILIAL.CD_FILIAL
													INNER JOIN GLB_CID ON
														PRC_FILIAL.CD_CID = GLB_CID.CD_CID
													INNER JOIN GLB_NT_OP T2 ON
														T1.CD_NT_OP = T2.CD_NT_OP
													LEFT JOIN GLB_CID_GLB_MUNICIPIO T3 ON
														PRC_FILIAL.CD_CID = T3.CD_CID
													LEFT JOIN GLB_MUNICIPIO T4 ON
														T3.CD_MUNICIPIO = T4.CD_MUNICIPIO
													LEFT JOIN FP_CONTABILISTA FP ON
														T1.CD_EMP = FP.CD_EMP
												WHERE
															T1.CD_NF     = @CD_NF
													AND T1.CD_EMP    = 1
													AND T1.CD_FILIAL = @CD_FILIAL;
												IF @UFDEST = ''
													BEGIN
															PRINT 'INSERINDO #DADOS_EMIT';
															INSERT INTO #DADOS_EMIT
															SELECT DISTINCT
																@CD_NFE_LOTE
																,@CD_NFE_LOTE_IT
																,@CD_FILIAL
																,@CD_NF
																,LEFT(T2.RZ_FILIAL, 60)
																,T2.CD_FILIAL                                                                  AS CD_EMIT
																,LEFT(T2.NM_FANT, 60)                                                          AS NM_FANT
																,( CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) = '' THEN 'ISENTO'
																	 ELSE
																		 CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) <> 'ISENTO' THEN
																			 CASE WHEN RTRIM(LTRIM(DBO.F_LIMPA_NUMEROS(T2.INSC_EST))) = '' THEN 'ISENTO'
																			 ELSE DBO.F_LIMPA_NUMEROS(T2.INSC_EST)
																			 END
																		 ELSE RTRIM(LTRIM(ISNULL(T2.INSC_EST, '')))
																		 END
																	 END )																			AS INSC_EST
																,REPLACE(REPLACE(REPLACE(ISNULL(T2.INSC_MUNI, ''), '/', ''), '-', ''), '.', '')		AS INSC_MUNI
																,'0000000'																			AS CNAE_FISCAL
																,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CGC)), '/', ''), '-', ''), '.', '')			AS CGC
																,(CASE 
																	WHEN ISNULL(VLR_BASE_ICMS_SUBS, 0) > 0 THEN 
																		(CASE 
																			WHEN ( T1.TP_MOV_OP_TRANSA = 1 ) AND ( T1.TP_MOV = 11 ) THEN '0'
																			ELSE (CASE 
																					WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) = '' THEN ''
																					ELSE DBO.F_LIMPA_NUMEROS(T2.INSC_EST)
																				 END)
																		 END)
																	 ELSE '0'
																 END)																		   AS INSC_SUBS
																,@FILIAL_OPTANTE_SIMPLES_NACIONAL
																,LEFT(T2.END_FILILAL, 60)                                                      AS ENDE
																,isnull(T1.NUMERO,'')                                                          AS NUMERO
																,''                                                                            AS COMPLEMENTO
																,LEFT(T2.BAIRRO, 60)                                                           AS BAIRRO
																,LEFT(T4.DS_CID, 60)                                                           AS DS_CID
																,T4.UF                                                                         AS UF
																,REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', '')                  AS CEP
																,1058                                                                          AS CD_PAIS
																,'BRASIL'                                                                      AS NM_PAIS
																,T2.TEL                                                                        AS TEL
																,ISNULL(T6.COD_MUNICIPIO_SINTEGRA, 0)                                          AS CD_IBGE
															FROM
																V_EST_NF_ENT T1
																INNER JOIN PRC_FILIAL T2 ON
																			T1.CD_FILIAL = T2.CD_FILIAL
																	AND T1.CD_EMP    = T2.CD_EMP
																LEFT JOIN GLB_CID T4 ON
																	T2.CD_CID = T4.CD_CID
																LEFT JOIN GLB_CID_GLB_MUNICIPIO T5 ON
																	T4.CD_CID = T5.CD_CID
																LEFT JOIN GLB_MUNICIPIO T6 ON
																	T5.CD_MUNICIPIO = T6.CD_MUNICIPIO
															WHERE
																	CD_NF        = @CD_NF
																AND T1.CD_EMP    = 1
																AND T1.CD_FILIAL = @CD_FILIAL
													END

												IF @UFDEST <> ''
													BEGIN
															PRINT 'INSERINDO #DADOS_EMIT';
															INSERT INTO #DADOS_EMIT
															SELECT DISTINCT
																@CD_NFE_LOTE
																,@CD_NFE_LOTE_IT
																,@CD_FILIAL
																,@CD_NF
																,LEFT(T2.RZ_FILIAL, 60)
																,T2.CD_FILIAL                                                                   AS CD_EMIT
																,LEFT(T2.NM_FANT, 60)                                                           AS NM_FANT
																,( CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) = '' THEN 'ISENTO'
																	 ELSE
																		 CASE WHEN RTRIM(LTRIM(ISNULL(T2.INSC_EST, ''))) <> 'ISENTO' THEN
																			 CASE WHEN RTRIM(LTRIM(DBO.F_LIMPA_NUMEROS(T2.INSC_EST))) = '' THEN 'ISENTO'
																			 ELSE DBO.F_LIMPA_NUMEROS(T2.INSC_EST)
																			 END
																		 ELSE RTRIM(LTRIM(ISNULL(T2.INSC_EST, '')))
																		 END
																	 END )                                                                      AS INSC_EST
																,REPLACE(REPLACE(REPLACE(ISNULL(T2.INSC_MUNI, ''), '/', ''), '-', ''), '.', '') AS INSC_MUNI
																,'0000000'                                                                      AS CNAE_FISCAL
																,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CGC)), '/', ''), '-', ''), '.', '')        AS CGC
																,(CASE WHEN RTRIM(LTRIM(ISNULL(T3.INSC_EST_ST, ''))) = '' THEN ''
																       ELSE DBO.F_LIMPA_NUMEROS(ISNULL(T3.INSC_EST_ST, '0')) 
																   END )	 																	  INSC_SUBS
																	 
																,@FILIAL_OPTANTE_SIMPLES_NACIONAL
																,LEFT(T2.END_FILILAL, 60)                                                       AS ENDE
																,ISNULL(T1.NUMERO_EMI,'')														AS NUMERO
																,''                                                                             AS COMPLEMENTO
																,LEFT(T2.BAIRRO, 60)                                                            AS BAIRRO
																,LEFT(T4.DS_CID, 60)                                                            AS DS_CID
																,LEFT(T4.UF, 2)                                                                 AS UF
																,REPLACE(REPLACE(REPLACE(T2.CEP, '/', ''), '-', ''), '.', '')                   AS CEP
																,1058                                                                           AS CD_PAIS
																,'BRASIL'                                                                       AS NM_PAIS
																,T2.TEL                                                                         AS TEL
																,ISNULL(T6.COD_MUNICIPIO_SINTEGRA, 0)                                           AS CD_IBGE
															FROM
																V_EST_NF_SAI T1
																INNER JOIN PRC_FILIAL T2 ON
																			T1.CD_FILIAL = T2.CD_FILIAL
																	AND T1.CD_EMP    = T2.CD_EMP
																LEFT JOIN PRC_FILIAL_INSC_EST_ST T3 ON
																			T1.CD_FILIAL = T3.CD_FILIAL
																	AND T1.CD_EMP    = T3.CD_EMP
																	AND T3.UF        = @UFDEST
																LEFT JOIN GLB_CID T4 ON
																	T2.CD_CID = T4.CD_CID
																LEFT JOIN GLB_CID_GLB_MUNICIPIO T5 ON
																	T4.CD_CID = T5.CD_CID
																LEFT JOIN GLB_MUNICIPIO T6 ON
																	T5.CD_MUNICIPIO = T6.CD_MUNICIPIO
															WHERE
																CD_NF        = @CD_NF
																AND T1.CD_EMP    = 1
																AND T1.CD_FILIAL = @CD_FILIAL
													END
												--DADOS DESTINATARIO
												--SE FOR NOTA ORIUNDA DO TELEVENDAS, PEGA OS DADOS DO DESTINAT?RIO DA PR?PRIA NOTA
												--SEN?O, PEGA OS DADOS DO DESTINAT?RIO DO CADASTRO
												--ALTERACAO: 05/09/2019 AGORA PEGA SO O ENDERECO DA EST_NF_SAI 
												PRINT 'INSERINDO #DADOS_DEST';	 
												BEGIN
													INSERT INTO #DADOS_DEST (CD_NFE_LOTE,  CD_NFE_LOTE_IT,  CD_FILIAL,  CD_NF,  NOME,  INSC_EST,  CGC_CPF,  ENVIAR_XML_NFE,  EMAIL,  ENDE,  NUMERO,  COMPLEMENTO,  BAIRRO,  CD_IBGE,  DS_CID,  UF,  CEP,  CD_PAIS_SINTEGRA,  DS_PAIS,  DEST_FONE,  CONTRIBUINTE,  DEST_XNOME_ENTREGA,  DEST_IE_ENTREGA,  DEST_XLGR_ENTREGA,  DEST_NRO_ENTREGA,  DEST_XCPL_ENTREGA,  DEST_XBAIRRO_ENTREGA,  DEST_CMUN_ENTREGA,  DEST_XMUN_ENTREGA,  DEST_UF_ENTREGA,  DEST_CEP_ENTREGA,  DEST_FONE_ENTREGA,  DEST_EMAIL_ENTREGA, DEST_CD_IBGE_ENTREGA, DEST_CPF_CNPJ_ENTREGA)
													SELECT DISTINCT
														@CD_NFE_LOTE																				AS CD_NFE_LOTE
														,@CD_NFE_LOTE_IT																			AS CD_NFE_LOTE_IT
														,@CD_FILIAL																					AS CD_FILIAL
														,@CD_NF																						AS CD_NF
														,LEFT(SAI.NM_DEST, 60)																		AS NOME
														,SAI.INSC_EST																				AS INSC_EST
														,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SAI.CGC_CPF)), '/', ''), '-', ''), '.', '')			AS CGC_CPF
														,ISNULL(SAI.ENVIAR_XML_NFE, 0)																AS ENVIAR_XML_NFE
														,LEFT(SAI.EMAIL, 60)																		AS EMAIL
														,LEFT(ISNULL(SAI.ENDE, ''), 60)																AS ENDE
														,LEFT(SAI.NUMERO, 60)																		AS NUMERO -- NR_END_DESTINATARIO 
														,''																							AS COMPLEMENTO
														,LEFT(SAI.BAIRRO, 60)																		AS BAIRRO
														,CASE WHEN SAI.COD_IBGE = '' THEN 0
															ELSE ISNULL(SAI.COD_IBGE, 0) END														AS CD_IBGE
														,LEFT(SAI.DS_CID, 60)																		AS CIDADE_DESTINATARIO
														,SAI.UF																						AS UF_DESTINATARIO
														,COALESCE(REPLACE(REPLACE(REPLACE(SAI.CEP, '/', ''), '-', ''), '.', ''), '')				AS CEP
														,T6.CD_PAIS_SINTEGRA																		AS CD_PAIS_SINTEGRA
														,T6.DS_PAIS																					AS DS_PAIS
														,SAI.TEL																					AS DEST_FONE
														,SAI.CONTRIBUINTE																			AS CONTRIBUINTE
														,SAI.RZ_DESTINATARIO																		AS DEST_XNOME_ENTREGA
														--,SAI.INSC_ESTADUAL_DESTINATARIO															AS DEST_IE_ENTREGADEST_XLGR_ENTREGA
														,''																							AS DEST_IE_ENTREGA
														,SAI.END_DESTINATARIO																		AS DEST_XLGR_ENTREGA
														,SAI.NR_END_DESTINATARIO																	AS DEST_NRO_ENTREGA
														,LEFT(SAI.PONTO_REF_DESTINATARIO, 60)														AS DEST_XCPL_ENTREGA
														,SAI.BAIRRO_DESTINATARIO																	AS DEST_XBAIRRO_ENTREGA
														,CASE WHEN SAI.COD_IBGE = '' THEN 0
															ELSE ISNULL(SAI.COD_IBGE, 0) END														AS DEST_CMUN_ENTREGA
														,LEFT(SAI.CIDADE_DESTINATARIO, 60)															AS DEST_XMUN_ENTREGA
														,SAI.UF																						AS DEST_UF_ENTREGA
														,COALESCE(REPLACE(REPLACE(REPLACE(SAI.CEP_DESTINATARIO, '/', ''), '-', ''), '.', ''), '')	AS DEST_CEP_ENTREGA
														,SAI.TEL																					AS DEST_FONE_ENTREGA
														,SAI.EMAIL																					AS DEST_EMAIL_ENTREGA
														,CASE WHEN SAI.COD_IBGE = '' THEN 0	ELSE ISNULL(SAI.COD_IBGE, 0) END						AS DEST_CD_IBGE_ENTREGA
														,ISNULL (REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(SAI.DEST_CPF_CNPJ_ENTREGA)), '/', ''), '-', ''), '.', ''),'')	AS DEST_CPF_CNPJ_ENTREGA
												FROM
														V_EST_NF_SAI SAI   
														INNER JOIN GLB_MOV_OP OP ON
															OP.CD_MOV_OP = SAI.CD_MOV_OP
														LEFT JOIN GLB_CID T2 ON
															SAI.CD_CID = T2.CD_CID
														LEFT JOIN GLB_CID_GLB_MUNICIPIO T3 ON
															T2.CD_CID = T3.CD_CID
														LEFT JOIN GLB_MUNICIPIO T4 ON
															T3.CD_MUNICIPIO = T4.CD_MUNICIPIO
														LEFT JOIN GLB_PAIS T5 ON
															T2.CD_PAIS = T5.CD_PAIS
														LEFT JOIN RC_CLI CLI ON
															CLI.CD_EMP = SAI.CD_EMP
															AND CLI.CD_CLI = SAI.CD_CLI
														LEFT JOIN PG_FORN FRN ON
															FRN.CD_EMP  = SAI.CD_EMP
															AND FRN.CD_FORN = SAI.CD_CLI
														LEFT JOIN PRC_FILIAL FL ON
															FL.CD_EMP    = SAI.CD_EMP
															AND FL.CD_FILIAL = SAI.CD_CLI_FILIAL
														LEFT JOIN PRC_FILIAL VFL ON
															VFL.CD_EMP    = SAI.CD_EMP
															AND VFL.CD_FILIAL = SAI.CD_FILIAL
														LEFT JOIN GLB_CID CID ON
															SAI.CD_CID_DESTINATARIO = CID.CD_CID
														LEFT JOIN GLB_PAIS T6 ON 
															T6.CD_PAIS = CID.CD_PAIS
													WHERE
														SAI.CD_NF     = @CD_NF
														AND SAI.CD_EMP    = 1
														AND SAI.CD_FILIAL = @CD_FILIAL;
												END
												--DADOS CONTIGENCIA  
												PRINT 'INSERINDO #DADOS_CONTINGENCIA';
												INSERT INTO #DADOS_CONTIGENCIA
												SELECT TOP 1
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,ISNULL(JUSTICATIVA, '')
													,CONVERT(VARCHAR(8), ISNULL(DT_CONTINGENCIA, '1900-01-01'), 112)
												FROM
													EST_CONTINGENCIA
												WHERE
															CD_FILIAL = @CD_FILIAL
													AND ( CONVERT(VARCHAR(8), ISNULL(DT_CONTINGENCIA, '1900-01-01'), 112) = CONVERT(VARCHAR(8), GETDATE(), 112) )
												ORDER  BY
													DT_CONTINGENCIA DESC
												--DADOS PRODUTOS   
												PRINT 'INSERINDO #DADOS_PROD';
												PRINT '  - @CD_NFE_LOTE = '
															+ CAST(@CD_NFE_LOTE AS VARCHAR(20));
												PRINT '  - @CD_NFE_LOTE_IT = '
															+ CAST(@CD_NFE_LOTE_IT AS VARCHAR(20));
												PRINT '  - @CD_FILIAL = '
															+ CAST(@CD_FILIAL AS VARCHAR(20));
												PRINT '  - @CD_NF = '
															+ CAST(@CD_NF AS VARCHAR(20));
												PRINT '  - @TP_SAI_ENT = '
															+ CAST(@TP_SAI_ENT AS VARCHAR(20));
												INSERT INTO #DADOS_PROD
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,RIGHT('00000000' + CAST(@CD_NF AS VARCHAR(8)), 8)
													,@TP_SAI_ENT
													,@CD_FILIAL
													,'1.0'
													,T1.CD_PROD                                         AS CD_PROD
													,T1.CD_IT
													,LEFT(ISNULL((SELECT TOP 1
																					CD_BARRA
																				FROM
																					EST_PROD_CD_BARRA
																				WHERE
																						 EST_PROD_CD_BARRA.CD_EMP                        = T1.CD_EMP
																				 AND EST_PROD_CD_BARRA.CD_PROD                       = T1.CD_PROD
																				 AND EST_PROD_CD_BARRA.NAO_EXPORTAR                  = 0
																				 AND EST_PROD_CD_BARRA.EAN_CAIXA_FECHADA=0 -- EAN VALIDO
																				 AND EST_PROD_CD_BARRA.FLAG_PRE_VENCIDOS = 0 
																				 AND EST_PROD_CD_BARRA.FLAG_NAO_ENVIA_NFE = 0
																				ORDER  BY
																				 GTIN DESC, CD_BARRA), 'SEM GTIN'), 14)                  AS CD_BARRA
													,LEFT(DBO.F_RETIRA_CARAC_ESPECIAIS(T1.DS_PROD), 60) AS DS_PROD
													,CASE WHEN T1.TP_MOV = 25 or T1.TP_MOV = 58 THEN LEFT(RIGHT('00000000'
																															 + RTRIM(LTRIM(ISNULL(T2.NR_NCM, 0))), 8), 8)
													 ELSE RIGHT('00000000'
																			+ RTRIM(LTRIM(ISNULL(T2.NR_NCM, 0))), 8)
													 END                                                AS CD_NCM
													,CD_NT_OP_FC                                        AS CD_FISCAL
													,UN
													,QT_IT
													,--- 4 CASAS DECIMAIS  
													VLR_IT
													,--- 4 CASAS DECIMAIS           
													( CASE WHEN FLAG_NF_PROD_TOTAL_BRUTO = 0 THEN VLR_TOT_IT
														 ELSE VLR_TOT_IT + VLR_DESC_IT
														 END )                                            AS VLR_TOT_IT
													,ISNULL((SELECT TOP 1
																		 CD_BARRA
																	 FROM
																		 EST_PROD_CD_BARRA
																	 WHERE
																				EST_PROD_CD_BARRA.CD_EMP                        = T1.CD_EMP
																		AND EST_PROD_CD_BARRA.CD_PROD                       = T1.CD_PROD
																		AND EST_PROD_CD_BARRA.NAO_EXPORTAR                  = 0
																		AND EST_PROD_CD_BARRA.EAN_CAIXA_FECHADA=0 -- EAN VALIDO
																		AND EST_PROD_CD_BARRA.FLAG_PRE_VENCIDOS = 0 
																		AND EST_PROD_CD_BARRA.FLAG_NAO_ENVIA_NFE = 0
																	 ORDER  BY
																		GTIN DESC, CD_BARRA), 'SEM GTIN')                            AS EAN_TRIB
													,UN                                                 AS UND_TRIB
													,QT_IT                                              AS QTDE_TRIB
													,( CASE WHEN QT_IT = 0 THEN 0
														ELSE ( CONVERT(float,( CASE WHEN FLAG_NF_PROD_TOTAL_BRUTO = 0 THEN VLR_TOT_IT
														ELSE VLR_TOT_IT + VLR_DESC_IT
														END )) / QT_IT )
														END ) AS VLR_UNIT_TRIB
													,---4 CASAS DECIMAIS  
													T1.VLR_PROD_FRETE                                   AS VLR_TOT_FRETE
													,---- 4 CASAS DECIMAIS  
													0                                                   AS VLR_TOT_SEGURO
													,VLR_DESC_IT
													,VLR_OUTROS_DESPESAS                                AS VLR_OUTRAS_DESPESAS
													,CASE WHEN T1.XPED_NOTA_FISCAL = '' THEN T3.XPED_PEDIDO 
														  ELSE T1.XPED_NOTA_FISCAL 
													  END AS XPED_NOTA_FISCAL
													,0                                                  AS NITEMPED
													,CASE WHEN T1.TP_MOV = 52 THEN 0 --nf adjudicacao
													 ELSE 1
													 END                                                AS DET_INDTOT
													,ISNULL(T1.NR_CEST,'') 								AS NR_CEST
																	   
										   
													,CASE WHEN T2.CTR_PMC = 1 AND @ACRES_LAB_SUBSTANCIA_DESC_PRODUTO_NFE = 1 THEN 'LAB : ' + ISNULL(EST_PROD_FABRIC.NM_FABRIC, '')  + ' - LISTA SUBSTANCIA: ' + ISNULL(EST_PROD_EST_SNGPC.CLASSIFICACAO, '') 
														  ELSE ''
																																								   
				 
				 
				
													  END  
												FROM
													V_EST_NF_SAI T1
													INNER JOIN EST_PROD T2 ON T1.CD_EMP  = T2.CD_EMP
																		  AND T1.CD_PROD = T2.CD_PROD
													LEFT JOIN EST_PROD_FABRIC ON T2.CD_FABRIC = EST_PROD_FABRIC.CD_FABRIC
													LEFT JOIN EST_PROD_EST_SNGPC ON EST_PROD_EST_SNGPC.CD_EMP  = T2.CD_EMP
																				AND EST_PROD_EST_SNGPC.CD_PROD = T2.CD_PROD
													LEFT JOIN EST_PED_VD T3 ON T1.CD_EMP	= T3.CD_EMP
																		   AND T1.CD_FILIAL = T3.CD_FILIAL
																		   AND T1.CD_PED	= T3.CD_PED
												
												WHERE
															CD_NF        = @CD_NF
													AND T1.CD_EMP    = 1
													AND T1.CD_FILIAL = @CD_FILIAL
												ORDER  BY
													T1.CD_PROD
												--DADOS DE TRANSPORTE    
												PRINT 'INSERINDO #DADOS_TRANSPORTE';
												INSERT INTO #DADOS_TRANSPORTE
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,TP_FRETE                                                                           AS MODA_FRETE
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CGC_CPF_TRANSP)), '/', ''), '-', ''), '.', '') AS CGC_CPF
													,LTRIM(LEFT(DS_TRANSP, 60))                                                         DS_TRANSP
													,REPLACE(REPLACE(REPLACE(INSC_EST_TRANSP, '/', ''), '-', ''), '.', '')              AS INSC_EST
													,LTRIM(LEFT(ENDE_TRANSP, 60))                                                       AS ENDE
													,REPLACE(LTRIM(LEFT(DS_CID_TRANSP, 60)), 'NAO INFORMADO', '')                       AS DS_CID
													,REPLACE(LEFT(LTRIM(RTRIM(REPLACE(UF_TRANSP, ' ', ''))), 2), 'SN', '')              AS UF
													,REPLACE(LEFT(PLACA_VEIC, 8), '-', '')                                              AS PLACA
													,LEFT(LTRIM(RTRIM(REPLACE(PLACA_UF, ' ', ''))), 2)                                  AS SIGLA_UF
													,''                                                                                 AS REG_NAC_TRANS
													,QT_VOL                                                                             AS QTDE_VOL
													,LEFT(ESPCIE_VOL, 60)                                                               AS ESP_VOL_TRANS
													,LEFT(MC_VOL, 60)                                                                   AS MARCA
													,ROUND(ISNULL(NR_VOL, 0), 0)                                                        AS NUM_VOL
													,PES_LIQ                                                                            AS PESO_LIQ
													,---3 CASAS DECIMAIS  
													PESO_BRT                                                                            AS PESO_BRUTO
													,--- 3 CASAS DECIMAIS  
													ISNULL(EMAIL_TRANSP, '')                                                            AS EMAIL_TRANSP
												FROM
													V_EST_NF_SAI_CAB
												WHERE
															CD_NF     = @CD_NF
													AND CD_EMP    = 1
													AND CD_FILIAL = @CD_FILIAL;
												--DADOS ADICIONAIS  
												PRINT 'INSERINDO #DADOS_ADICIONAIS';
												INSERT INTO #DADOS_ADICIONAIS
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS1, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS2, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS3, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS4, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS5, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS6, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS7, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS8, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS9, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' +
													 CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS10, ';', ''), ''))
													 COLLATE
														SQL_LATIN1_GENERAL_CP1_CI_AS + ' ' + CONVERT(VARCHAR(400), ISNULL(REPLACE(OBS11, ';', ''), '')) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS AS OBSERVACAO
													,XPED_NOTA_FISCAL
												FROM
													EST_NF_SAI
												WHERE
															CD_NF     = @CD_NF
													AND CD_EMP    = 1
													AND CD_FILIAL = @CD_FILIAL
												--DADOS IMPOSTOS ITEM  
												PRINT 'INSERINDO #DADOS_IMP_ITEM';
												INSERT INTO #DADOS_IMP_ITEM
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NOTA_FISCAL.CD_IT
													,NOTA_FISCAL.CD_TRIB_FC                                      AS MOD_TRIB
													,NOTA_FISCAL.ORIGEM_PRODUTO   AS ORIGEM
													,NOTA_FISCAL.CD_PROD
													,3                        AS MOD_BC_ICMS
													,ROUND(NOTA_FISCAL.VLR_BASE_ICMS_IT, 2) AS VLR_BASE_ICMS
													,ROUND(NOTA_FISCAL.VLR_ICMS_IT, 2) AS VLR_ICMS
													,NOTA_FISCAL.ALIQ_ICMS AS ALIQUOTA												
													 ,NOTA_FISCAL.VLR_CRED_BASE_SN
													,NOTA_FISCAL.VLR_CRED_ICMS_SN
													--,( CASE WHEN GLB_CID.UF <> NOTA_FISCAL.UF THEN ISNULL(EST_PROD_CPL_TRIB_INTER.VLR_PAUTA_IVA, 0)
													--	 ELSE EST_PROD_CPL_TRIB_EST.VLR_PAUTA_IVA
													--	 END )                                                  AS PERC_MARG
													,NOTA_FISCAL.PERC_IVA                                        AS PERC_MARG
													,NOTA_FISCAL.PERC_REDU_BASE_ICMS_IT                          PERC_REDU
													,NOTA_FISCAL.PERC_REDU_BASE_SUBS_IT                          PERC_REDU_ST
													,ROUND(NOTA_FISCAL.VLR_BASE_SUBS, 2)                         AS VLR_BASE_SUBS
													,ROUND(NOTA_FISCAL.VLR_ICMS_SUBS, 2)                         AS VLR_ICMS_SUBS
													,4                                                           AS MOD_BC_ICMS_ST
													,NOTA_FISCAL.ALIQ_ICMS_INTERNO                               AS ALIQ_ICMS_ST
													,VLR_BASE_ICMS_RET
													,NOTA_FISCAL.VLR_ICMS_RET
													,NOTA_FISCAL.VLR_ICMS_DESONERADO
													,NOTA_FISCAL.ICMS_DESONERADO_MOTIVO
                                                    ,NOTA_FISCAL.CD_BENEF_IT									 AS CBENEF
                                                    ,NOTA_FISCAL.CD_SIT_OPERA_SIMPLES_NACIO
													,NOTA_FISCAL.MODALI_BASE_ICMS_SUBST
													,NOTA_FISCAL.MODALI_BASE_ICMS
													,NOTA_FISCAL.PERC_ALIQ_COFINS
													,NOTA_FISCAL.PERC_ALIQ_PIS
													,NOTA_FISCAL.VLR_BASE_COFINS
													,NOTA_FISCAL.VLR_BASE_PIS
													,NOTA_FISCAL.VLR_PIS
													,NOTA_FISCAL.VLR_COFINS
													,999                                                         AS ENQUADRAMENTO
													,VLR_IPI_IT                                                  AS VLR_IPI
													,( CASE WHEN ISNULL(PERC_IPI, 0) = 0 THEN 0
														 ELSE NOTA_FISCAL.VLR_TOT_IT + NOTA_FISCAL.VLR_OUTROS_DESPESAS
														 END )                                                    AS VLR_BC_IPI
													,PERC_IPI                                                    AS ALIQ_IPI
													,NOTA_FISCAL.VLR_IT                                                      AS VLR_UND_TRIB
													,QT_IT                                                       AS QTDE_TOT_UND
													,99                                                          AS CST_IPI
													,RIGHT('00000000'
																 + RTRIM(LTRIM(ISNULL(NOTA_FISCAL.CST_PIS, 0))), 2)     AS CST_PIS
													,RIGHT('00000000'
																 + RTRIM(LTRIM(ISNULL(NOTA_FISCAL.CST_COFINS, 0))), 2)  AS CST_COFINS
													, NOTA_FISCAL.PERC_ICMS_DESTINO_DIFAL                               AS PERC_ICMS_INTRA
													, NOTA_FISCAL.PERC_ICMS_ORIGEM_DIFAL                                AS PERC_ICMS_INTER
													, NOTA_FISCAL.PERC_FECOP                                            AS PERC_FECOP
													, NOTA_FISCAL.BASE_ICMS_DESTINO_DIFAL                               AS BASE_DIFAL
													, 100               										        AS ICMS_INTER_PART
                                                    , NOTA_FISCAL.VLR_FECOP                                             AS VALOR_FECOP_DEST
													-- alteração realizada, em virtude da sefaz aceitar somente base dupla na validação
													, NOTA_FISCAL.VALOR_ICMS_DESTINO_DIFAL								AS VALOR_DIFAL_DEST
		   										    --,ROUND((VLR_BASE_ICMS_IT * (PERC_ICMS_DESTINO_DIFAL/100)) - (VLR_BASE_ICMS_IT * (PERC_ICMS_ORIGEM_DIFAL/100)),2) AS VALOR_DIFAL_DEST
													, 0                                                                 AS VALOR_DIFAL_ORIG
												,0 AS VLR_BASE_ICMS_OP
												,NOTA_FISCAL.VLR_ICMS_OP AS VLR_ICMS_OP
												,0 AS PERC_ICMS_OP
												,ISNULL(NOTA_FISCAL.PERC_DIFERIMENTO,0)
												,ISNULL(NOTA_FISCAL.VLR_ICMS_DIFERIMENTO,0)
												,PERC_FECOP_ST_IT        
												,VLR_BASE_FECOP_ST_IT          
												,VLR_FECOP_ST_IT                
												,VLR_BASE_FECOP_IT                 
												,VLR_FECOP_IT                        
												,VLR_BASE_FECOP_RET_IT                 
												,VLR_FECOP_RET_IT                     
												,PERC_FECOP_RET_IT                 
								   --	,NR_ITEM_XPED_IT     
												,CASE WHEN NOTA_FISCAL.NR_ITEM_XPED_IT = '' OR NOTA_FISCAL.NR_ITEM_XPED_IT IS NULL THEN CONVERT(varchar, TELEORCIT.XPED_IT)
													  ELSE CONVERT(varchar, NOTA_FISCAL.NR_ITEM_XPED_IT) 
												  END AS NR_ITEM_XPED												
												,ALIQUOTA_PST_IT               
												,VLR_BC_UF_DEST_IT
												,NOTA_FISCAL.PERC_ICMS_RET
                                                , CASE  WHEN
                                                   NOTA_FISCAL.FLAG_DEVOLUCAO = 1   THEN
                                                     VLR_IPI_IT 
                                                   ELSE
                                                     0  
                                                   END
                                                   AS VLR_IPI_DEVOL   
                                                ,CASE  WHEN
                                                     NOTA_FISCAL.FLAG_DEVOLUCAO = 1    THEN
                                                        (
                                                           SELECT        TOP 1                                                
                                                            (NOTA_FISCAL.QT_IT /  TENT.QT_IT) *100  AS PERC 
                                                          FROM 
                                                            EST_NF_ENT_NF_SAI AS TES 									
                                                          INNER JOIN EST_NF_ENT_IT AS TENT
                                                              ON TENT.CD_EMP = TES.CD_EMP
                                                              AND TENT.CD_FILIAL = TES.CD_FILIAL		 
                                                              AND TENT.CD_NF = TES.CD_NF_ENT	
                                                          WHERE  
                                                             TES.CD_EMP = 1
                                                             AND TES.CD_FILIAL   = NOTA_FISCAL.CD_FILIAL				
                                                             AND TES.CD_NF_SAI   =NOTA_FISCAL.CD_NF
                                                             AND TENT.CD_PROD	 =NOTA_FISCAL.CD_PROD
                                                          )
                                                       ELSE
                                                          0                                               
                                                    END AS PERC_IPI_DEVOL 
													----- ALTERAO WILSON 10/10/2019
                                                   ,(SELECT  
                                                        ROUND((
                                                             --CASE 
                                                             --   WHEN  I.VLR_BASE_ICMS > 0 THEN I.VLR_BASE_ICMS
                                                             --   WHEN  I.VLR_BASE_ICMS_RET > 0 THEN I.VLR_BASE_ICMS_RET
                                                             --  ELSE  I.VLR_BASE_SUBS
                                                             --END 
															 CASE 
                                                                WHEN  I.VLR_ICMS_IT > 0 THEN I.VLR_ICMS_IT
                                                                WHEN  I.VLR_ICMS_RET > 0 THEN I.VLR_ICMS_RET
                                                               ELSE  I.VLR_ICMS_SUBS
                                                             END 
                                                             / I.QT_IT),2)
                                                     FROM 
                                                          EST_NF_ENT_IT I                                                     
                                                     WHERE 
                                                        I.CD_EMP = 1
                                                        AND I.QT_IT> 0
                                                        AND  I.CD_FILIAL = NOTA_FISCAL.CD_FILIAL
                                                        AND  I.CD_PROD   =NOTA_FISCAL.CD_PROD
                                                        --PEGAR  AQUI A ULTIMA ENTRADA COM VALORES
                                                        AND  I.CD_NF = (
                                                                SELECT  
                                                                   MAX( CD_NF)
                                                                FROM
                                                                   EST_NF_ENT_IT  IX
                                                                INNER JOIN EST_TRIB TX ON
                                                                   IX.CD_TRIB=TX.CD_TRIB  
                                                                WHERE
                                                                  IX.CD_EMP = I.CD_EMP
                                                                  AND IX.CD_FILIAL   =I.CD_FILIAL
                                                                  AND IX.CD_PROD     =I.CD_PROD
                                                                  AND( (IX.VLR_BASE_SUBS  > 0  AND TX.SUBS_TRIB=1)
                                                                  OR (IX.VLR_BASE_ICMS_RET  > 0 AND TX.SUBS_TRIB_ANT=1)   
                                                            )                        
                                                         )
													)  * NOTA_FISCAL.QT_IT AS  VLR_ICMS_SUBSTITUTO ,
													---------
													CASE WHEN NOTA_FISCAL.TIPO_TRANSACAO = 1 THEN 
														NOTA_FISCAL.ALIQ_EFET_ICMS_IT 
                                                    ELSE 
														0 
													END AS  ALIQ_EFET_ICMS_IT,

													
													CASE WHEN NOTA_FISCAL.TIPO_TRANSACAO = 1 THEN 
														NOTA_FISCAL.PERC_EFET_RED_BASE_CALC_IT 
                                                    ELSE 
														0 
													END AS  PERC_EFET_RED_BASE_CALC_IT,
													
													CASE WHEN NOTA_FISCAL.TIPO_TRANSACAO = 1 THEN 
														NOTA_FISCAL.VLR_EFET_BASE_ICMS_IT 
                                                    ELSE 
														0 
													END AS  VLR_EFET_BASE_ICMS_IT,
													
												   CASE WHEN NOTA_FISCAL.TIPO_TRANSACAO = 1 THEN 
														NOTA_FISCAL.VLR_EFET_ICMS_IT
                                                    ELSE 
														0 
													END AS  VLR_EFET_ICMS_IT 
												FROM
													V_EST_NF_SAI NOTA_FISCAL
													INNER JOIN PRC_FILIAL ON
																NOTA_FISCAL.CD_EMP    = PRC_FILIAL.CD_EMP
														AND NOTA_FISCAL.CD_FILIAL = PRC_FILIAL.CD_FILIAL
													INNER JOIN GLB_CID ON
														PRC_FILIAL.CD_CID = GLB_CID.CD_CID
													LEFT JOIN EST_PROD_CPL_TRIB_EST ON
																NOTA_FISCAL.CD_EMP           = EST_PROD_CPL_TRIB_EST.CD_EMP
														AND NOTA_FISCAL.CD_FILIAL        = EST_PROD_CPL_TRIB_EST.CD_FILIAL
														AND NOTA_FISCAL.CD_PROD          = EST_PROD_CPL_TRIB_EST.CD_PROD
														AND NOTA_FISCAL.CD_TRIB          = EST_PROD_CPL_TRIB_EST.CD_TRIB
														AND NOTA_FISCAL.TP_MOV           = EST_PROD_CPL_TRIB_EST.TP_MOV
														AND NOTA_FISCAL.TP_MOV_OP_TRANSA = EST_PROD_CPL_TRIB_EST.TP_TRANSA
													/*LEFT JOIN EST_PROD_CPL_TRIB_INTER ON
														NOTA_FISCAL.CD_EMP                                   = EST_PROD_CPL_TRIB_INTER.CD_EMP
														AND NOTA_FISCAL.CD_FILIAL                                = EST_PROD_CPL_TRIB_INTER.CD_FILIAL
														AND NOTA_FISCAL.CD_PROD                                  = EST_PROD_CPL_TRIB_INTER.CD_PROD
														AND NOTA_FISCAL.CD_TRIB                                  = EST_PROD_CPL_TRIB_INTER.CD_TRIB
														AND NOTA_FISCAL.TP_MOV                                   = EST_PROD_CPL_TRIB_INTER.TP_MOV
														AND NOTA_FISCAL.TP_MOV_OP_TRANSA                         = EST_PROD_CPL_TRIB_INTER.TP_TRANSA
														AND NOTA_FISCAL.UF COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = EST_PROD_CPL_TRIB_INTER.UF COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
													LEFT JOIN EST_NF_SAI_DAR DAR ON 
														NOTA_FISCAL.CD_EMP           = DAR.CD_EMP
														AND NOTA_FISCAL.CD_FILIAL        = DAR.CD_FILIAL
														AND NOTA_FISCAL.CD_NF          = DAR.CD_NF
														AND DAR.UF_DESTINO = 1
													LEFT JOIN EST_NF_SAI_DAR_IT DAR_IT ON
														DAR.CD_EMP           = DAR_IT.CD_EMP
														AND DAR.CD_FILIAL        = DAR_IT.CD_FILIAL
														AND DAR.CD_NF          = DAR_IT.CD_NF
														AND DAR.CD_DAR          = DAR_IT.CD_DAR
														AND NOTA_FISCAL.CD_PROD = DAR_IT.CD_PROD
													OUTER APPLY UFD_EST_PROD_TRIB(NOTA_FISCAL.CD_EMP,
														NOTA_FISCAL.CD_FILIAL,
														NOTA_FISCAL.CD_MOV_OP,
														NOTA_FISCAL.CD_CLI,
														NOTA_FISCAL.CD_PROD,0,0,0,0,0,0,0,0,0,NOTA_FISCAL.TIPO_TRANSACAO,0)  A*/
														LEFT JOIN EST_PED_VD T3 ON NOTA_FISCAL.CD_EMP	= T3.CD_EMP
																		       AND NOTA_FISCAL.CD_FILIAL = T3.CD_FILIAL
																		       AND NOTA_FISCAL.CD_PED	= T3.CD_PED
														
														LEFT JOIN TELE_ORC_EST_PED_VD TELEORCPED ON NOTA_FISCAL.CD_EMP = TELEORCPED.CD_EMP
																								AND NOTA_FISCAL.CD_FILIAL = TELEORCPED.CD_FILIAL																								
																								AND NOTA_FISCAL.CD_PED = TELEORCPED.CD_PED
																								
														LEFT JOIN TELE_ORC	TELEORC ON	TELEORC.CD_FILIAL = TELEORCPED.CD_FILIAL
																						AND TELEORC.CD_EMP = TELEORCPED.CD_EMP
																						AND TELEORC.CD_ORC = TELEORCPED.CD_ORC
														
														LEFT JOIN TELE_ORC_ITEM TELEORCIT ON TELEORCIT.CD_ORC = TELEORC.CD_ORC
																						
												WHERE
													NOTA_FISCAL.CD_NF     = @CD_NF
													AND NOTA_FISCAL.CD_EMP    = 1
													AND NOTA_FISCAL.CD_FILIAL = @CD_FILIAL
												ORDER  BY
													NOTA_FISCAL.CD_PROD;
													
													--SELECT * FROM #DADOS_IMP_ITEM;
													
												--DADOS DUPLICATAS  
												PRINT 'INSERINDO #DADOS_DUP';
												INSERT INTO #DADOS_DUP
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,CAST(RC_DEB.NR_DP AS VARCHAR(10)) + '-'
													 + CAST((ROW_NUMBER() OVER (ORDER BY RC_DEB.NR_DP)) AS VARCHAR(10))
													,RC_DEB.DT_VENCTO
													,RC_DEB.VLR_DP
												FROM
													RC_DEB_EST_NF_SAI
													INNER JOIN RC_DEB ON
																RC_DEB_EST_NF_SAI.CD_EMP    = RC_DEB.CD_EMP
														AND RC_DEB_EST_NF_SAI.CD_FILIAL = RC_DEB.CD_FILIAL
														AND RC_DEB_EST_NF_SAI.NR_DP     = RC_DEB.NR_DP
														AND RC_DEB_EST_NF_SAI.ANO       = RC_DEB.ANO
												WHERE
															RC_DEB_EST_NF_SAI.CD_NF     = @CD_NF
													AND RC_DEB_EST_NF_SAI.CD_EMP    = 1
													AND RC_DEB_EST_NF_SAI.CD_FILIAL = @CD_FILIAL
												--------------    
												PRINT 'INSERINDO #DADOS_IMP_NF';
												INSERT INTO #DADOS_IMP_NF
												SELECT TOP 1
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,VLR_BASE_ICMS                      AS BASE_CAL_ICMS
													,A.VLR_ICMS                         AS VLR_ICMS
													,A.VLR_BASE_SUBS_CAB                AS BASE_CAL_ICMS_ST
													,A.VLR_ICMS_SUBS_CAB                AS VLR_TOT_ICMS_ST
													,VLR_TOT_PROD		                
													,VLR_FRETE                          AS VLR_TOT_FRETE
													,0                                  AS VLR_TOT_SEG
													,VLR_DESC_CORPO                     AS VLR_DESC_CORPO
													,0                                  AS VLR_TOT_II
													,VLR_IPI                            AS VLR_TOT_IPI
													,B.VLR_PIS                          AS VLR_PIS
													,B.VLR_COFINS                       AS VLR_COFINS
													,B.VLR_OUTROS_DESPESAS              AS VLR_OUTRAS_DES
													,VLR_NF
													,VLR_ICMS_DESONERADO_CAB            AS VLR_ICMS_DESONERADO
													,ISNULL(C.DIFAL_RECOLHER_ORIG,0)    AS VLR_TOT_DIFAL_ORIG
													,ISNULL(C.DIFAL_RECOLHER_DEST ,0)   AS VLR_TOT_DIFAL_DEST
													,0							        AS VLR_TOT_FECOP_DEST                                                
													,ISNULL(VLR_FECOP_ST ,0)            AS VLR_FECOP_ST                                                
													,ISNULL(VLR_FECOP ,0)               AS VLR_FECOP
													,ISNULL(VLR_FECOP_RET ,0)           AS VLR_FECOP_RET
                                                    , CASE  WHEN FLAG_DEVOLUCAO = 1  THEN VLR_IPI 
                                                            ELSE 0  
                                                       END 								AS VLR_TOT_IPI_DEVOL 
													, VLR_BASE_CALC_EFET
													, VLR_EFET_ICMS
												FROM
													V_EST_NF_SAI_CAB A
													INNER JOIN (SELECT
																	CD_EMP
																	,CD_FILIAL
																	,CD_NF
																	,SUM(ROUND(ISNULL(VLR_PIS,0), 2))             AS VLR_PIS
																	,SUM(ROUND(ISNULL(VLR_COFINS,0), 2))          AS VLR_COFINS
																	,SUM(ROUND(ISNULL(VLR_OUTROS_DESPESAS,0), 2)) AS VLR_OUTROS_DESPESAS
																FROM
																	EST_NF_SAI_IT
																GROUP  BY
																	CD_EMP
																	,CD_FILIAL
																	,CD_NF) B ON
														A.CD_EMP        = B.CD_EMP
														AND A.CD_FILIAL = B.CD_FILIAL
														AND A.CD_NF     = B.CD_NF
													LEFT OUTER JOIN V_EST_NF_SAI_CAB_DIFAL_FECOP C ON
														A.CD_EMP        = C.CD_EMP
														AND A.CD_FILIAL = C.CD_FILIAL
														AND A.CD_NF     = C.CD_NF
												WHERE
													A.CD_NF         = @CD_NF
													AND A.CD_EMP    = 1
													AND A.CD_FILIAL = @CD_FILIAL
											;WITH FECOP AS 
											(SELECT 
												CD_FILIAL as CD_FILIAL,
												CD_NF as CD_NF,
												SUM (VALOR_FECOP_DEST) as VALOR_FECOP_DEST
											  FROM 
												 #DADOS_IMP_ITEM
												 GROUP BY CD_FILIAL,
														 CD_NF    )
											  UPDATE NF
												SET
												   VLR_TOT_FECOP_DEST = CTE.VALOR_FECOP_DEST
											  FROM #DADOS_IMP_NF NF
												  INNER JOIN FECOP CTE ON NF.CD_FILIAL = CTE.CD_FILIAL
																	 AND NF.CD_NF = CTE.CD_NF;
												--------------  

												--DADOS LOTES ITENS  
												PRINT 'INSERINDO #DADOS_LOTE_PROD';
												INSERT INTO #DADOS_LOTE_PROD
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NOTAFISCAL.CD_PROD
													,NOTAFISCAL.CD_IT
													,LEFT(EST_PROD_CPL_LOTE.NR_LOTE, 20) AS NR_LOTE
													,NOTAFISCAL_LOTE.QTDE_PROD
													,EST_PROD_CPL_LOTE.DT_FABR
													,EST_PROD_CPL_LOTE.DT_VALID
													,EST_PROD_PRECO.VLR_TABELA
													,LEFT(COALESCE( LOTE_SNGPC.REGISTRO_MS,PRODUTO.NR_REG_MS), 25) AS  REGISTRO_MS
													,LOTE_SNGPC.CD_DCB
													,EST_PROD_CPL_LOTE.CD_AGREGADOR
													, CASE WHEN PRODUTO.NR_REG_MS = 'ISENTO' AND MOT_ISENCAO_ANVISA.DS_MOT_ISENCAO_ANVISA IS NOT NULL THEN MOT_ISENCAO_ANVISA.DS_MOT_ISENCAO_ANVISA ELSE '' END AS MOT_ISENCAO_ANVISA
													, CASE WHEN NOTAFISCAL.OBS_CONTRIBUINTE IS NOT NULL THEN NOTAFISCAL.OBS_CONTRIBUINTE ELSE NULL END AS OBS_CONTRIBUINTE 
													, CASE WHEN NOTAFISCAL.OBS_FISCO IS NOT NULL  THEN NOTAFISCAL.OBS_FISCO ELSE NULL END AS OBS_FISCO 
													,CASE 
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30010000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30019999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30020000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30029999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30030000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30039999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30040000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30049999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30050000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30059999 THEN 1
														WHEN ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) >= 30060000 AND ISNULL(CAST(INFORMA_LOTE_NFE.NR_NCM AS INTEGER),0) <= 30069999 THEN 1
														WHEN LEFT(EST_PROD_CPL_LOTE.NR_LOTE, 20) IS NOT NULL THEN 1
														ELSE 0
													END	AS DET_MED
												FROM
													EST_NF_SAI_IT NOTAFISCAL
													LEFT JOIN EST_NF_SAI_IT_EST_PROD_CPL_LOTE NOTAFISCAL_LOTE ON
																NOTAFISCAL.CD_EMP    = NOTAFISCAL_LOTE.CD_EMP
														AND NOTAFISCAL.CD_FILIAL = NOTAFISCAL_LOTE.CD_FILIAL
														AND NOTAFISCAL.CD_NF     = NOTAFISCAL_LOTE.CD_NF
														AND NOTAFISCAL.CD_PROD   = NOTAFISCAL_LOTE.CD_PROD
													LEFT JOIN EST_PROD_EST_SNGPC  LOTE_SNGPC ON
														NOTAFISCAL.CD_EMP        = LOTE_SNGPC.CD_EMP											
														AND NOTAFISCAL.CD_PROD   = LOTE_SNGPC.CD_PROD  
													LEFT JOIN EST_PROD_CPL_LOTE ON
																NOTAFISCAL_LOTE.CD_EMP    = EST_PROD_CPL_LOTE.CD_EMP
														AND NOTAFISCAL_LOTE.CD_FILIAL = EST_PROD_CPL_LOTE.CD_FILIAL
														AND NOTAFISCAL_LOTE.CD_LOTE   = EST_PROD_CPL_LOTE.CD_LOTE
														AND NOTAFISCAL_LOTE.CD_PROD   = EST_PROD_CPL_LOTE.CD_PROD
													INNER JOIN EST_PROD_PRECO ON
																NOTAFISCAL.CD_EMP    = EST_PROD_PRECO.CD_EMP
														AND NOTAFISCAL.CD_FILIAL = EST_PROD_PRECO.CD_FILIAL
														AND NOTAFISCAL.CD_PROD   = EST_PROD_PRECO.CD_PROD
													INNER JOIN EST_PROD PRODUTO ON
														NOTAFISCAL.CD_EMP    = PRODUTO.CD_EMP													
														AND NOTAFISCAL.CD_PROD   = PRODUTO.CD_PROD
													LEFT JOIN MOT_ISENCAO_ANVISA ON
															PRODUTO.CD_MOT_ISENCAO_ANVISA = MOT_ISENCAO_ANVISA.CD_MOT_ISENCAO_ANVISA
													LEFT JOIN INFORMA_LOTE_NFE ON 
														NOTAFISCAL.CD_PROD = INFORMA_LOTE_NFE.CD_PROD
												
												WHERE
														NOTAFISCAL.CD_EMP    = 1
													AND NOTAFISCAL.CD_FILIAL = @CD_FILIAL
													AND NOTAFISCAL.CD_NF     = @CD_NF
												ORDER  BY
													NOTAFISCAL.CD_PROD
										END --IF (@TP_SAI_ENT = 1)  
							END --IF (@TP_LOTE = 0) OR (@TP_LOTE = 3)  
						IF ( @TP_LOTE = 1 ) --CANCELAMENTO  
							BEGIN
									IF @TP_SAI_ENT = 0 --ENTRADA  
										BEGIN
												PRINT 'INSERINDO #DADOS_CANCELAMENTO';
												INSERT INTO #DADOS_CANCELAMENTO
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NF_CANCEL.CD_MT_CANCEL
													,NF_CANCEL.DS_MT_CANCEL
													,NFE.NR_AUTORIZADOR
													,NFE.NR_PROTOCOLO
												FROM
													EST_NF_ENT NF
													INNER JOIN EST_NF_ENT_NFE NFE ON
															NF.CD_EMP    = NFE.CD_EMP
														AND NF.CD_FILIAL = NFE.CD_FILIAL
														AND NF.CD_NF     = NFE.CD_NF
													LEFT JOIN GLB_MT_CANCEL_NF NF_CANCEL ON
														NF.CD_MT_CANCEL = NF_CANCEL.CD_MT_CANCEL
												WHERE
														NF.CD_EMP    = 1
													AND NF.CD_FILIAL = @CD_FILIAL
													AND NF.CD_NF     = @CD_NF
										END -- IF @TP_SAI_ENT = 0  
									IF @TP_SAI_ENT = 1 --SAIDA  
										BEGIN
												PRINT 'INSERINDO #DADOS_CANCELAMENTO';
												INSERT INTO #DADOS_CANCELAMENTO
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,NF_CANCEL.CD_MT_CANCEL
													,NF_CANCEL.DS_MT_CANCEL
													,NFE.NR_AUTORIZADOR
													,NFE.NR_PROTOCOLO
												FROM
													EST_NF_SAI NF
													INNER JOIN EST_NF_SAI_NFE NFE ON
															NF.CD_EMP    = NFE.CD_EMP
														AND NF.CD_FILIAL = NFE.CD_FILIAL
														AND NF.CD_NF     = NFE.CD_NF
													LEFT JOIN GLB_MT_CANCEL_NF NF_CANCEL ON
														NF.CD_MT_CANCEL = NF_CANCEL.CD_MT_CANCEL
												WHERE
														NF.CD_EMP    = 1
													AND NF.CD_FILIAL = @CD_FILIAL
													AND NF.CD_NF     = @CD_NF
										END -- IF @TP_SAI_ENT = 1  
							END -- IF (@TP_LOTE = 1)  
						IF ( @TP_LOTE = 2 ) --CARTA DE CORREO          
							BEGIN
									IF ( @TP_SAI_ENT = 0 ) --ENTRADA          
										BEGIN
												PRINT 'INSERINDO #DADOS_CARTA_CORRECAO';
												INSERT INTO #DADOS_CARTA_CORRECAO
												SELECT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,@TP_SAI_ENT
													,NF.NR_NF
													,LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, CONVERT(CHAR(10), cc.DT_ARQUIVO, 120) + ' '
																																				 + CONVERT(CHAR(5), cc.HORA_ARQUIVO, 114), 126), 'T', ''), '-', ''), ':', ''), 14) AS DH_EVENTO
													,NFE.NR_AUTORIZADOR
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(PRC_FILIAL.CGC)), '/', ''), '-', ''), '.', '')                                              AS CGC_ORIGEM
													,UPPER(DBO.F_RETIRA_CARAC_ESPECIAIS(CC.DS_MOTIVO))                                                                               DS_MOTIVO
													,CC.CD_CTR_CARTA_CORRECAO
													,CC_NF.NR_CARTA
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(PRC_FILIAL.CGC)), '/', ''), '-', ''), '.', '')                                              AS CGC_DESTINO
													,PRC_FILIAL.CD_FILIAL                                                                                                            AS CD_CLI
													,LEFT(PRC_FILIAL.RZ_FILIAL, 80)                                                                                                  AS NM_DEST
												FROM
													EST_NFE_CARTA_CORRECAO CC
													INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NFE_LOTE LOTE_CC ON
														LOTE_CC.CD_CTR_CARTA_CORRECAO = CC.CD_CTR_CARTA_CORRECAO
													INNER JOIN EST_NFE_LOTE LOTE ON
														LOTE_CC.CD_NFE_LOTE = LOTE.CD_NFE_LOTE
													INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_ENT CC_NF ON
														CC_NF.CD_CTR_CARTA_CORRECAO = CC.CD_CTR_CARTA_CORRECAO
													INNER JOIN V_EST_NF_ENT_CAB NF ON
																NF.CD_EMP    = CC_NF.CD_EMP
														AND NF.CD_FILIAL = CC_NF.CD_FILIAL
														AND NF.CD_NF     = CC_NF.CD_NF
													INNER JOIN EST_NF_ENT_NFE NFE ON
																NF.CD_EMP    = NFE.CD_EMP
														AND NF.CD_FILIAL = NFE.CD_FILIAL
														AND NF.CD_NF     = NFE.CD_NF
													INNER JOIN PRC_FILIAL ON
														NF.CD_FILIAL = PRC_FILIAL.CD_FILIAL
												WHERE
															NF.CD_EMP           = 1
													AND NF.CD_FILIAL        = @CD_FILIAL
													AND NF.CD_NF            = @CD_NF
													AND LOTE_CC.CD_NFE_LOTE = @NR_LOTE
										END
									IF ( @TP_SAI_ENT = 1 ) --SAIDA          
										BEGIN
												PRINT 'INSERINDO #DADOS_CARTA_CORRECAO';
												INSERT INTO #DADOS_CARTA_CORRECAO
												SELECT DISTINCT
													@CD_NFE_LOTE
													,@CD_NFE_LOTE_IT
													,@CD_FILIAL
													,@CD_NF
													,@TP_SAI_ENT
													,NF.NF_NF
													,LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, CONVERT(CHAR(10), cc.DT_ARQUIVO, 120) + ' '
																																				 + CONVERT(CHAR(5), cc.HORA_ARQUIVO, 114), 126), 'T', ''), '-', ''), ':', ''), 14) AS DH_EVENTO
													,NFE.NR_AUTORIZADOR
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(PRC_FILIAL.CGC)), '/', ''), '-', ''), '.', '')                                              AS CGC_ORIGEM
													,UPPER(DBO.F_RETIRA_CARAC_ESPECIAIS(CC.DS_MOTIVO))                                                                               DS_MOTIVO
													,CC.CD_CTR_CARTA_CORRECAO
													,CC_NF.NR_CARTA
													,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(CGC_CPF)), '/', ''), '-', ''), '.', '')                                                     AS CGC_DESTINO
													,NF.CD_CLI                                                                                                                       AS CD_CLI
													,LEFT(NM_DEST, 80)                                                                                                               AS NM_DEST
												FROM
													EST_NFE_CARTA_CORRECAO CC
													INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NFE_LOTE LOTE_CC ON
														LOTE_CC.CD_CTR_CARTA_CORRECAO = CC.CD_CTR_CARTA_CORRECAO
													INNER JOIN EST_NFE_LOTE LOTE ON
														LOTE_CC.CD_NFE_LOTE = LOTE.CD_NFE_LOTE
													INNER JOIN EST_NFE_CARTA_CORRECAO_EST_NF_SAI CC_NF ON
														CC_NF.CD_CTR_CARTA_CORRECAO = CC.CD_CTR_CARTA_CORRECAO
													INNER JOIN V_EST_NF_SAI_CAB NF ON
																NF.CD_EMP    = CC_NF.CD_EMP
														AND NF.CD_FILIAL = CC_NF.CD_FILIAL
														AND NF.CD_NF     = CC_NF.CD_NF
													INNER JOIN EST_NF_SAI_NFE NFE ON
																NF.CD_EMP    = NFE.CD_EMP
														AND NF.CD_FILIAL = NFE.CD_FILIAL
														AND NF.CD_NF     = NFE.CD_NF
													INNER JOIN PRC_FILIAL ON
														NF.CD_FILIAL = PRC_FILIAL.CD_FILIAL
												WHERE
															NF.CD_EMP           = 1
													AND NF.CD_FILIAL        = @CD_FILIAL
													AND NF.CD_NF            = @CD_NF
													AND LOTE_CC.CD_NFE_LOTE = @NR_LOTE
										END
							END
						IF ( @TP_LOTE = 4 ) --MANIFESTO          
							BEGIN
									INSERT INTO #DADOS_MANIFESTO
									SELECT
										DANFE.NR_AUTORIZADOR
										,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(PRC_FILIAL.CGC)), '/', ''), '-', ''), '.', '')
										,DT_ENT
										,0 AS TP_EVENT
									FROM
										EST_NF_ENT_DANFE_PROVISORIO DANFE
										INNER JOIN V_EST_NF_ENT_CAB NF ON
												DANFE.CD_EMP    = NF.CD_EMP
											AND DANFE.CD_FILIAL = NF.CD_FILIAL
											AND DANFE.NR_NF     = NF.NR_NF
											AND DANFE.CD_FORN   = NF.CD_FORN
										INNER JOIN PRC_FILIAL ON
												NF.CD_EMP    = PRC_FILIAL.CD_EMP
											AND NF.CD_FILIAL = PRC_FILIAL.CD_FILIAL
									WHERE
											DANFE.CD_EMP    = 1
										AND DANFE.CD_FILIAL = @CD_FILIAL
										AND NF.CD_NF        = @CD_NF
							END
						IF ( @TP_LOTE = 5 ) --INUTILIZAO          
							BEGIN
									INSERT INTO #DADOS_INUTILIZACAO
									SELECT
										INU.CD_INU
										,REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(PRC_FILIAL.CGC)), '/', ''), '-', ''), '.', '')
										,RIGHT(INU.ANO, 2)
										,INU.CD_MODELO AS MODELO
										,INU.CD_NF_SERIE
										,INU.NR_NF
										,MOT.DS_MT_CANCEL
									FROM
										EST_NF_NFE_INU INU
										INNER JOIN PRC_FILIAL ON
												INU.CD_EMP    = PRC_FILIAL.CD_EMP
											AND INU.CD_FILIAL = PRC_FILIAL.CD_FILIAL
										INNER JOIN GLB_MT_CANCEL_NF MOT ON
											INU.CD_MOTIVO = MOT.CD_MT_CANCEL
										INNER JOIN EST_NFE_LOTE_EST_NF_NFE_INU LOTE_INU ON
											LOTE_INU.CD_INU = INU.CD_INU
										INNER JOIN EST_NFE_LOTE LOTE ON
											LOTE_INU.CD_NFE_LOTE = LOTE.CD_NFE_LOTE
									WHERE
											LOTE.CD_EMP      = 1
										AND LOTE.CD_FILIAL   = @CD_FILIAL
										AND LOTE.CD_NFE_LOTE = @CD_NFE_LOTE
							END
						FETCH NEXT FROM NOTAS INTO  @CD_NFE_LOTE,
													@CD_NFE_LOTE_IT,
													@CD_FILIAL,
													@CD_NF,
													@TP_LOTE,
													@TP_SAI_ENT,
													@REG_CONTG
				END
			--FECHANDO CURSOS  
			CLOSE NOTAS;
			--DESALOCANDO CURSOS DA MEMORIA  
			DEALLOCATE NOTAS;
			IF ( @TP_LOTE = 0 )
					OR ( @TP_LOTE = 3 ) --ENTRADAS/SAIDAS  
				BEGIN
						IF NOT EXISTS(SELECT 1 FROM #DADOS_PROD)
							BEGIN
								PRINT 'Nenhum produto retornado.'
								RETURN
							END
						SELECT DISTINCT
							--CHAVES  
							 DADOS_PROD.CD_NFE_LOTE																						AS CD_NFE_LOTE
							,DADOS_PROD.CD_NFE_LOTE_IT																					AS CD_NFE_LOTE_IT
							,DADOS_PROD.CD_FILIAL																						AS CD_FILIAL
							,DADOS_PROD.CD_NF																							AS CD_NF
							,DADOS_PROD.NR																								AS NR
							,DADOS_PROD.VTIPONOTAPROCESSAMENTO																			AS VTIPONOTAPROCESSAMENTO
							,DADOS_PROD.FILIAL																							AS FILIAL
							,DADOS_PROD.VS																								AS VS
							
							--CABECALHO  
							,NF_01.UF                                                                                                   AS IDE_CUF
							,NF_01.DS_MOV_OP                                                                                            AS IDE_NATOP
							,NF_01.FORMA_PAG                                                                                            AS IDE_INDPAG
							,NF_01.MODELO                                                                                               AS IDE_MOD
							,(CASE WHEN CHARINDEX('-', NF_01.SERIE) > 0 THEN RIGHT('000'
									 + SUBSTRING(NF_01.SERIE, CHARINDEX('-', NF_01.SERIE)+1, LEN(NF_01.SERIE)), 3)
								ELSE RIGHT('000' + NF_01.SERIE, 3)
							   END)																										AS IDE_SERIE
							,NF_01.NF_NF                                                                                                AS IDE_NNF
							,NF_01.DT_EMI_NF                                                                                            AS IDE_DEMI
							,NF_01.DT_SAIDA                                                                                             AS IDE_DSAIENT
							,NF_01.TP_DOC_FISCAL                                                                                        AS IDE_TPNF
							,NF_01.CD_IBGE                                                                                              AS IDE_CMUNFG
							,NF_01.FIN_NFE                                                                                              AS IDE_FINNEF
							,NF_01.FOR_IMP                                                                                              AS IDE_TPIMP
							,NF_01.REG_CONTG                                                                                            AS IDE_TPEMIS
							,NF_01.IND_PRES																								AS IDE_IND_PRES
							,NF_01.IND_INTERMED																							AS IDE_IND_INTERMED
							,(CASE WHEN NF_01.IND_PRES=9 THEN 0
		 
								ELSE 1
		 
							 END)					  
							,0                                                                                                          AS IDE_PROCEMI
							,0                                                                                                          AS IDE_CDV
			 
							,1																											AS IDE_TPAMB
							,(CASE 
								WHEN DADOS_DEST.CD_PAIS_SINTEGRA = 1058 THEN 
								(CASE 
									WHEN DADOS_EMIT.UF = DADOS_DEST.UF THEN 1
									ELSE (CASE 
											WHEN NF_01.TIPO_TRANSACAO=1 THEN 1
					 
											ELSE 2
					 
										 END)
								END)
								ELSE 3
		  
							END)																										AS IDE_ID_DEST
							
							--DADOS DE CUPONS VINCULADOS A NOTA FISCAL  
							,ISNULL(NF_01.FIN_NFE, 0)                                                                                   AS IDE_FINNFE
							,(CASE WHEN ISNULL(NF_01.NF_REF, '') <> '' THEN NF_01.NF_REF COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS 
								ELSE PDV_VD_NFCE.CH_NFE COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS  
							  END)																										AS IDE_REFNFECHAVE
							,ISNULL(NF_01.IDE_ECF_NMOD, '')                                                                             AS IDE_ECF_NMOD
							,ISNULL(NF_01.IDE_ECF_NECF, '')                                                                             AS IDE_ECF_NECF
							,ISNULL(RIGHT(NF_01.IDE_ECF_NCOO,6), '')                                                                    AS IDE_ECF_NCOO
							,NF_01.IDE_NF_1_MOD																							AS IDE_NF_1_MOD
							,NF_01.IDE_NF_1_UF																							AS IDE_NF_1_UF
							,NF_01.IDE_NF_1_ANO_MES																						AS IDE_NF_1_ANO_MES
							,NF_01.IDE_NF_1_CNPJ																						AS IDE_NF_1_CNPJ
							,NF_01.IDE_NF_1_SERIE																						AS IDE_NF_1_SERIE
							,NF_01.IDE_NF_1_NR_NF																						AS IDE_NF_1_NR_NF
							,NF_RESPTECNICO.CNPJ_RESPTEC                                                                                AS IDE_CNPJ_RESPTEC 
                            ,NF_RESPTECNICO.CONTATO_RESPTEC															                    AS IDE_CONTATO_RESPTEC
                            ,NF_RESPTECNICO.EMAIL_RESPTEC																				AS IDE_EMAIL_RESPTEC
                            ,NF_RESPTECNICO.FONE_RESPTEC																				AS IDE_FONE_RESPTEC                    
							--DADOS DE CONTIGENCIA REFERENTE A NOTA FISCAL  
							,ISNULL(JUSTIFICATIVA, '')                                                                                  AS IDE_XJUST
							,ISNULL(DHCONT, '')                                                                                         AS IDE_DHCONT
							,'ITEC BRAZIL 12.11'                                                                                        AS IDE_VERPROC
							
							--DADOS TAG AUTXML
							,NF_01.AUTXML                                                                                               AS AUTXML
							
							--DADOS EMITENTE  
							,DADOS_EMIT.CGC                                                                                             AS EMIT_CNPJ
							,DADOS_EMIT.RZ_FILIAL                                                                                       AS EMIT_XNOME
							,DADOS_EMIT.NM_FANT                                                                                         AS EMIT_XFANT
							,DADOS_EMIT.ENDE                                                                                            AS EMIT_XLGR
							,DADOS_EMIT.NUMERO                                                                                          AS EMIT_NRO
							,DADOS_EMIT.BAIRRO                                                                                          AS EMIT_XBAIRRO
							,DADOS_EMIT.CD_IBGE                                                                                         AS EMIT_CMUN
							,DADOS_EMIT.DS_CID                                                                                          AS EMIT_XMUN
							,DADOS_EMIT.UF                                                                                              AS EMIT_UF
							,DADOS_EMIT.CEP                                                                                             AS EMIT_CEP
							,DADOS_EMIT.CD_PAIS                                                                                         AS EMIT_CPAIS
							,DADOS_EMIT.NM_PAIS                                                                                         AS EMIT_XPAIS
							,DADOS_EMIT.TEL                                                                                             AS EMIT_FONE
							,DADOS_EMIT.INSC_EST                                                                                        AS EMIT_IE
							,DADOS_EMIT.INSC_SUBS                                                                                       AS EMIT_IEST
							,DADOS_EMIT.INSC_MUNI                                                                                       AS EMIT_IM
					   		,RIGHT('0000000' + ISNULL(DADOS_EMIT.CNAE_FISCAL, ''), 7)                                                   AS EMIT_CNAE
							,(CASE WHEN DADOS_EMIT.CRT = 1 THEN 1 WHEN DADOS_EMIT.CRT = 2 THEN 2 ELSE 3 END)															AS EMIT_CRT
							,DADOS_EMIT.COMPLEMENTO                                                                                     AS EMIT_XCPL
							
							--DADOS DESTINATARIO  
							,(CASE WHEN LEN(DADOS_DEST.CGC_CPF) = 11 THEN DADOS_DEST.CGC_CPF ELSE '' END)								AS DEST_CPF
							,(CASE WHEN LEN(DADOS_DEST.CGC_CPF) = 14 THEN DADOS_DEST.CGC_CPF ELSE '' END)								AS DEST_CNPJ
							,DADOS_DEST.NOME                                                                                            AS DEST_XNOME
							,DADOS_DEST.ENDE                                                                                            AS DEST_XLGR
							,DADOS_DEST.NUMERO                                                                                          AS DEST_NRO
							,DADOS_DEST.BAIRRO                                                                                          AS DEST_XBAIRRO
							,DADOS_DEST.CD_IBGE                                                                                         AS DEST_CMUN
							,DADOS_DEST.DS_CID                                                                                          AS DEST_XMUN
							,DADOS_DEST.UF                                                                                              AS DEST_UF
							,DADOS_DEST.CEP                                                                                             AS DEST_CEP
							,DADOS_DEST.CD_PAIS_SINTEGRA                                                                                AS DEST_CPAIS
							,DADOS_DEST.DS_PAIS                                                                                         AS DEST_XPAIS
							,(CASE WHEN DADOS_DEST.INSC_EST = 'ISENTO' THEN '' 
								ELSE REPLACE(REPLACE(REPLACE(DADOS_DEST.INSC_EST, '.', ''), '-', ''), ' ', '')
							 END)		                                                                                                AS DEST_IE
							,DADOS_DEST.CONTRIBUINTE                                                                                    AS DEST_INDIEDEST
							,DADOS_DEST.COMPLEMENTO                                                                                     AS DEST_XCPL
							,DADOS_DEST.ENVIAR_XML_NFE                                                                                  AS DEST_FLAGPERMITEENVIOMAIL
							,DADOS_DEST.EMAIL                                                                                           AS DEST_EMAIL
							,DADOS_DEST.DEST_FONE

							--DADOS LOCAL ENTREGA DESTINATARIO
							,DADOS_DEST.DEST_XNOME_ENTREGA																				AS DEST_XNOME_ENTREGA
							,DADOS_DEST.DEST_IE_ENTREGA																					AS DEST_IE_ENTREGA
							,DADOS_DEST.DEST_XLGR_ENTREGA																				AS DEST_XLGR_ENTREGA
							,DADOS_DEST.DEST_NRO_ENTREGA																				AS DEST_NRO_ENTREGA
							,DADOS_DEST.DEST_XCPL_ENTREGA																				AS DEST_XCPL_ENTREGA
							,DADOS_DEST.DEST_XBAIRRO_ENTREGA																			AS DEST_XBAIRRO_ENTREGA
							,DADOS_DEST.DEST_CMUN_ENTREGA																				AS DEST_CMUN_ENTREGA
							,DADOS_DEST.DEST_XMUN_ENTREGA																				AS DEST_XMUN_ENTREGA
							,DADOS_DEST.DEST_UF_ENTREGA																					AS DEST_UF_ENTREGA
							,DADOS_DEST.DEST_CEP_ENTREGA																				AS DEST_CEP_ENTREGA
							,DADOS_DEST.DEST_FONE_ENTREGA																				AS DEST_FONE_ENTREGA
							,DADOS_DEST.DEST_EMAIL_ENTREGA																				AS DEST_EMAIL_ENTREGA
							,DADOS_DEST.DEST_CD_IBGE_ENTREGA																			AS DEST_CD_IBGE_ENTREGA
							,CASE 
								WHEN (DADOS_DEST.DEST_CPF_CNPJ_ENTREGA IS NULL OR DADOS_DEST.DEST_CPF_CNPJ_ENTREGA = '') THEN DADOS_DEST.CGC_CPF 				
								ELSE DADOS_DEST.DEST_CPF_CNPJ_ENTREGA
							END																										AS DEST_CNPJ_ENTREGA		
												
							--DADOS DOS ITENS NOTA   
							,DADOS_PROD.DET_CPROD																						AS DET_CPROD
							,DADOS_PROD.DET_NITEM																						AS DET_NITEM
							,DADOS_PROD.DET_CEAN																						AS DET_CEAN
							,DADOS_PROD.DET_XPROD																						AS DET_XPROD
							,DADOS_PROD.DET_NCM																							AS DET_NCM
							,DADOS_PROD.DET_CFOP																						AS DET_CFOP
							,DADOS_PROD.DET_UCOM																						AS DET_UCOM
							,DADOS_PROD.DET_QCOM																						AS DET_QCOM
							,DADOS_PROD.DET_VUNCOM																						AS DET_VUNCOM
							,DADOS_PROD.DET_VPROD																						AS DET_VPROD
							,DADOS_PROD.DET_CEANTRIB																					AS DET_CEANTRIB
							,DADOS_PROD.DET_UTRIB																						AS DET_UTRIB
							,DADOS_PROD.DET_QTRIB																						AS DET_QTRIB
							,DADOS_PROD.DET_VUNTRIB																						AS DET_VUNTRIB
							,DADOS_PROD.DET_VFRETE																						AS DET_VFRETE
							,DADOS_PROD.DET_VSEG																						AS DET_VSEG
							,DADOS_PROD.DET_VDESC																						AS DET_VDESC
							,DADOS_PROD.DET_VOUTRO																						AS DET_VOUTRO
							,ISNULL(DADOS_PROD.DET_INDTOT, 0)                                                                           AS DET_INDTOT
							,DADOS_PROD.DET_XPED																						AS DET_XPED
							,DADOS_PROD.DET_NITEMPED																					AS DET_NITEMPED
							,DADOS_PROD.NR_CEST                                                                                         AS NR_CEST
							,DADOS_PROD.DET_INFADPROD																					AS DET_INFADPROD
						
							--DADOS IMPOSTOS DOS ITENS DA NOTA  
							,DADOS_IMP_ITEM.ORIGEM                                                                                      AS DET_IMPOSTO_ORIG
							,CASE WHEN DADOS_IMP_ITEM.MOD_TRIB IN(40, 41, 50) THEN '040' ELSE DADOS_IMP_ITEM.MOD_TRIB END 				AS DET_IMPOSTO_MOD_TRIB
							,RIGHT('00' + DADOS_IMP_ITEM.MOD_TRIB, 2)																    AS DET_IMPOSTO_CST
							,DADOS_IMP_ITEM.MODALI_BASE_ICMS                                                                            AS DET_IMPOSTO_MODBC
							,DADOS_IMP_ITEM.VLR_BASE_ICMS                                                                               AS DET_IMPOSTO_VBC
							,DADOS_IMP_ITEM.ALIQUOTA                                                                                    AS DET_IMPOSTO_PICMS
							,DADOS_IMP_ITEM.VLR_ICMS                                                                                    AS DET_IMPOSTO_VICMS
							,(CASE 
								WHEN RIGHT('00' + DADOS_IMP_ITEM.MOD_TRIB, 2) IN (20) THEN DADOS_IMP_ITEM.PERC_REDU
								ELSE 0
		 
							 END)																										AS DET_IMPOSTO_PREDBC
							,DADOS_IMP_ITEM.MODALI_BASE_ICMS_SUBST                                                                      AS DET_IMPOSTO_MODBCST
							,DADOS_IMP_ITEM.PERC_REDU_ST                                                                                AS DET_IMPOSTO_PREDBCST
							,DADOS_IMP_ITEM.VLR_BASE_SUBS                                                                               AS DET_IMPOSTO_VBCST
							,DADOS_IMP_ITEM.ALIQ_ICMS_ST                                                                                AS DET_IMPOSTO_PICMSST
							,DADOS_IMP_ITEM.VLR_ICMS_SUBS                                                                               AS DET_IMPOSTO_VICMSST
							,DADOS_IMP_ITEM.VLR_BASE_ICMS_RET                                                                           AS DET_IMPOSTO_VBCSTRET
							,DADOS_IMP_ITEM.VLR_ICMS_RET                                                                                AS DET_IMPOSTO_VICMSSTRET
							,DADOS_IMP_ITEM.VLR_ICMS_DESONERADO		                    											    AS DET_IMPOSTO_VICMSDESON         
							,DADOS_IMP_ITEM.ICMS_DESONERADO_MOTIVO	                    											    AS DET_IMPOSTO_MOTDESON  
							,DADOS_IMP_ITEM.CBENEF	                    																AS DET_CBENEF  
                            ,DADOS_IMP_ITEM.CD_SIT_OPERA_SIMPLES_NACIO                                                                  AS DET_IMPOSTO_CSOSN
							,(CASE 
								WHEN DADOS_IMP_ITEM.VLR_CRED_ICMS_SN > 0  AND  DADOS_IMP_ITEM.VLR_CRED_BASE_SN > 0 THEN                                 
								 (DADOS_IMP_ITEM.VLR_CRED_ICMS_SN / DADOS_IMP_ITEM.VLR_CRED_BASE_SN ) * 100
								ELSE 0 
							 END)                                                                         								AS DET_IMPOSTO_PCREDSN
							,DADOS_IMP_ITEM.VLR_CRED_ICMS_SN                                                                            AS DET_IMPOSTO_VCREDICMSSN
							,DADOS_IMP_ITEM.PERC_MARG                                                                                   AS DET_IMPOSTO_PMVAST
							
							--'01' AS DET_IMPOSTO_PIS_CST,   
							,CST_PIS                                                                                                    AS DET_IMPOSTO_PIS_CST
							,DADOS_IMP_ITEM.VLR_BASE_PIS                                                                                AS DET_IMPOSTO_PIS_VBC
							,DADOS_IMP_ITEM.PERC_ALIQ_PIS                                                                               AS DET_IMPOSTO_PIS_PPIS
							,DADOS_IMP_ITEM.VLR_PIS                                                                                     AS DET_IMPOSTO_PIS_VPIS
							
							--'01' AS DET_IMPOSTO_COFINS_CST,
							,CST_COFINS                                                                                                 AS DET_IMPOSTO_COFINS_CST
							,DADOS_IMP_ITEM.VLR_BASE_COFINS                                                                             AS DET_IMPOSTO_COFINS_VBC
							,DADOS_IMP_ITEM.PERC_ALIQ_COFINS                                                                            AS DET_IMPOSTO_COFINS_PCOFINS
							,DADOS_IMP_ITEM.VLR_COFINS                                                                                  AS DET_IMPOSTO_COFINS_VCOFINS
							,DADOS_IMP_ITEM.CST_IPI                                                                                     AS DET_IMPOSTO_IPI_CST
							--TRATAR "-"  
							,DADOS_IMP_ITEM.ENQUADRAMENTO                                                                               AS DET_IMPOSTO_IPI_CENQ
							,DADOS_IMP_ITEM.VLR_BC_IPI                                                                                  AS DET_IMPOSTO_IPI_VBC
							,DADOS_IMP_ITEM.ALIQ_IPI                                                                                    AS DET_IMPOSTO_IPI_PIPI
							,DADOS_IMP_ITEM.VLR_IPI                                                                                     AS DET_IMPOSTO_IPI_VIPI
							,DADOS_IMP_ITEM.PERC_ICMS_INTRA                                                                             AS DET_ICMS_INTRA_DEST
							,DADOS_IMP_ITEM.PERC_ICMS_INTER                                                                             AS DET_ICMS_INTER_DEST
							,DADOS_IMP_ITEM.PERC_FECOP                                                                                  AS DET_IMPOSTO_PERC_FECOP_DEST
							,DADOS_IMP_ITEM.BASE_DIFAL                                                                                  AS DET_IMPOSTO_BASE_DIFAL_DEST                                                                
							,DADOS_IMP_ITEM.ICMS_INTER_PART                                                                             AS DET_IMPOSTO_ICMS_INTER_PART_DEST
							,DADOS_IMP_ITEM.VALOR_FECOP_DEST                                                                            AS DET_IMPOSTO_VALOR_FECOP_DEST
							,DADOS_IMP_ITEM.VALOR_DIFAL_DEST                                                                            AS DET_IMPOSTO_VLR_DIFAL_DEST
							,DADOS_IMP_ITEM.VALOR_DIFAL_ORIG                                                                            AS DET_IMPOSTO_VLR_DIFAL_ORIG
							,DADOS_IMP_ITEM.VLR_BASE_ICMS_OP                                                                            AS DET_IMPOSTO_VLR_BASE_ICMS_OP
							,DADOS_IMP_ITEM.VLR_ICMS_OP										                                            AS DET_IMPOSTO_VLR_ICMS_OP 
							,DADOS_IMP_ITEM.PERC_ICMS_OP										                                        AS DET_IMPOSTO_PERC_ICMS_OP
							,DADOS_IMP_ITEM.PERC_DIFERIMENTO									                                        AS DET_IMPOSTO_PERC_DIFERIMENTO
							,DADOS_IMP_ITEM.VLR_ICMS_DIFERIMENTO									                                    AS DET_IMPOSTO_VLR_ICMS_DIFERIMENTO
							,DADOS_IMP_ITEM.PERC_FECOP_ST                                                                               AS DET_PERC_FECOP_ST
							,DADOS_IMP_ITEM.VLR_BASE_FECOP_ST                                                                           AS DET_VLR_BASE_FECOP_ST
							,DADOS_IMP_ITEM.VLR_FECOP_ST                                                                                AS DET_VLR_FECOP_ST  
							,DADOS_IMP_ITEM.VLR_BASE_FECOP                                                                              AS DET_VLR_BASE_FECOP    
							,DADOS_IMP_ITEM.VLR_FECOP                                                                                   AS DET_VLR_FECOP      
							,DADOS_IMP_ITEM.VLR_BASE_FECOP_RET                                                                          AS DET_VLR_BASE_FECOP_RET      
							,DADOS_IMP_ITEM.VLR_FECOP_RET                                                                               AS DET_VLR_FECOP_RET  
                            ,DADOS_IMP_ITEM.VLR_IPI_DEVOL                                                                               AS DET_VLR_IPI_DEVOL                                   
							,DADOS_IMP_ITEM.PERC_IPI_DEVOL                                                                              AS DET_PERC_IPI_DEVOL     
                            ,DADOS_IMP_ITEM.VLR_ICMS_SUBSTITUTO                                                                         AS DET_IMPOSTO_VLR_ICMS_SUBSTITUTO 
                            ,DADOS_IMP_ITEM.PERC_FECOP_RET                                                                              AS DET_PERC_FECOP_RET    
							,DADOS_IMP_ITEM.NR_ITEM_XPED                                                                                AS DET_NR_ITEM_XPED  
							,DADOS_IMP_ITEM.PERC_ICMS_RET                                                                               AS DET_ALIQUOTA_PST 
							,DADOS_IMP_ITEM.VLR_BC_UF_DEST                                                                              AS DET_VLR_BC_UF_DEST 
							--DADOS DOS LOTES DE MEDICAMENTO   
							,DADOS_LOTE_PROD.DET_MED																					AS DET_MED
							,ISNULL(DADOS_LOTE_PROD.NR_LOTE, '')                                                                        AS DET_MED_NLOTE
							,DADOS_LOTE_PROD.QTDE_PROD                                                                                  AS DET_MED_QLOTE
							,ISNULL(DADOS_LOTE_PROD.DT_FABR, DATEADD(YEAR, -1, DADOS_LOTE_PROD.DT_VALID))                               AS DET_MED_DFAB
							,DADOS_LOTE_PROD.DT_VALID                                                                                   AS DET_MED_DVAL
							,ISNULL(DADOS_LOTE_PROD.VLR_TABELA, 0)                                                                      AS DET_MED_VPMC
							,ISNULL(DADOS_LOTE_PROD.CD_DCB, '')                                                                         AS DET_MED_DCB
							,(CASE 
								WHEN ISNULL(NF_01.FIN_NFE, 0) = 4 AND ISNULL(CAST(DADOS_LOTE_PROD.REGISTRO_MS AS VARCHAR(25)), '') = '' THEN 'ISENTO'
								WHEN ISNULL(CAST(DADOS_LOTE_PROD.REGISTRO_MS AS VARCHAR(25)), '0') = '0' THEN 'ISENTO'
								ELSE ISNULL(DADOS_LOTE_PROD.REGISTRO_MS, '')
							 END)																										AS DET_MED_REGISTRO_MS		--CODIGO DE PRODUTO DA ANVISA 'K01.a'
							,ISNULL(DADOS_LOTE_PROD.CD_AGREGADOR, '')                                                                   AS DET_MED_CD_AGEGADOR      --DADOS DOS IMPOSTOS DA NOTA  
							,DADOS_IMP_NF.BASE_CAL_ICMS                                                                                 AS TOTAL_VBC
							,DADOS_IMP_NF.VLR_ICMS                                                                                      AS TOTAL_VICMS
							,DADOS_IMP_NF.BASE_CAL_ICMS_ST                                                                              AS TOTAL_VBCST
							,DADOS_IMP_NF.VLR_TOT_ICMS_ST                                                                               AS TOTAL_VST
							,DADOS_IMP_NF.VLR_TOT_PROD                                                                                  AS TOTAL_VPROD
							,DADOS_IMP_NF.VLR_TOT_FRETE                                                                                 AS TOTAL_VFRETE
							,DADOS_IMP_NF.VLR_TOT_SEG                                                                                   AS TOTAL_VSEG
							,DADOS_IMP_NF.VLR_DESC_CORPO                                                                                AS TOTAL_VDESC
							,DADOS_IMP_NF.VLR_TOT_II                                                                                    AS TOTAL_VII
							,DADOS_IMP_NF.VLR_TOT_IPI                                                                                   AS TOTAL_VIPI
							,DADOS_IMP_NF.VLR_PIS                                                                                       AS TOTAL_VPIS
							,DADOS_IMP_NF.VLR_COFINS                                                                                    AS TOTAL_VCOFINS
							,DADOS_IMP_NF.VLR_OUTRAS_DES                                                                                AS TOTAL_VOUTRO
							,DADOS_IMP_NF.VLR_NF                                                                                        AS TOTAL_VNF
							,DADOS_IMP_NF.VLR_ICMS_DESONERADO																			AS TOTAL_VICMSDESON
							,DADOS_IMP_NF.VLR_TOT_DIFAL_ORIG																			AS TOTAL_VDIFALORIG
							,DADOS_IMP_NF.VLR_TOT_DIFAL_DEST																			AS TOTAL_VDIFALDEST
							,DADOS_IMP_NF.VLR_TOT_FECOP_DEST																			AS TOTAL_VFECOPDEST
                            ,DADOS_IMP_NF.VLR_TOT_IPI_DEVOL										                                        AS TOTAL_VLR_IPI_DEVOL
						    ,DADOS_IMP_NF.VLR_FECOP_ST																			        AS TOTAL_VLR_FECOP_ST  
						    ,DADOS_IMP_NF.VLR_FECOP											                                            AS TOTAL_VLR_FECOP
						    ,DADOS_IMP_NF.VLR_FECOP_RET																			        AS TOTAL_VLR_FECOP_RET                        
							,DADOS_TRANSP.MODA_FRETE                                                                                    AS TRANSP_MODFRETE
							,DADOS_TRANSP.CGC_CPF                                                                                       AS TRANSP_CNPJ
							,DADOS_TRANSP.DS_TRANSP                                                                                     AS TRANSP_XNOME
							,REPLACE(REPLACE(REPLACE(DADOS_TRANSP.INSC_EST, '.', ''), '-', ''), ' ', '')                                AS TRANSP_IE
							,DADOS_TRANSP.ENDE                                                                                          AS TRANSP_XENDER
							,DADOS_TRANSP.DS_CID                                                                                        AS TRANSP_XMUN
							,DADOS_TRANSP.UF                                                                                            AS TRANSP_UF
							,DADOS_TRANSP.EMAIL                                                                                         AS TRANSP_EMAIL
							--DADOS VOLUMES  
							,DADOS_TRANSP.QTDE_VOL                                                                                      AS TRANSP_VOLUMES_QVOL
							,DADOS_TRANSP.ESP_VOL_TRANS                                                                                 AS TRANSP_VOLUMES_ESP
							,DADOS_TRANSP.MARCA                                                                                         AS TRANSP_VOLUMES_MARCA
							,DADOS_TRANSP.NUM_VOL                                                                                       AS TRANSP_VOLUMES_NVOL
							,DADOS_TRANSP.PESO_LIQ                                                                                      AS TRANSP_VOLUMES_PESOL
			 
							,DADOS_TRANSP.PESO_BRUTO                                                                                    AS TRANSP_VOLUMES_PESOB
			 
							,0                                                                                                          AS TRANSP_VOLUMES_NLACRE
							
							--DADOS VEICULOS  
							,DADOS_TRANSP.PLACA                                                                                         AS TRANSP_VEICULO_PLACA
							,DADOS_TRANSP.SIGLA_UF                                                                                      AS TRANSP_VEICULO_UF
							,''                                                                                                         AS TRANSP_VEICULO_RNTC
							
							--DADOS DA DUPLICATA DA NOTA         
							,DADOS_DUP.NR_DP                                                                                			AS DUP_NDUP
							,DADOS_DUP.DT_VENCTO				                                                                   		AS DUP_DVENC
							,DADOS_DUP.VLR_DP                                                                                			AS DUP_VDUP
							,DADOS_ADICIONAIS.OBS + ' Chave de acesso NFCe Referenciada: ' + COALESCE(PDV_VD_NFCE.CH_NFE,'')			AS ADIC_INFCPL
							,DADOS_PROD.DET_XPED																						AS XPED
							,'NF-ELETRONICA.COM'                                                                                        AS REPROC_VERPROC
							,''                                                                                                         AS REPROC_CHAVENFE
							,0                                                                                                          AS REPROC_CNF
							,0                                                                                                          AS REPROC_CDV
							,''                                                                                                         AS REPROC_NRPROTOCOLO
							,''                                                                                                         AS REPROC_VSPROTOCOLO
							,''                                                                                                         AS REPROC_VSAPLIC
							,'1900-01-01'                                                                                               AS REPROC_DTRECBTO
							,''                                                                                                         AS REPROC_DGVAL
							,0																											AS REPROC_TPAMBIENTE
							,(CASE 
								WHEN NF_01.TIPO_TRANSACAO=1 THEN	1
		 
		   
								ELSE 0
							 END)																										AS IND_FINAL                                                                                                      
							, ATOS_CONCESSORIOS.N_PROC																					AS N_PROC
							, ATOS_CONCESSORIOS.ORIG_PROC																				AS ORIG_PROC
							, ATOS_CONCESSORIOS.TIPO_ATO_CONCESSORIO																	AS TIPO_ATO_CONCESSORIO
							,DADOS_LOTE_PROD.MOT_ISENCAO_ANVISA																			AS MOT_ISENCAO_ANVISA
							,ISNULL(DADOS_LOTE_PROD.OBS_CONTRIBUINTE,'')																AS OBS_CONTRIBUINTE
							,ISNULL(DADOS_LOTE_PROD.OBS_FISCO,'')																		AS OBS_FISCO
						    ,DADOS_IMP_ITEM.PERC_EFET_RED_BASE_CALC																		AS DET_PERC_EFET_RED_BASE_CALC
							,DADOS_IMP_ITEM.VLR_EFET_BASE_ICMS      																	AS DET_VLR_EFET_BASE_ICMS
							,DADOS_IMP_ITEM.VLR_EFET_ICMS            																	AS DET_VLR_EFET_ICMS
							,DADOS_IMP_ITEM.ALIQ_EFET_ICMS            																	AS DET_ALIQ_EFET_ICMS
							,DADOS_IMP_NF.VLR_BASE_CALC_EFET     																		AS TOTAL_VLRBASECALCEFET   
							,DADOS_IMP_NF.VLR_EFET_ICMS            																		AS TOTAL_VLREFETICMS
						FROM
							#NF_01 NF_01
                            LEFT  JOIN #NF_RESPTECNICO  NF_RESPTECNICO  ON
                              NF_RESPTECNICO.CD_EMP = 1
							INNER JOIN #DADOS_EMIT DADOS_EMIT ON
									NF_01.CD_NFE_LOTE    = DADOS_EMIT.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_EMIT.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_EMIT.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_EMIT.CD_NF
							INNER JOIN #DADOS_DEST DADOS_DEST ON
									NF_01.CD_NFE_LOTE    = DADOS_DEST.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_DEST.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_DEST.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_DEST.CD_NF
							INNER JOIN #DADOS_PROD DADOS_PROD ON
									NF_01.CD_NFE_LOTE    = DADOS_PROD.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_PROD.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_PROD.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_PROD.CD_NF
							LEFT JOIN #DADOS_TRANSPORTE DADOS_TRANSP ON
									NF_01.CD_NFE_LOTE    = DADOS_TRANSP.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_TRANSP.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_TRANSP.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_TRANSP.CD_NF
							LEFT JOIN #DADOS_DUP DADOS_DUP ON
									NF_01.CD_NFE_LOTE    = DADOS_DUP.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_DUP.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_DUP.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_DUP.CD_NF
							INNER JOIN #DADOS_IMP_NF DADOS_IMP_NF ON
									NF_01.CD_NFE_LOTE    = DADOS_IMP_NF.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_IMP_NF.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_IMP_NF.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_IMP_NF.CD_NF
							INNER JOIN #DADOS_IMP_ITEM DADOS_IMP_ITEM ON
									DADOS_PROD.CD_NFE_LOTE    = DADOS_IMP_ITEM.CD_NFE_LOTE
								AND DADOS_PROD.CD_NFE_LOTE_IT = DADOS_IMP_ITEM.CD_NFE_LOTE_IT
								AND DADOS_PROD.CD_FILIAL      = DADOS_IMP_ITEM.CD_FILIAL
								AND DADOS_PROD.CD_NF          = DADOS_IMP_ITEM.CD_NF
								AND DADOS_PROD.DET_CPROD      = DADOS_IMP_ITEM.CD_PROD
								AND DADOS_PROD.DET_NITEM      = DADOS_IMP_ITEM.NITEM
							LEFT JOIN #DADOS_LOTE_PROD DADOS_LOTE_PROD ON
									DADOS_PROD.CD_NFE_LOTE    = DADOS_LOTE_PROD.CD_NFE_LOTE
								AND DADOS_PROD.CD_NFE_LOTE_IT = DADOS_LOTE_PROD.CD_NFE_LOTE_IT
								AND DADOS_PROD.CD_FILIAL      = DADOS_LOTE_PROD.CD_FILIAL
								AND DADOS_PROD.CD_NF          = DADOS_LOTE_PROD.CD_NF
								AND DADOS_PROD.DET_CPROD      = DADOS_LOTE_PROD.CD_PROD
							LEFT JOIN #DADOS_CONTIGENCIA DADOS_CONTIGENCIA ON
									NF_01.CD_NFE_LOTE    = DADOS_CONTIGENCIA.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_CONTIGENCIA.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_CONTIGENCIA.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_CONTIGENCIA.CD_NF
							LEFT JOIN #DADOS_ADICIONAIS DADOS_ADICIONAIS ON
									NF_01.CD_NFE_LOTE    = DADOS_ADICIONAIS.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = DADOS_ADICIONAIS.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = DADOS_ADICIONAIS.CD_FILIAL
								AND NF_01.CD_NF          = DADOS_ADICIONAIS.CD_NF
							LEFT JOIN PDV_VD_NFCE ON 
									NF_01.CD_EMP			= PDV_VD_NFCE.CD_EMP
								AND PDV_VD_NFCE.CD_FILIAL	= NF_01.CD_FILIAL
								AND PDV_VD_NFCE.NR_NF		= NF_01.NR_ECF
								AND PDV_VD_NFCE.CD_CX		= NF_01.CD_CX
							LEFT JOIN #DADOS_INTERMEDIADOR INTERMED ON
									NF_01.CD_NFE_LOTE	 = INTERMED.CD_NFE_LOTE
								AND NF_01.CD_NFE_LOTE_IT = INTERMED.CD_NFE_LOTE_IT
								AND NF_01.CD_FILIAL      = INTERMED.CD_FILIAL
								AND NF_01.CD_NF          = INTERMED.CD_NF
							LEFT JOIN #ATOS_CONCESSORIOS ATOS_CONCESSORIOS ON 
								NF_01.CD_FILIAL = ATOS_CONCESSORIOS.CD_FILIAL
								AND NF_01.CD_NF = ATOS_CONCESSORIOS.CD_NF
		
  
						ORDER  BY
							 DADOS_PROD.CD_FILIAL
							,DADOS_PROD.CD_NF
							,DADOS_PROD.DET_NITEM
				END
			IF ( @TP_LOTE = 1 ) -- CANCELAMENTOS ENTRADAS/SAIDAS  
				BEGIN
						  
			   
				 
						IF NOT EXISTS(SELECT 1 FROM	#DADOS_CANCELAMENTO)
						BEGIN
							RETURN
						END
						SELECT
							 NF_00.CD_FILIAL                   AS CAN_CD_FILIAL
							,NF_00.CD_NF                       AS CAN_CD_NF
							,NF_00.CD_NFE_LOTE                 AS CAN_CD_NFE_LOTE
							,NF_00.CD_NFE_LOTE_IT              AS CAN_CD_NFE_LOTE_IT
							,NF_00.TP_SAI_ENT                  AS CAN_TP_SAI_ENT
							,DADOS_CANCELAMENTO.CD_MT_CANCEL   AS CAN_CD_MT_CANCEL
							,DADOS_CANCELAMENTO.DS_MT_CANCEL   AS CAN_JUSTIFICATIVA
							,DADOS_CANCELAMENTO.NR_AUTORIZADOR AS CAN_NR_AUTORIZADOR
							,DADOS_CANCELAMENTO.NR_PROTOCOLO   AS CAN_NR_PROTOCOLO
						FROM
							#NF_00 NF_00
							INNER JOIN #DADOS_CANCELAMENTO DADOS_CANCELAMENTO ON
									NF_00.CD_NFE_LOTE    = DADOS_CANCELAMENTO.CD_NFE_LOTE
								AND NF_00.CD_NFE_LOTE_IT = DADOS_CANCELAMENTO.CD_NFE_LOTE_IT
								AND NF_00.CD_FILIAL      = DADOS_CANCELAMENTO.CD_FILIAL
								AND NF_00.CD_NF          = DADOS_CANCELAMENTO.CD_NF
				END
			IF ( @TP_LOTE = 2 ) -- CARTA DE CORREO ENTRADAS/SAIDAS  
				BEGIN
						  
			   
				 
						IF NOT EXISTS(SELECT 1 FROM	#DADOS_CARTA_CORRECAO)
							BEGIN
									RETURN
							END
						SELECT
							CD_NFE_LOTE            AS CDC_CD_NFE_LOTE
							,CD_NF                 AS CDC_CD_NF
							,TP_SAI_ENT            AS CDC_TP_MOV
							,NF_NF                 AS CDC_NR_NF
							,DH_EVENTO             AS CDC_DH_EVENTO
							,NR_AUTORIZADOR        AS CDC_NR_AUTORIZADOR
							,CGC_ORIGEM            AS CDC_CNPJ_EMITENTE
							,DS_MOTIVO             AS CDC_JUSTIFICATIVA
							,NR_CARTA              AS CDC_NR_CARTA
							,CD_CTR_CARTA_CORRECAO AS CDC_CD_CTR_CARTA_CORRECAO
							,CGC_DESTINO           AS CDC_CNPJ_DESTINO
							,CD_CLI                AS CDC_CD_CLI
							,NM_DEST               AS CDC_NM_DEST
						FROM
							#DADOS_CARTA_CORRECAO
				END
			IF ( @TP_LOTE = 4 ) --MANIFESTO          
				BEGIN
						IF NOT EXISTS(SELECT 1 FROM	#DADOS_MANIFESTO)
						BEGIN
				 
							   
			
							RETURN
						END
						SELECT
							NR_AUTORIZADOR                                                                                 AS MAN_NR_AUTORIZADOR
							,CGC_CPF                                                                                       AS MAN_CNPJAUTOR
							,LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, DT_ENT, 126), 'T', ''), '-', ''), ':', ''), 14) AS MAN_DT_EVENTO
							,TP_EVENT                                                                                      AS MAN_TP_EVENTO
							,''                                                                                            AS MAN_JUSTIFICATIVA
						FROM
							#DADOS_MANIFESTO
				END
			IF ( @TP_LOTE = 5 ) -- INUTILIZAO ENTRADAS/SAIDAS  
				BEGIN
						  
			   
				 
						IF NOT EXISTS(SELECT 1 FROM	#DADOS_INUTILIZACAO)
						BEGIN
							RETURN
						END
						SELECT
							CD_INU                             AS INU_CD_INU
							,CGC                               AS INU_CNPJ
							,RIGHT(CAST(ANO AS VARCHAR(4)), 2) AS INU_ANO
							,MODELO                            AS INU_MODELO
							,CD_NF_SERIE                       AS INU_CD_NF_SERIE
							,NR_NF                             AS INU_NR_INI
							,NR_NF                             AS INU_NR_FIM
							,DS_MT_CANCEL                      AS INU_JUSTIFICATIVA
						FROM
							#DADOS_INUTILIZACAO
				END
  


