IF EXISTS (SELECT
              *
           FROM
              SYSOBJECTS O, SYSUSERS U
           WHERE
              O.NAME = 'ARQUIVO_SINTEGRA_54' AND
              TYPE   = 'P' 		 AND
              U.UID  = O.UID             AND
              U.NAME = USER)

DROP PROCEDURE ARQUIVO_SINTEGRA_54
GO
CREATE PROCEDURE ARQUIVO_SINTEGRA_54
   @P_CDEMPRESA        VARCHAR(02),
   @P_CDIMPOSTO        VARCHAR(02),
   @P_CDINSCESTA       VARCHAR(16),
   @P_DTINICIAL        DATETIME,
   @P_DTFINAL          DATETIME,
   @P_CDPAIS           VARCHAR(04),
   @P_SGESTADO         VARCHAR(02),
   @P_IPI              VARCHAR(02),
   @P_NRSEQARQMAG      VARCHAR(10),
   @P_GERAFORSALE      VARCHAR(01),
   @P_CDSITUCRT        VARCHAR(01),
   @P_CONTADOR         NUMERIC      OUTPUT

AS
   DECLARE @V_CONTREG        NUMERIC;
   DECLARE @V_CDFILIAL       VARCHAR(04);
   DECLARE @V_NRLANCTONF     VARCHAR(10);
   DECLARE @V_SEQUENCIAL     VARCHAR(03);
   DECLARE @V_DSINICIO       VARCHAR(250);
   DECLARE @V_DSARQMAG       VARCHAR(250);
   DECLARE @V_CDARVPROD      VARCHAR(20);
   DECLARE @V_CDCLASFISC     VARCHAR(20);
   DECLARE @V_NMPRODUTO      VARCHAR(100);
   DECLARE @V_SGUNIDADE      VARCHAR(10);
   DECLARE @V_CDSITUTRIBIT   VARCHAR(30);
   DECLARE @V_VRALIQIPI      VARCHAR(30);
   DECLARE @V_VRALIQICMS     VARCHAR(30);
   DECLARE @V_VRPERREDBCIT   VARCHAR(30);
   DECLARE @V_NRSEQITPROD    VARCHAR(10);
   DECLARE @V_MAXCDARVPROD   VARCHAR(20);
   DECLARE @V_CONTADOR       NUMERIC;
   DECLARE @V_CDFILIANT      VARCHAR(04);
   DECLARE @V_NRLANCTANT     VARCHAR(10);
   DECLARE @V_NRINSJURNF     VARCHAR(20);
   DECLARE @V_CDFISCOPERNF   VARCHAR(11);
   DECLARE @V_DSARQMAG1      VARCHAR(250);
