DROP TABLE TMP_REGISTRO_PRODUTO
/

CREATE GLOBAL TEMPORARY TABLE TMP_REGISTRO_PRODUTO
(CDTIPOREG     VARCHAR2(2) NOT NULL,
 CDARVPROD     VARCHAR2(20) NOT NULL,
 DTINICIAL     DATE,
 DTFINAL       DATE,
 CDCLASFISC    VARCHAR2(20),
 NMPRODUTO     VARCHAR2(50),
 SGUNIDADE     VARCHAR2(06),
 CDSITUTRIBPR  VARCHAR2(3),
 VRALIQIPI     FLOAT,
 VRALIQICMS    FLOAT,
 VRPERREDBCIT  FLOAT,
 VRBASESUBST   FLOAT,
 CONSTRAINT PK_TMP_REGISTRO_PRODUTO PRIMARY KEY(CDTIPOREG, CDARVPROD)) ON COMMIT PRESERVE ROWS
/

CREATE OR REPLACE FUNCTION REGISTRO54_A_B_C_SINTEGRA(
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA    IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA   IN EMPRESA.CDINSCESTA%TYPE,
   P_CDINSCESTAST IN NOTAFISCAL.CDINSCESTAST%TYPE,
   P_DTINICIAL    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL      IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQUENCIAL IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_CONTTOTAL    IN VARCHAR2,
   P_IPI          IN VARCHAR2,
   P_GERAFORSALE  IN VARCHAR2 DEFAULT 'N') RETURN VARCHAR2
AS
   V_CONTGERAL         NUMBER;
   V_CONTREG           NUMBER;
   V_CONTREG54A        NUMBER;
   V_CONTREG54B        NUMBER;
   V_CONTREG54C        NUMBER;
   V_CONTREGSN54A      NUMBER;
   V_CONTREGSN54B      NUMBER;
   V_CONTREGSN54C      NUMBER;
   V_ENCARGO           FLOAT;
   V_STTOTAL           VARCHAR2(160);
   V_CDARVPROD         VARCHAR2(20);
   V_SEQUENCIAL        VARCHAR2(03);
   V_CDFILIAL          VARCHAR2(04);
   V_NRLANCTONF        VARCHAR2(10);
   V_CDSITUCRT         VARCHAR2(01);
   V_NRINSEST          VARCHAR2(255);
   V_IDENDEMIS         VARCHAR2(255);
   V_IDTPIJURCLIE      VARCHAR2(255);
   V_SGESTADO          VARCHAR2(255);
   V_IDSIMPLESNACIONAL VARCHAR2(1);

   DSARQMAG         VARCHAR2(2000);