BEGIN
   SET @V_CDFILIANT  = ' ';
   SET @V_NRLANCTANT = ' ';
   SET @V_CONTREG = 0;
   SET @V_CONTADOR = @P_CONTADOR + 1;
   SET @V_SEQUENCIAL = '001';
   SET @V_CDFILIAL   = ' ';
   SET @V_NRLANCTONF = ' ';

   IF @P_CDSITUCRT = '3' BEGIN
       DECLARE C_REGISTRO54A CURSOR FOR
          SELECT '54'+
                 RIGHT(REPLICATE('0',14)+ISNULL(IT.NRINSJURNF, '00000000000000'),14)+
                 LEFT(ISNULL(IT.CDMODDOCFISC,'01')+REPLICATE(' ',2),2)+
                 LEFT(CASE RTRIM(ISNULL(IT.CDSERIE,' ')) WHEN '-' THEN ' ' ELSE REPLACE(ISNULL(IT.CDSERIE,' '),'-', '')END+REPLICATE(' ',3),3)+
                 RIGHT(REPLICATE('0',6)+SUBSTRING(IT.NRNOTAFISC,LEN(IT.NRNOTAFISC)-5,6),6)+
                 LEFT(ISNULL(IT.CDFISCOPERNF,'0')+REPLICATE(' ',4),4)+
                 RIGHT(REPLICATE('0',3)+ISNULL(IT.CDSITUTRIBIT,'0'),3)AS DSINICIO,
                 LEFT(ISNULL(PR.CDARVPROD,' ')+REPLICATE(' ',14),14)+
                 RIGHT(REPLICATE('0',11)+LTRIM(REPLACE(CONVERT(NUMERIC(11,3),ISNULL(IT.QTITEMPROD,0)),'.','')),11)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRPRODUTO,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRDESCITEM,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRBASECALCIT,0)),'.','')),12)+
                 '000000000000' +
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1')),0)),'.','')),12)+
                 RIGHT(REPLICATE('0',4)+LTRIM(REPLACE(CONVERT(NUMERIC(4,2),ISNULL(IT.VRALIQITEM,0)),'.','')),4)AS DSARQMAG,
                 PR.CDARVPROD,
                 ISNULL(PR.CDCLASFISC, ' ')AS CDCLASFISC,
                 PR.NMPRODUTO, ISNULL(PR.SGUNIDADE, '  ') AS SGUNIDADE,
                 ISNULL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                 ABS(ISNULL(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1'),0)) AS VRALIQIPI,
                 ISNULL(IT.VRALIQITEM,0)AS VRALIQICMS,
                 ISNULL(IT.VRPERREDBCIT,0) AS VRPERREDBCIT,
                 IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
             FROM PRODUTO PR,
                  (SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          CL.SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3)AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          II.VRCONTITIMP AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          MAX(CASE II.CDFISCALIT WHEN '1' THEN  II.VRALIQITEM ELSE 0 END) AS VRALIQITEM,
                          CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END AS VRBASECALCIT,
                          MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                          MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDCLIENTE,C.IDTPIJURCLIE,C.NRINSJURCLIE,E.SGESTADO
                                                     FROM CLIENTE C LEFT JOIN ENDECLIE E ON (C.CDCLIENTE = E.CDCLIENTE AND
                                                                                             ISNULL(C.IDENDEMISCLI,'P') = E.IDTPENDECLIE)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDCLIENTE = CL.CDCLIENTE),
                          ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                             FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                            WHERE M.CDEMPRESA    = @P_CDEMPRESA
                              AND M.CDINSCESTA   = @P_CDINSCESTA
                              AND M.DTINIPERIODO >= @P_DTINICIAL
                              AND M.DTFIMPERIODO <= @P_DTFINAL
                              AND M.CDIMPOSTO    = @P_CDIMPOSTO
                              AND M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                              AND IP.CDIMPOSTO   = P.CDIMPOSTO
                              AND P.CDIMPOSTONFE = @P_CDIMPOSTO) IP
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND NF.CDFILIAL        = IP.CDFILIAL
                      AND NF.NRLANCTONF      = IP.NRLANCTONF
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND II.CDFILIAL        = IT.CDFILIAL
                      AND II.NRLANCTONF      = IT.NRLANCTONF
                      AND II.NRSEQITPROD     = IT.NRSEQITPROD
                      AND II.CDIMPOSTO       = P.CDIMPOSTO
                      AND P.CDIMPOSTONFE     = @P_CDIMPOSTO
                      AND II.NRITPROIMPOS    IN ('1','2')
                      AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '3'
                      AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '7'
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                    GROUP BY NF.CDFILIAL, NF.NRLANCTONF, CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END,
                             NF.DTEMISSAO,NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                             CL.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                             IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                             IT.VRDESCITEM, II.VRALIQITEM, CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END
                  UNION ALL
                   SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          '00000000000000' AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          'EX' AS SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3)AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          II.VRCONTITIMP  AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          MAX(CASE II.CDFISCALIT WHEN '1' THEN  II.VRALIQITEM ELSE 0 END)AS VRALIQITEM,
                          CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END AS VRBASECALCIT,
                          MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                          MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDCLIENTE,C.IDTPIJURCLIE,C.NRINSJURCLIE,E.SGESTADO
                                                     FROM CLIENTE C LEFT JOIN ENDECLIE E ON (C.CDCLIENTE = E.CDCLIENTE AND
                                                                                             ISNULL(C.IDENDEMISCLI,'P') = E.IDTPENDECLIE)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDCLIENTE = CL.CDCLIENTE),
                          ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                             FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                            WHERE M.CDEMPRESA     = @P_CDEMPRESA
                              AND CDINSCESTA      = @P_CDINSCESTA
                              AND M.DTINIPERIODO >= @P_DTINICIAL
                              AND M.DTFIMPERIODO <= @P_DTFINAL
                              AND M.CDIMPOSTO     = @P_CDIMPOSTO
                              AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                              AND IP.CDIMPOSTO   = P.CDIMPOSTO
                              AND P.CDIMPOSTONFE = @P_CDIMPOSTO) IP
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND NF.CDFILIAL        = IP.CDFILIAL
                      AND NF.NRLANCTONF      = IP.NRLANCTONF
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND II.CDFILIAL        = IT.CDFILIAL
                      AND II.NRLANCTONF      = IT.NRLANCTONF
                      AND II.NRSEQITPROD     = IT.NRSEQITPROD
                      AND II.CDIMPOSTO       = P.CDIMPOSTO
                      AND P.CDIMPOSTONFE     = @P_CDIMPOSTO
                      AND II.NRITPROIMPOS    IN ('1','2')
                      AND SUBSTRING(II.CDFISCOPERIT, 1, 1) = '7'
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                     GROUP BY NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO,
                              NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                              NF.NRNOTAFISC, IT.NRSEQITPROD,
                              IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                              IT.VRDESCITEM, II.VRALIQITEM, CASE II.CDFISCALIT WHEN '1' THEN  II.VRBASECALCIT ELSE 0 END
                  UNION ALL
                   SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          '00000000000000' AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          'EX' AS SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                             THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END)
                             ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END) AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          II.VRCONTITIMP AS  VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          MAX(CASE II.CDFISCALIT WHEN '1' THEN II.VRALIQITEM ELSE 0 END)AS VRALIQITEM,
                          CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END AS VRBASECALCIT,
                          MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                          MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDFORNECED,C.IDTPIJURFORN,C.NRINSJURFORN,E.SGESTADO
                                                     FROM FORNECEDOR C LEFT JOIN ENDEFORN E ON (C.CDFORNECED = E.CDFORNECED AND
                                                                                                ISNULL(C.IDENDEMISFOR,'P') = E.IDTPENDEFORN)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDFORNECED = CL.CDFORNECED)
                                        LEFT JOIN PARAMFORNE PF ON (NF.CDFORNECED = PF.CDFORNECED) ,
                          ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                           FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                           WHERE M.CDEMPRESA     = @P_CDEMPRESA
                             AND M.CDINSCESTA    = @P_CDINSCESTA
                             AND M.DTINIPERIODO >= @P_DTINICIAL
                             AND M.DTFIMPERIODO <= @P_DTFINAL
                             AND M.CDIMPOSTO     = @P_CDIMPOSTO
                             AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                             AND IP.CDIMPOSTO    = P.CDIMPOSTO
                             AND P.CDIMPOSTONFE  = @P_CDIMPOSTO) IP
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'F'
                      AND NF.CDFILIAL        = IP.CDFILIAL
                      AND NF.NRLANCTONF      = IP.NRLANCTONF
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND II.CDFILIAL        = IT.CDFILIAL
                      AND II.NRLANCTONF      = IT.NRLANCTONF
                      AND II.NRSEQITPROD     = IT.NRSEQITPROD
                      AND II.CDIMPOSTO       = P.CDIMPOSTO
                      AND P.CDIMPOSTONFE     = @P_CDIMPOSTO
                      AND II.NRITPROIMPOS    IN ('1','2')
                      AND SUBSTRING(II.CDFISCOPERIT, 1, 1) = '3'
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                    GROUP BY NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO,
                             NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                             NF.NRNOTAFISC, IT.NRSEQITPROD,
                             IT.CDPRODUTO,
                             (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                                THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END)
                                ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END),
                             IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                             IT.VRDESCITEM, II.VRALIQITEM, CASE II.CDFISCALIT WHEN '1' THEN  II.VRBASECALCIT ELSE 0 END
                 UNION ALL
                 /*54 FOS*/
                 SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          CL.SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3)AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          II.VRCONTITIMP AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          MAX(CASE II.CDFISCALIT WHEN '1' THEN  II.VRALIQITEM ELSE 0 END) AS VRALIQITEM,
                          CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END AS VRBASECALCIT,
                          MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                          MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDCLIENTE,C.IDTPIJURCLIE,C.NRINSJURCLIE,E.SGESTADO
                                                     FROM CLIENTE C LEFT JOIN ENDECLIE E ON (C.CDCLIENTE = E.CDCLIENTE AND
                                                                                             ISNULL(C.IDENDEMISCLI,'P') = E.IDTPENDECLIE)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDCLIENTE = CL.CDCLIENTE),
                          ITEMPRODUTO IT, ITEMPROIMPOS II, IMPOSTO P
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND (NF.IDSTATUS IN ('A','E') OR NF.IDSTATUSNFE IN('A','T'))
                      AND II.CDFILIAL        = IT.CDFILIAL
                      AND II.NRLANCTONF      = IT.NRLANCTONF
                      AND II.NRSEQITPROD     = IT.NRSEQITPROD
                      AND II.CDIMPOSTO       = P.CDIMPOSTO
                      AND P.CDIMPOSTONFE     = @P_CDIMPOSTO
                      AND II.NRITPROIMPOS    IN ('1','2')
                      AND NF.CDINSCESTA      = @P_CDINSCESTA
                      AND NF.CDEMPRESA       = @P_CDEMPRESA
                      AND NF.DTEMISSAO  BETWEEN @P_DTINICIAL AND @P_DTFINAL
                      AND NF.IDENTRSAIDOP = 'S'
                      AND NF.CDMODDOCFISC IN ('01','04','55','06','21','22','2D')
                      AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '3'
                      AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '7'
                      AND ISNULL(@P_GERAFORSALE,'N')  = 'S'
                    GROUP BY NF.CDFILIAL, NF.NRLANCTONF, CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END,
                             NF.DTEMISSAO,NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                             CL.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                             IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                             IT.VRDESCITEM, II.VRALIQITEM, CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END) IT
           WHERE IT.CDPRODUTO = PR.CDPRODUTO
           ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD;
       OPEN C_REGISTRO54A;
       FETCH NEXT FROM C_REGISTRO54A INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD,
                                          @V_NRINSJURNF,
                                          @V_CDFISCOPERNF;

       WHILE (@@FETCH_STATUS = 0) BEGIN

          IF (@V_CDFILIAL+@V_NRLANCTONF <> @V_CDFILIANT+@V_NRLANCTANT) BEGIN
             SET @V_CDFILIANT  = @V_CDFILIAL;
             SET @V_NRLANCTANT = @V_NRLANCTONF;
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+'1',3);

             DECLARE C_REGISTRO54A1 CURSOR FOR
                        SELECT ('54'+
                                    RIGHT(REPLICATE('0',14)+ISNULL(@V_NRINSJURNF, '00000000000000'),14) +
                                        LEFT (ISNULL(CDMODDOCFISC,'01')+REPLICATE(' ',2 ),2)                +
                                        LEFT (CASE RTRIM(ISNULL(CDSERIE,' ')) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(CDSERIE,' '),'-', '') END + REPLICATE(' ',3 ),3) +
                                        RIGHT(REPLICATE('0',6 )+SUBSTRING(NRNOTAFISC,LEN(NRNOTAFISC)-5,6),6)+
                                        LEFT (ISNULL(@V_CDFISCOPERNF,'0') + REPLICATE(' ',4 ),4) +
                                        RIGHT(REPLICATE('0',3 )+ISNULL(@V_CDSITUTRIBIT,'0'),3) +
                                        NUMERO_DO_ITEM                                   +
                                        '              '                                 +
                                        '00000000000'                                    +
                                        '000000000000'                                   +
                                        RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(VRIMPOSTONF),0)),'.','')),12) +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '0000') AS DSARQMAG
                              FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL      = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF    = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO    AND
                                                       IE.CDFILIAL      = NF.CDFILIAL     AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF   AND
                                                       EN.CDENCARGONFE  = '01'            AND --Frete
                                                       EN.IDENCARGO     = 'A'             AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL    = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF  = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO  AND
                                                       IE.CDFILIAL      = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE  = '02'          AND --Seguro
                                                       EN.IDENCARGO     = 'A'           AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, ISNULL(N.CDSERIE, N.CDSERIEENTR) AS CDSERIE,
                                                       N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                              FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                             WHERE IP.CDFILIAL    = @V_CDFILIANT  AND
                                                       IP.NRLANCTONF  = @V_NRLANCTANT AND
                                                       IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                       ((IM.CDIMPOSTONFE = '50') OR (IM.CDIMPOSTONFE = '60')) AND --Valores de Pis e Cofins
                                                       IP.CDFILIAL    = N.CDFILIAL    AND
                                                       IP.NRLANCTONF  = N.NRLANCTONF  AND
                                                       IP.VRIMPOSTONF > 0
                                             GROUP BY ISNULL(N.CDSERIE, N.CDSERIEENTR), N.CDMODDOCFISC, N.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL     = @V_CDFILIANT  AND
                                                       IE.NRLANCTONF   = @V_NRLANCTANT AND
                                                       IE.CDENCARGO    = EN.CDENCARGO  AND
                                                       IE.CDFILIAL     = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF   = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE = '04'          AND --Outras Despesas
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC)A;

                     OPEN C_REGISTRO54A1;
                     FETCH NEXT FROM C_REGISTRO54A1 INTO @V_DSARQMAG1;

                     WHILE (@@FETCH_STATUS = 0) BEGIN
                        INSERT INTO ARQMAGNETICO
                               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                        VALUES
                               (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSARQMAG1,@P_NRSEQARQMAG);

                        SET @V_CONTREG = @V_CONTREG + 1;
                        FETCH NEXT FROM C_REGISTRO54A1 INTO @V_DSARQMAG1;
                     END;
                     CLOSE C_REGISTRO54A1;
                     DEALLOCATE C_REGISTRO54A1;
		 
                     SET @V_DSARQMAG1   = NULL;
          END;

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSINICIO+@V_SEQUENCIAL+@V_DSARQMAG,@P_NRSEQARQMAG);

          SET @V_CONTREG = @V_CONTREG + 1;
          SET @V_SEQUENCIAL  = RIGHT(REPLICATE('0',3)+RTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC,@V_SEQUENCIAL)+1)),3);


          SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
            FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = @V_CDARVPROD;

          IF @V_MAXCDARVPROD IS NULL BEGIN
             INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                              SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
             VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                    @V_SGUNIDADE, @V_CDSITUTRIBIT, @V_VRALIQIPI, @V_VRALIQICMS,
                    @V_VRPERREDBCIT, 0);
          END;
       FETCH NEXT FROM C_REGISTRO54A INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD, 
                                          @V_NRINSJURNF, 
                                          @V_CDFISCOPERNF;
       END;
       CLOSE C_REGISTRO54A;
       DEALLOCATE C_REGISTRO54A;

       SET @V_DSINICIO      = NULL;
       SET @V_DSARQMAG      = NULL;
       SET @V_CDARVPROD     = NULL;
       SET @V_CDCLASFISC    = NULL;
       SET @V_NMPRODUTO     = NULL;
       SET @V_SGUNIDADE     = NULL;
       SET @V_CDSITUTRIBIT  = NULL;
       SET @V_VRALIQIPI     = NULL;
       SET @V_VRALIQICMS    = NULL;
       SET @V_VRPERREDBCIT  = NULL;
       SET @V_CDFILIAL      = NULL;
       SET @V_NRLANCTONF    = NULL;
       SET @V_NRSEQITPROD   = NULL;
       SET @V_NRINSJURNF    = NULL;
       SET @V_CDFISCOPERNF  = NULL;

    /*REGISTRO B*/
       SET @V_CONTADOR = @V_CONTADOR + 1;
       SET @V_SEQUENCIAL = '001';
       SET @V_CDFILIAL   = ' ';
       SET @V_NRLANCTONF = ' ';
       SET @V_CDFILIANT  = ' ';
       SET @V_NRLANCTANT = ' ';


       DECLARE C_REGISTRO54B CURSOR FOR
          SELECT '54'+
                 RIGHT(REPLICATE('0',14)+ISNULL(IT.NRINSJURNF, '00000000000000'),14)+
                 LEFT(ISNULL(IT.CDMODDOCFISC,'01')+REPLICATE(' ',2),2)+
                 LEFT(CASE RTRIM(ISNULL(IT.CDSERIE,' ')) WHEN '-' THEN ' ' ELSE REPLACE(ISNULL(IT.CDSERIE,' '),'-', '') END+REPLICATE(' ',3),3)+
                 RIGHT(REPLICATE('0',6)+SUBSTRING(IT.NRNOTAFISC,LEN(IT.NRNOTAFISC)-5,6),6)+
                 LEFT(ISNULL(IT.CDFISCOPERNF,'0')+REPLICATE(' ',4),4)+
                 RIGHT(REPLICATE('0',3)+ISNULL(IT.CDSITUTRIBIT,'0'),3)AS DSINICIO,
                 LEFT(ISNULL(PR.CDARVPROD,' ')+REPLICATE(' ',14),14)+
                 RIGHT(REPLICATE('0',11)+LTRIM(REPLACE(CONVERT(NUMERIC(11,3),ISNULL(IT.QTITEMPROD,0)),'.','')),11)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRPRODUTO,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRDESCITEM,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRBASECALCIT,0)),'.','')),12)+
                 '000000000000' +
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1')),0)),'.','')),12)+
                 RIGHT(REPLICATE('0',4)+LTRIM(REPLACE(CONVERT(NUMERIC(4,2),ISNULL(IT.VRALIQITEM,0)),'.','')),4)AS DSARQMAG,
                 PR.CDARVPROD,
                 ISNULL(PR.CDCLASFISC, ' ') AS CDCLASFISC,
                 PR.NMPRODUTO,
                 ISNULL(PR.SGUNIDADE, '  ') AS SGUNIDADE,
                 ISNULL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                 ABS(ISNULL(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1'),0)) AS VRALIQIPI,
                 ISNULL(IT.VRALIQITEM,0) AS VRALIQICMS,
                 ISNULL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                 IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
            FROM PRODUTO PR,
                 (SELECT NF.CDFILIAL,
                         NF.NRLANCTONF,
                         CASE CL.IDTPIJURFORN WHEN 'I' THEN '' WHEN 'O' THEN ''ELSE CL.NRINSJURFORN END AS NRINSJURNF,
                         NF.DTENTRSAID,
                         NF.CDMODDOCFISC,
                         ISNULL(NF.CDSERIEENTR, NF.CDSERIE) AS CDSERIE,
                         CL.SGESTADO,
                         NF.NRNOTAFISC,
                         IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                          (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                             THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END)
                             ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END) AS CDSITUTRIBIT,
                         IT.QTITEMPROD,
                         II.VRCONTITIMP AS VRPRODUTO,
                         IT.VRTOTAITEM,
                         IT.VRDESCITEM,
                         MAX(CASE II.CDFISCALIT WHEN '1' THEN II.VRALIQITEM ELSE 0 END) AS VRALIQITEM,
                         CASE II.CDFISCALIT WHEN '1' THEN  II.VRBASECALCIT ELSE 0 END AS VRBASECALCIT,
                         MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                         MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                    FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDFORNECED,C.IDTPIJURFORN,C.NRINSJURFORN,E.SGESTADO
                                                    FROM FORNECEDOR C LEFT JOIN ENDEFORN E ON (C.CDFORNECED = E.CDFORNECED AND
                                                                                               ISNULL(C.IDENDEMISFOR,'P') = E.IDTPENDEFORN)
                                                   WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                     AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDFORNECED = CL.CDFORNECED)
                                       LEFT JOIN PARAMFORNE PF ON (NF.CDFORNECED = PF.CDFORNECED),
                         ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                         (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                            FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                           WHERE M.CDEMPRESA     = @P_CDEMPRESA
                             AND M.CDINSCESTA    = @P_CDINSCESTA
                             AND M.DTINIPERIODO >= @P_DTINICIAL
                             AND M.DTFIMPERIODO <= @P_DTFINAL
                             AND M.CDIMPOSTO     = @P_CDIMPOSTO
                             AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                             AND IP.CDIMPOSTO    = P.CDIMPOSTO
                             AND P.CDIMPOSTONFE  = @P_CDIMPOSTO) IP
                WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN  'E' THEN  'F'ELSE  'C' END) = 'F'
                  AND NF.CDFILIAL        = IP.CDFILIAL
                  AND NF.NRLANCTONF      = IP.NRLANCTONF
                  AND NF.CDFILIAL        = IT.CDFILIAL
                  AND NF.NRLANCTONF      = IT.NRLANCTONF
                  AND NF.IDSTATUS IN ('A','E')
                  AND II.CDFILIAL        = IT.CDFILIAL
                  AND II.NRLANCTONF      = IT.NRLANCTONF
                  AND II.NRSEQITPROD     = IT.NRSEQITPROD
                  AND II.CDIMPOSTO       = P.CDIMPOSTO
                  AND P.CDIMPOSTONFE     = @P_CDIMPOSTO
                  AND II.NRITPROIMPOS    IN ('1','2')
                  AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '3'
                  AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                  AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                GROUP BY NF.CDFILIAL, NF.NRLANCTONF,CASE CL.IDTPIJURFORN WHEN 'I' THEN '' WHEN 'O' THEN '' ELSE CL.NRINSJURFORN END,
                         NF.DTENTRSAID, NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR,NF.CDSERIE),
                         CL.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                         (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                             THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END)
                             ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBIT,3) END),
                         IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                         IT.VRDESCITEM, II.VRALIQITEM,CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END
          UNION ALL
                  SELECT NF.CDFILIAL,
                         NF.NRLANCTONF,
                         '00000000000000' AS NRINSJURNF,
                         NF.DTENTRSAID,
                         NF.CDMODDOCFISC,
                         ISNULL(NF.CDSERIEENTR, NF.CDSERIE) AS CDSERIE,
                         'EX' SGESTADO,
                         NF.NRNOTAFISC,
                         IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                         RIGHT(REPLICATE('0',3)+ CDSITUTRIBIT,3) AS CDSITUTRIBIT,
                         IT.QTITEMPROD,
                         II.VRCONTITIMP AS VRPRODUTO,
                         IT.VRTOTAITEM, IT.VRDESCITEM,
                         MAX(CASE II.CDFISCALIT WHEN '1' THEN II.VRALIQITEM ELSE 0 END) AS VRALIQITEM,
                         CASE II.CDFISCALIT WHEN '1' THEN  II.VRBASECALCIT ELSE 0 END  AS VRBASECALCIT,
                         MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                         MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                    FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDFORNECED,C.IDTPIJURFORN,C.NRINSJURFORN,E.SGESTADO
                                                    FROM FORNECEDOR C LEFT JOIN ENDEFORN E ON (C.CDFORNECED = E.CDFORNECED AND
                                                                                               ISNULL(C.IDENDEMISFOR,'P') = E.IDTPENDEFORN)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDFORNECED = CL.CDFORNECED),
                         ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                         (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                            FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                           WHERE M.CDEMPRESA     = @P_CDEMPRESA
                             AND M.CDINSCESTA    = @P_CDINSCESTA
                             AND M.DTINIPERIODO >= @P_DTINICIAL
                             AND M.DTFIMPERIODO <= @P_DTFINAL
                             AND M.CDIMPOSTO     = @P_CDIMPOSTO
                             AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                             AND IP.CDIMPOSTO    = P.CDIMPOSTO
                             AND P.CDIMPOSTONFE  = @P_CDIMPOSTO) IP
                   WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN  'E' THEN 'F' ELSE 'C' END) = 'C' AND
                         NF.CDFILIAL        = IT.CDFILIAL                       AND
                         NF.NRLANCTONF      = IT.NRLANCTONF                     AND
                         NF.CDFILIAL        = IP.CDFILIAL                       AND
                         NF.NRLANCTONF      = IP.NRLANCTONF                     AND
                         NF.IDSTATUS IN ('A','E')                               AND
                         II.CDFILIAL        = IT.CDFILIAL                       AND
                         II.NRLANCTONF      = IT.NRLANCTONF                     AND
                         II.NRSEQITPROD     = IT.NRSEQITPROD                    AND
                         II.CDIMPOSTO       = P.CDIMPOSTO                       AND
                         P.CDIMPOSTONFE     = @P_CDIMPOSTO                      AND
                         II.NRITPROIMPOS    IN ('1','2')                        AND
                         SUBSTRING(II.CDFISCOPERIT,1,1) = '3'                   AND
                         NF.CDMODDOCFISC    = MD.CDMODDOCFISC                   AND
                         ISNULL(MD.CDTPREGFISC, '50')  = '50'
                   GROUP BY NF.CDFILIAL, NF.NRLANCTONF, NF.DTENTRSAID,
                            NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR,NF.CDSERIE),
                            NF.NRNOTAFISC, IT.NRSEQITPROD,
                            IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                            IT.VRDESCITEM, II.VRALIQITEM, CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END)  IT
           WHERE IT.CDPRODUTO = PR.CDPRODUTO
           ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD;

       OPEN C_REGISTRO54B;
       FETCH NEXT FROM C_REGISTRO54B INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD,
                                          @V_NRINSJURNF,
                                          @V_CDFISCOPERNF;
       WHILE (@@FETCH_STATUS = 0) BEGIN

          IF (@V_CDFILIAL+@V_NRLANCTONF <> @V_CDFILIANT+@V_NRLANCTANT) BEGIN
             SET @V_CDFILIANT  = @V_CDFILIAL;
             SET @V_NRLANCTANT = @V_NRLANCTONF;
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+'1',3);

             DECLARE C_REGISTRO54B1 CURSOR FOR
                        SELECT ('54'+
                                    RIGHT(REPLICATE('0',14)+ISNULL(@V_NRINSJURNF, '00000000000000'),14) +
                                        LEFT (ISNULL(CDMODDOCFISC,'01')+REPLICATE(' ',2 ),2)                +
                                        LEFT (CASE RTRIM(ISNULL(CDSERIE,' ')) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(CDSERIE,' '),'-', '') END + REPLICATE(' ',3 ),3) +
                                        RIGHT(REPLICATE('0',6 )+SUBSTRING(NRNOTAFISC,LEN(NRNOTAFISC)-5,6),6)+
                                        LEFT (ISNULL(@V_CDFISCOPERNF,'0') + REPLICATE(' ',4 ),4) +
                                        RIGHT(REPLICATE('0',3 )+ISNULL(@V_CDSITUTRIBIT,'0'),3) +
                                        NUMERO_DO_ITEM                                   +
                                        '              '                                 +
                                        '00000000000'                                    +
                                        '000000000000'                                   +
                                        RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(VRIMPOSTONF),0)),'.','')),12) +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '0000') AS DSARQMAG
                              FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL      = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF    = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO    AND
                                                       IE.CDFILIAL      = NF.CDFILIAL     AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF   AND
                                                       EN.CDENCARGONFE  = '01'            AND --Frete
                                                       EN.IDENCARGO     = 'A'             AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL    = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF  = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO  AND
                                                       IE.CDFILIAL      = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE  = '02'          AND --Seguro
                                                       EN.IDENCARGO     = 'A'           AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, ISNULL(N.CDSERIE, N.CDSERIEENTR) AS CDSERIE,
                                                       N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                              FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                             WHERE IP.CDFILIAL    = @V_CDFILIANT  AND
                                                       IP.NRLANCTONF  = @V_NRLANCTANT AND
                                                       IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                       ((IM.CDIMPOSTONFE = '50') OR (IM.CDIMPOSTONFE = '60')) AND --Valores de Pis e Cofins
                                                       IP.CDFILIAL    = N.CDFILIAL    AND
                                                       IP.NRLANCTONF  = N.NRLANCTONF  AND
                                                       IP.VRIMPOSTONF > 0
                                             GROUP BY ISNULL(N.CDSERIE, N.CDSERIEENTR), N.CDMODDOCFISC, N.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL     = @V_CDFILIANT  AND
                                                       IE.NRLANCTONF   = @V_NRLANCTANT AND
                                                       IE.CDENCARGO    = EN.CDENCARGO  AND
                                                       IE.CDFILIAL     = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF   = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE = '04'          AND --Outras Despesas
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC)A;

                     OPEN C_REGISTRO54B1;
                     FETCH NEXT FROM C_REGISTRO54B1 INTO @V_DSARQMAG1;

                     WHILE (@@FETCH_STATUS = 0) BEGIN
                        INSERT INTO ARQMAGNETICO
                               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                        VALUES
                               (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSARQMAG1,@P_NRSEQARQMAG);

                        SET @V_CONTREG = @V_CONTREG + 1;
                        FETCH NEXT FROM C_REGISTRO54B1 INTO @V_DSARQMAG1;
                     END;
                     CLOSE C_REGISTRO54B1;
                     DEALLOCATE C_REGISTRO54B1;

                     SET @V_DSARQMAG1   = NULL;
          END;

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+RTRIM(CONVERT(VARCHAR,@V_CONTREG)),8),@V_DSINICIO+@V_SEQUENCIAL+@V_DSARQMAG,@P_NRSEQARQMAG);

          SET @V_CONTREG = @V_CONTREG + 1;
          SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+CONVERT(VARCHAR,CONVERT(NUMERIC,@V_SEQUENCIAL)+1),3);


          SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
            FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = @V_CDARVPROD;

          IF @V_MAXCDARVPROD IS NULL BEGIN
             INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                              SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
             VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                    @V_SGUNIDADE, @V_CDSITUTRIBIT, @V_VRALIQIPI, @V_VRALIQICMS,
                    @V_VRPERREDBCIT, 0);
          END;
       FETCH NEXT FROM C_REGISTRO54B INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD,
                                          @V_NRINSJURNF,
                                          @V_CDFISCOPERNF;
       END;
       CLOSE C_REGISTRO54B;
       DEALLOCATE C_REGISTRO54B;

       SET @V_DSINICIO      = NULL;
       SET @V_DSARQMAG      = NULL;
       SET @V_CDARVPROD     = NULL;
       SET @V_CDCLASFISC    = NULL;
       SET @V_NMPRODUTO     = NULL;
       SET @V_SGUNIDADE     = NULL;
       SET @V_CDSITUTRIBIT  = NULL;
       SET @V_VRALIQIPI     = NULL;
       SET @V_VRALIQICMS    = NULL;
       SET @V_VRPERREDBCIT  = NULL;
       SET @V_CDFILIAL      = NULL;
       SET @V_NRLANCTONF    = NULL;
       SET @V_NRSEQITPROD   = NULL;
       SET @V_CDFILIANT     = ' ';
       SET @V_NRLANCTANT    = ' ';
       SET @V_NRINSJURNF    = NULL;
       SET @V_CDFISCOPERNF  = NULL;


    /*REGISTRO C*/
       SET @V_SEQUENCIAL = '001';
       SET @V_CONTADOR = @V_CONTADOR + 1;
       DECLARE C_REGISTRO54C CURSOR FOR
          SELECT('54'+
                RIGHT(REPLICATE('0',14) + ISNULL(IT.NRINSJURNF, '00000000000000'),14)+
                LEFT(ISNULL(IT.CDMODDOCFISC,'01') + REPLICATE(' ',2),2)+
                LEFT((CASE LTRIM(ISNULL(IT.CDSERIE,' '))WHEN '-' THEN ' 'ELSE ISNULL(IT.CDSERIE,' ')END) + REPLICATE(' ',3),3)+
                RIGHT(REPLICATE('0',6) + ISNULL(SUBSTRING(IT.NRNOTAFISC,LEN(IT.NRNOTAFISC)-5,6),'0'),6) +
                LEFT(ISNULL(IT.CDFISCOPERNF,' ') + REPLICATE(' ',4),4)+
                RIGHT(REPLICATE('0',3)+ ISNULL(IT.CDSITUTRIBIT,' '),3)) AS DSINICIO,

               (LEFT(PR.CDARVPROD + REPLICATE(' ',14),14)+
                RIGHT(REPLICATE('0',11)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(11,3), ABS(IT.QTITEMPROD))),'.',''),11)+
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ABS(IT.VRPRODUTO))),'.',''),12)+
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ABS(IT.VRDESCITEM))),'.',''),12)+
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ISNULL(ABS(IT.VRBASECALCIT),0))),'.',''),12)+
               '000000000000' +
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ISNULL(ABS(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A', NULL, '1')),0))),'.',''),11)+
                RIGHT(REPLICATE('0',4)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(4,2), ISNULL(ABS(IT.VRALIQITEM),0))),'.',''),4)) AS DSARQMAG,

               PR.CDARVPROD, ISNULL(PR.CDCLASFISC, ' ')AS CDCLASFISC, ISNULL(PR.NMPRODUTO, ' ')AS NMPRODUTO, ISNULL(PR.SGUNIDADE, '  ') AS SGUNIDADE, ISNULL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
               ABS(ISNULL(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A', NULL, '1'),0)) AS VRALIQIPI,
               ISNULL(IT.VRALIQITEM,0)  AS VRALIQICMS,
               ISNULL(IT.VRPERREDBCIT,0)AS VRPERREDBCIT,
               IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
          FROM PRODUTO PR,
               (SELECT NF.CDFILIAL, NF.NRLANCTONF, REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', '') AS NRINSJURNF, NF.DTEMISSAO,
                       NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) CDSERIE,
                       EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                       IT.CDPRODUTO, RIGHT('000'+CDSITUTRIBIT,3) CDSITUTRIBIT,
                       IT.QTITEMPROD, (II.VRCONTITIMP) VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,
                       MAX((CASE II.CDFISCALIT WHEN '1' THEN II.VRALIQITEM ELSE 0 END)) AS VRALIQITEM,
                       (CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END) AS VRBASECALCIT,
                       MAX(CDFISCOPERIT) AS CDFISCOPERNF, MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                FROM ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                  (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                   FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                   WHERE M.CDEMPRESA    = @P_CDEMPRESA     AND
                         M.CDINSCESTA   = @P_CDINSCESTA    AND
                         M.DTINIPERIODO = @P_DTINICIAL     AND
                         M.DTFIMPERIODO = @P_DTFINAL       AND
                         M.CDIMPOSTO    = @P_CDIMPOSTO     AND
                         M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU  AND
                         IP.CDIMPOSTO   = P.CDIMPOSTO      AND
                         P.CDIMPOSTONFE = @P_CDIMPOSTO) IP,
                  NOTAFISCAL NF LEFT JOIN FILIAL FI ON (FI.CDFILIAL = NF.CDFILIRECE),
                  FILIAL FA LEFT JOIN ENDEFILI EF ON (EF.CDFILIAL = FA.CDFILIAL) AND
                                                     (EF.IDTPENDEFILI = ISNULL(FA.IDENDEMISFIL,'P'))
                WHERE NF.IDENTRSAIDOP    = 'S'                           AND
                      ISNULL(NF.IDORIGDESTNF, (CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END)) = 'L' AND
                      NF.CDFILIAL        = IP.CDFILIAL                   AND
                      NF.NRLANCTONF      = IP.NRLANCTONF                 AND
                      NF.CDFILIAL        = IT.CDFILIAL                   AND
                      NF.NRLANCTONF      = IT.NRLANCTONF                 AND
                      II.CDFILIAL        = IT.CDFILIAL                   AND
                      II.NRLANCTONF      = IT.NRLANCTONF                 AND
                      II.NRSEQITPROD     = IT.NRSEQITPROD                AND
                      II.CDIMPOSTO       = P.CDIMPOSTO                   AND
                      P.CDIMPOSTONFE     = @P_CDIMPOSTO                  AND
                      II.NRITPROIMPOS    IN ('1','2')                    AND
                      SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '7'  AND
                      FA.CDFILIAL        = NF.CDFILIRECE                 AND
                      EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS)   AND
                      (CHARINDEX(EF.SGESTADO, ISNULL(@P_SGESTADO, EF.SGESTADO)) > 0) AND
                      NF.CDMODDOCFISC    = MD.CDMODDOCFISC               AND
                      ISNULL(MD.CDTPREGFISC, '50')  = '50'
                GROUP BY NF.CDFILIAL, NF.NRLANCTONF, FI.NRINSJURFILI, NF.DTEMISSAO,
                         NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                         EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                         IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                         IT.VRDESCITEM, II.VRALIQITEM, (CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END)
                UNION ALL
                SELECT NF.CDFILIAL, NF.NRLANCTONF, REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', '') AS NRINSJURNF, NF.DTENTRSAID,
                       NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR, NF.CDSERIE) CDSERIE,
                       EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                       IT.CDPRODUTO, RIGHT('000'+CDSITUTRIBIT,3) CDSITUTRIBIT,
                       IT.QTITEMPROD, (II.VRCONTITIMP) VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,
                       MAX((CASE II.CDFISCALIT WHEN '1' THEN II.VRALIQITEM ELSE 0 END)) AS VRALIQITEM,
                       (CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END) AS VRBASECALCIT,
                       MAX(CDFISCOPERIT) AS CDFISCOPERNF, MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                FROM ITEMPRODUTO IT, ITEMPROIMPOS II, MODDOCFISC MD, IMPOSTO P,
                  (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                   FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO P
                   WHERE M.CDEMPRESA    = @P_CDEMPRESA     AND
                         M.CDINSCESTA   = @P_CDINSCESTA    AND
                         M.DTINIPERIODO = @P_DTINICIAL     AND
                         M.DTFIMPERIODO = @P_DTFINAL       AND
                         M.CDIMPOSTO    = @P_CDIMPOSTO     AND
                         M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU  AND
                         IP.CDIMPOSTO   = P.CDIMPOSTO      AND
                         P.CDIMPOSTONFE = @P_CDIMPOSTO) IP,
                  NOTAFISCAL NF LEFT JOIN FILIAL FI ON (FI.CDFILIAL = NF.CDFILIRECE),
                  FILIAL FA LEFT JOIN ENDEFILI EF ON (EF.CDFILIAL = FA.CDFILIAL) AND
                                                     (EF.IDTPENDEFILI = ISNULL(FA.IDENDEMISFIL,'P'))
                WHERE NF.IDENTRSAIDOP    = 'E'                                            AND
                      ISNULL(NF.IDORIGDESTNF, (CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END)) = 'L' AND
                      NF.CDFILIAL        = IP.CDFILIAL                  AND
                      NF.NRLANCTONF      = IP.NRLANCTONF                AND
                      NF.CDFILIAL        = IT.CDFILIAL                  AND
                      NF.NRLANCTONF      = IT.NRLANCTONF                AND
                      II.CDFILIAL        = IT.CDFILIAL                  AND
                      II.NRLANCTONF      = IT.NRLANCTONF                AND
                      II.NRSEQITPROD     = IT.NRSEQITPROD               AND
                      II.CDIMPOSTO       = P.CDIMPOSTO                  AND
                      P.CDIMPOSTONFE     = @P_CDIMPOSTO                 AND
                      II.NRITPROIMPOS    IN ('1','2')                   AND
                      SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '3' AND
                      FA.CDFILIAL        = NF.CDFILIEMIT                AND
                      EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS)  AND
                      (CHARINDEX(EF.SGESTADO, ISNULL(@P_SGESTADO, EF.SGESTADO)) > 0) AND
                      NF.CDMODDOCFISC    = MD.CDMODDOCFISC              AND
                      ISNULL(MD.CDTPREGFISC, '50')  = '50'
                GROUP BY NF.CDFILIAL, NF.NRLANCTONF, FI.NRINSJURFILI, NF.DTENTRSAID,
                         NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR,NF.CDSERIE),
                         EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                         IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                         IT.VRDESCITEM, II.VRALIQITEM, (CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END) ) IT
          WHERE IT.CDPRODUTO = PR.CDPRODUTO
          ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD;

       OPEN C_REGISTRO54C;
       FETCH NEXT FROM C_REGISTRO54C INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD,
                                          @V_NRINSJURNF,
                                          @V_CDFISCOPERNF;

       WHILE (@@FETCH_STATUS = 0) BEGIN

          IF (@V_CDFILIAL+@V_NRLANCTONF <> @V_CDFILIANT+@V_NRLANCTANT) BEGIN
             SET @V_CDFILIANT  = @V_CDFILIAL;
             SET @V_NRLANCTANT = @V_NRLANCTONF;
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+'1',3);

             DECLARE C_REGISTRO54C1 CURSOR FOR
                        SELECT ('54'+
                                    RIGHT(REPLICATE('0',14)+ISNULL(@V_NRINSJURNF, '00000000000000'),14) +
                                        LEFT (ISNULL(CDMODDOCFISC,'01') + REPLICATE(' ',2 ),2)                +
                                        LEFT (CASE RTRIM(ISNULL(CDSERIE,' ')) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(CDSERIE,' '),'-', '') END + REPLICATE(' ',3 ),3) +
                                        RIGHT(REPLICATE('0',6 )+SUBSTRING(NRNOTAFISC,LEN(NRNOTAFISC)-5,6),6)+
                                        LEFT (ISNULL(@V_CDFISCOPERNF,'0') + REPLICATE(' ',4 ),4) +
                                        RIGHT(REPLICATE('0',3 )+ISNULL(@V_CDSITUTRIBIT,'0'),3) +
                                        NUMERO_DO_ITEM                                   +
                                        '              '                                 +
                                        '00000000000'                                    +
                                        '000000000000'                                   +
                                        RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(VRIMPOSTONF),0)),'.','')),12) +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '0000') AS DSARQMAG
                              FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL      = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF    = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO    AND
                                                       IE.CDFILIAL      = NF.CDFILIAL     AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF   AND
                                                       EN.CDENCARGONFE  = '01'            AND --Frete
                                                       EN.IDENCARGO     = 'A'             AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL    = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF  = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO  AND
                                                       IE.CDFILIAL      = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE  = '02'          AND --Seguro
                                                       EN.IDENCARGO     = 'A'           AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, ISNULL(N.CDSERIE, N.CDSERIEENTR) AS CDSERIE,
                                                       N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                              FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                             WHERE IP.CDFILIAL    = @V_CDFILIANT  AND
                                                       IP.NRLANCTONF  = @V_NRLANCTANT AND
                                                       IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                       ((IM.CDIMPOSTONFE = '50') OR (IM.CDIMPOSTONFE = '60')) AND --Valores de Pis e Cofins
                                                       IP.CDFILIAL    = N.CDFILIAL    AND
                                                       IP.NRLANCTONF  = N.NRLANCTONF  AND
                                                       IP.VRIMPOSTONF > 0
                                             GROUP BY ISNULL(N.CDSERIE, N.CDSERIEENTR), N.CDMODDOCFISC, N.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL     = @V_CDFILIANT  AND
                                                       IE.NRLANCTONF   = @V_NRLANCTANT AND
                                                       IE.CDENCARGO    = EN.CDENCARGO  AND
                                                       IE.CDFILIAL     = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF   = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE = '04'          AND --Outras Despesas
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC)A;

                     OPEN C_REGISTRO54C1;
                     FETCH NEXT FROM C_REGISTRO54C1 INTO @V_DSARQMAG1;

                     WHILE (@@FETCH_STATUS = 0) BEGIN
                        INSERT INTO ARQMAGNETICO
                               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                        VALUES
                               (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSARQMAG1,@P_NRSEQARQMAG);

                        SET @V_CONTREG = @V_CONTREG + 1;
                        FETCH NEXT FROM C_REGISTRO54C1 INTO @V_DSARQMAG1;
                     END;
                     CLOSE C_REGISTRO54C1;
                     DEALLOCATE C_REGISTRO54C1;

                     SET @V_DSARQMAG1   = NULL;
          END;

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+ RIGHT(REPLICATE('0',8)+RTRIM(CONVERT(VARCHAR,@V_CONTREG)),8),@V_DSINICIO+@V_SEQUENCIAL+@V_DSARQMAG, @P_NRSEQARQMAG);

          SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+CONVERT(VARCHAR,CONVERT(NUMERIC,@V_SEQUENCIAL)+1),3);
          SET @V_CONTREG = @V_CONTREG + 1;

          SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
            FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = @V_CDARVPROD;

          IF @V_MAXCDARVPROD IS NULL BEGIN
             INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                              SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
             VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                    @V_SGUNIDADE, @V_CDSITUTRIBIT, @V_VRALIQIPI, @V_VRALIQICMS,
                    @V_VRPERREDBCIT, 0);
          END;
       FETCH NEXT FROM C_REGISTRO54C INTO @V_DSINICIO,
                                       @V_DSARQMAG,
                                       @V_CDARVPROD,
                                       @V_CDCLASFISC,
                                       @V_NMPRODUTO,
                                       @V_SGUNIDADE,
                                       @V_CDSITUTRIBIT,
                                       @V_VRALIQIPI,
                                       @V_VRALIQICMS,
                                       @V_VRPERREDBCIT,
                                       @V_CDFILIAL,
                                       @V_NRLANCTONF,
                                       @V_NRSEQITPROD,
                                       @V_NRINSJURNF,
                                       @V_CDFISCOPERNF;
       END;
       CLOSE C_REGISTRO54C;
       DEALLOCATE C_REGISTRO54C;
   END;

  /* EMPRESA DO SIMPLES NACIONAL*/
  IF @P_CDSITUCRT <> '3' BEGIN
       DECLARE C_REGISTRO54ASN CURSOR FOR
          SELECT '54'+
                 RIGHT(REPLICATE('0',14)+ISNULL(IT.NRINSJURNF, '00000000000000'),14)+
                 LEFT(ISNULL(IT.CDMODDOCFISC,'01')+REPLICATE(' ',2),2)+
                 LEFT(CASE RTRIM(ISNULL(IT.CDSERIE,' ')) WHEN '-' THEN ' ' ELSE REPLACE(ISNULL(IT.CDSERIE,' '),'-', '')END+REPLICATE(' ',3),3)+
                 RIGHT(REPLICATE('0',6)+SUBSTRING(IT.NRNOTAFISC,LEN(IT.NRNOTAFISC)-5,6),6)+
                 LEFT(ISNULL(IT.CDFISCOPERNF,'0')+REPLICATE(' ',4),4)+
                 RIGHT(REPLICATE('0',3)+ISNULL(IT.CDSITUTRIBIT,'0'),3)AS DSINICIO,
                 LEFT(ISNULL(PR.CDARVPROD,' ')+REPLICATE(' ',14),14)+
                 RIGHT(REPLICATE('0',11)+LTRIM(REPLACE(CONVERT(NUMERIC(11,3),ISNULL(IT.QTITEMPROD,0)),'.','')),11)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRPRODUTO,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRDESCITEM,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRBASECALCIT,0)),'.','')),12)+
                 '000000000000' +
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1')),0)),'.','')),12)+
                 RIGHT(REPLICATE('0',4)+LTRIM(REPLACE(CONVERT(NUMERIC(4,2),ISNULL(IT.VRALIQITEM,0)),'.','')),4)AS DSARQMAG,
                 PR.CDARVPROD,
                 ISNULL(PR.CDCLASFISC, ' ')AS CDCLASFISC,
                 PR.NMPRODUTO, ISNULL(PR.SGUNIDADE, '  ') AS SGUNIDADE,
                 ISNULL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                 ABS(ISNULL(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1'),0)) AS VRALIQIPI,
                 ISNULL(IT.VRALIQITEM,0)AS VRALIQICMS,
                 ISNULL(IT.VRPERREDBCIT,0) AS VRPERREDBCIT,
                 IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
             FROM PRODUTO PR,
                  (SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          CL.SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          SUM(IT.VRCONTITEM) AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          0 AS VRALIQITEM,
                          0 AS VRBASECALCIT,
                          MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                          0 AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDCLIENTE,C.IDTPIJURCLIE,C.NRINSJURCLIE,E.SGESTADO
                                                     FROM CLIENTE C LEFT JOIN ENDECLIE E ON (C.CDCLIENTE = E.CDCLIENTE AND
                                                                                             ISNULL(C.IDENDEMISCLI,'P') = E.IDTPENDECLIE)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDCLIENTE = CL.CDCLIENTE),
                          MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND M.CDEMPRESA        = @P_CDEMPRESA
                      AND NF.CDINSCESTA      = @P_CDINSCESTA
                      AND M.DTINIPERIODO     = @P_DTINICIAL
                      AND M.DTFIMPERIODO     = @P_DTFINAL
                      AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                    GROUP BY NF.CDFILIAL, NF.NRLANCTONF, CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END,
                             NF.DTEMISSAO,NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                             CL.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                             IT.CDPRODUTO,
                          (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                             IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM
                  UNION ALL
                   SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          '00000000000000' AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          'EX' AS SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          SUM(IT.VRCONTITEM) AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          0 AS VRALIQITEM,
                          0 AS VRBASECALCIT,
                          MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                          0 AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDCLIENTE,C.IDTPIJURCLIE,C.NRINSJURCLIE,E.SGESTADO
                                                     FROM CLIENTE C LEFT JOIN ENDECLIE E ON (C.CDCLIENTE = E.CDCLIENTE AND
                                                                                             ISNULL(C.IDENDEMISCLI,'P') = E.IDTPENDECLIE)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDCLIENTE = CL.CDCLIENTE),
                          MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND M.CDEMPRESA        = @P_CDEMPRESA
                      AND NF.CDINSCESTA      = @P_CDINSCESTA
                      AND M.DTINIPERIODO     = @P_DTINICIAL
                      AND M.DTFIMPERIODO     = @P_DTFINAL
                      AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                     GROUP BY NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO,
                              NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                              NF.NRNOTAFISC, IT.NRSEQITPROD,
                              IT.CDPRODUTO,
                          (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                              IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM
                  UNION ALL
                   SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          '00000000000000' AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          'EX' AS SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                             (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                                 THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END)
                                 ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          SUM(IT.VRCONTITEM) AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          0 AS VRALIQITEM,
                          0 AS VRBASECALCIT,
                          MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                          0 AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDFORNECED,C.IDTPIJURFORN,C.NRINSJURFORN,E.SGESTADO
                                                     FROM FORNECEDOR C LEFT JOIN ENDEFORN E ON (C.CDFORNECED = E.CDFORNECED AND
                                                                                                ISNULL(C.IDENDEMISFOR,'P') = E.IDTPENDEFORN)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDFORNECED = CL.CDFORNECED)
                                        LEFT JOIN PARAMFORNE PF ON (NF.CDFORNECED = PF.CDFORNECED),
                          MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'F'
                      AND M.CDEMPRESA        = @P_CDEMPRESA
                      AND NF.CDINSCESTA      = @P_CDINSCESTA
                      AND M.DTINIPERIODO     = @P_DTINICIAL
                      AND M.DTFIMPERIODO     = @P_DTFINAL
                      AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                    GROUP BY NF.CDFILIAL, NF.NRLANCTONF, NF.DTEMISSAO,
                             NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                             NF.NRNOTAFISC, IT.NRSEQITPROD,
                             IT.CDPRODUTO,
                             (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                                 THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END)
                                 ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                             IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM
                 UNION ALL
                 /*54 FOS*/
                 SELECT NF.CDFILIAL,
                          NF.NRLANCTONF,
                          CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END AS NRINSJURNF,
                          NF.DTEMISSAO,
                          NF.CDMODDOCFISC,
                          ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                          CL.SGESTADO,
                          NF.NRNOTAFISC,
                          IT.NRSEQITPROD,
                          IT.CDPRODUTO,
                          (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                          IT.QTITEMPROD,
                          II.VRCONTITIMP AS VRPRODUTO,
                          IT.VRTOTAITEM,
                          IT.VRDESCITEM,
                          MAX(CASE II.CDFISCALIT WHEN '1' THEN  II.VRALIQITEM ELSE 0 END) AS VRALIQITEM,
                          CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END AS VRBASECALCIT,
                          MAX(CDFISCOPERIT) AS CDFISCOPERNF,
                          MAX(II.VRPERREDBCIT) AS VRPERREDBCIT
                     FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDCLIENTE,C.IDTPIJURCLIE,C.NRINSJURCLIE,E.SGESTADO
                                                     FROM CLIENTE C LEFT JOIN ENDECLIE E ON (C.CDCLIENTE = E.CDCLIENTE AND
                                                                                             ISNULL(C.IDENDEMISCLI,'P') = E.IDTPENDECLIE)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDCLIENTE = CL.CDCLIENTE),
                          ITEMPRODUTO IT, ITEMPROIMPOS II, IMPOSTO P
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND (NF.IDSTATUS IN ('A','E') OR NF.IDSTATUSNFE IN('A','T'))
                      AND II.CDFILIAL        = IT.CDFILIAL
                      AND II.NRLANCTONF      = IT.NRLANCTONF
                      AND II.NRSEQITPROD     = IT.NRSEQITPROD
                      AND II.CDIMPOSTO       = P.CDIMPOSTO
                      AND P.CDIMPOSTONFE     = @P_CDIMPOSTO
                      AND II.NRITPROIMPOS    IN ('1','2')
                      AND NF.CDINSCESTA      = @P_CDINSCESTA
                      AND NF.CDEMPRESA       = @P_CDEMPRESA
                      AND NF.DTEMISSAO  BETWEEN @P_DTINICIAL AND @P_DTFINAL
                      AND NF.IDENTRSAIDOP = 'S'
                      AND NF.CDMODDOCFISC IN ('01','04','55','06','21','22','2D')
                      AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '3'
                      AND SUBSTRING(ISNULL(II.CDFISCOPERIT,' '),1,1) <> '7'
                      AND ISNULL(@P_GERAFORSALE,'N')  = 'S'
                    GROUP BY NF.CDFILIAL, NF.NRLANCTONF, CASE CL.IDTPIJURCLIE WHEN 'I' THEN '' WHEN 'O' THEN  '' ELSE CL.NRINSJURCLIE END,
                             NF.DTEMISSAO,NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                             CL.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                             IT.CDPRODUTO,
                             (CASE NF.CDMODDOCFISC WHEN '55'
                                 THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                 ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                             IT.QTITEMPROD, II.VRCONTITIMP, IT.VRTOTAITEM,
                             IT.VRDESCITEM, II.VRALIQITEM, CASE II.CDFISCALIT WHEN '1' THEN II.VRBASECALCIT ELSE 0 END) IT
           WHERE IT.CDPRODUTO = PR.CDPRODUTO
           ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD;
       OPEN C_REGISTRO54ASN;
       FETCH NEXT FROM C_REGISTRO54ASN INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD,
                                          @V_NRINSJURNF,
                                          @V_CDFISCOPERNF;

       WHILE (@@FETCH_STATUS = 0) BEGIN

          IF (@V_CDFILIAL+@V_NRLANCTONF <> @V_CDFILIANT+@V_NRLANCTANT) BEGIN
             SET @V_CDFILIANT  = @V_CDFILIAL;
             SET @V_NRLANCTANT = @V_NRLANCTONF;
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+'1',3);

             DECLARE C_REGISTRO54A1SN CURSOR FOR
                        SELECT ('54'+
                                    RIGHT(REPLICATE('0',14)+ISNULL(@V_NRINSJURNF, '00000000000000'),14) +
                                        LEFT (ISNULL(CDMODDOCFISC,'01')+REPLICATE(' ',2 ),2)                +
                                        LEFT (CASE RTRIM(ISNULL(CDSERIE,' ')) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(CDSERIE,' '),'-', '') END + REPLICATE(' ',3 ),3) +
                                        RIGHT(REPLICATE('0',6 )+SUBSTRING(NRNOTAFISC,LEN(NRNOTAFISC)-5,6),6)+
                                        LEFT (ISNULL(@V_CDFISCOPERNF,'0') + REPLICATE(' ',4 ),4) +
                                        '   '                                            +
                                        NUMERO_DO_ITEM                                   +
                                        '              '                                 +
                                        '00000000000'                                    +
                                        '000000000000'                                   +
                                        RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(VRIMPOSTONF),0)),'.','')),12) +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '0000') AS DSARQMAG
                              FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL      = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF    = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO    AND
                                                       IE.CDFILIAL      = NF.CDFILIAL     AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF   AND
                                                       EN.CDENCARGONFE  = '01'            AND --Frete
                                                       EN.IDENCARGO     = 'A'             AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL    = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF  = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO  AND
                                                       IE.CDFILIAL      = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE  = '02'          AND --Seguro
                                                       EN.IDENCARGO     = 'A'           AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, ISNULL(N.CDSERIE, N.CDSERIEENTR) AS CDSERIE,
                                                       N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                              FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                             WHERE IP.CDFILIAL    = @V_CDFILIANT  AND
                                                       IP.NRLANCTONF  = @V_NRLANCTANT AND
                                                       IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                       ((IM.CDIMPOSTONFE = '50') OR (IM.CDIMPOSTONFE = '60')) AND --Valores de Pis e Cofins
                                                       IP.CDFILIAL    = N.CDFILIAL    AND
                                                       IP.NRLANCTONF  = N.NRLANCTONF  AND
                                                       IP.VRIMPOSTONF > 0
                                             GROUP BY ISNULL(N.CDSERIE, N.CDSERIEENTR), N.CDMODDOCFISC, N.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL     = @V_CDFILIANT  AND
                                                       IE.NRLANCTONF   = @V_NRLANCTANT AND
                                                       IE.CDENCARGO    = EN.CDENCARGO  AND
                                                       IE.CDFILIAL     = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF   = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE = '04'          AND --Outras Despesas
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC)A;

                     OPEN C_REGISTRO54A1SN;
                     FETCH NEXT FROM C_REGISTRO54A1SN INTO @V_DSARQMAG1;

                     WHILE (@@FETCH_STATUS = 0) BEGIN
                        INSERT INTO ARQMAGNETICO
                               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                        VALUES
                               (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSARQMAG1,@P_NRSEQARQMAG);

                        SET @V_CONTREG = @V_CONTREG + 1;
                        FETCH NEXT FROM C_REGISTRO54A1SN INTO @V_DSARQMAG1;
                     END;
                     CLOSE C_REGISTRO54A1SN;
                     DEALLOCATE C_REGISTRO54A1SN;

                     SET @V_DSARQMAG1   = NULL;
          END;

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSINICIO+@V_SEQUENCIAL+@V_DSARQMAG,@P_NRSEQARQMAG);

          SET @V_CONTREG = @V_CONTREG + 1;
          SET @V_SEQUENCIAL  = RIGHT(REPLICATE('0',3)+RTRIM(CONVERT(VARCHAR,CONVERT(NUMERIC,@V_SEQUENCIAL)+1)),3);


          SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
            FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = @V_CDARVPROD;

          IF @V_MAXCDARVPROD IS NULL BEGIN
             INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                              SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
             VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                    @V_SGUNIDADE, @V_CDSITUTRIBIT, @V_VRALIQIPI, @V_VRALIQICMS,
                    @V_VRPERREDBCIT, 0);
          END;
       FETCH NEXT FROM C_REGISTRO54ASN INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD,
                                          @V_NRINSJURNF,
                                          @V_CDFISCOPERNF;
       END;
       CLOSE C_REGISTRO54ASN;
       DEALLOCATE C_REGISTRO54ASN;

       SET @V_DSINICIO      = NULL;
       SET @V_DSARQMAG      = NULL;
       SET @V_CDARVPROD     = NULL;
       SET @V_CDCLASFISC    = NULL;
       SET @V_NMPRODUTO     = NULL;
       SET @V_SGUNIDADE     = NULL;
       SET @V_CDSITUTRIBIT  = NULL;
       SET @V_VRALIQIPI     = NULL;
       SET @V_VRALIQICMS    = NULL;
       SET @V_VRPERREDBCIT  = NULL;
       SET @V_CDFILIAL      = NULL;
       SET @V_NRLANCTONF    = NULL;
       SET @V_NRSEQITPROD   = NULL;
       SET @V_NRINSJURNF    = NULL;
       SET @V_CDFISCOPERNF  = NULL;

    /*REGISTRO B*/
       SET @V_CONTADOR = @V_CONTADOR + 1;
       SET @V_SEQUENCIAL = '001';
       SET @V_CDFILIAL   = ' ';
       SET @V_NRLANCTONF = ' ';
       SET @V_CDFILIANT  = ' ';
       SET @V_NRLANCTANT = ' ';


       DECLARE C_REGISTRO54BSN CURSOR FOR
          SELECT '54'+
                 RIGHT(REPLICATE('0',14)+ISNULL(IT.NRINSJURNF, '00000000000000'),14)+
                 LEFT(ISNULL(IT.CDMODDOCFISC,'01')+REPLICATE(' ',2),2)+
                 LEFT(CASE RTRIM(ISNULL(IT.CDSERIE,' ')) WHEN '-' THEN ' ' ELSE REPLACE(ISNULL(IT.CDSERIE,' '),'-', '') END+REPLICATE(' ',3),3)+
                 RIGHT(REPLICATE('0',6)+SUBSTRING(IT.NRNOTAFISC,LEN(IT.NRNOTAFISC)-5,6),6)+
                 LEFT(ISNULL(IT.CDFISCOPERNF,'0')+REPLICATE(' ',4),4)+
                 RIGHT(REPLICATE('0',3)+ISNULL(IT.CDSITUTRIBIT,'0'),3)AS DSINICIO,
                 LEFT(ISNULL(PR.CDARVPROD,' ')+REPLICATE(' ',14),14)+
                 RIGHT(REPLICATE('0',11)+LTRIM(REPLACE(CONVERT(NUMERIC(11,3),ISNULL(IT.QTITEMPROD,0)),'.','')),11)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRPRODUTO,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRDESCITEM,0)),'.','')),12)+
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(IT.VRBASECALCIT,0)),'.','')),12)+
                 '000000000000' +
                 RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1')),0)),'.','')),12)+
                 RIGHT(REPLICATE('0',4)+LTRIM(REPLACE(CONVERT(NUMERIC(4,2),ISNULL(IT.VRALIQITEM,0)),'.','')),4)AS DSARQMAG,
                 PR.CDARVPROD,
                 ISNULL(PR.CDCLASFISC, ' ') AS CDCLASFISC,
                 PR.NMPRODUTO,
                 ISNULL(PR.SGUNIDADE, '  ') AS SGUNIDADE,
                 ISNULL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                 ABS(ISNULL(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, @P_IPI, 'A',NULL,'1'),0)) AS VRALIQIPI,
                 ISNULL(IT.VRALIQITEM,0) AS VRALIQICMS,
                 ISNULL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                 IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
            FROM PRODUTO PR,
                 (SELECT NF.CDFILIAL,
                         NF.NRLANCTONF,
                         CASE CL.IDTPIJURFORN WHEN 'I' THEN '' WHEN 'O' THEN ''ELSE CL.NRINSJURFORN END AS NRINSJURNF,
                         NF.DTENTRSAID,
                         NF.CDMODDOCFISC,
                         ISNULL(NF.CDSERIEENTR, NF.CDSERIE) AS CDSERIE,
                         CL.SGESTADO,
                         NF.NRNOTAFISC,
                         IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                         (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                             THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                  THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                           THEN SUBSTRING(CDFISCOPERSN,2,3)
                                           ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                  ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END)
                             ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                         IT.QTITEMPROD,
                         SUM(IT.VRCONTITEM) AS VRPRODUTO,
                         IT.VRTOTAITEM,
                         IT.VRDESCITEM,
                         0 AS VRALIQITEM,
                         0 AS VRBASECALCIT,
                         MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                         0 AS VRPERREDBCIT
                    FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDFORNECED,C.IDTPIJURFORN,C.NRINSJURFORN,E.SGESTADO
                                                    FROM FORNECEDOR C LEFT JOIN ENDEFORN E ON (C.CDFORNECED = E.CDFORNECED AND
                                                                                               ISNULL(C.IDENDEMISFOR,'P') = E.IDTPENDEFORN)
                                                   WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                     AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDFORNECED = CL.CDFORNECED)
                                       LEFT JOIN PARAMFORNE PF ON (NF.CDFORNECED = PF.CDFORNECED),
                         MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD
                WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN  'E' THEN  'F'ELSE  'C' END) = 'F'
                  AND M.CDEMPRESA        = @P_CDEMPRESA
                  AND NF.CDINSCESTA      = @P_CDINSCESTA
                  AND M.DTINIPERIODO     = @P_DTINICIAL
                  AND M.DTFIMPERIODO     = @P_DTFINAL
                  AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                  AND NF.CDFILIAL        = IT.CDFILIAL
                  AND NF.NRLANCTONF      = IT.NRLANCTONF
                  AND NF.IDSTATUS IN ('A','E')
                  AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                  AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                GROUP BY NF.CDFILIAL, NF.NRLANCTONF,CASE CL.IDTPIJURFORN WHEN 'I' THEN '' WHEN 'O' THEN '' ELSE CL.NRINSJURFORN END,
                         NF.DTENTRSAID, NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR,NF.CDSERIE),
                         CL.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                         (CASE PF.IDSIMPLESNACIONAL WHEN 'S'
                             THEN (CASE NF.CDMODDOCFISC WHEN '55'
                                      THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                               THEN SUBSTRING(CDFISCOPERSN,2,3)
                                               ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                                      ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END)
                             ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                         IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM
          UNION ALL
                  SELECT NF.CDFILIAL,
                         NF.NRLANCTONF,
                         '00000000000000' AS NRINSJURNF,
                         NF.DTENTRSAID,
                         NF.CDMODDOCFISC,
                         ISNULL(NF.CDSERIEENTR, NF.CDSERIE) AS CDSERIE,
                         'EX' SGESTADO,
                         NF.NRNOTAFISC,
                         IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                          (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                         IT.QTITEMPROD,
                         SUM(IT.VRCONTITEM) AS VRPRODUTO,
                         IT.VRTOTAITEM, IT.VRDESCITEM,
                         0 AS VRALIQITEM,
                         0 AS VRBASECALCIT,
                         MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                         0 AS VRPERREDBCIT
                    FROM NOTAFISCAL NF LEFT JOIN (SELECT C.CDFORNECED,C.IDTPIJURFORN,C.NRINSJURFORN,E.SGESTADO
                                                    FROM FORNECEDOR C LEFT JOIN ENDEFORN E ON (C.CDFORNECED = E.CDFORNECED AND
                                                                                               ISNULL(C.IDENDEMISFOR,'P') = E.IDTPENDEFORN)
                                                    WHERE E.CDPAIS  = ISNULL(@P_CDPAIS,E.CDPAIS)
                                                      AND (CHARINDEX(ISNULL(@P_SGESTADO, E.SGESTADO), E.SGESTADO) > 0))CL ON (NF.CDFORNECED = CL.CDFORNECED),
                         MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD
                    WHERE ISNULL(NF.IDORIGDESTNF, CASE NF.IDENTRSAIDOP WHEN  'E' THEN 'F' ELSE 'C' END) = 'C'
                      AND M.CDEMPRESA        = @P_CDEMPRESA
                      AND NF.CDINSCESTA      = @P_CDINSCESTA
                      AND M.DTINIPERIODO     = @P_DTINICIAL
                      AND M.DTFIMPERIODO     = @P_DTFINAL
                      AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                      AND NF.CDFILIAL        = IT.CDFILIAL
                      AND NF.NRLANCTONF      = IT.NRLANCTONF
                      AND NF.IDSTATUS IN ('A','E')
                      AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                      AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                   GROUP BY NF.CDFILIAL, NF.NRLANCTONF, NF.DTENTRSAID,
                            NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR,NF.CDSERIE),
                            NF.NRNOTAFISC, IT.NRSEQITPROD,
                            IT.CDPRODUTO,
                            (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                          ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                            IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM)  IT
           WHERE IT.CDPRODUTO = PR.CDPRODUTO
           ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD;

       OPEN C_REGISTRO54BSN;
       FETCH NEXT FROM C_REGISTRO54BSN INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD, 
                                          @V_NRINSJURNF, 
                                          @V_CDFISCOPERNF;
       WHILE (@@FETCH_STATUS = 0) BEGIN

          IF (@V_CDFILIAL+@V_NRLANCTONF <> @V_CDFILIANT+@V_NRLANCTANT) BEGIN
             SET @V_CDFILIANT  = @V_CDFILIAL;
             SET @V_NRLANCTANT = @V_NRLANCTONF;
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+'1',3);
         
             DECLARE C_REGISTRO54B1SN CURSOR FOR
                        SELECT ('54'+
                                    RIGHT(REPLICATE('0',14)+ISNULL(@V_NRINSJURNF, '00000000000000'),14) +
                                        LEFT (ISNULL(CDMODDOCFISC,'01')+REPLICATE(' ',2 ),2)                +
                                        LEFT (CASE RTRIM(ISNULL(CDSERIE,' ')) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(CDSERIE,' '),'-', '') END + REPLICATE(' ',3 ),3) +
                                        RIGHT(REPLICATE('0',6 )+SUBSTRING(NRNOTAFISC,LEN(NRNOTAFISC)-5,6),6)+    
                                        LEFT (ISNULL(@V_CDFISCOPERNF,'0') + REPLICATE(' ',4 ),4) +
                                        '   '                                            +
                                        NUMERO_DO_ITEM                                   +
                                        '              '                                 +
                                        '00000000000'                                    +
                                        '000000000000'                                   +
                                        RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(VRIMPOSTONF),0)),'.','')),12) +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '0000') AS DSARQMAG
                              FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL      = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF    = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO    AND
                                                       IE.CDFILIAL      = NF.CDFILIAL     AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF   AND
                                                       EN.CDENCARGONFE  = '01'            AND --Frete
                                                       EN.IDENCARGO     = 'A'             AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL    = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF  = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO  AND
                                                       IE.CDFILIAL      = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE  = '02'          AND --Seguro
                                                       EN.IDENCARGO     = 'A'           AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, ISNULL(N.CDSERIE, N.CDSERIEENTR) AS CDSERIE,
                                                       N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                              FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                             WHERE IP.CDFILIAL    = @V_CDFILIANT  AND
                                                       IP.NRLANCTONF  = @V_NRLANCTANT AND
                                                       IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                       ((IM.CDIMPOSTONFE = '50') OR (IM.CDIMPOSTONFE = '60')) AND --Valores de Pis e Cofins
                                                       IP.CDFILIAL    = N.CDFILIAL    AND
                                                       IP.NRLANCTONF  = N.NRLANCTONF  AND
                                                       IP.VRIMPOSTONF > 0
                                             GROUP BY ISNULL(N.CDSERIE, N.CDSERIEENTR), N.CDMODDOCFISC, N.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL     = @V_CDFILIANT  AND
                                                       IE.NRLANCTONF   = @V_NRLANCTANT AND
                                                       IE.CDENCARGO    = EN.CDENCARGO  AND
                                                       IE.CDFILIAL     = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF   = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE = '04'          AND --Outras Despesas
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC)A;
		    
                     OPEN C_REGISTRO54B1SN;
                     FETCH NEXT FROM C_REGISTRO54B1SN INTO @V_DSARQMAG1;
		    
                     WHILE (@@FETCH_STATUS = 0) BEGIN
                        INSERT INTO ARQMAGNETICO
                               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                        VALUES
                               (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSARQMAG1,@P_NRSEQARQMAG);

                        SET @V_CONTREG = @V_CONTREG + 1;      
                        FETCH NEXT FROM C_REGISTRO54B1SN INTO @V_DSARQMAG1;
                     END;
                     CLOSE C_REGISTRO54B1SN;
                     DEALLOCATE C_REGISTRO54B1SN;
		 
                     SET @V_DSARQMAG1   = NULL;         		 
          END;

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+RTRIM(CONVERT(VARCHAR,@V_CONTREG)),8),@V_DSINICIO+@V_SEQUENCIAL+@V_DSARQMAG,@P_NRSEQARQMAG);

          SET @V_CONTREG = @V_CONTREG + 1;
          SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+CONVERT(VARCHAR,CONVERT(NUMERIC,@V_SEQUENCIAL)+1),3);


          SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
            FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = @V_CDARVPROD;

          IF @V_MAXCDARVPROD IS NULL BEGIN
             INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                              SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
             VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                    @V_SGUNIDADE, @V_CDSITUTRIBIT, @V_VRALIQIPI, @V_VRALIQICMS,
                    @V_VRPERREDBCIT, 0);
          END;
       FETCH NEXT FROM C_REGISTRO54BSN INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD, 
                                          @V_NRINSJURNF, 
                                          @V_CDFISCOPERNF;
       END;
       CLOSE C_REGISTRO54BSN;
       DEALLOCATE C_REGISTRO54BSN;

       SET @V_DSINICIO      = NULL;
       SET @V_DSARQMAG      = NULL;
       SET @V_CDARVPROD     = NULL;
       SET @V_CDCLASFISC    = NULL;
       SET @V_NMPRODUTO     = NULL;
       SET @V_SGUNIDADE     = NULL;
       SET @V_CDSITUTRIBIT  = NULL;
       SET @V_VRALIQIPI     = NULL;
       SET @V_VRALIQICMS    = NULL;
       SET @V_VRPERREDBCIT  = NULL;
       SET @V_CDFILIAL      = NULL;
       SET @V_NRLANCTONF    = NULL;
       SET @V_NRSEQITPROD   = NULL;
       SET @V_CDFILIANT     = ' ';
       SET @V_NRLANCTANT    = ' ';
       SET @V_NRINSJURNF    = NULL;
       SET @V_CDFISCOPERNF  = NULL;


    /*REGISTRO C*/
       SET @V_SEQUENCIAL = '001';
       SET @V_CONTADOR = @V_CONTADOR + 1;
       DECLARE C_REGISTRO54CSN CURSOR FOR
          SELECT('54'+
                RIGHT(REPLICATE('0',14) + ISNULL(IT.NRINSJURNF, '00000000000000'),14)+
                LEFT(ISNULL(IT.CDMODDOCFISC,'01') + REPLICATE(' ',2),2)+
                LEFT((CASE LTRIM(ISNULL(IT.CDSERIE,' '))WHEN '-' THEN ' 'ELSE ISNULL(IT.CDSERIE,' ')END) + REPLICATE(' ',3),3)+
                RIGHT(REPLICATE('0',6) + ISNULL(SUBSTRING(IT.NRNOTAFISC,LEN(IT.NRNOTAFISC)-5,6),'0'),6) +
                LEFT(ISNULL(IT.CDFISCOPERNF,' ') + REPLICATE(' ',4),4)+
                RIGHT(REPLICATE('0',3)+ ISNULL(IT.CDSITUTRIBIT,' '),3)) AS DSINICIO,

               (LEFT(PR.CDARVPROD + REPLICATE(' ',14),14)+
                RIGHT(REPLICATE('0',11)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(11,3), ABS(IT.QTITEMPROD))),'.',''),11)+
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ABS(IT.VRPRODUTO))),'.',''),12)+
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ABS(IT.VRDESCITEM))),'.',''),12)+
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ISNULL(ABS(IT.VRBASECALCIT),0))),'.',''),12)+
               '000000000000' +
                RIGHT(REPLICATE('0',12)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(12,2), ISNULL(ABS(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A', NULL, '1')),0))),'.',''),11)+
                RIGHT(REPLICATE('0',4)+REPLACE(CONVERT(VARCHAR,CONVERT(NUMERIC(4,2), ISNULL(ABS(IT.VRALIQITEM),0))),'.',''),4)) AS DSARQMAG,

               PR.CDARVPROD, ISNULL(PR.CDCLASFISC, ' ')AS CDCLASFISC, ISNULL(PR.NMPRODUTO, ' ')AS NMPRODUTO, ISNULL(PR.SGUNIDADE, '  ') AS SGUNIDADE, ISNULL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
               ABS(ISNULL(TEKNISA.VALOR_IMPOSTO_ITEM(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A', NULL, '1'),0)) AS VRALIQIPI,
               ISNULL(IT.VRALIQITEM,0)  AS VRALIQICMS,
               ISNULL(IT.VRPERREDBCIT,0)AS VRPERREDBCIT,
               IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
          FROM PRODUTO PR,
               (SELECT NF.CDFILIAL, NF.NRLANCTONF, REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', '') AS NRINSJURNF, NF.DTEMISSAO,
                       NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) CDSERIE,
                       EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                       IT.CDPRODUTO,
                       (CASE NF.CDMODDOCFISC WHEN '55'
                           THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                    THEN SUBSTRING(CDFISCOPERSN,2,3)
                                     ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                            ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                       IT.QTITEMPROD, SUM(IT.VRCONTITEM) VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,
                       0 AS VRALIQITEM,
                       0 AS VRBASECALCIT,
                       MAX(CDFISOPEITEM) AS CDFISCOPERNF, 0 AS VRPERREDBCIT
                FROM MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD,
                  NOTAFISCAL NF LEFT JOIN FILIAL FI ON (FI.CDFILIAL = NF.CDFILIRECE),
                  FILIAL FA LEFT JOIN ENDEFILI EF ON (EF.CDFILIAL = FA.CDFILIAL) AND
                                                     (EF.IDTPENDEFILI = ISNULL(FA.IDENDEMISFIL,'P'))
                WHERE NF.IDENTRSAIDOP    = 'S'
                  AND ISNULL(NF.IDORIGDESTNF, (CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END)) = 'L'
                  AND M.CDEMPRESA        = @P_CDEMPRESA
                  AND NF.CDINSCESTA      = @P_CDINSCESTA
                  AND M.DTINIPERIODO     = @P_DTINICIAL
                  AND M.DTFIMPERIODO     = @P_DTFINAL
                  AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                  AND NF.CDFILIAL        = IT.CDFILIAL
                  AND NF.NRLANCTONF      = IT.NRLANCTONF
                  AND FA.CDFILIAL        = NF.CDFILIRECE
                  AND EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS)
                  AND (CHARINDEX(EF.SGESTADO, ISNULL(@P_SGESTADO, EF.SGESTADO)) > 0)
                  AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                  AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                GROUP BY NF.CDFILIAL, NF.NRLANCTONF, FI.NRINSJURFILI, NF.DTEMISSAO,
                         NF.CDMODDOCFISC, ISNULL(NF.CDSERIE, NF.CDSERIEENTR),
                         EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                         (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                       ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                         IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM
                UNION ALL
                SELECT NF.CDFILIAL, NF.NRLANCTONF, REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', '') AS NRINSJURNF, NF.DTENTRSAID,
                       NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR, NF.CDSERIE) CDSERIE,
                       EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                       IT.CDPRODUTO,
                       (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                       ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END) AS CDSITUTRIBIT,
                       IT.QTITEMPROD, SUM(IT.VRCONTITEM) VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,
                       0 AS VRALIQITEM,
                       0 AS VRBASECALCIT,
                       MAX(CDFISOPEITEM) AS CDFISCOPERNF, 0 AS VRPERREDBCIT
                FROM MAPAAPURACSN M, ITEMPRODUTO IT, MODDOCFISC MD,
                  NOTAFISCAL NF LEFT JOIN FILIAL FI ON (FI.CDFILIAL = NF.CDFILIRECE),
                  FILIAL FA LEFT JOIN ENDEFILI EF ON (EF.CDFILIAL = FA.CDFILIAL) AND
                                                     (EF.IDTPENDEFILI = ISNULL(FA.IDENDEMISFIL,'P'))
                WHERE NF.IDENTRSAIDOP    = 'E'
                  AND ISNULL(NF.IDORIGDESTNF, (CASE NF.IDENTRSAIDOP WHEN 'E' THEN 'F' ELSE 'C' END)) = 'L'
                  AND M.CDEMPRESA        = @P_CDEMPRESA
                  AND NF.CDINSCESTA      = @P_CDINSCESTA
                  AND M.DTINIPERIODO     = @P_DTINICIAL
                  AND M.DTFIMPERIODO     = @P_DTFINAL
                  AND M.NRSEQUMAPAPU     = NF.NRSEQUMAPAPUSN
                  AND NF.CDFILIAL        = IT.CDFILIAL
                  AND NF.NRLANCTONF      = IT.NRLANCTONF
                  AND FA.CDFILIAL        = NF.CDFILIEMIT
                  AND EF.CDPAIS          = ISNULL(@P_CDPAIS,EF.CDPAIS)
                  AND (CHARINDEX(EF.SGESTADO, ISNULL(@P_SGESTADO, EF.SGESTADO)) > 0)
                  AND NF.CDMODDOCFISC    = MD.CDMODDOCFISC
                  AND ISNULL(MD.CDTPREGFISC, '50')  = '50'
                GROUP BY NF.CDFILIAL, NF.NRLANCTONF, FI.NRINSJURFILI, NF.DTENTRSAID,
                         NF.CDMODDOCFISC, ISNULL(NF.CDSERIEENTR,NF.CDSERIE),
                         EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                         IT.CDPRODUTO,
                         (CASE NF.CDMODDOCFISC WHEN '55'
                              THEN (CASE LEN(CDFISCOPERSN) WHEN 4
                                       THEN SUBSTRING(CDFISCOPERSN,2,3)
                                       ELSE RIGHT(REPLICATE('0',3)+CDFISCOPERSN,3) END)
                              ELSE RIGHT(REPLICATE('0',3)+CDSITUTRIBPR,3) END),
                         IT.QTITEMPROD, IT.VRTOTAITEM, IT.VRDESCITEM) IT
          WHERE IT.CDPRODUTO = PR.CDPRODUTO
          ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD;

       OPEN C_REGISTRO54CSN;
       FETCH NEXT FROM C_REGISTRO54CSN INTO @V_DSINICIO,
                                          @V_DSARQMAG,
                                          @V_CDARVPROD,
                                          @V_CDCLASFISC,
                                          @V_NMPRODUTO,
                                          @V_SGUNIDADE,
                                          @V_CDSITUTRIBIT,
                                          @V_VRALIQIPI,
                                          @V_VRALIQICMS,
                                          @V_VRPERREDBCIT,
                                          @V_CDFILIAL,
                                          @V_NRLANCTONF,
                                          @V_NRSEQITPROD, 
                                          @V_NRINSJURNF, 
                                          @V_CDFISCOPERNF;

       WHILE (@@FETCH_STATUS = 0) BEGIN

          IF (@V_CDFILIAL+@V_NRLANCTONF <> @V_CDFILIANT+@V_NRLANCTANT) BEGIN
             SET @V_CDFILIANT  = @V_CDFILIAL;
             SET @V_NRLANCTANT = @V_NRLANCTONF;
             SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+'1',3);
         
             DECLARE C_REGISTRO54C1SN CURSOR FOR
                        SELECT ('54'+
                                    RIGHT(REPLICATE('0',14)+ISNULL(@V_NRINSJURNF, '00000000000000'),14) +
                                        LEFT (ISNULL(CDMODDOCFISC,'01') + REPLICATE(' ',2 ),2)                +
                                        LEFT (CASE RTRIM(ISNULL(CDSERIE,' ')) WHEN '-' THEN ' 'ELSE REPLACE(ISNULL(CDSERIE,' '),'-', '') END + REPLICATE(' ',3 ),3) +
                                        RIGHT(REPLICATE('0',6 )+SUBSTRING(NRNOTAFISC,LEN(NRNOTAFISC)-5,6),6)+
                                        LEFT (ISNULL(@V_CDFISCOPERNF,'0') + REPLICATE(' ',4 ),4) +
                                        '   '                                            +
                                        NUMERO_DO_ITEM                                   +
                                        '              '                                 +
                                        '00000000000'                                    +
                                        '000000000000'                                   +
                                        RIGHT(REPLICATE('0',12)+LTRIM(REPLACE(CONVERT(NUMERIC(12,2),ISNULL(ABS(VRIMPOSTONF),0)),'.','')),12) +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '000000000000'                                   +
                                        '0000') AS DSARQMAG           
                              FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL      = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF    = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO    AND
                                                       IE.CDFILIAL      = NF.CDFILIAL     AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF   AND
                                                       EN.CDENCARGONFE  = '01'            AND --Frete
                                                       EN.IDENCARGO     = 'A'             AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL    = @V_CDFILIANT    AND
                                                       IE.NRLANCTONF  = @V_NRLANCTANT   AND
                                                       IE.CDENCARGO     = EN.CDENCARGO  AND
                                                       IE.CDFILIAL      = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF    = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE  = '02'          AND --Seguro
                                                       EN.IDENCARGO     = 'A'           AND
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, ISNULL(N.CDSERIE, N.CDSERIEENTR) AS CDSERIE,
                                                       N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                              FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                             WHERE IP.CDFILIAL    = @V_CDFILIANT  AND
                                                       IP.NRLANCTONF  = @V_NRLANCTANT AND
                                                       IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                       ((IM.CDIMPOSTONFE = '50') OR (IM.CDIMPOSTONFE = '60')) AND --Valores de Pis e Cofins
                                                       IP.CDFILIAL    = N.CDFILIAL    AND
                                                       IP.NRLANCTONF  = N.NRLANCTONF  AND
                                                       IP.VRIMPOSTONF > 0
                                             GROUP BY ISNULL(N.CDSERIE, N.CDSERIEENTR), N.CDMODDOCFISC, N.NRNOTAFISC
                                             UNION ALL
                                            SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, ISNULL(NF.CDSERIE, NF.CDSERIEENTR) AS CDSERIE,
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                              FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                             WHERE IE.CDFILIAL     = @V_CDFILIANT  AND
                                                       IE.NRLANCTONF   = @V_NRLANCTANT AND
                                                       IE.CDENCARGO    = EN.CDENCARGO  AND
                                                       IE.CDFILIAL     = NF.CDFILIAL   AND
                                                       IE.NRLANCTONF   = NF.NRLANCTONF AND
                                                       EN.CDENCARGONFE = '04'          AND --Outras Despesas
                                                       IE.VRITEMENCA > 0
                                             GROUP BY ISNULL(NF.CDSERIE, NF.CDSERIEENTR), NF.CDMODDOCFISC, NF.NRNOTAFISC)A;

                     OPEN C_REGISTRO54C1SN;
                     FETCH NEXT FROM C_REGISTRO54C1SN INTO @V_DSARQMAG1;

                     WHILE (@@FETCH_STATUS = 0) BEGIN
                        INSERT INTO ARQMAGNETICO
                               (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                        VALUES
                               (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+RIGHT(REPLICATE('0',8)+CONVERT(VARCHAR,@V_CONTREG),8),@V_DSARQMAG1,@P_NRSEQARQMAG);

                        SET @V_CONTREG = @V_CONTREG + 1;
                        FETCH NEXT FROM C_REGISTRO54C1SN INTO @V_DSARQMAG1;
                     END;
                     CLOSE C_REGISTRO54C1SN;
                     DEALLOCATE C_REGISTRO54C1SN;

                     SET @V_DSARQMAG1   = NULL;
          END;

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            (RIGHT(REPLICATE('0',2)+CONVERT(VARCHAR,@V_CONTADOR),2)+ RIGHT(REPLICATE('0',8)+RTRIM(CONVERT(VARCHAR,@V_CONTREG)),8),@V_DSINICIO+@V_SEQUENCIAL+@V_DSARQMAG, @P_NRSEQARQMAG);

          SET @V_SEQUENCIAL = RIGHT(REPLICATE('0',3)+CONVERT(VARCHAR,CONVERT(NUMERIC,@V_SEQUENCIAL)+1),3);
          SET @V_CONTREG = @V_CONTREG + 1;

          SELECT @V_MAXCDARVPROD = MAX(CDARVPROD)
            FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = @V_CDARVPROD;

          IF @V_MAXCDARVPROD IS NULL BEGIN
             INSERT INTO TMP_REGISTRO_PRODUTO(CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                                              SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
             VALUES('75', @V_CDARVPROD, @P_DTINICIAL, @P_DTFINAL, @V_CDCLASFISC, @V_NMPRODUTO,
                    @V_SGUNIDADE, @V_CDSITUTRIBIT, @V_VRALIQIPI, @V_VRALIQICMS,
                    @V_VRPERREDBCIT, 0);
          END;
       FETCH NEXT FROM C_REGISTRO54CSN INTO @V_DSINICIO,
                                       @V_DSARQMAG,
                                       @V_CDARVPROD,
                                       @V_CDCLASFISC,
                                       @V_NMPRODUTO,
                                       @V_SGUNIDADE,
                                       @V_CDSITUTRIBIT,
                                       @V_VRALIQIPI,
                                       @V_VRALIQICMS,
                                       @V_VRPERREDBCIT,
                                       @V_CDFILIAL,
                                       @V_NRLANCTONF,
                                       @V_NRSEQITPROD,
                                       @V_NRINSJURNF,
                                       @V_CDFISCOPERNF;
       END;
       CLOSE C_REGISTRO54CSN;
       DEALLOCATE C_REGISTRO54CSN;
   END;

   SET @P_CONTADOR = @V_CONTADOR;
END;
GO

-- VERSAO --
EXEC ATUVEROBJ 'ARQUIVO_SINTEGRA_54','5.30.000';
GO