BEGIN
   /*VERIFICA SE A EMPRESA E DO SIMPLES NACIONAL, '3' NAO E DO SIMPLES, APENAS 1 E 2*/
   SELECT NVL(MAX(CDSITUCRT), '3') CDSITUCRT
     INTO V_CDSITUCRT
     FROM PARAMEMPRESA
    WHERE CDEMPRESA = P_CDEMPRESA;

   V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
   V_CDFILIAL   := ' ';
   V_NRLANCTONF := ' ';

   IF (V_CDSITUCRT = '3') THEN   /* EMPRESA QUE NAO E DO SIMPLES */
      FOR C_ITEM IN (SELECT N.NRNOTAFISC,
                            /*DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR)) AS CDSERIE,*/
                            DECODE(RTRIM(NVL(DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR)),' ')),'-',' ',
                                   REPLACE(NVL(DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR)),' '),'-', '')) AS CDSERIE,
                            N.CDCLIENTE, N.CDFORNECED, N.DTENTRSAID, N.CDFILIEMIT,
                            N.CDFILIRECE, N.DTEMISSAO, N.CDMODDOCFISC,  NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) AS IDORIGDESTNF, N.IDENTRSAIDOP, N.CDFILIAL, N.NRLANCTONF,
                            N.IDTPIJURNF, N.NRINSJURNF, M.NRSEQUMAPAPU, N.CDINSCESTA, DECODE(CFOP,'3','I','7','I','N') AS ID_NACIONAL, CDFISCOPERNF
                       FROM NOTAFISCAL N,
                            (SELECT I.CDFILIAL, I.NRLANCTONF, M.NRSEQUMAPAPU, SUBSTR(I.CDFISCOPERNF,1,1) AS CFOP, MAX(CDFISCOPERNF) AS CDFISCOPERNF
                               FROM MAPAAPURAC M, IMPOSTONF I
                              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 = I.NRSEQUMAPAPU
                              GROUP BY I.CDFILIAL, I.NRLANCTONF, M.NRSEQUMAPAPU, SUBSTR(I.CDFISCOPERNF,1,1) ) M
                      WHERE M.CDFILIAL       = N.CDFILIAL
                        AND M.NRLANCTONF     = N.NRLANCTONF
                        AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                        AND NVL(N.CDMODDOCFISC, '01') IN ('01', '1A', '04', '55') -- Os modelos '06','21' e '22' nao geram o registro
                        AND N.IDSTATUS       IN ('A','E')
                      ORDER BY N.NRNOTAFISC) LOOP

         IF C_ITEM.IDORIGDESTNF = 'C' THEN
            SELECT DECODE(IDTPIJURCLIE,'I','','O','',DECODE(C_ITEM.ID_NACIONAL,'N',NRINSJURCLIE,'00000000000000')), (NVL(IDENDEMISCLI,'P')), (IDTPIJURCLIE)
              INTO V_NRINSEST, V_IDENDEMIS, V_IDTPIJURCLIE
              FROM CLIENTE
             WHERE CDCLIENTE = C_ITEM.CDCLIENTE;

            BEGIN
               SELECT DECODE(C_ITEM.ID_NACIONAL,'N',SGESTADO,'EX')
                 INTO V_SGESTADO
                 FROM ENDECLIE
                WHERE CDCLIENTE    = C_ITEM.CDCLIENTE
                  AND IDTPENDECLIE = V_IDENDEMIS;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_SGESTADO := NULL;
            END;
         ELSIF C_ITEM.IDORIGDESTNF = 'F' THEN
            SELECT DECODE(IDTPIJURFORN,'I','','O','',DECODE(C_ITEM.ID_NACIONAL,'N',NRINSJURFORN,'00000000000000')), (NVL(IDENDEMISFOR,'P')), (IDTPIJURFORN)
              INTO V_NRINSEST, V_IDENDEMIS, V_IDTPIJURCLIE
              FROM FORNECEDOR F
             WHERE F.CDFORNECED = C_ITEM.CDFORNECED;

            BEGIN
               SELECT NVL(IDSIMPLESNACIONAL,'N')
                 INTO V_IDSIMPLESNACIONAL
                 FROM PARAMFORNE P
                WHERE P.CDFORNECED = C_ITEM.CDFORNECED;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                 V_IDSIMPLESNACIONAL     := 'N';
            END;
            BEGIN
               SELECT DECODE(C_ITEM.ID_NACIONAL,'N',SGESTADO,'EX')
                 INTO V_SGESTADO
                 FROM ENDEFORN
                WHERE CDFORNECED   = C_ITEM.CDFORNECED
                  AND IDTPENDEFORN = V_IDENDEMIS;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_SGESTADO := NULL;
            END;
         ELSIF C_ITEM.IDORIGDESTNF = 'L' THEN
            IF C_ITEM.IDENTRSAIDOP = 'S' THEN
               SELECT MAX(NRINSJURFILI), MAX(NVL(IDENDEMISFIL,'P'))
                 INTO V_NRINSEST, V_IDENDEMIS
                 FROM FILIAL
                WHERE CDFILIAL = C_ITEM.CDFILIRECE;

               IF V_IDENDEMIS = 'I' THEN
                  BEGIN
                     SELECT SGESTADO
                       INTO V_SGESTADO
                       FROM INSCRESTAD
                      WHERE CDEMPRESA  = P_CDEMPRESA
                        AND CDINSCESTA = C_ITEM.CDINSCESTA;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_SGESTADO := NULL;
                  END;
               ELSE
                  BEGIN
                     SELECT SGESTADO
                       INTO V_SGESTADO
                       FROM ENDEFILI
                      WHERE CDFILIAL     = C_ITEM.CDFILIRECE
                        AND IDTPENDEFILI = V_IDENDEMIS;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_SGESTADO := NULL;
                  END;
               END IF;
            ELSE
               SELECT MAX(NRINSJURFILI), MAX(NVL(IDENDEMISFIL,'P'))
                 INTO V_NRINSEST, V_IDENDEMIS
                 FROM FILIAL
                WHERE CDFILIAL = C_ITEM.CDFILIEMIT;

               IF V_IDENDEMIS = 'I' THEN
                  BEGIN
                     SELECT SGESTADO
                       INTO V_SGESTADO
                       FROM INSCRESTAD
                      WHERE CDEMPRESA  = P_CDEMPRESA
                        AND CDINSCESTA = C_ITEM.CDINSCESTA;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_SGESTADO := NULL;
                  END;
               ELSE
                  BEGIN
                     SELECT SGESTADO
                       INTO V_SGESTADO
                       FROM ENDEFILI
                      WHERE CDFILIAL     = C_ITEM.CDFILIEMIT
                        AND IDTPENDEFILI = V_IDENDEMIS;
                  EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                        V_SGESTADO := NULL;
                  END;
               END IF;
            END IF;
         END IF;


         IF (V_SGESTADO IS NOT NULL) OR V_SGESTADO <> '' THEN

            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');

            /*GERACAO DOS SEQUENCIAIS "991", "992", "993" E "999" PARA VALORES DE FRETE, SEGURO, PIS/COFINS E OUTROS ENCARGOS*/
            V_ENCARGO := 0;
            V_ENCARGO := VALOR_ENCARGO_NFE(C_ITEM.CDFILIAL, C_ITEM.NRLANCTONF, 'A','01');

            IF V_ENCARGO > 0 THEN
               DSARQMAG := '54'||
                           LPAD(NVL(V_NRINSEST, '00000000000000'),14,'0')   ||
                           RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')      ||
                           RPAD(C_ITEM.CDSERIE,3,' ')                       ||
                           SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)       ||
                           RPAD(NVL(C_ITEM.CDFISCOPERNF,'0'),4,' ')         ||
                           '   '                                            ||
                           '991'                                            ||
                           '              '                                 ||
                           LPAD('0',11,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LTRIM(REPLACE(TO_CHAR(NVL(ABS(V_ENCARGO),0),'0000000000.00'),'.','')) ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',4,'0');
               V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END IF;

            V_ENCARGO := 0;
            V_ENCARGO := VALOR_ENCARGO_NFE(C_ITEM.CDFILIAL, C_ITEM.NRLANCTONF, 'A','02');

            IF V_ENCARGO > 0 THEN
               DSARQMAG := '54'||
                           LPAD(NVL(V_NRINSEST, '00000000000000'),14,'0')   ||
                           RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')      ||
                           RPAD(C_ITEM.CDSERIE,3,' ')                       ||
                           SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)       ||
                           RPAD(NVL(C_ITEM.CDFISCOPERNF,'0'),4,' ')         ||
                           '   '                                            ||
                           '992'                                            ||
                           '              '                                 ||
                           LPAD('0',11,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LTRIM(REPLACE(TO_CHAR(NVL(ABS(V_ENCARGO),0),'0000000000.00'),'.','')) ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',4,'0');
               V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END IF;

            V_ENCARGO := 0;
            V_ENCARGO := NVL(VALOR_IMPOSTO_NFE(C_ITEM.CDFILIAL, C_ITEM.NRLANCTONF,'50','I','1'),0) + NVL(VALOR_IMPOSTO_NFE(C_ITEM.CDFILIAL, C_ITEM.NRLANCTONF,'60','I','1'),0);

            IF V_ENCARGO > 0 THEN
               DSARQMAG := '54'||
                           LPAD(NVL(V_NRINSEST, '00000000000000'),14,'0')   ||
                           RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')      ||
                           RPAD(C_ITEM.CDSERIE,3,' ')                       ||
                           SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)       ||
                           RPAD(NVL(C_ITEM.CDFISCOPERNF,'0'),4,' ')         ||
                           '   '                                            ||
                           '993'                                            ||
                           '              '                                 ||
                           LPAD('0',11,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LTRIM(REPLACE(TO_CHAR(NVL(ABS(V_ENCARGO),0),'0000000000.00'),'.','')) ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',4,'0');
               V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END IF;

            V_ENCARGO := 0;
            V_ENCARGO := NVL(VALOR_ENCARGO_NFE(C_ITEM.CDFILIAL, C_ITEM.NRLANCTONF, 'A','04'),0) + NVL(VALOR_IMPOSTO_NFE(C_ITEM.CDFILIAL, C_ITEM.NRLANCTONF,'80','I','1'),0);

            IF V_ENCARGO > 0 THEN
               DSARQMAG := '54'||
                           LPAD(NVL(V_NRINSEST, '00000000000000'),14,'0')   ||
                           RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')      ||
                           RPAD(C_ITEM.CDSERIE,3,' ')                       ||
                           SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)       ||
                           RPAD(NVL(C_ITEM.CDFISCOPERNF,'0'),4,' ')         ||
                           '   '                                            ||
                           '999'                                            ||
                           '              '                                 ||
                           LPAD('0',11,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LTRIM(REPLACE(TO_CHAR(NVL(ABS(V_ENCARGO),0),'0000000000.00'),'.','')) ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',12,'0')                                 ||
                           LPAD('0',4,'0');
               V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END IF;

            IF C_ITEM.IDORIGDESTNF = 'C' THEN
               FOR C_REGISTRO54A IN (SELECT ('54'||
                                            LPAD(NVL(IT.NRINSJURNF, '00000000000000'),14,'0')||
                                            RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')||
                                            RPAD(C_ITEM.CDSERIE,3,' ')||
                                            SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)||
                                            RPAD(NVL(IT.CDFISCOPERNF,'0'),4,' ')||
                                            LPAD(NVL(TRIM(IT.CDSITUTRIBIT),'0'),3,'0')) AS DSINICIO,
                                            (RPAD(PR.CDARVPROD,14,' ')||
                                            LTRIM(REPLACE(TO_CHAR(ABS(IT.QTITEMPROD),'00000000.000'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(ABS(IT.VRPRODUTO),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(ABS(CASE WHEN NVL(IT.VRDESCITEM,0) = 0 THEN NVL(IT.VRENCARGODESC,0) ELSE NVL(IT.VRDESCITEM,0) END),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRBASECALCIT),0),'0000000000.00'),'.',''))||
                                            '000000000000' ||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'I')),0),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRALIQITEM),0),'00.00'),'.',''))) AS DSARQMAG,
                                            PR.CDARVPROD, NVL(IT.CDPRODNCMTRANS, PR.CDCLASFISC) AS CDCLASFISC, PR.NMPRODUTO, NVL(PR.SGUNIDADE, '  ') AS SGUNIDADE, NVL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                                            ABS(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A'),0)) AS VRALIQIPI,
                                            NVL(IT.VRALIQITEM,0) AS VRALIQICMS, NVL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                                            IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
                                       FROM PRODUTO PR,
                                            (SELECT /*+ INDEX(PK_ITEMPROIMPOS) II*/
                                                    IT.CDFILIAL, IT.NRLANCTONF,
                                                    DECODE(V_IDTPIJURCLIE,'I','','O', '',V_NRINSEST) AS NRINSJURNF,
                                                    IT.NRSEQITPROD, IT.CDPRODUTO, LPAD(CDSITUTRIBIT,3,'0') CDSITUTRIBIT,
                                                    IT.QTITEMPROD, SUM(II.VRCONTITIMP + VALOR_IMPOSTO_ITEM_NFE(II.CDFILIAL,II.NRLANCTONF,II.NRSEQITPROD,P_CDIMPOSTO,'C','','2')) AS VRPRODUTO,
                                                    IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                                    MAX(DECODE(II.CDFISCALIT,'1', II.VRALIQITEM,0)) AS VRALIQITEM, DECODE(II.CDFISCALIT,'1', II.VRBASECALCIT, 0) AS VRBASECALCIT,
                                                    MAX(CDFISCOPERIT) AS CDFISCOPERNF, MAX(II.VRPERREDBCIT) AS VRPERREDBCIT,
                                                    IT.CDPRODNCMTRANS
                                               FROM ITEMPRODUTO IT, ITEMPROIMPOS II,IMPOSTO IM
                                              WHERE IT.CDFILIAL        = C_ITEM.CDFILIAL
                                                AND IT.NRLANCTONF      = C_ITEM.NRLANCTONF
                                                AND II.CDFILIAL        = IT.CDFILIAL
                                                AND II.NRLANCTONF      = IT.NRLANCTONF
                                                AND II.NRSEQITPROD     = IT.NRSEQITPROD
                                                AND IM.CDIMPOSTO       = II.CDIMPOSTO
                                                AND IM.CDIMPOSTONFE    = P_CDIMPOSTO
                                                AND II.NRITPROIMPOS    = '1'
                                              GROUP BY IT.CDFILIAL, IT.NRLANCTONF,
                                                       DECODE(V_IDTPIJURCLIE,'I','','O', '',V_NRINSEST),
                                                       IT.NRSEQITPROD, IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, IT.VRTOTAITEM,
                                                       IT.VRDESCITEM,IT.VRENCARGODESC, II.VRALIQITEM, DECODE(II.CDFISCALIT,'1', II.VRBASECALCIT, 0), IT.CDPRODNCMTRANS) IT
                                      WHERE IT.CDPRODUTO = PR.CDPRODUTO
                                      ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD) LOOP

                  V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
                  INSERT INTO ARQMAGNETICO
                    (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                  VALUES
                    ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), C_REGISTRO54A.DSINICIO||V_SEQUENCIAL||C_REGISTRO54A.DSARQMAG, P_NRSEQUENCIAL);
                  V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');
                  V_CONTREG := NVL(V_CONTREG,0) + 1;
                  V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
                  SELECT MAX(CDARVPROD)
                    INTO V_CDARVPROD
                    FROM TMP_REGISTRO_PRODUTO
                   WHERE CDARVPROD = C_REGISTRO54A.CDARVPROD;
                  IF V_CDARVPROD IS NULL THEN
                     INSERT INTO TMP_REGISTRO_PRODUTO
                        (CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                         SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
                     VALUES
                        ('75', C_REGISTRO54A.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO54A.CDCLASFISC, C_REGISTRO54A.NMPRODUTO,
                         C_REGISTRO54A.SGUNIDADE, C_REGISTRO54A.CDSITUTRIBIT, C_REGISTRO54A.VRALIQIPI, C_REGISTRO54A.VRALIQICMS,
                         C_REGISTRO54A.VRPERREDBCIT, 0);
                  END IF;
               END LOOP;
            END IF;

            IF C_ITEM.IDORIGDESTNF = 'F' THEN
               V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
               FOR C_REGISTRO54B IN (SELECT ('54'||
                                            LPAD(NVL(IT.NRINSJURNF, '00000000000000'),14,'0')||
                                            RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')||
                                            RPAD(C_ITEM.CDSERIE,3,' ')||
                                            SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)||
                                            RPAD(NVL(IT.CDFISCOPERNF,'0'),4,' ')||
                                            LPAD(NVL(IT.CDSITUTRIBIT,' '),3,'0')) AS DSINICIO,
                                            (RPAD(PR.CDARVPROD,14,' ')||
                                            LTRIM(REPLACE(TO_CHAR(ABS(IT.QTITEMPROD),'00000000.000'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(ABS(IT.VRPRODUTO) ,'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(ABS(CASE WHEN NVL(IT.VRDESCITEM,0) = 0 THEN NVL(IT.VRENCARGODESC,0) ELSE NVL(IT.VRDESCITEM,0) END),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRBASECALCIT),0),'0000000000.00'),'.',''))||
                                            '000000000000' ||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'I')),0),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRALIQITEM),0),'00.00'),'.',''))) AS DSARQMAG,
                                            PR.CDARVPROD, NVL(IT.CDPRODNCMTRANS, PR.CDCLASFISC) AS CDCLASFISC, PR.NMPRODUTO, NVL(PR.SGUNIDADE, '  ') AS SGUNIDADE, NVL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                                            ABS(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A'),0)) AS VRALIQIPI,
                                            NVL(IT.VRALIQITEM,0) AS VRALIQICMS, NVL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                                            IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
                                       FROM PRODUTO PR,
                                            (SELECT /*+ INDEX(PK_ITEMPROIMPOS) II*/
                                                    IT.CDFILIAL, IT.NRLANCTONF, DECODE(V_IDTPIJURCLIE,'I','','O', '',V_NRINSEST) AS NRINSJURNF,
                                                    IT.NRSEQITPROD, IT.CDPRODUTO,
                                                    LPAD(CDSITUTRIBIT,3,'0') AS CDSITUTRIBIT,
                                                    IT.QTITEMPROD, SUM(II.VRCONTITIMP + VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL,IT.NRLANCTONF,II.NRSEQITPROD,P_CDIMPOSTO,'C','','2')) AS VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                                    MAX(DECODE(II.CDFISCALIT,'1', II.VRALIQITEM,0)) AS VRALIQITEM, DECODE(II.CDFISCALIT,'1', II.VRBASECALCIT, 0) AS VRBASECALCIT,
                                                    MAX(CDFISCOPERIT) AS CDFISCOPERNF, MAX(II.VRPERREDBCIT) AS VRPERREDBCIT,
                                                    IT.CDPRODNCMTRANS
                                               FROM ITEMPRODUTO IT, ITEMPROIMPOS II, IMPOSTO IM
                                              WHERE IT.CDFILIAL        = C_ITEM.CDFILIAL
                                                AND IT.NRLANCTONF      = C_ITEM.NRLANCTONF
                                                AND II.CDFILIAL        = IT.CDFILIAL
                                                AND II.NRLANCTONF      = IT.NRLANCTONF
                                                AND II.NRSEQITPROD     = IT.NRSEQITPROD
                                                AND IM.CDIMPOSTONFE    = P_CDIMPOSTO
                                                AND II.CDIMPOSTO       = IM.CDIMPOSTO
                                                AND II.NRITPROIMPOS    = '1'
                                              GROUP BY IT.CDFILIAL, IT.NRLANCTONF, DECODE(V_IDTPIJURCLIE,'I','','O', '',V_NRINSEST),
                                                    IT.NRSEQITPROD, IT.CDPRODUTO,
                                                    LPAD(CDSITUTRIBIT,3,'0'),
                                                    IT.QTITEMPROD, IT.VRTOTAITEM,
                                                    IT.VRDESCITEM,IT.VRENCARGODESC, II.VRALIQITEM, DECODE(II.CDFISCALIT,'1', II.VRBASECALCIT, 0), IT.CDPRODNCMTRANS) IT
                                      WHERE IT.CDPRODUTO = PR.CDPRODUTO
                                      ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD) LOOP

                  V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
                  INSERT INTO ARQMAGNETICO
                    (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                  VALUES
                    ('06'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), C_REGISTRO54B.DSINICIO||V_SEQUENCIAL||C_REGISTRO54B.DSARQMAG, P_NRSEQUENCIAL);
                  V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)),3,'0');
                  V_CONTREG := NVL(V_CONTREG,0) + 1;
                  V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
                  SELECT MAX(CDARVPROD)
                    INTO V_CDARVPROD
                    FROM TMP_REGISTRO_PRODUTO
                   WHERE CDARVPROD = C_REGISTRO54B.CDARVPROD;
                  IF V_CDARVPROD IS NULL THEN
                     INSERT INTO TMP_REGISTRO_PRODUTO
                        (CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                         SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
                     VALUES
                        ('75', C_REGISTRO54B.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO54B.CDCLASFISC, C_REGISTRO54B.NMPRODUTO,
                         C_REGISTRO54B.SGUNIDADE, C_REGISTRO54B.CDSITUTRIBIT, C_REGISTRO54B.VRALIQIPI, C_REGISTRO54B.VRALIQICMS,
                         C_REGISTRO54B.VRPERREDBCIT, 0);
                  END IF;
               END LOOP;
            END IF;

            IF C_ITEM.IDORIGDESTNF = 'L' THEN
               V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
               FOR C_REGISTRO54C IN (SELECT ('54'||
                                            LPAD(NVL(IT.NRINSJURNF, '00000000000000'),14,'0')||
                                            RPAD(NVL(C_ITEM.CDMODDOCFISC,'01'), 2, ' ')||
                                            RPAD(C_ITEM.CDSERIE,3,' ')||
                                            SUBSTR(LPAD(C_ITEM.NRNOTAFISC,20,'0'),15,6)||
                                            RPAD(NVL(IT.CDFISCOPERNF,'0'),4,' ')||
                                            LPAD(NVL(IT.CDSITUTRIBIT,' '),3,'0')) AS DSINICIO,
                                            (RPAD(PR.CDARVPROD,14,' ')||
                                            LTRIM(REPLACE(TO_CHAR(ABS(IT.QTITEMPROD),'00000000.000'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(ABS(IT.VRPRODUTO) ,'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(ABS(CASE WHEN NVL(IT.VRDESCITEM,0) = 0 THEN NVL(IT.VRENCARGODESC,0) ELSE NVL(IT.VRDESCITEM,0) END),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRBASECALCIT),0),'0000000000.00'),'.',''))||
                                            '000000000000' ||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'I')),0),'0000000000.00'),'.',''))||
                                            LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRALIQITEM),0),'00.00'),'.',''))) AS DSARQMAG,
                                            PR.CDARVPROD, NVL(IT.CDPRODNCMTRANS, PR.CDCLASFISC) AS CDCLASFISC, PR.NMPRODUTO, NVL(PR.SGUNIDADE, '  ') AS SGUNIDADE, NVL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                                            ABS(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A'),0)) AS VRALIQIPI,
                                            NVL(IT.VRALIQITEM,0) AS VRALIQICMS, NVL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                                            IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
                                       FROM PRODUTO PR,
                                            (SELECT /*+ INDEX(PK_ITEMPROIMPOS) II*/
                                                    IT.CDFILIAL, IT.NRLANCTONF, REPLACE(REPLACE(REPLACE(V_NRINSEST, '-', ''), '.', ''), '/', '') AS NRINSJURNF,
                                                    IT.NRSEQITPROD, IT.CDPRODUTO, LPAD(CDSITUTRIBIT,3,'0') CDSITUTRIBIT,
                                                    IT.QTITEMPROD, SUM(II.VRCONTITIMP + VALOR_IMPOSTO_ITEM_NFE(II.CDFILIAL,II.NRLANCTONF,II.NRSEQITPROD,P_CDIMPOSTO,'C','','2')) AS VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                                    MAX(DECODE(II.CDFISCALIT,'1', II.VRALIQITEM,0)) AS VRALIQITEM, DECODE(II.CDFISCALIT,'1', II.VRBASECALCIT, 0) AS VRBASECALCIT,
                                                    MAX(CDFISCOPERIT) AS CDFISCOPERNF, MAX(II.VRPERREDBCIT) AS VRPERREDBCIT,
                                                    IT.CDPRODNCMTRANS
                                               FROM ITEMPRODUTO IT, ITEMPROIMPOS II, IMPOSTO IM
                                              WHERE IT.CDFILIAL        = C_ITEM.CDFILIAL
                                                AND IT.NRLANCTONF      = C_ITEM.NRLANCTONF
                                                AND II.CDFILIAL        = IT.CDFILIAL
                                                AND II.NRLANCTONF      = IT.NRLANCTONF
                                                AND II.NRSEQITPROD     = IT.NRSEQITPROD
                                                AND IM.CDIMPOSTONFE    = P_CDIMPOSTO
                                                AND IM.CDIMPOSTO       = II.CDIMPOSTO
                                                AND II.NRITPROIMPOS    = '1'
                                              GROUP BY IT.CDFILIAL, IT.NRLANCTONF, REPLACE(REPLACE(REPLACE(V_NRINSEST, '-', ''), '.', ''), '/', ''),
                                                    IT.NRSEQITPROD, IT.CDPRODUTO, CDSITUTRIBIT, IT.QTITEMPROD, IT.VRTOTAITEM,
                                                    IT.VRDESCITEM,IT.VRENCARGODESC, II.VRALIQITEM, DECODE(II.CDFISCALIT,'1', II.VRBASECALCIT, 0), IT.CDPRODNCMTRANS) IT
                                      WHERE IT.CDPRODUTO = PR.CDPRODUTO
                                      ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD) LOOP
                  V_CONTREG54A := NVL(V_CONTREG54A,0)+1;
                  INSERT INTO ARQMAGNETICO
                    (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
                  VALUES
                    ('07'||LPAD(RTRIM(TO_CHAR(V_CONTREG54A)), 8, '0'), C_REGISTRO54C.DSINICIO||V_SEQUENCIAL||C_REGISTRO54C.DSARQMAG, P_NRSEQUENCIAL);
                  V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)),3,'0');
                  V_CONTREG := NVL(V_CONTREG,0) + 1;
                  V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
                  SELECT MAX(CDARVPROD)
                    INTO V_CDARVPROD
                    FROM TMP_REGISTRO_PRODUTO
                   WHERE CDARVPROD = C_REGISTRO54C.CDARVPROD;
                  IF V_CDARVPROD IS NULL THEN
                     INSERT INTO TMP_REGISTRO_PRODUTO
                        (CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                         SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
                     VALUES
                        ('75', C_REGISTRO54C.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO54C.CDCLASFISC, C_REGISTRO54C.NMPRODUTO,
                         C_REGISTRO54C.SGUNIDADE, C_REGISTRO54C.CDSITUTRIBIT, C_REGISTRO54C.VRALIQIPI, C_REGISTRO54C.VRALIQICMS,
                         C_REGISTRO54C.VRPERREDBCIT, 0);
                  END IF;
               END LOOP;
            END IF;
         END IF;
      END LOOP;
   END IF;

   IF (V_CDSITUCRT <> '3') THEN
      FOR C_REGISTRO54ASN IN (SELECT
                                ('54'||
                                LPAD(NVL(IT.NRINSJURNF, '00000000000000'),14,'0')||
                                RPAD(NVL(IT.CDMODDOCFISC,'01'), 2, ' ')||
                                RPAD(DECODE(RTRIM(NVL(IT.CDSERIE,' ')),'-',' ',REPLACE(NVL(IT.CDSERIE,' '),'-', '')),3,' ')||
                                SUBSTR(LPAD(IT.NRNOTAFISC,20,'0'),15,6)||
                                RPAD(NVL(IT.CDFISCOPERNF,'0'),4,' ')||
                                LPAD(NVL(TRIM(IT.CDSITUTRIBIT),'0'),3,'0')) AS DSINICIO,
                                (RPAD(PR.CDARVPROD,14,' ')||
                                LTRIM(REPLACE(TO_CHAR(ABS(IT.QTITEMPROD),'00000000.000'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(IT.VRTOTAITEM) ,'0000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(CASE WHEN NVL(IT.VRDESCITEM,0) = 0 THEN NVL(IT.VRENCARGODESC,0) ELSE NVL(IT.VRDESCITEM,0) END),'0000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRBASECALCIT),0),'0000000000.00'),'.',''))||
                                '000000000000' ||
                                LTRIM(REPLACE(TO_CHAR(NVL(ABS(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'I')),0),'0000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRALIQITEM),0),'00.00'),'.',''))) AS DSARQMAG,
                                PR.CDARVPROD, NVL(IT.CDPRODNCMTRANS, PR.CDCLASFISC) AS CDCLASFISC, PR.NMPRODUTO, NVL(PR.SGUNIDADE, '  ') AS SGUNIDADE, NVL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                                ABS(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A'),0)) AS VRALIQIPI,
                                NVL(IT.VRALIQITEM,0) AS VRALIQICMS, NVL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                                IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
                           FROM PRODUTO PR,
                                (SELECT /*+ INDEX(PK_ITEMPRODUTO) IT*/
                                        NF.CDFILIAL, NF.NRLANCTONF, DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE) AS NRINSJURNF, NF.DTEMISSAO,
                                        NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                        EC.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                                        IT.CDPRODUTO,
                                        DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                               LPAD(CDSITUTRIBPR,3,'0')) AS CDSITUTRIBIT,
                                        IT.QTITEMPROD, SUM(IT.VRCONTITEM) AS VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                        0 AS VRALIQITEM,
                                        0 AS VRBASECALCIT,
                                        MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                                        0 AS VRPERREDBCIT,
                                        IT.CDPRODNCMTRANS
                                   FROM MAPAAPURACSN M, NOTAFISCAL NF, ITEMPRODUTO IT,
                                        CLIENTE CL, ENDECLIE EC
                                  WHERE 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 NVL(NF.IDORIGDESTNF, DECODE(NF.IDENTRSAIDOP, 'E', 'F', 'C')) = 'C'
                                    AND NF.CDFILIAL        = IT.CDFILIAL
                                    AND NF.NRLANCTONF      = IT.NRLANCTONF
                                    AND NF.IDSTATUS IN ('A','E')
                                    AND CL.CDCLIENTE(+)    = NF.CDCLIENTE
                                    AND EC.CDCLIENTE(+)    = CL.CDCLIENTE
                                    AND EC.IDTPENDECLIE(+) = NVL(CL.IDENDEMISCLI,'P')
                                    AND NF.CDMODDOCFISC    IN ('01', '1A', '04', '55')
                                  GROUP BY NF.CDFILIAL, NF.NRLANCTONF, DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE), NF.DTEMISSAO,
                                        NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ),
                                        EC.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                                        IT.CDPRODUTO, DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                        LPAD(CDSITUTRIBPR,3,'0')),
                                        IT.QTITEMPROD, IT.VRTOTAITEM,
                                        IT.VRDESCITEM,IT.VRENCARGODESC, IT.CDPRODNCMTRANS) IT
                          WHERE IT.CDPRODUTO = PR.CDPRODUTO
                          ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD) LOOP
         IF ((C_REGISTRO54ASN.CDFILIAL||C_REGISTRO54ASN.NRLANCTONF) <> (V_CDFILIAL||V_NRLANCTONF)) THEN
            V_CDFILIAL   := C_REGISTRO54ASN.CDFILIAL;
            V_NRLANCTONF := C_REGISTRO54ASN.NRLANCTONF;
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
            /*Geracao dos sequenciais "991", "992", "993" e "999" para valores de frete, seguro, PIS/COFINS e outros encargos*/
            FOR C_REGISTRO54ASN1 IN (SELECT ('54'||
                                          LPAD(NVL(C_REGISTRO54ASN.NRINSJURNF, '00000000000000'),14,'0') ||
                                          RPAD(NVL(CDMODDOCFISC,'01'), 2, ' ')             ||
                                          RPAD(DECODE(RTRIM(NVL(CDSERIE,' ')),'-',' ',REPLACE(NVL(CDSERIE,' '),'-', '')),3,' ') ||
                                          SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6)              ||
                                          RPAD(NVL(C_REGISTRO54ASN.CDFISCOPERNF,'0'),4,' ')||
                                          '   '                                            ||
                                          NUMERO_DO_ITEM                                   ||
                                          '              '                                 ||
                                          '00000000000'                                    ||
                                          '000000000000'                                   ||
                                          LTRIM(REPLACE(TO_CHAR(SUM(NVL(ABS(VRIMPOSTONF),0)),'0000000000.00'),'.','')) ||
                                          '000000000000'                                   ||
                                          '000000000000'                                   ||
                                          '000000000000'                                   ||
                                          '0000') AS DSARQMAG
                                     FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                  NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                             FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                            WHERE IE.CDFILIAL     = V_CDFILIAL    AND
                                                  IE.NRLANCTONF   = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                            UNION ALL
                                           SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                  NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                             FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                            WHERE IE.CDFILIAL     = V_CDFILIAL    AND
                                                  IE.NRLANCTONF   = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                            UNION ALL
                                           /*SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR) ) CDSERIE,
                                                  N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                             FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                            WHERE IP.CDFILIAL    = V_CDFILIAL   AND
                                                  IP.NRLANCTONF  = V_NRLANCTONF 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 DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR) ), N.CDMODDOCFISC, N.NRNOTAFISC
                                            UNION ALL */
                                           SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                  NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                             FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                            WHERE IE.CDFILIAL    = V_CDFILIAL    AND
                                                  IE.NRLANCTONF  = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                           /*UNION ALL retirado pois o valor do imposto do ST ja consta no registro 53
                                           SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                  NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                             FROM NOTAFISCAL NF, IMPOSTONF IP, IMPOSTO IM
                                            WHERE NF.CDFILIAL    = V_CDFILIAL    AND
                                                  NF.NRLANCTONF  = V_NRLANCTONF  AND
                                                  IP.CDFILIAL    = NF.CDFILIAL   AND
                                                  IP.NRLANCTONF  = NF.NRLANCTONF AND
                                                  IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                  IM.CDIMPOSTONFE = '80'         AND
                                                  IP.VRIMPOSTONF > 0                  --Outras Despesas
                                            GROUP BY DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC*/)
                                    GROUP BY LPAD(NVL(C_REGISTRO54ASN.NRINSJURNF, '00000000000000'),14,'0'),
                                             RPAD(NVL(CDMODDOCFISC,'01'), 2, ' '),
                                             RPAD(DECODE(RTRIM(NVL(CDSERIE,' ')),'-',' ',REPLACE(NVL(CDSERIE,' '),'-', '')),3,' '),
                                             SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6),
                                             RPAD(NVL(C_REGISTRO54ASN.CDFISCOPERNF,'0'),4,' '),
                                             NUMERO_DO_ITEM) LOOP
               V_CONTREGSN54A := NVL(V_CONTREGSN54A,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREGSN54A)), 8, '0'), C_REGISTRO54ASN1.DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END LOOP;
         END IF;
         V_CONTREGSN54A := NVL(V_CONTREGSN54A,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('05'||LPAD(RTRIM(TO_CHAR(V_CONTREGSN54A)), 8, '0'), C_REGISTRO54ASN.DSINICIO||V_SEQUENCIAL||C_REGISTRO54ASN.DSARQMAG, P_NRSEQUENCIAL);
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)), 3,'0');
         V_CONTREG := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
         SELECT MAX(CDARVPROD)
           INTO V_CDARVPROD
           FROM TMP_REGISTRO_PRODUTO
          WHERE CDARVPROD = C_REGISTRO54ASN.CDARVPROD;
         IF V_CDARVPROD IS NULL THEN
            INSERT INTO TMP_REGISTRO_PRODUTO
               (CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
            VALUES
               ('75', C_REGISTRO54ASN.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO54ASN.CDCLASFISC, C_REGISTRO54ASN.NMPRODUTO,
                C_REGISTRO54ASN.SGUNIDADE, C_REGISTRO54ASN.CDSITUTRIBIT, C_REGISTRO54ASN.VRALIQIPI, C_REGISTRO54ASN.VRALIQICMS,
                C_REGISTRO54ASN.VRPERREDBCIT, 0);
         END IF;
      END LOOP;
      V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
      V_CDFILIAL   := ' ';
      V_NRLANCTONF := ' ';
      V_CONTREGSN54B := NVL(V_CONTREGSN54A,0)+1;
      FOR C_REGISTRO54BSN IN (SELECT ('54'||
                                     LPAD(NVL(IT.NRINSJURNF, '00000000000000'),14,'0')||
                                     RPAD(NVL(IT.CDMODDOCFISC,'01'), 2, ' ')||
                                     RPAD(DECODE(RTRIM(NVL(IT.CDSERIE,' ')),'-',' ',REPLACE(NVL(IT.CDSERIE,' '),'-', '')),3,' ')||
                                     SUBSTR(LPAD(IT.NRNOTAFISC,20,'0'),15,6)||
                                     RPAD(NVL(IT.CDFISCOPERNF,'0'),4,' ')||
                                     LPAD(NVL(IT.CDSITUTRIBIT,' '),3,'0')) AS DSINICIO,
                                     (RPAD(PR.CDARVPROD,14,' ')||
                                     LTRIM(REPLACE(TO_CHAR(ABS(IT.QTITEMPROD),'00000000.000'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(IT.VRTOTAITEM) ,'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(CASE WHEN NVL(IT.VRDESCITEM,0) = 0 THEN NVL(IT.VRENCARGODESC,0) ELSE NVL(IT.VRDESCITEM,0) END),'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRBASECALCIT),0),'0000000000.00'),'.',''))||
                                     '000000000000' ||
                                     LTRIM(REPLACE(TO_CHAR(NVL(ABS(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'I')),0),'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRALIQITEM),0),'00.00'),'.',''))) AS DSARQMAG,
                                     PR.CDARVPROD, NVL(IT.CDPRODNCMTRANS, PR.CDCLASFISC) AS CDCLASFISC, PR.NMPRODUTO, NVL(PR.SGUNIDADE, '  ') AS SGUNIDADE, NVL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                                     ABS(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A'),0)) AS VRALIQIPI,
                                     NVL(IT.VRALIQITEM,0) AS VRALIQICMS, NVL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                                     IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
                                FROM PRODUTO PR,
                                     (SELECT /*+ INDEX(PK_ITEMPRODUTO) IP*/
                                             NF.CDFILIAL, NF.NRLANCTONF, DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN) AS NRINSJURNF, NF.DTENTRSAID,
                                             NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                             EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                                             IT.CDPRODUTO,
                                             DECODE(PF.IDSIMPLESNACIONAL,'S',DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                                    LPAD(CDSITUTRIBPR,3,'0')),LPAD(CDSITUTRIBPR,3,'0')) AS CDSITUTRIBIT,
                                             IT.QTITEMPROD, SUM(IT.VRCONTITEM) AS VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                             0 AS VRALIQITEM,
                                             0 AS VRBASECALCIT,
                                             MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                                             0 AS VRPERREDBCIT,
                                             IT.CDPRODNCMTRANS
                                        FROM MAPAAPURACSN M, NOTAFISCAL NF, ITEMPRODUTO IT,
                                             FORNECEDOR FO, ENDEFORN EF, PARAMFORNE PF
                                       WHERE 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 NVL(NF.IDORIGDESTNF, DECODE(NF.IDENTRSAIDOP, 'E', 'F', 'C')) = 'F'
                                         AND NF.CDFILIAL        = IT.CDFILIAL
                                         AND NF.NRLANCTONF      = IT.NRLANCTONF
                                         AND NF.IDSTATUS IN ('A','E')
                                         AND PF.CDFORNECED(+)   = NF.CDFORNECED
                                         AND FO.CDFORNECED(+)   = NF.CDFORNECED
                                         AND EF.CDFORNECED(+)   = FO.CDFORNECED
                                         AND EF.IDTPENDEFORN(+) = NVL(FO.IDENDEMISFOR,'P')
                                         AND NF.CDMODDOCFISC    IN ('01', '1A', '04', '55')
                                       GROUP BY NF.CDFILIAL, NF.NRLANCTONF, DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN), NF.DTENTRSAID,
                                             NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ),
                                             EF.SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                                             IT.CDPRODUTO, DECODE(PF.IDSIMPLESNACIONAL,'S',DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                             LPAD(CDSITUTRIBPR,3,'0')),LPAD(CDSITUTRIBPR,3,'0')), IT.QTITEMPROD, IT.VRTOTAITEM,
                                             IT.VRDESCITEM,IT.VRENCARGODESC, IT.CDPRODNCMTRANS) IT
                               WHERE IT.CDPRODUTO = PR.CDPRODUTO
                               ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD) LOOP
         IF ((C_REGISTRO54BSN.CDFILIAL||C_REGISTRO54BSN.NRLANCTONF) <> (V_CDFILIAL||V_NRLANCTONF)) THEN
            V_CDFILIAL   := C_REGISTRO54BSN.CDFILIAL;
            V_NRLANCTONF := C_REGISTRO54BSN.NRLANCTONF;
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
            /*Geracao dos sequenciais "991", "992", "993" e "999" para valores de frete, seguro, PIS/COFINS e outros encargos*/
            FOR C_REGISTRO54BSN1 IN (SELECT ('54'||
                                            LPAD(NVL(C_REGISTRO54BSN.NRINSJURNF, '00000000000000'),14,'0') ||
                                            RPAD(NVL(CDMODDOCFISC,'01'), 2, ' ')             ||
                                            RPAD(DECODE(RTRIM(NVL(CDSERIE,' ')),'-',' ',REPLACE(NVL(CDSERIE,' '),'-', '')),3,' ') ||
                                            SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6)              ||
                                            RPAD(NVL(C_REGISTRO54BSN.CDFISCOPERNF,'0'),4,' ')  ||
                                            '   '                                            ||
                                            NUMERO_DO_ITEM                                   ||
                                            '              '                                 ||
                                            '00000000000'                                    ||
                                            '000000000000'                                   ||
                                            LTRIM(REPLACE(TO_CHAR(SUM(NVL(ABS(VRIMPOSTONF),0)),'0000000000.00'),'.','')) ||
                                            '000000000000'                                   ||
                                            '000000000000'                                   ||
                                            '000000000000'                                   ||
                                            '0000') AS DSARQMAG
                                       FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                               FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                              WHERE IE.CDFILIAL     = V_CDFILIAL    AND
                                                    IE.NRLANCTONF   = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                              UNION ALL
                                             SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                               FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                              WHERE IE.CDFILIAL     = V_CDFILIAL    AND
                                                    IE.NRLANCTONF   = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                              UNION ALL
                                             /*SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR) ) CDSERIE,
                                                    N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                               FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                              WHERE IP.CDFILIAL    = V_CDFILIAL   AND
                                                    IP.NRLANCTONF  = V_NRLANCTONF 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 DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR) ), N.CDMODDOCFISC, N.NRNOTAFISC
                                              UNION ALL */
                                             SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                               FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                              WHERE IE.CDFILIAL    = V_CDFILIAL    AND
                                                    IE.NRLANCTONF  = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                             /*UNION ALL retirado pois o valor do imposto do ST ja consta no registro 53
                                             SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                               FROM NOTAFISCAL NF, IMPOSTONF IP, IMPOSTO IM
                                              WHERE NF.CDFILIAL    = V_CDFILIAL    AND
                                                    NF.NRLANCTONF  = V_NRLANCTONF  AND
                                                    IP.CDFILIAL    = NF.CDFILIAL   AND
                                                    IP.NRLANCTONF  = NF.NRLANCTONF AND
                                                    IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                    IM.CDIMPOSTONFE = '80'         AND
                                                    IP.VRIMPOSTONF > 0                  --Outras Despesas
                                              GROUP BY DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC*/)
                                      GROUP BY LPAD(NVL(C_REGISTRO54BSN.NRINSJURNF, '00000000000000'),14,'0'),
                                               RPAD(NVL(CDMODDOCFISC,'01'), 2, ' '),
                                               RPAD(DECODE(RTRIM(NVL(CDSERIE,' ')),'-',' ',REPLACE(NVL(CDSERIE,' '),'-', '')),3,' '),
                                               SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6),
                                               RPAD(NVL(C_REGISTRO54BSN.CDFISCOPERNF,'0'),4,' '),
                                               NUMERO_DO_ITEM) LOOP
               V_CONTREGSN54B := NVL(V_CONTREGSN54B,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('06'||LPAD(RTRIM(TO_CHAR(V_CONTREGSN54B)), 8, '0'), C_REGISTRO54BSN1.DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END LOOP;
         END IF;
         V_CONTREGSN54B := NVL(V_CONTREGSN54B,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('06'||LPAD(RTRIM(TO_CHAR(V_CONTREGSN54B)), 8, '0'), C_REGISTRO54BSN.DSINICIO||V_SEQUENCIAL||C_REGISTRO54BSN.DSARQMAG, P_NRSEQUENCIAL);
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)),3,'0');
         V_CONTREG := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
         SELECT MAX(CDARVPROD)
           INTO V_CDARVPROD
           FROM TMP_REGISTRO_PRODUTO
           WHERE CDARVPROD = C_REGISTRO54BSN.CDARVPROD;
         IF V_CDARVPROD IS NULL THEN
            INSERT INTO TMP_REGISTRO_PRODUTO
               (CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
            VALUES
               ('75', C_REGISTRO54BSN.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO54BSN.CDCLASFISC, C_REGISTRO54BSN.NMPRODUTO,
                C_REGISTRO54BSN.SGUNIDADE, C_REGISTRO54BSN.CDSITUTRIBIT, C_REGISTRO54BSN.VRALIQIPI, C_REGISTRO54BSN.VRALIQICMS,
                C_REGISTRO54BSN.VRPERREDBCIT, 0);
         END IF;
      END LOOP;
      V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
      V_CDFILIAL   := ' ';
      V_NRLANCTONF := ' ';
      V_CONTREGSN54C := NVL(V_CONTREGSN54B,V_CONTREGSN54A)+1;
      FOR C_REGISTRO54CSN IN (SELECT ('54'||
                                     RPAD(NVL(IT.NRINSJURNF, '00000000000000'),14,' ')||
                                     RPAD(NVL(IT.CDMODDOCFISC,'01'), 2, ' ')||
                                     RPAD(DECODE(RTRIM(NVL(IT.CDSERIE,' ')),'-',' ',REPLACE(NVL(IT.CDSERIE,' '),'-', '')),3,' ')||
                                     SUBSTR(LPAD(IT.NRNOTAFISC,20,'0'),15,6)||
                                     RPAD(NVL(IT.CDFISCOPERNF,'0'),4,' ')||
                                     LPAD(NVL(IT.CDSITUTRIBIT,' '),3,'0')) AS DSINICIO,
                                     (RPAD(PR.CDARVPROD,14,' ')||
                                     LTRIM(REPLACE(TO_CHAR(ABS(IT.QTITEMPROD),'00000000.000'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(IT.VRTOTAITEM) ,'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(ABS(CASE WHEN NVL(IT.VRDESCITEM,0) = 0 THEN NVL(IT.VRENCARGODESC,0) ELSE NVL(IT.VRDESCITEM,0) END),'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRBASECALCIT),0),'0000000000.00'),'.',''))||
                                     '000000000000' ||
                                     LTRIM(REPLACE(TO_CHAR(NVL(ABS(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'I')),0),'0000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(NVL(ABS(IT.VRALIQITEM),0),'00.00'),'.',''))) AS DSARQMAG,
                                     PR.CDARVPROD, NVL(IT.CDPRODNCMTRANS, PR.CDCLASFISC) AS CDCLASFISC, PR.NMPRODUTO, NVL(PR.SGUNIDADE, '  ') AS SGUNIDADE, NVL(IT.CDSITUTRIBIT, '   ') AS CDSITUTRIBIT,
                                     ABS(NVL(VALOR_IMPOSTO_ITEM_NFE(IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, '10', 'A'),0)) AS VRALIQIPI,
                                     NVL(IT.VRALIQITEM,0) AS VRALIQICMS, NVL(IT.VRPERREDBCIT,0) VRPERREDBCIT,
                                     IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD, IT.NRINSJURNF, IT.CDFISCOPERNF
                                FROM PRODUTO PR,
                                     (SELECT /*+ INDEX(PK_ITEMPRODUTO) IP*/
                                             NF.CDFILIAL, NF.NRLANCTONF, REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', '') AS NRINSJURNF, NF.DTEMISSAO,
                                             NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                             DECODE(NVL(FI.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO) AS SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                                             IT.CDPRODUTO,
                                             DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                             LPAD(CDSITUTRIBPR,3,'0')) AS CDSITUTRIBIT,
                                             IT.QTITEMPROD, SUM(IT.VRCONTITEM) AS VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                             0 AS VRALIQITEM,
                                             0 AS VRBASECALCIT,
                                             MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                                             0 AS VRPERREDBCIT,
                                             IT.CDPRODNCMTRANS
                                        FROM MAPAAPURACSN M, NOTAFISCAL NF, ITEMPRODUTO IT,
                                             FILIAL FI, ENDEFILI EF, INSCRESTAD IE
                                       WHERE 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.IDENTRSAIDOP    = 'S'
                                         AND NVL(NF.IDORIGDESTNF, DECODE(NF.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                         AND NF.CDFILIAL        = IT.CDFILIAL
                                         AND NF.NRLANCTONF      = IT.NRLANCTONF
                                         AND NF.IDSTATUS IN ('A','E')
                                         AND FI.CDFILIAL(+)     = NF.CDFILIRECE
                                         AND EF.CDFILIAL(+)     = FI.CDFILIAL
                                         AND EF.IDTPENDEFILI(+) = NVL(FI.IDENDEMISFIL,'P')
                                         AND NF.CDINSCESTA      = IE.CDINSCESTA
                                         AND NF.CDMODDOCFISC    IN ('01', '1A', '04', '55')
                                       GROUP BY NF.CDFILIAL, NF.NRLANCTONF, FI.NRINSJURFILI, NF.DTEMISSAO,
                                             NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ),
                                             DECODE(NVL(FI.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO), NF.NRNOTAFISC, IT.NRSEQITPROD,
                                             IT.CDPRODUTO, DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                             LPAD(CDSITUTRIBPR,3,'0')),
                                             IT.QTITEMPROD, IT.VRTOTAITEM,
                                             IT.VRDESCITEM,IT.VRENCARGODESC, IT.CDPRODNCMTRANS
                                       UNION ALL
                                      SELECT /*+ INDEX(PK_ITEMPRODUTO) IP*/
                                             NF.CDFILIAL, NF.NRLANCTONF, REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', '') AS NRINSJURNF, NF.DTENTRSAID,
                                             NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                             DECODE(NVL(FI.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO) AS SGESTADO, NF.NRNOTAFISC, IT.NRSEQITPROD,
                                             IT.CDPRODUTO,
                                             DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                                    LPAD(CDSITUTRIBPR,3,'0')) AS CDSITUTRIBIT,
                                             IT.QTITEMPROD, SUM(VRCONTITEM) AS VRPRODUTO, IT.VRTOTAITEM, IT.VRDESCITEM,IT.VRENCARGODESC,
                                             0 AS VRALIQITEM,
                                             0 AS VRBASECALCIT,
                                             MAX(CDFISOPEITEM) AS CDFISCOPERNF,
                                             0 AS VRPERREDBCIT,
                                             IT.CDPRODNCMTRANS
                                        FROM MAPAAPURACSN M, NOTAFISCAL NF, ITEMPRODUTO IT,
                                             FILIAL FI, ENDEFILI EF, INSCRESTAD IE
                                       WHERE 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.IDENTRSAIDOP    = 'E'
                                         AND NVL(NF.IDORIGDESTNF, DECODE(NF.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                         AND NF.CDFILIAL        = IT.CDFILIAL
                                         AND NF.NRLANCTONF      = IT.NRLANCTONF
                                         AND NF.IDSTATUS IN ('A','E')
                                         AND FI.CDFILIAL(+)     = NF.CDFILIEMIT
                                         AND EF.CDFILIAL(+)     = FI.CDFILIAL
                                         AND EF.IDTPENDEFILI(+) = NVL(FI.IDENDEMISFIL,'P')
                                         AND NF.CDINSCESTA      = IE.CDINSCESTA
                                         AND NF.CDMODDOCFISC    IN ('01', '1A', '04', '55')
                                       GROUP BY NF.CDFILIAL, NF.NRLANCTONF, FI.NRINSJURFILI, NF.DTENTRSAID,
                                             NF.CDMODDOCFISC, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ),
                                             DECODE(NVL(FI.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO), NF.NRNOTAFISC, IT.NRSEQITPROD,
                                             IT.CDPRODUTO, DECODE(NF.CDMODDOCFISC,'55',(DECODE(LENGTH(CDFISCOPERSN),4,SUBSTR(CDFISCOPERSN,2,3),LPAD(CDFISCOPERSN,3,'0'))),
                                             LPAD(CDSITUTRIBPR,3,'0')),
                                             IT.QTITEMPROD, IT.VRTOTAITEM,
                                             IT.VRDESCITEM,IT.VRENCARGODESC, IT.CDPRODNCMTRANS) IT
                               WHERE IT.CDPRODUTO = PR.CDPRODUTO
                               ORDER BY IT.CDFILIAL, IT.NRLANCTONF, IT.NRSEQITPROD) LOOP
         IF ((C_REGISTRO54CSN.CDFILIAL||C_REGISTRO54CSN.NRLANCTONF) <> (V_CDFILIAL||V_NRLANCTONF)) THEN
            V_CDFILIAL   := C_REGISTRO54CSN.CDFILIAL;
            V_NRLANCTONF := C_REGISTRO54CSN.NRLANCTONF;
            V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER('1'))),3,'0');
            /*Geracao dos sequenciais "991", "992", "993" e "999" para valores de frete, seguro, PIS/COFINS e outros encargos*/
            FOR C_REGISTRO54CSN1 IN (SELECT ('54'||
                                            LPAD(NVL(C_REGISTRO54CSN.NRINSJURNF, '00000000000000'),14,'0') ||
                                            RPAD(NVL(CDMODDOCFISC,'01'), 2, ' ')             ||
                                            RPAD(DECODE(RTRIM(NVL(CDSERIE,' ')),'-',' ',REPLACE(NVL(CDSERIE,' '),'-', '')),3,' ') ||
                                            SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6)              ||
                                            RPAD(NVL(C_REGISTRO54CSN.CDFISCOPERNF,'0'),4,' ')  ||
                                            '   '                                            ||
                                            NUMERO_DO_ITEM                                   ||
                                            '              '                                 ||
                                            '00000000000'                                    ||
                                            '000000000000'                                   ||
                                            LTRIM(REPLACE(TO_CHAR(SUM(NVL(ABS(VRIMPOSTONF),0)),'0000000000.00'),'.','')) ||
                                            '000000000000'                                   ||
                                            '000000000000'                                   ||
                                            '000000000000'                                   ||
                                            '0000') AS DSARQMAG
                                       FROM (SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '991' AS NUMERO_DO_ITEM
                                               FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                              WHERE IE.CDFILIAL     = V_CDFILIAL    AND
                                                    IE.NRLANCTONF   = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                              UNION ALL
                                             SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '992' AS NUMERO_DO_ITEM
                                               FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                              WHERE IE.CDFILIAL     = V_CDFILIAL    AND
                                                    IE.NRLANCTONF   = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ),
                                                       NF.CDMODDOCFISC, NF.NRNOTAFISC
                                              UNION ALL
                                             /*SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR) ) CDSERIE,
                                                    N.CDMODDOCFISC, N.NRNOTAFISC, '993' AS NUMERO_DO_ITEM
                                               FROM IMPOSTONF IP, NOTAFISCAL N, IMPOSTO IM
                                              WHERE IP.CDFILIAL    = V_CDFILIAL   AND
                                                    IP.NRLANCTONF  = V_NRLANCTONF 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 DECODE(NVL(N.CDSERIE, N.CDSERIEENTR), '0', ' ', NVL(N.CDSERIE, N.CDSERIEENTR) ), N.CDMODDOCFISC, N.NRNOTAFISC
                                              UNION ALL */
                                             SELECT SUM(VRITEMENCA) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                               FROM ITEMENCARGO IE, ENCARGO EN, NOTAFISCAL NF
                                              WHERE IE.CDFILIAL    = V_CDFILIAL    AND
                                                    IE.NRLANCTONF  = V_NRLANCTONF  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 DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC
                                            /* UNION ALL retirado pois o valor do imposto do ST ja consta no registro 53
                                             SELECT SUM(IP.VRIMPOSTONF) AS VRIMPOSTONF, DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ) CDSERIE,
                                                    NF.CDMODDOCFISC, NF.NRNOTAFISC, '999' AS NUMERO_DO_ITEM
                                               FROM NOTAFISCAL NF, IMPOSTONF IP, IMPOSTO IM
                                              WHERE NF.CDFILIAL    = V_CDFILIAL    AND
                                                    NF.NRLANCTONF  = V_NRLANCTONF  AND
                                                    IP.CDFILIAL    = NF.CDFILIAL   AND
                                                    IP.NRLANCTONF  = NF.NRLANCTONF AND
                                                    IP.CDIMPOSTO   = IM.CDIMPOSTO  AND
                                                    IM.CDIMPOSTONFE = '80'         AND
                                                    IP.VRIMPOSTONF > 0                  --Outras Despesas
                                              GROUP BY DECODE(NVL(NF.CDSERIE, NF.CDSERIEENTR), '0', ' ', NVL(NF.CDSERIE, NF.CDSERIEENTR) ), NF.CDMODDOCFISC, NF.NRNOTAFISC*/)
                                      GROUP BY LPAD(NVL(C_REGISTRO54CSN.NRINSJURNF, '00000000000000'),14,'0'),
                                            RPAD(NVL(CDMODDOCFISC,'01'), 2, ' '),
                                            RPAD(DECODE(RTRIM(NVL(CDSERIE,' ')),'-',' ',REPLACE(NVL(CDSERIE,' '),'-', '')),3,' '),
                                            SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6),
                                            RPAD(NVL(C_REGISTRO54CSN.CDFISCOPERNF,'0'),4,' '),
                                            NUMERO_DO_ITEM) LOOP
               V_CONTREGSN54C := NVL(V_CONTREGSN54C,0)+1;
               INSERT INTO ARQMAGNETICO
                  (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                  ('07'||LPAD(RTRIM(TO_CHAR(V_CONTREGSN54C)), 8, '0'), C_REGISTRO54CSN1.DSARQMAG, P_NRSEQUENCIAL);
               V_CONTREG := NVL(V_CONTREG,0) + 1;
               V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
            END LOOP;
         END IF;
         V_CONTREGSN54C := NVL(V_CONTREGSN54C,0)+1;
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('07'||LPAD(RTRIM(TO_CHAR(V_CONTREGSN54C)), 8, '0'), C_REGISTRO54CSN.DSINICIO||V_SEQUENCIAL ||C_REGISTRO54CSN.DSARQMAG, P_NRSEQUENCIAL);
         V_SEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_SEQUENCIAL))+1)),3,'0');
         V_CONTREG := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
         SELECT MAX(CDARVPROD)
           INTO V_CDARVPROD
           FROM TMP_REGISTRO_PRODUTO
          WHERE CDARVPROD = C_REGISTRO54CSN.CDARVPROD;
         IF V_CDARVPROD IS NULL THEN
            INSERT INTO TMP_REGISTRO_PRODUTO
               (CDTIPOREG, CDARVPROD, DTINICIAL, DTFINAL, CDCLASFISC, NMPRODUTO,
                SGUNIDADE, CDSITUTRIBPR, VRALIQIPI, VRALIQICMS, VRPERREDBCIT, VRBASESUBST)
            VALUES
               ('75', C_REGISTRO54CSN.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO54CSN.CDCLASFISC, C_REGISTRO54CSN.NMPRODUTO,
                C_REGISTRO54CSN.SGUNIDADE, C_REGISTRO54CSN.CDSITUTRIBIT, C_REGISTRO54CSN.VRALIQIPI, C_REGISTRO54CSN.VRALIQICMS,
                C_REGISTRO54CSN.VRPERREDBCIT, 0);
         END IF;
      END LOOP;
   END IF;

   IF V_CONTREG > 0 THEN
      V_STTOTAL := P_CONTTOTAL||'54'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
   END IF;
   RETURN(V_STTOTAL||';'||TO_CHAR(V_CONTGERAL));
END;
/