CREATE OR REPLACE PROCEDURE ARQUIVO_MAGNETICO( 
   P_REGISTRO     IN VARCHAR2,
   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_DTPOSICAO    IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_CDPAIS       IN ESTADO.CDPAIS%TYPE,
   P_SGESTADO     IN VARCHAR2,
   P_VERSAO       IN NOTAFISCAL.IDSTATUS%TYPE,
   P_FINALIDADE   IN NOTAFISCAL.IDSTATUS%TYPE,
   P_NRSEQARQMAG  IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_LIVRO        IN NOTAFISCAL.NRLANCTONF%TYPE,
   P_STARQEXP     IN VARCHAR2,
   P_INVENTARIO   IN VARCHAR2,
   P_GERAFORSALE  IN VARCHAR2 DEFAULT 'N')
AS
   V_CONTGERAL      NUMBER;
   V_CONTREG        NUMBER;
   V_CONTADOR       NUMBER;
   V_CONTREG54A     NUMBER;
   V_CONTREG54B     NUMBER;
   V_CONTREG54C     NUMBER;
   V_CONTREG54      NUMBER;
   V_CONTREG60      NUMBER;
   V_TOT_REG88      NUMBER;
   V_TOT_REG        NUMBER;
   V_TOTAL_LINHA    VARCHAR2(2000);
   V_STTOTAL        VARCHAR2(160);
   V_REG60MAIDR     VARCHAR2(160);
   V_TOTALRE60      VARCHAR2(160);
   V_TOTALRE54      VARCHAR2(160);
   V_TOTALGERAL     VARCHAR2(10);
   V_CDARVPROD      VARCHAR2(20);
   V_SEQUENCIAL     VARCHAR2(03);
   V_CDFILIAL       VARCHAR2(04);
   V_NRLANCTONF     VARCHAR2(10);
   V_SEQMAGNET      VARCHAR2(20);
   V_REGVAZIO       VARCHAR2(2000);
   V_TOTAL_REG      VARCHAR2(2000);
   V_CDSITUCRT      VARCHAR2(01);
   V_CDIMPOSTRIB    VARCHAR2(02);

   V_NRINSJUR        VARCHAR2(30);
   V_NRINSEST        VARCHAR2(50);
   V_IDENDEMIS       VARCHAR2(01);
   V_IDTPIJURCLIE    VARCHAR2(01);
   V_SGESTADO        VARCHAR2(02);

   V_SGESTAINSC      VARCHAR2(02);

   DSARQMAG         VARCHAR2(2000);

BEGIN

   FOR C_REGISTRO10 IN (SELECT SUBSTR(
                               '10'||
                               RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(NVL(ST.NRINSJURIEST,IE.NRINSJURIEST),'.',''),',',''),'-',''),'/',''),1,14),14,' ')||
                               RPAD(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(NVL(ST.CDINSCESTAST,IE.CDINSCESTA)),1,5),'ISENT','ISENTO',NVL(ST.CDINSCESTAST,IE.CDINSCESTA)),'.',''),',',''),'-',''),'/',''),14,' ')||
                               RPAD(EP.NMRAZSOCEMPR,35,' ')||
                               RPAD(NVL(ST.NMMUNICIPIO,MU.NMMUNICIPIO),30,' ') ||
                               RPAD(NVL(ST.SGESTADO,IE.SGESTADO),2,' ')     ||
                               LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(NVL(ST.NRTELEIEST,IE.NRTELEIEST),'0'),' ',''),')',''),'(',''),'-',''),'.',''),10,'0')||
                               TO_CHAR(TO_DATE(P_DTINICIAL),'yyyymmdd')||
                               TO_CHAR(TO_DATE(P_DTFINAL),'yyyymmdd')||
                               P_VERSAO ||'3'||P_FINALIDADE,1,126) AS DSARQMAG
                          FROM EMPRESA EP, INSCRESTAD IE, MUNICIPIO MU,
                               (SELECT I.CDEMPRESA, I.CDINSCESTA, I.NRINSJURIEST, I.CDINSCESTAST,
                                       M.NMMUNICIPIO, I.SGESTADO, I.NRTELEIEST
                                  FROM INSCRESTADST I, MUNICIPIO M
                                 WHERE I.CDEMPRESA    = P_CDEMPRESA
                                   AND I.CDINSCESTA   = P_CDINSCESTA
                                   AND I.CDINSCESTAST = P_CDINSCESTAST
                                   AND I.CDPAIS       = M.CDPAIS(+)
                                   AND I.SGESTADO     = M.SGESTADO(+)
                                   AND I.CDMUNICIPIO  = M.CDMUNICIPIO(+)) ST
                         WHERE EP.CDEMPRESA   = P_CDEMPRESA       AND
                               IE.CDEMPRESA   = EP.CDEMPRESA      AND
                               IE.CDINSCESTA  = P_CDINSCESTA      AND
                               IE.CDPAIS      = MU.CDPAIS(+)      AND
                               IE.SGESTADO    = MU.SGESTADO(+)    AND
                               IE.CDMUNICIPIO = MU.CDMUNICIPIO(+) AND
                               IE.CDEMPRESA   = ST.CDEMPRESA(+)   AND
                               IE.CDINSCESTA  = ST.CDINSCESTA(+)) LOOP

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('00'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO10.DSARQMAG, P_NRSEQARQMAG);
   END LOOP;

   FOR C_REGISTRO11 IN (SELECT SUBSTR(
                               '11'||
                               RPAD(NVL(ST.DSENDEIEST,IE.DSENDEIEST),34,' ')||
                               RPAD('00000s/n',27,' ')||
                               RPAD(NVL(ST.NMBAIRIEST,IE.NMBAIRIEST),15,' ')||
                               LPAD(REPLACE(REPLACE(NVL(ST.NRCEPIEST,IE.NRCEPIEST),'.',''),'-',''),8,'0')||
                               RPAD(NVL(NVL(ST.NMREPRINSEST,IE.NMREPRINSEST), ' '),28,' ')||
                               LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(NVL(ST.NRTELEIEST,IE.NRTELEIEST),'0'),' ',''),')',''),'(',''),'-',''),'.',''),12,'0')
                               ,1,126) AS DSARQMAG
                          FROM INSCRESTAD IE, (SELECT I.CDEMPRESA, I.CDINSCESTA, I.DSENDEIEST, I.NMBAIRIEST,
                                                      I.NRCEPIEST, I.NMREPRINSEST, I.NRTELEIEST
                                                 FROM INSCRESTADST I
                                                WHERE I.CDEMPRESA    = P_CDEMPRESA
                                                  AND I.CDINSCESTA   = P_CDINSCESTA
                                                  AND I.CDINSCESTAST = P_CDINSCESTAST) ST
                         WHERE IE.CDEMPRESA  = P_CDEMPRESA     AND
                               IE.CDINSCESTA = P_CDINSCESTA    AND
                               IE.CDEMPRESA  = ST.CDEMPRESA(+) AND
                               IE.CDINSCESTA = ST.CDINSCESTA(+)) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('01'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO11.DSARQMAG, P_NRSEQARQMAG);
   END LOOP;

   /*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;

   /*SELECIONA O ESTADO DA INSCRICAO ESTADUAL*/
   SELECT SGESTADO
     INTO V_SGESTAINSC
     FROM INSCRESTAD
    WHERE CDINSCESTA = P_CDINSCESTA
      AND CDEMPRESA  = P_CDEMPRESA;

   /* GERACAO DO REGISTRO 50 */
   V_CONTGERAL := 2;
   V_CONTREG := 0;
   V_CONTADOR := 0;

   IF (NVL(P_GERAFORSALE,'N') = 'N') AND (V_CDSITUCRT = '3') THEN
      FOR C_REGISTRO50_CANC 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.CDFORNECED, N.CDCLIENTE, N.DTENTRSAID,
                                       N.DTEMISSAO, N.CDMODDOCFISC, N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTIFICAOP,
                                       N.VRNOTAFISC AS VRCONTABILNF,
                                       TO_CHAR(DECODE(N.IDENTRSAIDOP, 'S', N.DTEMISSAO, N.DTENTRSAID),'yyyymmdd') AS DATANOTA,
                                       (CASE WHEN N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL THEN 'S' ELSE 'X' END) AS CANCELADA
                                  FROM NOTAFISCAL N
                                 WHERE N.IDSTATUS     = 'C'
                                   AND TRUNC(N.DTCANCELANF) BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND N.CDEMPRESA    = P_CDEMPRESA
                                   AND N.CDINSCESTA   = P_CDINSCESTA
                                   AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                   AND N.IDENTRSAIDOP = 'S'
                                   AND N.IDORIGDESTNF IN ('C','F')
                                   AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')) LOOP

         IF C_REGISTRO50_CANC.IDORIGDESTNF = 'C' THEN
            SELECT DECODE(IDTPIJURCLIE,'I','','O','',NRINSJURCLIE),
                   RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NRINSESTCLIE, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' '),
                   (NVL(IDENDEMISCLI,'P')), IDTPIJURCLIE
              INTO V_NRINSJUR, V_NRINSEST, V_IDENDEMIS, V_IDTPIJURCLIE
              FROM CLIENTE
             WHERE CDCLIENTE = C_REGISTRO50_CANC.CDCLIENTE;

            BEGIN
               SELECT RPAD(NVL(SGESTADO,' '),2,' ')
                 INTO V_SGESTADO
                 FROM ENDECLIE
                WHERE CDCLIENTE    = C_REGISTRO50_CANC.CDCLIENTE
                  AND IDTPENDECLIE = V_IDENDEMIS;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_SGESTADO := NULL;
            END;
         ELSE
            SELECT DECODE(IDTPIJURFORN,'I','','O','',NRINSJURFORN),
                   RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NRINSESTFORN, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' '),
                   (NVL(IDENDEMISFOR,'P')),
                   IDTPIJURFORN
              INTO V_NRINSJUR, V_NRINSEST, V_IDENDEMIS, V_IDTPIJURCLIE
              FROM FORNECEDOR F
             WHERE F.CDFORNECED = C_REGISTRO50_CANC.CDFORNECED;

            BEGIN
               SELECT RPAD(NVL(SGESTADO,' '),2,' ')
                 INTO V_SGESTADO
                 FROM ENDEFORN
                WHERE CDFORNECED   = C_REGISTRO50_CANC.CDFORNECED
                  AND IDTPENDEFORN = V_IDENDEMIS;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_SGESTADO := NULL;
            END;
         END IF;

         IF ((V_SGESTADO IS NOT NULL) OR (V_SGESTADO <> '')) THEN
            DSARQMAG := SUBSTR('50'||
                               LPAD(NVL(V_NRINSJUR, '00000000000000'),14,'0')   ||
                               V_NRINSEST                                       ||
                               C_REGISTRO50_CANC.DATANOTA                       ||
                               V_SGESTADO                                       ||
                               C_REGISTRO50_CANC.CDMODDOCFISC                   ||
                               RPAD(C_REGISTRO50_CANC.CDSERIE,3,' ')                       ||
                               SUBSTR(LPAD(C_REGISTRO50_CANC.NRNOTAFISC,20,'0'),15,6)      ||
                               '0000'                                                      ||
                               C_REGISTRO50_CANC.IDENTIFICAOP                              ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_CANC.VRCONTABILNF),'00000000000.00'),'.','')) ||
                               '0000000000000' ||
                               '0000000000000' ||
                               '0000000000000' ||
                               '0000000000000' ||
                               '0000' ||
                               C_REGISTRO50_CANC.CANCELADA ,1,126);

            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('02'||LPAD(RTRIM(V_CONTADOR), 8, '0'), DSARQMAG, P_NRSEQARQMAG);

            V_CONTADOR  := NVL(V_CONTADOR,0) + 1;
            V_CONTREG   := NVL(V_CONTREG,0) + 1;
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
         END IF;
      END LOOP;

      FOR C_REGISTRO50_NOTA 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.CDFILIEMIT,
                                       TO_CHAR(DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID, N.DTEMISSAO),'yyyymmdd') AS DATANOTA,
                                       N.CDFILIRECE, RPAD(NVL(N.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                       NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) AS IDORIGDESTNF,
                                       N.IDENTRSAIDOP, DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'3','I','7','I','N') AS ID_NACIONAL,
                                       RPAD(NVL(I.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                       DECODE(N.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTIFICAOP,
                                       DECODE(I.CDFISCALNF, '1', I.VRALIQNF, 0) AS VRALIQNF,
                                       SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                       SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                       SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                       SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                       SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                       (CASE N.IDENTRSAIDOP WHEN 'S' THEN (CASE WHEN N.DTEMISSAO  BETWEEN M.DTINIPERIODO AND M.DTFIMPERIODO THEN 'N' ELSE 'E' END)
                                                                     ELSE (CASE WHEN N.DTENTRSAID BETWEEN M.DTINIPERIODO AND M.DTFIMPERIODO THEN 'N' ELSE 'E' END) END) AS CANCELADA
                                  FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM
                                 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 I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                   AND I.CDIMPOSTO       = IM.CDIMPOSTO
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND N.CDFILIAL        = I.CDFILIAL
                                   AND N.NRLANCTONF      = I.NRLANCTONF
                                   AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                   AND N.IDSTATUS        IN ('A','E')
                                   AND N.CDMODDOCFISC    IN ('01', '1A', '04', '06', '21', '22', '55')
                                 GROUP BY N.NRNOTAFISC,
                                       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)),' '),'-', '')),
                                       N.CDCLIENTE, N.CDFORNECED, N.CDFILIEMIT, TO_CHAR(DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID, N.DTEMISSAO),'yyyymmdd'),
                                       N.CDFILIRECE, RPAD(NVL(N.CDMODDOCFISC,'01'), 2, ' '),
                                       NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')),
                                       N.IDENTRSAIDOP, DECODE(SUBSTR(I.CDFISCOPERNF,1,1),'3','I','7','I','N'),
                                       RPAD(NVL(I.CDFISCOPERNF,'0'),4,' '),
                                       DECODE(N.IDENTRSAIDOP, 'E', 'T', 'P'),
                                       DECODE(I.CDFISCALNF, '1', I.VRALIQNF, 0),
                                       (CASE N.IDENTRSAIDOP WHEN 'S' THEN (CASE WHEN N.DTEMISSAO  BETWEEN M.DTINIPERIODO AND M.DTFIMPERIODO THEN 'N' ELSE 'E' END)
                                                                     ELSE (CASE WHEN N.DTENTRSAID BETWEEN M.DTINIPERIODO AND M.DTFIMPERIODO THEN 'N' ELSE 'E' END) END)
                                 ORDER BY DATANOTA, NRNOTAFISC, CDFISCOPERNF) LOOP

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

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

            BEGIN
               SELECT DECODE(C_REGISTRO50_NOTA.ID_NACIONAL,'N',RPAD(NVL(SGESTADO,' '),2,' '),'EX')
                 INTO V_SGESTADO
                 FROM ENDEFORN
                WHERE CDFORNECED   = C_REGISTRO50_NOTA.CDFORNECED
                  AND IDTPENDEFORN = V_IDENDEMIS;
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_SGESTADO := NULL;
            END;
         ELSIF C_REGISTRO50_NOTA.IDORIGDESTNF = 'L' THEN
            IF C_REGISTRO50_NOTA.IDENTRSAIDOP = 'S' THEN
               SELECT NVL(REPLACE(REPLACE(REPLACE(NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),
                      DECODE(SUBSTR(UPPER(CDINSCESTA),1,5),'ISENT','ISENTO',CDINSCESTA),
                     (NVL(IDENDEMISFIL,'P'))
                 INTO V_NRINSJUR, V_NRINSEST, V_IDENDEMIS
                 FROM FILIAL
                WHERE CDFILIAL = C_REGISTRO50_NOTA.CDFILIRECE;

               BEGIN
                  SELECT RPAD(NVL(DECODE(V_IDENDEMIS ,'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ')
                    INTO V_SGESTADO
                    FROM (SELECT E.SGESTADO, E.CDFILIAL
                            FROM ENDEFILI E
                           WHERE E.CDFILIAL     = C_REGISTRO50_NOTA.CDFILIRECE
                             AND E.IDTPENDEFILI = V_IDENDEMIS) EF,
                         (SELECT I.SGESTADO, F.CDFILIAL
                            FROM FILIAL F, INSCRESTAD I
                           WHERE F.CDFILIAL   = C_REGISTRO50_NOTA.CDFILIRECE
                             AND F.CDINSCESTA = I.CDINSCESTA
                             AND I.CDEMPRESA  = P_CDEMPRESA) IE
                   WHERE EF.CDFILIAL(+) = IE.CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_SGESTADO := NULL;
               END;
            ELSE
               SELECT NVL(REPLACE(REPLACE(REPLACE(NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),
                      DECODE(SUBSTR(UPPER(CDINSCESTA),1,5),'ISENT','ISENTO',CDINSCESTA),
                     (NVL(IDENDEMISFIL,'P'))
                 INTO V_NRINSJUR, V_NRINSEST, V_IDENDEMIS
                 FROM FILIAL
                WHERE CDFILIAL = C_REGISTRO50_NOTA.CDFILIEMIT;

               BEGIN
                  SELECT RPAD(NVL(DECODE(V_IDENDEMIS ,'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ')
                    INTO V_SGESTADO
                    FROM (SELECT E.SGESTADO, E.CDFILIAL
                            FROM ENDEFILI E
                           WHERE E.CDFILIAL     = C_REGISTRO50_NOTA.CDFILIEMIT
                             AND E.IDTPENDEFILI = V_IDENDEMIS) EF,
                         (SELECT I.SGESTADO, F.CDFILIAL
                            FROM FILIAL F, INSCRESTAD I
                           WHERE F.CDFILIAL   = C_REGISTRO50_NOTA.CDFILIEMIT
                             AND F.CDINSCESTA = I.CDINSCESTA
                             AND I.CDEMPRESA  = P_CDEMPRESA) IE
                   WHERE EF.CDFILIAL(+) = IE.CDFILIAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_SGESTADO := NULL;
               END;
            END IF;
         END IF;

         IF ((V_SGESTADO IS NOT NULL) OR (V_SGESTADO <> '')) AND
            ((C_REGISTRO50_NOTA.IDORIGDESTNF = 'C' OR C_REGISTRO50_NOTA.IDORIGDESTNF = 'F') OR
             (C_REGISTRO50_NOTA.IDORIGDESTNF = 'L' AND C_REGISTRO50_NOTA.ID_NACIONAL = 'N')) THEN    /*PARA NAO CONSIDERAR OS CFOPS 3 E 7 QUANDO FOR NF DE TRANSFERENCIA*/
            DSARQMAG := SUBSTR('50'||
                               LPAD(NVL(V_NRINSJUR, '00000000000000'),14,'0')   ||
                               RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(V_NRINSEST, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ') ||
                               C_REGISTRO50_NOTA.DATANOTA                                  ||
                               V_SGESTADO                                                  ||
                               C_REGISTRO50_NOTA.CDMODDOCFISC                              ||
                               RPAD(C_REGISTRO50_NOTA.CDSERIE,3,' ')                       ||
                               SUBSTR(LPAD(C_REGISTRO50_NOTA.NRNOTAFISC,20,'0'),15,6)      ||
                               C_REGISTRO50_NOTA.CDFISCOPERNF                              ||
                               C_REGISTRO50_NOTA.IDENTIFICAOP                              ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_NOTA.VRCONTABILNF),'00000000000.00'),'.','')) ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_NOTA.VRBASECALCNF),'00000000000.00'),'.','')) ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_NOTA.VRIMPOSTONF) ,'00000000000.00'),'.','')) ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_NOTA.VRISENTRIB)  ,'00000000000.00'),'.','')) ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_NOTA.VROUTRCRED)  ,'00000000000.00'),'.','')) ||
                               LTRIM(REPLACE(TO_CHAR(ABS(C_REGISTRO50_NOTA.VRALIQNF)    ,'00.00'),'.',''))          ||
                               C_REGISTRO50_NOTA.CANCELADA ,1,126);

            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('02'||LPAD(RTRIM(V_CONTADOR), 8, '0'), DSARQMAG, P_NRSEQARQMAG);

            V_CONTADOR  := NVL(V_CONTADOR,0) + 1;
            V_CONTREG   := NVL(V_CONTREG,0) + 1;
            V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
         END IF;
      END LOOP;
   END IF;

   /*GERA O REGISTRO 50 PARA O FOR SALE - 50 FOS*/
   IF NVL(P_GERAFORSALE,'N') = 'S' THEN
      FOR C_REG50FOS IN (SELECT SUBSTR('50'                                                                       ||
                                LPAD(REPLACE(REPLACE(REPLACE(NRINSJUR,'/'),'.'),'-'),14,'0')               ||
                                RPAD(REPLACE(REPLACE(REPLACE(NVL(NRINSEST,'ISENTO'),'/'),'.'),'-'),14,' ') ||
                                TO_CHAR(DATA,'YYYYMMDD')                                                   ||
                                DECODE(IDORIGDESTNF,'L',EFI.SGESTADO,'F',EF.SGESTADO,'C',EC.SGESTADO)      ||
                                NVL(CDMODDOCFISC,01)                                                       ||
                                RPAD(SERIE,3,' ')                                                          ||
                                SUBSTR(LPAD(NRNOTAFISC,20,'0'),15,6)                                       ||
                                CDFISCOPERNF                                                               ||
                                DECODE(IDENTRSAIDOP,'E','T','P')                                           ||
                                LTRIM(REPLACE(TO_CHAR(VRCONTABILNF,'00000000000.00'),'.'))                 ||
                                LTRIM(REPLACE(TO_CHAR(VRBASECALCNF,'00000000000.00'),'.'))                 ||
                                LTRIM(REPLACE(TO_CHAR(VRIMPOSTONF,'00000000000.00'),'.'))                  ||
                                LTRIM(REPLACE(TO_CHAR(VRISENTRIB,'00000000000.00'),'.'))                   ||
                                LTRIM(REPLACE(TO_CHAR(VROUTCRED,'00000000000.00'),'.'))                    ||
                                LTRIM(REPLACE(TO_CHAR(VRALIQNF,'00.00'),'.'))                              ||
                                'N' ,1,126) AS DSARQMAG
                           FROM ENDECLIE EC, ENDEFORN EF, ENDEFILI EFI,
                                (SELECT DECODE(NVL(N.IDORIGDESTNF,DECODE(N.IDENTRSAIDOP,'S','C','F')),'C',CL.NRINSJURCLIE,'F',FO.NRINSJURFORN,'L',FI.NRINSJURFILI) AS NRINSJUR,
                                        DECODE(NVL(N.IDORIGDESTNF,DECODE(N.IDENTRSAIDOP,'S','C','F')),'C',CL.NRINSESTCLIE,'F',FO.NRINSESTFORN,'L',FI.CDINSCESTA  ) AS NRINSEST,
                                        N.IDORIGDESTNF,
                                        N.CDCLIENTE,
                                        N.CDFORNECED,
                                        N.CDFILIEMIT,
                                        N.CDFILIRECE,
                                        N.NRNOTAFISC,
                                        I.CDFISCOPERNF,
                                        I.VRALIQNF,
                                        N.NRLANCTONF,
                                        N.CDFILIAL,
                                        N.IDENTRSAIDOP,
                                        DECODE(N.CDMODDOCFISC,'2D','55',N.CDMODDOCFISC) AS CDMODDOCFISC,
                                        DECODE(N.IDENTRSAIDOP,'E', N.CDSERIEENTR, N.CDSERIE) AS SERIE,
                                        DECODE(N.IDENTRSAIDOP,'E', N.DTENTRSAID, N.DTEMISSAO) AS DATA,
                                        SUM(VRCONTABILNF) AS VRCONTABILNF,
                                        DECODE(I.CDFISCALNF,'1',SUM(VRBASECALCNF),0) AS VRBASECALCNF,
                                        DECODE(I.CDFISCALNF,'2',SUM(VRCONTABILNF),0) AS VRISENTRIB,
                                        DECODE(I.CDFISCALNF,'3',SUM(VRCONTABILNF),0) AS VROUTCRED,
                                        DECODE(I.CDFISCALNF,'1',SUM(VRIMPOSTONF ),0) AS VRIMPOSTONF
                                  FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IMP, CLIENTE CL, FORNECEDOR FO, FILIAL FI
                                 WHERE N.CDFILIAL    = I.CDFILIAL
                                   AND N.NRLANCTONF  = I.NRLANCTONF
                                   AND IMP.CDIMPOSTO = I.CDIMPOSTO
                                   AND N.CDEMPRESA   = P_CDEMPRESA
                                   AND N.CDCLIENTE   = CL.CDCLIENTE(+)
                                   AND N.CDFORNECED  = FO.CDFORNECED(+)
                                   AND N.CDFILIRECE  = FI.CDFILIAL(+)
                                   AND N.CDFILIEMIT  = FI.CDFILIAL(+)
                                   AND DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                                   AND ((N.IDSTATUS = 'A' OR N.IDSTATUS = 'E') OR (N.IDSTATUSNFE = 'A' OR N.IDSTATUSNFE = 'T'))
                                   AND N.CDINSCESTA = P_CDINSCESTA
                                   AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                   AND IMP.CDIMPOSTONFE = P_CDIMPOSTO
                                   AND N.IDENTRSAIDOP = 'S'
                                   AND (I.IDIMPOSTONF <> '3' OR IDIMPOSTONF IS NULL)
                                   AND N.CDMODDOCFISC IN ('01','04','55','06','21','22','2D')
                                   GROUP BY DECODE(NVL(N.IDORIGDESTNF,DECODE(N.IDENTRSAIDOP,'S','C','F')),'C',CL.NRINSJURCLIE,'F',FO.NRINSJURFORN,'L',FI.NRINSJURFILI),
                                           DECODE(NVL(N.IDORIGDESTNF,DECODE(N.IDENTRSAIDOP,'S','C','F')),'C',CL.NRINSESTCLIE,'F',FO.NRINSESTFORN,'L',FI.CDINSCESTA),N.IDORIGDESTNF,
                                           N.CDCLIENTE, N.CDFORNECED, N.CDFILIEMIT, N.CDFILIRECE, N.NRNOTAFISC, I.CDFISCOPERNF, I.VRALIQNF, N.NRLANCTONF,
                                           N.CDFILIAL, N.IDENTRSAIDOP, N.CDMODDOCFISC, DECODE(N.IDENTRSAIDOP,'E', N.CDSERIEENTR, N.CDSERIE),
                                           DECODE(N.IDENTRSAIDOP,'E',N.DTENTRSAID,N.DTEMISSAO),  I.CDFISCALNF)NF
                          WHERE NF.CDCLIENTE  = EC.CDCLIENTE(+)
                            AND NF.CDFORNECED = EF.CDFORNECED(+)
                            AND NF.CDFILIEMIT = EFI.CDFILIAL(+)
                            AND NF.CDFILIRECE = EFI.CDFILIAL(+)
                          ORDER BY DATA, NRNOTAFISC,CDFISCOPERNF)LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('02'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REG50FOS.DSARQMAG, P_NRSEQARQMAG);
         V_CONTADOR  := NVL(V_CONTADOR,0) + 1;
         V_CONTREG   := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
      END LOOP;
   END IF;

  /*GERA REGISTRO 50 PARA O SIMPLES NACIONAL*/
   IF (NVL(P_GERAFORSALE,'N') = 'N') AND (V_CDSITUCRT <> '3') THEN
      FOR C_REGISTRO50SN IN (SELECT DATA, SUBSTR('50'        ||
                                                 NRINSJUR    ||
                                                 NRINSEST    ||
                                                 DATA        ||
                                                 SGESTADO    ||
                                                 CDMODDOCFISC||
                                                 SERIE       ||
                                                 NRNOTAFISC  ||
                                                 CDFISCOPERNF||
                                                 IDENTRSAIDOP||
                                                 VRCONTABILNF||
                                                 VRBASECALCNF||
                                                 VRIMPOSTONF ||
                                                 VRISENTRIB  ||
                                                 VROUTCRED   ||
                                                 VRALIQNF    ||
                                                 CANCELADA ,1,126) AS DSARQMAG
                               FROM (SELECT '50',
                                            LPAD(NVL(DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN),'00000000000000'),14,'0') AS NRINSJUR,
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')AS NRINSEST,
                                            TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA,
                                            RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                            RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                            RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            (CASE WHEN NF.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL THEN 'S' ELSE 'X' END) AS CANCELADA
                                       FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                                            (SELECT N.CDFILIAL, N.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFORNECED, N.DTENTRSAID,
                                                    N.DTEMISSAO, 0 AS VRALIQNF, '0000' AS CDFISCOPERNF, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                                    N.VRNOTAFISC AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    0 AS VROUTRCRED,
                                                    0 AS VRISENTRIB
                                               FROM NOTAFISCAL N
                                              WHERE N.IDSTATUS     = 'C'
                                                AND TRUNC(N.DTCANCELANF) BETWEEN P_DTINICIAL AND P_DTFINAL
                                                AND N.CDEMPRESA    = P_CDEMPRESA
                                                AND N.CDINSCESTA   = P_CDINSCESTA
                                                AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                                AND N.IDENTRSAIDOP = 'S'
                                                AND N.IDORIGDESTNF = 'F'
                                                AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                              GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                       DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDFORNECED, N.DTENTRSAID,
                                                       N.DTEMISSAO, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, N.VRNOTAFISC) NF
                                      WHERE FO.CDFORNECED(+)   = NF.CDFORNECED
                                        AND FA.CDFORNECED      = NF.CDFORNECED
                                        AND EF.CDFORNECED(+)   = FA.CDFORNECED
                                        AND EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P')
                                      UNION ALL
                                     SELECT '50',
                                            LPAD(NVL(DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE),'00000000000000'),14,'0') AS NRINSJUR,
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')AS NRINSEST,
                                            TO_CHAR(DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                            RPAD(NVL(EC.SGESTADO,' '),2,' ')AS SGESTADO,
                                            RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                            RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            (CASE WHEN NF.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL THEN 'S' ELSE 'X' END) AS CANCELADA
                                       FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                            (SELECT N.CDFILIAL, N.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDCLIENTE, N.DTENTRSAID,
                                                    N.DTEMISSAO, 0 AS VRALIQNF, '0000' AS CDFISCOPERNF, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                                    N.VRNOTAFISC AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    0 AS VROUTRCRED,
                                                    0 AS VRISENTRIB
                                               FROM NOTAFISCAL N
                                              WHERE N.IDSTATUS    = 'C'
                                                AND TRUNC(N.DTCANCELANF) BETWEEN P_DTINICIAL AND P_DTFINAL
                                                AND N.CDEMPRESA   = P_CDEMPRESA
                                                AND N.CDINSCESTA  = P_CDINSCESTA
                                                AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                                AND N.IDENTRSAIDOP = 'S'
                                                AND N.IDORIGDESTNF = 'C'
                                                AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                              GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                       DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                                       N.CDCLIENTE, N.DTENTRSAID, N.DTEMISSAO, N.VRNOTAFISC,
                                                       N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                                      WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE
                                        AND CA.CDCLIENTE       = NF.CDCLIENTE
                                        AND EC.CDCLIENTE(+)    = CA.CDCLIENTE
                                        AND EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P')
                                      UNION ALL
                                     SELECT '50',
                                            LPAD(NVL(DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE),'00000000000000'),14,'0') AS NRINSJUR,
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')AS NRINSEST,
                                            TO_CHAR(DECODE(NF.IDENTRSAIDOP,'E',NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                            RPAD(NVL(EC.SGESTADO,' '),2,' ')AS SGESTADO,
                                            RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                            RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            RPAD(NVL(NF.CDFISOPEITEM,'0'),4,' ') AS CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            'N' AS CANCELADA
                                       FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                            (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDCLIENTE, N.DTENTRSAID,
                                                    N.DTEMISSAO,
                                                    0 AS VRALIQNF,
                                                    I.CDFISOPEITEM, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                                    SUM(I.VRBRUTITEM) AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    SUM(I.VRBRUTITEM) AS VROUTRCRED,
                                                    0 AS VRISENTRIB
                                               FROM MAPAAPURACSN M, NOTAFISCAL N, ITEMPRODUTO I
                                              WHERE M.CDEMPRESA       = P_CDEMPRESA
                                                AND M.DTINIPERIODO    = P_DTINICIAL
                                                AND M.DTFIMPERIODO    = P_DTFINAL
                                                AND N.CDINSCESTA      = P_CDINSCESTA
                                                AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                                AND N.NRSEQUMAPAPUSN  = M.NRSEQUMAPAPU
                                                AND N.CDFILIAL        = I.CDFILIAL
                                                AND N.NRLANCTONF      = I.NRLANCTONF
                                                AND N.IDSTATUS IN ('A','E')
                                                AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'C'
                                                AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                              GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                                    N.CDCLIENTE, N.DTENTRSAID, N.DTEMISSAO, I.CDFISOPEITEM,
                                                    N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                                      WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE
                                        AND CA.CDCLIENTE       = NF.CDCLIENTE
                                        AND EC.CDCLIENTE(+)    = CA.CDCLIENTE
                                        AND EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P')
                                      UNION ALL
                                     SELECT '50',
                                            LPAD(NVL(DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN),'00000000000000'),14,'0') AS NRINSJUR,
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')AS NRINSEST,
                                            TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA,
                                            RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                            RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                            RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            RPAD(NVL(NF.CDFISOPEITEM,'0'),4,' ') AS CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            'N' AS CANCELADA
                                       FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                                            (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFORNECED,
                                                    N.DTENTRSAID, N.DTEMISSAO, 0 AS VRALIQNF, I.CDFISOPEITEM, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                                    SUM(I.VRBRUTITEM) AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    SUM(I.VRBRUTITEM) AS VROUTRCRED,
                                                    0 AS VRISENTRIB
                                               FROM MAPAAPURACSN M, NOTAFISCAL N, ITEMPRODUTO I
                                              WHERE M.CDEMPRESA       = P_CDEMPRESA
                                                AND M.DTINIPERIODO    = P_DTINICIAL
                                                AND M.DTFIMPERIODO    = P_DTFINAL
                                                AND N.CDINSCESTA      = P_CDINSCESTA
                                                AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                                AND N.NRSEQUMAPAPUSN  = M.NRSEQUMAPAPU
                                                AND N.CDFILIAL        = I.CDFILIAL
                                                AND N.NRLANCTONF      = I.NRLANCTONF
                                                AND N.IDSTATUS IN ('A','E')
                                                AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'F'
                                                AND N.CDMODDOCFISC    IN ('01', '1A', '04', '06', '21', '22', '55')
                                              GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                                    N.CDFORNECED, N.DTENTRSAID, N.DTEMISSAO, I.CDFISOPEITEM,
                                                    N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                                      WHERE FO.CDFORNECED(+)   = NF.CDFORNECED
                                        AND FA.CDFORNECED      = NF.CDFORNECED
                                        AND EF.CDFORNECED(+)   = FA.CDFORNECED
                                        AND EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P')
                                      UNION ALL
                                     SELECT '50',
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),14,' ') AS NRINSJUR,
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(FI.CDINSCESTA),1,5),'ISENT','ISENTO',FI.CDINSCESTA), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ') AS NRINSEST,
                                            TO_CHAR(NF.DTENTRSAID,'yyyymmdd') AS DATA,
                                            RPAD(NVL(DECODE(NVL(F.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ') AS SGESTADO,
                                            RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                            RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            RPAD(NVL(NF.CDFISOPEITEM,'0'),4,' ') AS CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            'N' AS CANCELADA
                                       FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                            (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFILIEMIT,
                                                    N.DTENTRSAID, 0 AS VRALIQNF, I.CDFISOPEITEM,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                                    SUM(I.VRBRUTITEM) AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    SUM(I.VRBRUTITEM) AS VROUTRCRED,
                                                    0 AS VRISENTRIB, N.CDINSCESTA
                                               FROM MAPAAPURACSN M, NOTAFISCAL N, ITEMPRODUTO I
                                              WHERE M.CDEMPRESA       = P_CDEMPRESA
                                                AND M.DTINIPERIODO    = P_DTINICIAL
                                                AND M.DTFIMPERIODO    = P_DTFINAL
                                                AND N.CDINSCESTA      = P_CDINSCESTA
                                                AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                                AND N.NRSEQUMAPAPUSN  = M.NRSEQUMAPAPU
                                                AND N.CDFILIAL        = I.CDFILIAL
                                                AND N.NRLANCTONF      = I.NRLANCTONF
                                                AND N.IDENTRSAIDOP    = 'E'
                                                AND N.IDSTATUS IN ('A','E')
                                                AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                                AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                              GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDFILIEMIT, N.CDINSCESTA,
                                                    N.DTENTRSAID, I.CDFISOPEITEM,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                                      WHERE FI.CDFILIAL(+)     = NF.CDFILIEMIT
                                        AND F.CDFILIAL         = NF.CDFILIEMIT
                                        AND EF.CDFILIAL(+)     = F.CDFILIAL
                                        AND EF.IDTPENDEFILI(+) = NVL(F.IDENDEMISFIL,'P')
                                        AND NF.CDINSCESTA      = IE.CDINSCESTA
                                        AND IE.CDEMPRESA       = P_CDEMPRESA
                                      UNION ALL
                                     SELECT '50',
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),14,' ')AS NRINSJUR,
                                            RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(FI.CDINSCESTA),1,5),'ISENT','ISENTO',FI.CDINSCESTA), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ') AS NRINSEST,
                                            TO_CHAR(NF.DTEMISSAO,'yyyymmdd') AS DATA,
                                            RPAD(NVL(DECODE(NVL(F.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ') AS SGESTADO,
                                            RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ') AS CDMODDOCFISC,
                                            RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            RPAD(NVL(NF.CDFISOPEITEM,'0'),4,' ') AS CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            'N' AS CANCELADA
                                       FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                            (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFILIRECE,
                                                    N.DTEMISSAO, 0 AS VRALIQNF,
                                                    I.CDFISOPEITEM,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                                    SUM(I.VRBRUTITEM) AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    SUM(I.VRBRUTITEM) AS VROUTRCRED,
                                                    0 AS VRISENTRIB, N.CDINSCESTA
                                               FROM MAPAAPURACSN M, NOTAFISCAL N, ITEMPRODUTO I
                                              WHERE M.CDEMPRESA       = P_CDEMPRESA
                                                AND M.DTINIPERIODO    = P_DTINICIAL
                                                AND M.DTFIMPERIODO    = P_DTFINAL
                                                AND N.CDINSCESTA      = P_CDINSCESTA
                                                AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                                AND N.NRSEQUMAPAPUSN  = M.NRSEQUMAPAPU
                                                AND N.CDFILIAL        = I.CDFILIAL
                                                AND N.NRLANCTONF      = I.NRLANCTONF
                                                AND N.IDENTRSAIDOP    = 'S'
                                                AND N.IDSTATUS IN ('A','E')
                                                AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                                AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                              GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                    DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.IDENTRSAIDOP, N.CDINSCESTA,
                                                    N.CDFILIRECE, N.DTEMISSAO, I.CDFISOPEITEM, N.CDMODDOCFISC, N.IDORIGDESTNF) NF
                                      WHERE FI.CDFILIAL(+)     = NF.CDFILIRECE
                                        AND F.CDFILIAL         = NF.CDFILIRECE
                                        AND EF.CDFILIAL(+)     = F.CDFILIAL
                                        AND EF.IDTPENDEFILI(+) = NVL(F.IDENDEMISFIL,'P')
                                        AND NF.CDINSCESTA      = IE.CDINSCESTA
                                        AND IE.CDEMPRESA       = P_CDEMPRESA
                                      UNION ALL /*NOTA INUTILIZADA*/
                                     SELECT '50',
                                            '00000000000000' AS NRINSJUR,
                                            RPAD(P_CDINSCESTA,14,' ') AS NRINSEST,
                                            TO_CHAR(NF.DTEMISSAO,'yyyymmdd') AS DATA,
                                            V_SGESTAINSC AS SGESTADO,
                                            '55' AS CDMODDOCFISC,
                                            RPAD(NF.CDSERIE,3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            '4' AS CANCELADA
                                       FROM (SELECT '' AS CDFILIAL, '' AS NRLANCTONF,NRINUTILNFELE AS NRNOTAFISC,
                                                    N.CDSERIE AS CDSERIE,
                                                    '' AS CDSERIEENTR, '' AS CDCLIENTE, N.DTINUTNFE AS DTENTRSAID,
                                                    N.DTINUTNFE AS DTEMISSAO, 0 AS VRALIQNF,
                                                    '0000' AS CDFISCOPERNF, '55' AS CDMODDOCFISC, '' AS IDORIGDESTNF, 'S' AS IDENTRSAIDOP,
                                                    0 AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    0 AS VROUTRCRED,
                                                    0 AS VRISENTRIB
                                              FROM NUMINUTILNFELE N
                                             WHERE TRUNC(N.DTINUTNFE)BETWEEN P_DTINICIAL AND P_DTFINAL
                                               AND N.CDEMPRESA  = P_CDEMPRESA
                                               AND N.CDINSCESTA = P_CDINSCESTA
                                               AND N.NRPROTOCINUTNFE  <> 'TEKNISA') NF
                                      UNION ALL /*NOTA DENEGADA*/
                                     SELECT '50',
                                            '00000000000000' AS NRINSJUR,
                                            RPAD(P_CDINSCESTA,14,' ') AS NRINSEST,
                                            TO_CHAR(NF.DTEMISSAO,'yyyymmdd') AS DATA,
                                            V_SGESTAINSC AS SGESTADO,
                                            '55' AS CDMODDOCFISC,
                                            RPAD(NF.CDSERIE,3,' ') AS SERIE,
                                            SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                            CDFISCOPERNF,
                                            DECODE(NF.IDENTRSAIDOP, 'E', 'T', 'P') AS IDENTRSAIDOP,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRCONTABILNF),'00000000000.00'),'.','')) AS VRCONTABILNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.','')) AS VRBASECALCNF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB)  ,'00000000000.00'),'.','')) AS VRISENTRIB,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED)  ,'00000000000.00'),'.','')) AS VROUTCRED,
                                            LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF)    ,'00.00'),'.','')) AS VRALIQNF,
                                            '2' AS CANCELADA
                                       FROM (SELECT '' AS CDFILIAL, '' AS NRLANCTONF, N.NRNOTAFISC AS NRNOTAFISC,
                                                    NVL(SS.CDSERIENFE,N.CDSERIE) AS CDSERIE,
                                                    '' AS CDSERIEENTR, '' AS CDCLIENTE, N.DTENTRSAID,
                                                    N.DTEMISSAO, 0 AS VRALIQNF, '0000' AS CDFISCOPERNF,
                                                    N.CDMODDOCFISC, '' AS IDORIGDESTNF, IDENTRSAIDOP,
                                                    0 AS VRCONTABILNF,
                                                    0 AS VRBASECALCNF,
                                                    0 AS VRIMPOSTONF,
                                                    0 AS VROUTRCRED,
                                                    0 AS VRISENTRIB
                                               FROM NOTAFISCAL N, SERIENF SS, SERIEENTR SE
                                              WHERE NVL(N.IDSISTEMA, 'DFS') <> 'REC'
                                                AND NVL(N.IDSISTEMA, 'DFS') <> 'PAG'
                                                AND NVL(N.IDSISTEMA, 'DFS') <> 'CMP'
                                                AND NVL(N.IDSISTEMA, 'DFS') <> 'SUP'
                                                AND NVL(N.IDSISTEMA, 'DFS') <> 'TMP'
                                                AND NVL(N.IDSISTEMA, 'DFS') <> 'FIS'
                                                AND SE.CDSERIEENTR(+) = N.CDSERIEENTR
                                                AND SS.CDEMPRESA(+)   = N.CDEMPRESA
                                                AND SS.CDINSCESTA(+)  = N.CDINSCESTA
                                                AND SS.CDINSCMUNI(+)  = N.CDINSCMUNI
                                                AND SS.CDSERIE(+)     = N.CDSERIE
                                                AND N.IDSTATUSNFE     = 'D'
                                                AND N.NRACESSONFE     IS NOT NULL
                                                AND N.CDMODDOCFISC    = '55'
                                                AND N.DTEMISSAO       BETWEEN P_DTINICIAL AND P_DTFINAL
                                                AND N.CDINSCESTA      = P_CDINSCESTA
                                                AND N.CDEMPRESA       = P_CDEMPRESA)NF)NF
                              ORDER BY DATA, NRNOTAFISC, CDFISCOPERNF) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('02'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO50SN.DSARQMAG, P_NRSEQARQMAG);
         V_CONTADOR  := NVL(V_CONTADOR,0) + 1;
         V_CONTREG   := NVL(V_CONTREG,0) + 1;
         V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
      END LOOP;
   END IF;

   IF V_CONTREG > 0 THEN
     V_STTOTAL := V_STTOTAL||'50'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
   END IF;

   /* GERACAO DO REGISTRO 51 */
   V_CONTREG := 0;
   V_CONTADOR := 0;
   IF (V_CDSITUCRT = '3') THEN
      FOR C_REGISTRO51 IN (SELECT SUBSTR('51'   ||
                                  NRINSJ        ||
                                  NRINSEST      ||
                                  DATA          ||
                                  SGESTADO      ||
                                  SERIE         ||
                                  NRNOTAFISC    ||
                                  CDFISCOPERNF  ||
                                  VRCONTABILNF  ||
                                  VRIMPOSTONF   ||
                                  VRISENTRIB    ||
                                  VROUTCRED     ||
                                  RPAD(' ',20, ' ')||
                                  IDSTATUS,1,126) AS DSARQUMAG, DATA
                             FROM (SELECT '51',
                                          LPAD(NVL(DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN),'00000000000000'),14,'0') AS NRINSJ,
                                          RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ') AS NRINSEST,
                                          TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA,
                                          RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                          RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ') AS SERIE,
                                          SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                          RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRCONTABILNF, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.',''))   AS VRIMPOSTONF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.',''))   AS VRISENTRIB,
                                          LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.',''))   AS VROUTCRED,
                                          DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                                     FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                                          (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFORNECED,
                                                  N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                                  SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                                  SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                                  SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                                  SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                             FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                                  (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                                     FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                                    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 IM.CDIMPOSTO    = IP.CDIMPOSTO
                                                      AND IM.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                            WHERE N.CDEMPRESA      = P_CDEMPRESA
                                              AND N.CDINSCESTA     = P_CDINSCESTA
                                              AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                              AND N.DTENTRSAID     BETWEEN P_DTINICIAL AND P_DTFINAL
                                              AND N.IDSTATUS       IN ('A', 'E')
                                              AND N.CDFILIAL       = I.CDFILIAL
                                              AND N.NRLANCTONF     = I.NRLANCTONF
                                              AND IM.CDIMPOSTONFE  = '10'
                                              AND IM.CDIMPOSTO     = I.CDIMPOSTO
                                              AND I.NRSEQUMAPAPU IS NOT NULL
                                              AND I.CDFILIAL       = IC.CDFILIAL
                                              AND I.NRLANCTONF     = IC.NRLANCTONF
                                              AND I.VRIMPOSTONF    > 0
                                              AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                              AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                              AND N.CDMODDOCFISC    IN ('01', '1A', '55')
                                            GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDFORNECED,
                                                  N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                                    WHERE FO.CDFORNECED(+)   = NF.CDFORNECED
                                      AND FA.CDFORNECED      = NF.CDFORNECED
                                      AND EF.CDFORNECED(+)   = FA.CDFORNECED
                                      AND EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P')
                                    UNION ALL
                                   SELECT '51',
                                          '00000000000000' AS NRINSJ,
                                          'ISENTO        ' AS NRINSEST,
                                          TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA,
                                          'EX' AS SGESTADO,
                                          RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ') AS SERIE,
                                          SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                          RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRCONTABILNF, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.',''))   AS VRIMPOSTONF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.',''))   AS VRISENTRIB,
                                          LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.',''))   AS VROUTCRED,
                                          DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                                     FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                                          (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFORNECED,
                                                  N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                                  SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                                  SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                                  SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                                  SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                             FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                                  (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                                     FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                                    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    = IM.CDIMPOSTO
                                                      AND IM.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                            WHERE N.CDEMPRESA       = P_CDEMPRESA
                                              AND N.CDINSCESTA      = P_CDINSCESTA
                                              AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                              AND N.DTENTRSAID BETWEEN P_DTINICIAL AND P_DTFINAL
                                              AND N.IDSTATUS   IN ('A', 'E')
                                              AND N.CDFILIAL        = I.CDFILIAL
                                              AND N.NRLANCTONF      = I.NRLANCTONF
                                              AND IM.CDIMPOSTONFE   = '10'
                                              AND I.CDIMPOSTO       = IM.CDIMPOSTO
                                              AND I.CDFILIAL        = IC.CDFILIAL
                                              AND I.NRLANCTONF      = IC.NRLANCTONF
                                              AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                              AND I.VRIMPOSTONF     > 0
                                              AND I.NRSEQUMAPAPU IS NOT NULL
                                              AND SUBSTR(I.CDFISCOPERNF,1,1) = '3'
                                              AND N.CDMODDOCFISC    IN ('01', '1A', '55')
                                            GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDFORNECED,
                                                  N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                                    WHERE FO.CDFORNECED(+)   = NF.CDFORNECED
                                      AND FA.CDFORNECED      = NF.CDFORNECED
                                      AND EF.CDFORNECED(+)   = FA.CDFORNECED
                                      AND EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P')
                                    UNION ALL
                                   SELECT '51',
                                          LPAD(NVL(DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE),'00000000000000'),14,'0')AS NRINSJ,
                                          RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE,'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')AS NRINSEST,
                                          TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                          RPAD(NVL(EC.SGESTADO,' '),2,' ') AS SGESTADO,
                                          RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                          SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                          RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.','')) AS VROUTRCRED,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.','')) AS VRISENTRIB,
                                          DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                                     FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                          (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDCLIENTE,
                                                  N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                                  SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                                  SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                                  SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                                  SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                             FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                                  (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                                     FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                                    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 IM.CDIMPOSTO    = IP.CDIMPOSTO
                                                      AND M.NRSEQUMAPAPU  = IP.NRSEQUMAPAPU
                                                      AND IM.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                            WHERE N.CDEMPRESA       = P_CDEMPRESA
                                              AND N.CDINSCESTA      = P_CDINSCESTA
                                              AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                              AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                                              AND N.IDSTATUS   IN ('A', 'E')
                                              AND N.CDFILIAL        = I.CDFILIAL
                                              AND N.NRLANCTONF      = I.NRLANCTONF
                                              AND IM.CDIMPOSTONFE   = '10'
                                              AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                              AND I.VRIMPOSTONF     > 0
                                              AND I.NRSEQUMAPAPU IS NOT NULL
                                              AND I.CDFILIAL        = IC.CDFILIAL
                                              AND I.NRLANCTONF      = IC.NRLANCTONF
                                              AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                              AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                              AND N.CDMODDOCFISC   IN ('01', '1A', '55')
                                            GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDCLIENTE,
                                                  N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                                    WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE
                                      AND CA.CDCLIENTE       = NF.CDCLIENTE
                                      AND EC.CDCLIENTE(+)    = CA.CDCLIENTE
                                      AND EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P')
                                    UNION ALL
                                   SELECT '51',
                                          '00000000000000' AS NRINSJ,
                                          'ISENTO        'AS NRINSEST,
                                          TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                          'EX' AS SGESTADO,
                                          RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ') AS SERIE,
                                          SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                          RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.','')) AS VRCONTABILNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF,'00000000000.00'),'.','')) AS VRIMPOSTONF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED, '00000000000.00'),'.','')) AS VROUTRCRED,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB, '00000000000.00'),'.','')) AS VRISENTRIB,
                                          DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                                     FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                          (SELECT I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDCLIENTE,
                                                  N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP,
                                                  SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                                  SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF ,0)) AS VRIMPOSTONF,
                                                  SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                                  SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                             FROM NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                                  (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                                     FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                                    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 IM.CDIMPOSTONFE = P_CDIMPOSTO
                                                      AND IM.CDIMPOSTO    = IP.CDIMPOSTO) IC
                                            WHERE N.CDEMPRESA       = P_CDEMPRESA
                                              AND N.CDINSCESTA      = P_CDINSCESTA
                                              AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                              AND N.DTEMISSAO BETWEEN P_DTINICIAL AND P_DTFINAL
                                              AND N.IDSTATUS   IN ('A', 'E')
                                              AND N.CDFILIAL        = I.CDFILIAL
                                              AND N.NRLANCTONF      = I.NRLANCTONF
                                              AND IM.CDIMPOSTONFE   = '10'
                                              AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                              AND I.VRIMPOSTONF     > 0
                                              AND I.NRSEQUMAPAPU IS NOT NULL
                                              AND I.CDFILIAL        = IC.CDFILIAL
                                              AND I.NRLANCTONF      = IC.NRLANCTONF
                                              AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                              AND SUBSTR(I.CDFISCOPERNF,1,1) = '7'
                                              AND N.CDMODDOCFISC    IN ('01', '1A', '55')
                                            GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDCLIENTE,
                                                  N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDSTATUS, N.IDENTRSAIDOP) NF
                                    WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE
                                      AND CA.CDCLIENTE       = NF.CDCLIENTE
                                      AND EC.CDCLIENTE(+)    = CA.CDCLIENTE
                                      AND EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P')) NF
                            ORDER BY DATA) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('03'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO51.DSARQUMAG, P_NRSEQARQMAG);

         V_CONTADOR  := NVL(V_CONTADOR,0)+1;
         V_CONTREG   := V_CONTREG + 1;
         V_CONTGERAL := V_CONTGERAL + 1;
      END LOOP;

      IF V_CONTREG > 0 THEN
        V_STTOTAL := V_STTOTAL||'51'||LPAD(RTRIM(TO_CHAR(V_CONTREG) ),8,'0');
      END IF;
   END IF;

   /* GERACAO DO REGISTRO 53 */
   V_CONTREG := 0;
   IF (V_CDSITUCRT = '3') THEN
      FOR C_REGISTRO53 IN (SELECT SUBSTR('53'||
                                  LPAD(NVL(DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE),'00000000000000'),14,'0')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd')||
                                  RPAD(NVL(EC.SGESTADO,' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDCLIENTE,
                                          N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                             FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                            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 IM.CDIMPOSTO    = IP.CDIMPOSTO
                                              AND IM.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.CDINSCESTA      = P_CDINSCESTA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.CDIMPOSTO       = '80'
                                      AND M.NRSEQUMAPAPU    = I.NRSEQUMAPAPU
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND I.CDFILIAL        = IC.CDFILIAL
                                      AND I.NRLANCTONF      = IC.NRLANCTONF
                                      AND I.VRIMPOSTONF     > 0
                                      AND IM.CDIMPOSTONFE   = '80'
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND N.IDSTATUS IN ('A','E')
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                      AND N.CDMODDOCFISC   IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                          N.CDCLIENTE,N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,
                                          N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                            WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE                 AND
                                  CA.CDCLIENTE       = NF.CDCLIENTE                 AND
                                  EC.CDCLIENTE(+)    = CA.CDCLIENTE                 AND
                                  EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P')
                            UNION ALL
                           SELECT SUBSTR('53'||
                                  LPAD(NVL(DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN),'00000000000000'),14,'0')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd')||
                                  RPAD(NVL(EF.SGESTADO,' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFORNECED,
                                          N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF, '1', DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                             FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                            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 IM.CDIMPOSTO    = IP.CDIMPOSTO
                                              AND IM.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.CDINSCESTA      = P_CDINSCESTA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.CDIMPOSTO       = '80'
                                      AND M.NRSEQUMAPAPU    = I.NRSEQUMAPAPU
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND I.CDFILIAL        = IC.CDFILIAL
                                      AND I.NRLANCTONF      = IC.NRLANCTONF
                                      AND I.VRIMPOSTONF     > 0
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND IM.CDIMPOSTONFE   = '80'
                                      AND N.IDSTATUS IN ('A','E')
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                      AND N.CDMODDOCFISC    IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                          N.CDFORNECED,N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,
                                          N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                            WHERE FO.CDFORNECED(+)   = NF.CDFORNECED            AND
                                  FA.CDFORNECED      = NF.CDFORNECED            AND
                                  EF.CDFORNECED(+)   = FA.CDFORNECED            AND
                                  EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P')
                            UNION ALL
                           SELECT SUBSTR('53'||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),14,' ')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(FI.CDINSCESTA),1,5),'ISENT','ISENTO',FI.CDINSCESTA), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(NF.DTENTRSAID,'yyyymmdd')||
                                  RPAD(NVL(DECODE(NVL(F.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFILIEMIT,
                                          N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.CDINSCESTA,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF, '1', DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                             FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                            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 IP.CDIMPOSTO   = IM.CDIMPOSTO
                                              AND M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU
                                              AND IM.CDIMPOSTONFE   = P_CDIMPOSTO) IC
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.CDINSCESTA      = P_CDINSCESTA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.CDIMPOSTO       = '80'
                                      AND M.NRSEQUMAPAPU    = I.NRSEQUMAPAPU
                                      AND N.IDENTRSAIDOP    = 'E'
                                      AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND I.CDFILIAL        = IC.CDFILIAL
                                      AND I.NRLANCTONF      = IC.NRLANCTONF
                                      AND I.VRIMPOSTONF     > 0
                                      AND IM.CDIMPOSTONFE   = '80'
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND N.IDSTATUS IN ('A','E')
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                      AND N.CDMODDOCFISC    IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDFILIEMIT, N.CDINSCESTA,
                                          N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF) NF
                            WHERE FI.CDFILIAL(+)     = NF.CDFILIEMIT           AND
                                  F.CDFILIAL         = NF.CDFILIEMIT           AND
                                  EF.CDFILIAL(+)     = F.CDFILIAL              AND
                                  EF.IDTPENDEFILI(+) = NVL(F.IDENDEMISFIL,'P') AND
                                  NF.CDINSCESTA      = IE.CDINSCESTA           AND
                                  IE.CDEMPRESA       = P_CDEMPRESA
                            UNION ALL
                           SELECT SUBSTR('53'||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),14,' ')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(FI.CDINSCESTA),1,5),'ISENT','ISENTO',FI.CDINSCESTA), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(NF.DTEMISSAO,'yyyymmdd')||
                                  RPAD(NVL(DECODE(NVL(F.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFILIRECE,
                                          N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.CDINSCESTA,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1', DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM,
                                          (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF
                                             FROM MAPAAPURAC M, IMPOSTONF IP, IMPOSTO IM
                                            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 IM.CDIMPOSTO    = IP.CDIMPOSTO
                                              AND IM.CDIMPOSTONFE = P_CDIMPOSTO) IC
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.CDINSCESTA      = P_CDINSCESTA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.CDIMPOSTO       = '80'
                                      AND M.NRSEQUMAPAPU    = I.NRSEQUMAPAPU
                                      AND N.IDENTRSAIDOP    = 'S'
                                      AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND I.CDFILIAL        = IC.CDFILIAL
                                      AND I.NRLANCTONF      = IC.NRLANCTONF
                                      AND I.VRIMPOSTONF     > 0
                                      AND IM.CDIMPOSTONFE   = '80'
                                      AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                      AND N.IDSTATUS IN ('A','E')
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                      AND N.CDMODDOCFISC   IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                             DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDINSCESTA,
                                             N.CDFILIRECE, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF) NF
                            WHERE FI.CDFILIAL(+)     = NF.CDFILIRECE
                              AND F.CDFILIAL         = NF.CDFILIRECE
                              AND EF.CDFILIAL(+)     = F.CDFILIAL
                              AND EF.IDTPENDEFILI(+) = NVL(F.IDENDEMISFIL,'P')
                              AND NF.CDINSCESTA      = IE.CDINSCESTA
                              AND IE.CDEMPRESA       = P_CDEMPRESA) LOOP

         INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
            ('04'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO53.DSARQMAG, P_NRSEQARQMAG);

         V_CONTREG := V_CONTREG+1;
         V_CONTGERAL := V_CONTGERAL + 1;
      END LOOP;

      IF V_CONTREG > 0 THEN
         V_STTOTAL := V_STTOTAL||'53'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
      END IF;
   END IF;

   IF (V_CDSITUCRT <> '3') THEN
      /*VERIFICA QUAL EH O IMPOSTO DE SUBSTITUICAO TRIBUTARIA */
      SELECT MAX(CDIMPOSTRIB)
      INTO V_CDIMPOSTRIB
      FROM IMPOSTO
      WHERE CDIMPOSTRIB IS NOT NULL;

      FOR C_REGISTRO53 IN (SELECT SUBSTR('53'||
                                  LPAD(NVL(DECODE(CL.IDTPIJURCLIE,'I','','O', '',CL.NRINSJURCLIE),'00000000000000'),14,'0')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CL.NRINSESTCLIE, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd')||
                                  RPAD(NVL(EC.SGESTADO,' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDCLIENTE,
                                          N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1',DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURACSN M, NOTAFISCAL N, IMPOSTONF I
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.NRSEQUMAPAPU    = N.NRSEQUMAPAPUSN
                                      AND N.CDINSCESTA      = P_CDINSCESTA
                                      AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND I.CDIMPOSTO       = V_CDIMPOSTRIB
                                      AND N.IDSTATUS IN ('A','E')
                                      AND I.VRIMPOSTONF     > 0
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                      AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                          N.CDCLIENTE,N.DTEMISSAO, N.DTENTRSAID, I.CDFISCOPERNF,
                                          N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                            WHERE CL.CDCLIENTE(+)    = NF.CDCLIENTE
                              AND CA.CDCLIENTE       = NF.CDCLIENTE
                              AND EC.CDCLIENTE(+)    = CA.CDCLIENTE
                              AND EC.IDTPENDECLIE(+) = NVL(CA.IDENDEMISCLI,'P')
                            UNION ALL
                           SELECT SUBSTR('53'||
                                  LPAD(NVL(DECODE(FO.IDTPIJURFORN,'I','','O', '',FO.NRINSJURFORN),'00000000000000'),14,'0')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN, ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd')||
                                  RPAD(NVL(EF.SGESTADO,' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIEENTR,NF.CDSERIE)),'-',' ',REPLACE(NVL(NF.CDSERIEENTR,NF.CDSERIE), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFORNECED,
                                          N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF, '1', DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURACSN M, NOTAFISCAL N, IMPOSTONF I
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.NRSEQUMAPAPU    = N.NRSEQUMAPAPUSN
                                      AND N.CDINSCESTA      = P_CDINSCESTA
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND I.CDIMPOSTO       = V_CDIMPOSTRIB
                                      AND N.IDSTATUS IN ('A','E')
                                      AND I.VRIMPOSTONF     > 0
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                      AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                          N.CDFORNECED,N.DTENTRSAID, N.DTEMISSAO, I.CDFISCOPERNF,
                                          N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP) NF
                            WHERE FO.CDFORNECED(+)   = NF.CDFORNECED
                              AND FA.CDFORNECED      = NF.CDFORNECED
                              AND EF.CDFORNECED(+)   = FA.CDFORNECED
                              AND EF.IDTPENDEFORN(+) = NVL(FA.IDENDEMISFOR,'P')
                            UNION ALL
                           SELECT SUBSTR('53'||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),14,' ')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(FI.CDINSCESTA),1,5),'ISENT','ISENTO',FI.CDINSCESTA), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(NF.DTENTRSAID,'yyyymmdd')||
                                  RPAD(NVL(DECODE(NVL(F.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFILIEMIT,
                                          N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF,  N.CDINSCESTA,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF, '1', DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURACSN M, NOTAFISCAL N, IMPOSTONF I
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.NRSEQUMAPAPU    = N.NRSEQUMAPAPUSN
                                      AND N.CDINSCESTA      = P_CDINSCESTA
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND N.IDENTRSAIDOP    = 'E'
                                      AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND I.CDIMPOSTO       = V_CDIMPOSTRIB
                                      AND I.VRIMPOSTONF     > 0
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND N.IDSTATUS IN ('A','E')
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'
                                      AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDFILIEMIT, N.CDINSCESTA,
                                          N.DTENTRSAID, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF) NF
                            WHERE FI.CDFILIAL(+)     = NF.CDFILIEMIT
                              AND F.CDFILIAL         = NF.CDFILIEMIT
                              AND EF.CDFILIAL(+)     = F.CDFILIAL
                              AND EF.IDTPENDEFILI(+) = NVL(F.IDENDEMISFIL,'P')
                              AND NF.CDINSCESTA      = IE.CDINSCESTA
                            UNION ALL
                           SELECT SUBSTR('53'||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(FI.NRINSJURFILI, '-', ''), '.', ''), '/', ''),'00000000000000'),14,' ')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(FI.CDINSCESTA),1,5),'ISENT','ISENTO',FI.CDINSCESTA), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                                  TO_CHAR(NF.DTEMISSAO,'yyyymmdd')||
                                  RPAD(NVL(DECODE(NVL(F.IDENDEMISFIL,'P'),'I',IE.SGESTADO,EF.SGESTADO),' '),2,' ')||
                                  RPAD(NVL(NF.CDMODDOCFISC,'01'), 2, ' ')||
                                  RPAD(DECODE(TRIM(NVL(NF.CDSERIE,NF.CDSERIEENTR)),'-',' ',REPLACE(NVL(NF.CDSERIE,NF.CDSERIEENTR), '-', '')),3,' ')||
                                  SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6)||
                                  RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ')||
                                  'P'||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF) ,'00000000000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(ABS(NF.VRDESPESAS)  ,'00000000000.00'),'.',''))||
                                  'N'||
                                  ' '||
                                  RPAD(' ', 30, ' '), 1, 126) AS DSARQMAG
                             FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                  (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE) AS CDSERIE,
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR, N.CDFILIRECE,
                                          N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF,
                                          SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.CDINSCESTA,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                          SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                          SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0))   AS VROUTRCRED,
                                          SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,'1', DECODE(I.VRPERREDBCNF,0, 0,I.VRCONTABILNF-I.VRBASECALCNF), 0)) AS VRISENTRIB,
                                          0 AS VRDESPESAS
                                     FROM MAPAAPURACSN M, NOTAFISCAL N, IMPOSTONF I
                                    WHERE M.CDEMPRESA       = P_CDEMPRESA
                                      AND M.DTINIPERIODO    = P_DTINICIAL
                                      AND M.DTFIMPERIODO    = P_DTFINAL
                                      AND M.NRSEQUMAPAPU    = N.NRSEQUMAPAPUSN
                                      AND N.CDINSCESTA      = P_CDINSCESTA
                                      AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                      AND N.IDENTRSAIDOP    = 'S'
                                      AND NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'L'
                                      AND N.CDFILIAL        = I.CDFILIAL
                                      AND N.NRLANCTONF      = I.NRLANCTONF
                                      AND I.CDIMPOSTO       = V_CDIMPOSTRIB
                                      AND N.IDSTATUS IN ('A','E')
                                      AND I.VRIMPOSTONF     > 0
                                      AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                      AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                      AND N.CDMODDOCFISC IN ('01', '1A', '04', '06', '21', '22', '55')
                                    GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, DECODE(N.CDSERIE, '0', ' ', N.CDSERIE),
                                          DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR), N.CDINSCESTA,
                                          N.CDFILIRECE, N.DTEMISSAO, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF) NF
                            WHERE FI.CDFILIAL(+)     = NF.CDFILIRECE
                              AND F.CDFILIAL         = NF.CDFILIRECE
                              AND EF.CDFILIAL(+)     = F.CDFILIAL
                              AND EF.IDTPENDEFILI(+) = NVL(F.IDENDEMISFIL,'P')
                              AND NF.CDINSCESTA      = IE.CDINSCESTA
                              AND IE.CDEMPRESA       = P_CDEMPRESA) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('04'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO53.DSARQMAG, P_NRSEQARQMAG);

         V_CONTREG := V_CONTREG+1;
         V_CONTGERAL := V_CONTGERAL + 1;
      END LOOP;

      IF V_CONTREG > 0 THEN
        V_STTOTAL := V_STTOTAL||'53'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
      END IF;
   END IF;

   /* GERACAO DO REGISTRO 54 */
   V_CONTREG := 0;
   V_SEQUENCIAL := 0;
   V_STTOTAL := REGISTRO54_A_B_C_SINTEGRA(P_CDIMPOSTO, P_CDEMPRESA, P_CDINSCESTA, P_CDINSCESTAST, P_DTINICIAL, P_DTFINAL, P_NRSEQARQMAG, V_STTOTAL, '10', P_GERAFORSALE);

   V_TOTALRE54 := SUBSTR(V_STTOTAL,1,INSTR(V_STTOTAL, ';')-1);
   V_CONTREG54 := TO_NUMBER(SUBSTR(V_STTOTAL,INSTR(V_STTOTAL, ';')+1,LENGTH(V_STTOTAL)));

   IF (V_CONTREG54 > 0) THEN
      V_CONTGERAL := V_CONTREG54+V_CONTGERAL;
   END IF;
   V_STTOTAL := V_TOTALRE54;

   /*  GERACAO DO REGISTRO 55 */
   V_CONTREG := 0;
   FOR C_REGISTRO55 IN (SELECT SUBSTR('55' ||
                               REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'-',''),'/','') ||
                               RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(NVL(N.CDINSCESTAST,M.CDINSCESTA)),1,5),'ISENT','ISENTO',NVL(N.CDINSCESTAST,M.CDINSCESTA)), ' ', ''),'.',''),',',''),'-',''),'/',''),'ISENTO'),14,' ')||
                               TO_CHAR(P.DTORIGVENPAG,'yyyymmdd')||
                               RPAD(I.SGESTADO,2,' ')||
                               RPAD(EF.SGESTADO,2,' ')||
                               NVL(B.CDBANCBOLP, '   ')||
                               NVL(SUBSTR(CDAGENBOLP,2,5), '    ') ||
                               NVL(M.NRAUTBANDOC, '                    ')||
                               LTRIM(REPLACE(TO_CHAR(ROUND(ABS(P.VRATUAPAG),2),'00000000000.00'),'.',''))||
                               TO_CHAR(P.DTATUAVENPAG,'yyyymmdd')||
                               TO_CHAR(P.DTATUAVENPAG,'mmyyyy')||
                               RPAD(SUBSTR(M.NRCONPROMER,1,22),30,' ') ,1,126) AS DSARQUMAG
                          FROM MAPAAPURAC M, CONTAPAGAR P,  NOTAFISCAL N ,ENDEFORN EF,
                               BOLETPAGAR B, INSCRESTAD I
                         WHERE M.CDEMPRESA    = P_CDEMPRESA
                           AND M.CDIMPOSTO    = P_CDIMPOSTO
                           AND M.CDINSCESTA   = P_CDINSCESTA
                           AND M.DTINIPERIODO = P_DTINICIAL
                           AND M.DTFIMPERIODO = P_DTFINAL
                           AND M.CDFILIAL     = P.CDFILIAL
                           AND M.NRLANCTONF   = P.NRLANCTONF
                           AND M.CDEMPRESA    = I.CDEMPRESA
                           AND M.CDINSCESTA   = I.CDINSCESTA
                           AND N.CDFILIAL     = P.CDFILIAL
                           AND N.NRLANCTONF   = P.NRLANCTONF
                           AND ((N.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                           AND N.CDFORNECED   = EF.CDFORNECED(+)
                           AND P.CDFILIBPAG   = B.CDFILIAL
                           AND P.NRLANBOLPAG  = B.NRLANBOLPAG
                           AND NVL(EF.IDTPENDEFORN,'P') = 'P') LOOP

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('08'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO55.DSARQUMAG, P_NRSEQARQMAG);
      V_CONTREG := V_CONTREG + 1;
      V_CONTGERAL := V_CONTGERAL + 1;
   END LOOP;

   IF V_CONTREG > 0 THEN
      V_STTOTAL := V_STTOTAL||'55'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
   END IF;

   /* GERACAO DO REGISTRO 60 */
   V_CONTREG  := 0;
   V_CONTADOR := 0;
   IF (P_CDINSCESTAST IS NULL) THEN
      V_REG60MAIDR := REGISTRO60_M_A_D_I_R_SINTEGRA(P_CDEMPRESA, P_CDINSCESTA, P_DTINICIAL, P_DTFINAL, P_NRSEQARQMAG, V_STTOTAL, P_CDIMPOSTO);
      V_TOTALRE60  := SUBSTR(V_REG60MAIDR,1,INSTR(V_REG60MAIDR, ';')-1);
      V_CONTREG54  := TO_NUMBER(SUBSTR(V_REG60MAIDR,INSTR(V_REG60MAIDR, ';')+1,LENGTH(V_REG60MAIDR)));

      IF (V_CONTREG60 > 0) THEN
         V_CONTGERAL := V_CONTREG60+V_CONTGERAL;
      END IF;
      V_STTOTAL := V_TOTALRE60;
   END IF;

   /* GERACAO DO REGISTRO 61 */
   V_CONTREG  := 0;
   V_CONTADOR := 0;
   FOR C_REGISTRO61 IN (SELECT SUBSTR('61'||
                               RPAD(' ', 14, ' ')||
                               RPAD(' ', 14, ' ')||
                               TO_CHAR(NF.DTEMISSAO, 'yyyymmdd')||
                               RPAD(NVL(NF.CDMODDOCFISC,'02'), 2, ' ')||
                               RPAD(DECODE(TRIM(NVL(NF.CDSERIE,' ')),'-',' ',NVL(NF.CDSERIE,' ')),5,' ')||
                               SUBSTR(LPAD(NF.NOTAMIN,20,'0'),15,6)||
                               SUBSTR(LPAD(NF.NOTAMAX,20,'0'),15,6)||
                               LTRIM(REPLACE(TO_CHAR(NF.VRCONTABILNF, '00000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF),'0000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB),'00000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED),'00000000000.00'),'.',''))||
                               LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF),'00.00'),'.',''))||
                               RPAD(' ', 1, ' '),1, 126) AS DSARQMAG, NF.DTEMISSAO
                          FROM (SELECT NF.NRLANCTONF, DECODE(NF.CDSERIE, '0', ' ', NF.CDSERIE) AS CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,
                                       SUM(I.VRCONTABILNF) AS VRCONTABILNF, NVL(N.NRNOTAMIN, NF.NRNOTAFISC) AS NOTAMIN, NVL(N.NRNOTAMAX, NF.NRNOTAFISC) AS NOTAMAX,
                                       SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                       SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                       SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED,
                                       SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,DECODE(SUBSTR(I.CDSITUTRIBNF,2,2),'20',I.VRCONTABILNF-I.VRBASECALCNF,0))) AS VRISENTRIB
                                  FROM MAPAAPURAC M, NOTAFISCAL NF, IMPOSTONF I, IMPOSTO IM,
                                       (SELECT MIN(NRNOTAFISCALCE) AS NRNOTAMIN,MAX(NRNOTAFISCALCE) AS NRNOTAMAX, R.CDFILIALNOTA, R.NRLANCTONF
                                          FROM RELACNFCEAPROV R, VENDA V
                                         WHERE R.CDFILIALVENDA = V.CDFILIAL
                                           AND R.CDCAIXA       = V.CDCAIXA
                                           AND R.NRSEQVENDA    = V.NRSEQVENDA
                                         GROUP BY R.CDFILIALNOTA, R.NRLANCTONF) N
                                 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 I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                   AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                   AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND NF.IDENTRSAIDOP   = 'S'
                                   AND NF.IDSTATUS IN ('A','E')
                                   AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                   AND NF.CDFILIAL       = I.CDFILIAL
                                   AND NF.NRLANCTONF     = I.NRLANCTONF
                                   AND ((NF.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND NF.CDINSCESTAST IS NULL))
                                   AND NF.CDMODDOCFISC   IN ('02','13','14','15','16','63','65')
                                   AND NF.NRLANCTONF     = N.NRLANCTONF(+)
                                   AND NF.CDFILIAL       = N.CDFILIALNOTA(+)
                                   AND V_CDSITUCRT       = '3'
                                 GROUP BY NF.NRLANCTONF, DECODE(NF.CDSERIE, '0', ' ', NF.CDSERIE), NF.DTEMISSAO, I.VRALIQNF,
                                      NF.CDMODDOCFISC, NF.VRNOTAFISC, NVL(N.NRNOTAMIN,NF.NRNOTAFISC), NVL(N.NRNOTAMAX,NF.NRNOTAFISC)
                                 UNION ALL
                                /* EMPRESA DO SIMPLES NACIONAL */
                                SELECT NF.NRLANCTONF, (CASE NF.CDSERIE WHEN '0' THEN ' ' ELSE DECODE(NF.CDMODDOCFISC,'65',' ',NF.CDSERIE) END) AS CDSERIE,
                                       NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,
                                       SUM(I.VRCONTABILNF) AS VRCONTABILNF, NVL(N.NRNOTAMIN, NF.NRNOTAFISC) AS NOTAMIN, NVL(N.NRNOTAMAX, NF.NRNOTAFISC) AS NOTAMAX,
                                       SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                       SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                       SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED,
                                       SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,DECODE(SUBSTR(I.CDSITUTRIBNF,2,2),'20',I.VRCONTABILNF-I.VRBASECALCNF,0))) AS VRISENTRIB
                                  FROM MAPAAPURACSN M, NOTAFISCAL NF, IMPOSTONF I, IMPOSTO IM,
                                       (SELECT MIN(NRNOTAFISCALCE) AS NRNOTAMIN,MAX(NRNOTAFISCALCE) AS NRNOTAMAX, R.CDFILIALNOTA, R.NRLANCTONF
                                          FROM RELACNFCEAPROV R, VENDA V
                                         WHERE R.CDFILIALVENDA = V.CDFILIAL
                                           AND R.CDCAIXA       = V.CDCAIXA
                                           AND R.NRSEQVENDA    = V.NRSEQVENDA
                                         GROUP BY R.CDFILIALNOTA, R.NRLANCTONF) N
                                 WHERE M.CDEMPRESA       = P_CDEMPRESA
                                   AND M.DTINIPERIODO    = P_DTINICIAL
                                   AND M.DTFIMPERIODO    = P_DTFINAL
                                   AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                   AND NF.NRSEQUMAPAPUSN = M.NRSEQUMAPAPU
                                   AND NF.CDINSCESTA     = P_CDINSCESTA
                                   AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                   AND IM.CDIMPOSTONFE   = '00'
                                   AND NF.IDENTRSAIDOP   = 'S'
                                   AND NF.IDSTATUS IN ('A','E')
                                   AND NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'
                                   AND NF.CDFILIAL       = I.CDFILIAL
                                   AND NF.NRLANCTONF     = I.NRLANCTONF
                                   AND ((NF.CDINSCESTAST = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND NF.CDINSCESTAST IS NULL))
                                   AND NF.CDMODDOCFISC   IN ('02','13','14','15','16','63','65')
                                   AND NF.NRLANCTONF     = N.NRLANCTONF(+)
                                   AND NF.CDFILIAL       = N.CDFILIALNOTA(+)
                                   AND V_CDSITUCRT       <> '3'
                                 GROUP BY NF.NRLANCTONF, CDSERIE, NF.DTEMISSAO, I.VRALIQNF,
                                       NF.CDMODDOCFISC, NF.VRNOTAFISC, NVL(N.NRNOTAMIN, NF.NRNOTAFISC),
                                       NVL(N.NRNOTAMAX, NF.NRNOTAFISC)) NF
                         ORDER BY NF.DTEMISSAO) LOOP

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('14'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO61.DSARQMAG, P_NRSEQARQMAG);

      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      V_CONTREG   := V_CONTREG + 1;
      V_CONTGERAL := V_CONTGERAL + 1;
   END LOOP;

   /*GERA REGISTRO 61 PARA O FORSALE - 61 FOS*/
   /*IF (NVL(P_GERAFORSALE,'N') = 'S') AND (P_CDINSCESTAST IS NULL) THEN
      FOR C_REG61FOS IN (SELECT SUBSTR(
                                '61'||
                                RPAD(' ', 14, ' ')||
                                RPAD(' ', 14, ' ')||
                                TO_CHAR(NF1.DTENTRVENDA, 'yyyymmdd')||
                                RPAD('02', 2, ' ')||
                                SUBSTR (NF1.NRSERIEVND||RPAD(' ', 3,' '),1,3)||
                                SUBSTR (NF1.NRSUBSERIEVND||RPAD(' ', 2,' '),1,2)||
                                SUBSTR(LPAD(NF1.NRORDEMVND,10,'0'),5,6)||
                                SUBSTR(LPAD(NF1.NRORDEMVND,10,'0'),5,6)||
                                LTRIM(REPLACE(TO_CHAR(NVL(NF1.VRNOTAFISC,0), '00000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(NF2.VRBASECALCNF,0)),'00000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(NF2.VRIMPOSTONF,0)),'0000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(NF3.VRISENTRIB,0)),'00000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(NF4.VROUTRCRED,0)),'00000000000.00'),'.',''))||
                                LTRIM(REPLACE(TO_CHAR(ABS(NVL(NF2.VRALIQNF,0)),'00.00'),'.',''))||
                                RPAD(' ', 1, ' '),1, 126) AS DSARQMAG, NF1.DTENTRVENDA
                         FROM (SELECT VE.DTENTRVENDA, VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND,
                                      SUM((IT.QTPRODVEND * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS VRNOTAFISC
                               FROM   VENDA VE, ITEMVENDA IT
                               WHERE VE.CDEMPRESA   =  P_CDEMPRESA
                                 AND VE.CDFILIAL     = IT.CDFILIAL
                                 AND VE.NRSEQVENDA   = IT.NRSEQVENDA
                                 AND VE.CDCAIXA      = IT.CDCAIXA
                                 AND VE.DTENTRVENDA BETWEEN P_DTINICIAL AND P_DTFINAL
                                 AND VE.IDSITUVENDA = 'O'
                                 AND VE.NRORDEMVND IS NOT NULL
                               GROUP BY VE.DTENTRVENDA, VE.NRSERIEVND,VE.NRSUBSERIEVND, VE.NRORDEMVND) NF1,

                              (SELECT VE.DTENTRVENDA, VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND,
                                      SUM ((IT.QTPRODVEND   * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) AS VRBASECALCNF,
                                      SUM (((IT.QTPRODVEND  * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) * (AI.VRPEALPRODIT/100)) AS VRIMPOSTONF,
                                      SUM (((IT.QTPRODVEND  * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) * (AI.VRPEALPRODIT/100)) /
                                      SUM ((IT.QTPRODVEND   * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)) * 100 AS VRALIQNF
                               FROM   VENDA VE, ITEMVENDA IT, ITVENDAIMPOS AI, IMPOSTO IM
                               WHERE VE.CDEMPRESA   =  P_CDEMPRESA
                                 AND VE.CDFILIAL     = IT.CDFILIAL
                                 AND VE.NRSEQVENDA   = IT.NRSEQVENDA
                                 AND VE.CDCAIXA      = IT.CDCAIXA
                                 AND IT.CDFILIAL     = AI.CDFILIAL
                                 AND IT.CDCAIXA      = AI.CDCAIXA
                                 AND IT.NRSEQVENDA   = AI.NRSEQVENDA
                                 AND IT.NRSEQUITVEND = AI.NRSEQUITVEND
                                 AND VE.DTENTRVENDA BETWEEN P_DTINICIAL AND P_DTFINAL
                                 AND VE.IDSITUVENDA = 'O'
                                 AND VE.NRORDEMVND IS NOT NULL
                                 AND AI.CDIMPOSTO   = IM.CDIMPOSTO
                                 AND IM.IDTPIMPOSFIS = 'T'
                                 AND AI.VRPEALPRODIT > 0
                               GROUP BY VE.DTENTRVENDA,VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND) NF2,

                              (SELECT VE.DTENTRVENDA, VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND,
                                      NVL(SUM ((IT.QTPRODVEND   * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)),0) AS VRISENTRIB
                               FROM  VENDA VE, ITEMVENDA IT, ITVENDAIMPOS AI, IMPOSTO IM
                               WHERE VE.CDEMPRESA   =  P_CDEMPRESA
                                 AND VE.CDFILIAL     = IT.CDFILIAL
                                 AND VE.NRSEQVENDA   = IT.NRSEQVENDA
                                 AND VE.CDCAIXA      = IT.CDCAIXA
                                 AND IT.CDFILIAL     = AI.CDFILIAL
                                 AND IT.CDCAIXA      = AI.CDCAIXA
                                 AND IT.NRSEQVENDA   = AI.NRSEQVENDA
                                 AND IT.NRSEQUITVEND = AI.NRSEQUITVEND
                                 AND VE.DTENTRVENDA BETWEEN P_DTINICIAL AND P_DTFINAL
                                 AND VE.IDSITUVENDA  = 'O'
                                 AND VE.NRORDEMVND IS NOT NULL
                                 AND AI.CDIMPOSTO    = IM.CDIMPOSTO
                                 AND IM.IDTPIMPOSFIS = 'I'
                              GROUP BY VE.DTENTRVENDA,VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND) NF3,

                            (SELECT  VE.DTENTRVENDA, VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND,
                                     NVL(SUM ((IT.QTPRODVEND   * (IT.VRUNITVEND+NVL(IT.VRUNITVENDCL,0))) - NVL(IT.VRDESITVEND,0) + NVL(IT.VRACRITVEND,0)),0) AS VROUTRCRED
                               FROM   VENDA VE, ITEMVENDA IT, ITVENDAIMPOS AI, IMPOSTO IM
                               WHERE VE.CDEMPRESA   =  P_CDEMPRESA
                                 AND VE.CDFILIAL     = IT.CDFILIAL
                                 AND VE.NRSEQVENDA   = IT.NRSEQVENDA
                                 AND VE.CDCAIXA      = IT.CDCAIXA
                                 AND IT.CDFILIAL     = AI.CDFILIAL
                                 AND IT.CDCAIXA      = AI.CDCAIXA
                                 AND IT.NRSEQVENDA   = AI.NRSEQVENDA
                                 AND IT.NRSEQUITVEND = AI.NRSEQUITVEND
                                 AND VE.DTENTRVENDA BETWEEN P_DTINICIAL AND P_DTFINAL
                                 AND VE.IDSITUVENDA  = 'O'
                                 AND VE.NRORDEMVND IS NOT NULL
                                 AND AI.CDIMPOSTO    = IM.CDIMPOSTO
                                 AND IM.IDTPIMPOSFIS NOT IN ('T','I')
                              GROUP BY VE.DTENTRVENDA,VE.NRSERIEVND,VE.NRSUBSERIEVND,VE.NRORDEMVND) NF4
                          WHERE NF1.DTENTRVENDA   = NF2.DTENTRVENDA(+)
                            AND NF1.NRSERIEVND    = NF2.NRSERIEVND(+)
                            AND NF1.NRSUBSERIEVND = NF2.NRSUBSERIEVND(+)
                            AND NF1.NRORDEMVND    = NF2.NRORDEMVND(+)
                            AND NF1.DTENTRVENDA   = NF3.DTENTRVENDA(+)
                            AND NF1.NRSERIEVND    = NF3.NRSERIEVND(+)
                            AND NF1.NRSUBSERIEVND = NF3.NRSUBSERIEVND(+)
                            AND NF1.NRORDEMVND    = NF3.NRORDEMVND(+)
                            AND NF1.DTENTRVENDA   = NF4.DTENTRVENDA(+)
                            AND NF1.NRSERIEVND    = NF4.NRSERIEVND(+)
                            AND NF1.NRSUBSERIEVND = NF4.NRSUBSERIEVND(+)
                            AND NF1.NRORDEMVND    = NF4.NRORDEMVND(+) ) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('14'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REG61FOS.DSARQMAG, P_NRSEQARQMAG);

         V_CONTADOR  := NVL(V_CONTADOR,0)+1;
         V_CONTREG   := V_CONTREG + 1;
         V_CONTGERAL := V_CONTGERAL + 1;
      END LOOP;
   END IF;

   IF V_CONTREG > 0 THEN
      V_STTOTAL := V_STTOTAL||'61'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
   END IF;
   */

   /* GERACAO DO REGISTRO 70 */
   V_CONTADOR := 0;
   V_CONTREG := 0;
   IF (V_CDSITUCRT = '3') THEN
      FOR C_REGISTRO70 IN (SELECT SUBSTR('70'   ||
                                  NRINSJURNF    ||
                                  NRINSESTFORN  ||
                                  DTENTRSAID    ||
                                  SGESTADO      ||
                                  CDMODDOCFISC  ||
                                  CDSERIEENTR   ||
                                  NRNOTAFISC    ||
                                  CDFISCOPERNF  ||
                                  VRNOTAFISC    ||
                                  VRBASECALCNF  ||
                                  VRIMPOSTONF   ||
                                  VRISENTRIB    ||
                                  VROUTRCRED    ||
                                  IDPGTOFRETE   ||
                                  IDSTATUS,1 , 126) AS DSARQMAG, DTENTRSAID
                             FROM (SELECT '70',
                                          RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NF.NRINSJURNF,'.',''),',',''),'-',''),'/',''),'0'),14,' ') AS NRINSJURNF,
                                          RPAD(NVL(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'ISENTO'),14,' ') AS NRINSESTFORN,
                                          TO_CHAR(NF.DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                          RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                          RPAD(NVL(NF.CDMODDOCFISC,' '), 2, ' ') AS CDMODDOCFISC,
                                          RPAD(REPLACE(NVL(NF.CDSERIEENTR,' '),'-',' '),3,' ') AS CDSERIEENTR,
                                          SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                          RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.',''))   AS VRNOTAFISC,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRBASECALCNF,'000000000000.00'),'.','')) AS VRBASECALCNF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF, '000000000000.00'),'.','')) AS VRIMPOSTONF,
                                          LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB,  '000000000000.00'),'.','')) AS VRISENTRIB,
                                          LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED,  '000000000000.00'),'.','')) AS VROUTRCRED,
                                          RPAD(DECODE(NVL(NF.IDPGTOFRETE, '0'), '0', '1','1','2','2','2','9','0'), 1, ' ') AS IDPGTOFRETE,
                                          DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                                     FROM FORNECEDOR FO, ENDEFORN EF,
                                          (SELECT N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID,
                                                  DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR) AS CDSERIEENTR,
                                                  N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE, I.CDFISCOPERNF, N.CDMODDOCFISC,
                                                  SUM(DECODE(I.CDFISCALNF,'1',I.VRBASECALCNF,0)) AS VRBASECALCNF,
                                                  SUM(DECODE(I.CDFISCALNF,'1',I.VRIMPOSTONF,0))  AS VRIMPOSTONF,
                                                  SUM(DECODE(I.CDFISCALNF,'2',I.VRCONTABILNF,0)) AS VRISENTRIB,
                                                  SUM(DECODE(I.CDFISCALNF,'3',I.VRCONTABILNF,0)) AS VROUTRCRED
                                             FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, IMPOSTO IM
                                            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 I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU
                                              AND IM.CDIMPOSTONFE   = P_CDIMPOSTO
                                              AND IM.CDIMPOSTO      = I.CDIMPOSTO
                                              AND NVL(I.IDIMPOSTONF, '1') <> '3'
                                              AND N.CDFILIAL        = I.CDFILIAL
                                              AND N.NRLANCTONF      = I.NRLANCTONF
                                              AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                              AND N.IDENTRSAIDOP    = 'E'
                                              AND N.IDSTATUS IN ('A', 'E')
                                              AND N.CDMODDOCFISC    IN ('07','08','8B','09','10','11','26','27','57','63','67')
                                            GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID,
                                                     DECODE(N.CDSERIEENTR, '0', ' ', N.CDSERIEENTR),
                                                     N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE,I.CDFISCOPERNF, N.CDMODDOCFISC) NF
                                    WHERE FO.CDFORNECED      = NF.CDFORNECED
                                      AND EF.CDFORNECED(+)   = FO.CDFORNECED
                                      AND EF.IDTPENDEFORN(+) = NVL(FO.IDENDEMISFOR,'P')) NF
                            ORDER BY DTENTRSAID) LOOP

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('15'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO70.DSARQMAG, P_NRSEQARQMAG);

          V_CONTADOR  := NVL(V_CONTADOR,0)+1;
          V_CONTREG   := V_CONTREG + 1;
          V_CONTGERAL := V_CONTGERAL + 1;
       END LOOP;

       IF V_CONTREG > 0 THEN
          V_STTOTAL := V_STTOTAL||'70'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
       END IF;
   END IF;

   IF (V_CDSITUCRT <> '3') THEN
       FOR C_REGISTRO70 IN (SELECT SUBSTR('70'   ||
                                   NRINSJURNF    ||
                                   NRINSESTFORN  ||
                                   DTENTRSAID    ||
                                   SGESTADO      ||
                                   CDMODDOCFISC  ||
                                   CDSERIEENTR   ||
                                   NRNOTAFISC    ||
                                   CDFISCOPERNF  ||
                                   VRNOTAFISC    ||
                                   VRBASECALCNF  ||
                                   VRIMPOSTONF   ||
                                   VRISENTRIB    ||
                                   VROUTRCRED    ||
                                   IDPGTOFRETE   ||
                                   IDSTATUS,1 , 126) AS DSARQMAG, DTENTRSAID
                              FROM (SELECT '70',
                                           RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NF.NRINSJURNF,'.',''),',',''),'-',''),'/',''),'0'),14,' ') AS NRINSJURNF,
                                           RPAD(NVL(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),',',''),'-',''),'ISENTO'),14,' ') AS NRINSESTFORN,
                                           TO_CHAR(NF.DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                           RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADO,
                                           RPAD(NVL(NF.CDMODDOCFISC,' '), 2, ' ') AS CDMODDOCFISC,
                                           RPAD(REPLACE(NVL(NF.CDSERIEENTR,' '),'-',' '),3,' ') AS CDSERIEENTR,
                                           SUBSTR(LPAD(NF.NRNOTAFISC,20,'0'),15,6) AS NRNOTAFISC,
                                           RPAD(NVL(NF.CDFISCOPERNF,'0'),4,' ') AS CDFISCOPERNF,
                                           LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.',''))   AS VRNOTAFISC,
                                           LTRIM(REPLACE(TO_CHAR(NF.VRBASECALCNF,'000000000000.00'),'.','')) AS VRBASECALCNF,
                                           LTRIM(REPLACE(TO_CHAR(NF.VRIMPOSTONF, '000000000000.00'),'.','')) AS VRIMPOSTONF,
                                           LTRIM(REPLACE(TO_CHAR(NF.VRISENTRIB,  '000000000000.00'),'.','')) AS VRISENTRIB,
                                           LTRIM(REPLACE(TO_CHAR(NF.VROUTRCRED,  '000000000000.00'),'.','')) AS VROUTRCRED,
                                           RPAD(DECODE(NVL(NF.IDPGTOFRETE, '0'), '0', '1','1','2','2','2','9','0'), 1, ' ') AS IDPGTOFRETE,
                                           DECODE(NF.IDSTATUS, 'C', 'S', 'N') AS IDSTATUS
                                      FROM FORNECEDOR FO, ENDEFORN EF,
                                           (SELECT N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, N.CDSERIEENTR,
                                                   N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE, I.CDFISOPEITEM  AS CDFISCOPERNF, N.CDMODDOCFISC,
                                                   0 AS VRBASECALCNF,
                                                   0 AS VRIMPOSTONF,
                                                   0 AS VRISENTRIB,
                                                   SUM(I.VRCONTITEM) AS VROUTRCRED
                                              FROM MAPAAPURACSN M, NOTAFISCAL N, ITEMPRODUTO I
                                             WHERE M.CDEMPRESA       = P_CDEMPRESA
                                               AND N.CDINSCESTA      = P_CDINSCESTA
                                               AND ((N.CDINSCESTAST  = P_CDINSCESTAST) OR (P_CDINSCESTAST IS NULL AND N.CDINSCESTAST IS NULL))
                                               AND M.DTINIPERIODO    = P_DTINICIAL
                                               AND M.DTFIMPERIODO    = P_DTFINAL
                                               AND N.NRSEQUMAPAPUSN  = M.NRSEQUMAPAPU
                                               AND N.CDFILIAL        = I.CDFILIAL
                                               AND N.NRLANCTONF      = I.NRLANCTONF
                                               AND N.IDENTRSAIDOP    = 'E'
                                               AND N.IDSTATUS        IN ('A', 'E')
                                               AND N.CDMODDOCFISC    IN ('07','08','8B','09','10','11','26','27','57','63','67')
                                             GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, N.CDSERIEENTR,
                                                    N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE,I.CDFISOPEITEM, N.CDMODDOCFISC) NF
                                     WHERE FO.CDFORNECED      = NF.CDFORNECED
                                       AND EF.CDFORNECED(+)   = FO.CDFORNECED
                                       AND EF.IDTPENDEFORN(+) = NVL(FO.IDENDEMISFOR,'P')) NF
                             ORDER BY DTENTRSAID) LOOP

          INSERT INTO ARQMAGNETICO
            (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
          VALUES
            ('15'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO70.DSARQMAG, P_NRSEQARQMAG);

          V_CONTADOR  := NVL(V_CONTADOR,0)+1;
          V_CONTREG   := V_CONTREG + 1;
          V_CONTGERAL := V_CONTGERAL + 1;
       END LOOP;

       IF V_CONTREG > 0 THEN
          V_STTOTAL := V_STTOTAL||'70'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
       END IF;
   END IF;

   /* GERACAO DO REGISTRO 74 */
   V_CONTREG  := 0;
   V_CONTADOR := 0;
   IF SUBSTR(P_REGISTRO,9,1) = 'S' THEN
      IF P_INVENTARIO = 'N' THEN
         IF (P_STARQEXP = 'SINTEGRA - RS') THEN
            FOR C_REGISTRO74 IN (SELECT SUBSTR(
                                        '74'||
                                        TO_CHAR(DTINVEFISCAL,'yyyymmdd')||
                                        RPAD(CDARVPROD,14, ' ')||
                                        LTRIM(REPLACE(TO_CHAR(QTESTINVFISC,'00000000000.00'),'.',''))||
                                        LTRIM(REPLACE(TO_CHAR(VRTOTINVFISC,'00000000000.00'),'.',''))||
                                        '1'||
                                        RPAD('0', 14, '0')||
                                        RPAD(' ', 14, ' ')||
                                        RPAD(' ',  2, ' ')||
                                        RPAD(' ', 45, ' '), 1, 126) AS DSARQMAG,
                                        CDARVPROD, NVL(CDCLASFISC, '00000000') AS CDCLASFISC, NMPRODUTO, SGUNIDADE , DTINVEFISCAL
                                   FROM (SELECT I.VRTOTINVFISC,I.QTESTINVFISC,I.DTINVEFISCAL, P.CDARVPROD,
                                                NVL(I.CDCLASFISC,P.CDCLASFISC) AS CDCLASFISC, P.NMPRODUTO, P.SGUNIDADE
                                           FROM INVENTFISC I, PRODUTO P
                                          WHERE I.CDEMPRESA    = P_CDEMPRESA
                                            AND I.CDINSCESTA   = P_CDINSCESTA
                                            AND I.CDPRODUTO    = P.CDPRODUTO
                                            AND I.QTESTINVFISC <> 0
                                            AND I.VRTOTINVFISC <> 0
                                            AND I.DTINVEFISCAL = (SELECT MAX(DTINVEFISCAL)
                                                                   FROM  INVENTFISC V
                                                                  WHERE V.CDEMPRESA    = P_CDEMPRESA
                                                                    AND V.CDINSCESTA   = P_CDINSCESTA
                                                                    AND V.DTINVEFISCAL >= TO_DATE('01/01/'||(TO_CHAR(P_DTINICIAL,'YYYY')-1))
                                                                    AND V.CDPRODUTO    = P.CDPRODUTO
                                                                    AND V.QTESTINVFISC <> 0
                                                                    AND V.VRTOTINVFISC <> 0))) LOOP

               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 ('16'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO74.DSARQMAG, P_NRSEQARQMAG);

               V_CONTADOR  := NVL(V_CONTADOR,0)+1;
               V_CONTREG   := V_CONTREG + 1;
               V_CONTGERAL := V_CONTGERAL + 1;

               SELECT MAX(CDARVPROD)
                 INTO V_CDARVPROD
                 FROM TMP_REGISTRO_PRODUTO
                WHERE CDARVPROD = C_REGISTRO74.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_REGISTRO74.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO74.CDCLASFISC, C_REGISTRO74.NMPRODUTO,
                      C_REGISTRO74.SGUNIDADE, '040', 0.00, 0.00, 0.00, 0);
               END IF;
            END LOOP;
         ELSE
            FOR C_REGISTRO74 IN (SELECT SUBSTR(
                                        '74'||
                                        TO_CHAR(DTINVEFISCAL,'yyyymmdd')||
                                        RPAD(CDARVPROD,14, ' ')||
                                        LTRIM(REPLACE(TO_CHAR(QTESTINVFISC,'00000000000.00'),'.',''))||
                                        LTRIM(REPLACE(TO_CHAR(VRTOTINVFISC,'00000000000.00'),'.',''))||
                                        '1'||
                                        RPAD('0', 14, '0')||
                                        RPAD(' ', 14, ' ')||
                                        RPAD(' ',  2, ' ')||
                                        RPAD(' ', 45, ' '), 1, 126) AS DSARQMAG,
                                        CDARVPROD, NVL(CDCLASFISC, '00000000') AS CDCLASFISC, NMPRODUTO, SGUNIDADE , DTINVEFISCAL
                                   FROM (SELECT I.VRTOTINVFISC, I.QTESTINVFISC, I.DTINVEFISCAL, P.CDARVPROD,
                                                NVL(I.CDCLASFISC,P.CDCLASFISC) AS CDCLASFISC, P.NMPRODUTO, P.SGUNIDADE
                                           FROM INVENTFISC I, PRODUTO P
                                          WHERE I.CDEMPRESA    = P_CDEMPRESA
                                            AND I.CDINSCESTA   = P_CDINSCESTA
                                            AND I.CDPRODUTO    = P.CDPRODUTO
                                            AND I.QTESTINVFISC <> 0
                                            AND I.VRTOTINVFISC <> 0
                                            AND I.DTINVEFISCAL = (SELECT MAX(DTINVEFISCAL)
                                                                   FROM  INVENTFISC V, PRODUTO PR
                                                                  WHERE V.CDEMPRESA    = P_CDEMPRESA
                                                                    AND V.CDINSCESTA   = P_CDINSCESTA
                                                                    AND V.DTINVEFISCAL >= TO_DATE('01/01/'||(TO_CHAR(P_DTINICIAL,'YYYY')-1))
                                                                    AND V.CDPRODUTO    = P.CDPRODUTO
                                                                    AND V.QTESTINVFISC <> 0
                                                                    AND V.VRTOTINVFISC <> 0))) LOOP

               INSERT INTO ARQMAGNETICO
                 (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
               VALUES
                 ('16'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO74.DSARQMAG, P_NRSEQARQMAG);

               V_CONTADOR  := NVL(V_CONTADOR,0)+1;
               V_CONTREG   := V_CONTREG + 1;
               V_CONTGERAL := V_CONTGERAL + 1;

               SELECT MAX(CDARVPROD)
                 INTO V_CDARVPROD
                 FROM TMP_REGISTRO_PRODUTO
                WHERE CDARVPROD = C_REGISTRO74.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_REGISTRO74.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO74.CDCLASFISC, C_REGISTRO74.NMPRODUTO,
                      C_REGISTRO74.SGUNIDADE, '040', 0.00, 0.00, 0.00, 0);
               END IF;
            END LOOP;
         END IF;
      ELSE
         FOR C_REGISTRO74 IN (SELECT SUBSTR(
                                     '74'||
                                     TO_CHAR(DTINVEFISCAL,'yyyymmdd')||
                                     RPAD(CDARVPROD,14, ' ')||
                                     LTRIM(REPLACE(TO_CHAR(QTESTINVFISC,'00000000000.00'),'.',''))||
                                     LTRIM(REPLACE(TO_CHAR(VRTOTINVFISC,'00000000000.00'),'.',''))||
                                     '1'||
                                     RPAD('0', 14, '0')||
                                     RPAD(' ', 14, ' ')||
                                     RPAD(' ',  2, ' ')||
                                     RPAD(' ', 45, ' '), 1, 126) AS DSARQMAG,
                                     CDARVPROD, NVL(CDCLASFISC, '00000000') AS CDCLASFISC, NMPRODUTO, SGUNIDADE , DTINVEFISCAL
                                FROM (SELECT I.VRTOTINVFISC, I.QTESTINVFISC, I.DTINVEFISCAL, P.CDARVPROD,
                                             NVL(I.CDCLASFISC,P.CDCLASFISC) AS CDCLASFISC, P.NMPRODUTO, P.SGUNIDADE
                                        FROM INVENTFISC I, PRODUTO P
                                       WHERE I.CDEMPRESA    = P_CDEMPRESA
                                         AND I.CDINSCESTA   = P_CDINSCESTA
                                         AND I.CDPRODUTO    = P.CDPRODUTO
                                         AND I.QTESTINVFISC <> 0
                                         AND I.VRTOTINVFISC <> 0
                                         AND I.DTINVEFISCAL = (SELECT MAX(DTINVEFISCAL)
                                                                FROM  INVENTFISC V
                                                               WHERE V.CDEMPRESA    = P_CDEMPRESA
                                                                 AND V.CDINSCESTA   = P_CDINSCESTA
                                                                 AND V.DTINVEFISCAL >= TO_DATE('01/01/'||(TO_CHAR(P_DTINICIAL,'YYYY')-1))
                                                                 AND V.CDPRODUTO    = P.CDPRODUTO
                                                                 AND V.QTESTINVFISC <> 0
                                                                 AND V.VRTOTINVFISC <> 0))) LOOP
            INSERT INTO ARQMAGNETICO
              (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
            VALUES
              ('16'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO74.DSARQMAG, P_NRSEQARQMAG);

            V_CONTADOR  := NVL(V_CONTADOR,0)+1;
            V_CONTREG   := V_CONTREG + 1;
            V_CONTGERAL := V_CONTGERAL + 1;

            SELECT MAX(CDARVPROD)
              INTO V_CDARVPROD
              FROM TMP_REGISTRO_PRODUTO
             WHERE CDARVPROD = C_REGISTRO74.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_REGISTRO74.CDARVPROD, P_DTINICIAL, P_DTFINAL, C_REGISTRO74.CDCLASFISC, C_REGISTRO74.NMPRODUTO,
                   C_REGISTRO74.SGUNIDADE, '040', 0.00, 0.00, 0.00, 0);
            END IF;
         END LOOP;
      END IF;

      IF V_CONTREG > 0 THEN
         V_STTOTAL := V_STTOTAL||'74'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
      END IF;
   END IF;

   /* GERACAO DO REGISTRO 75 */
   V_CONTREG := 0;

   IF P_VERSAO = '3' THEN
      FOR C_REGISTRO75 IN (SELECT SUBSTR(CDTIPOREG ||
                                  TO_CHAR(DTINICIAL, 'YYYYMMDD')||
                                  TO_CHAR(DTFINAL  , 'YYYYMMDD')||
                                  RPAD(CDARVPROD, 14, ' ')      ||
                                  RPAD(NVL(CDCLASFISC, '00000000'), 8, ' ')  ||
                                  RPAD(TRIM(NMPRODUTO), 53, ' ')      ||
                                  RPAD(SGUNIDADE, 6, ' ')       ||
                                  LTRIM(REPLACE(TO_CHAR(VRALIQIPI, '000.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(VRALIQICMS, '00.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(VRPERREDBCIT, '000.00'),'.',''))||
                                  '0000000000000', 1, 126) AS DSARQMAG
                             FROM TMP_REGISTRO_PRODUTO
                            ORDER BY CDARVPROD) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('17'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO75.DSARQMAG, P_NRSEQARQMAG);

         V_CONTREG := V_CONTREG + 1;
         V_CONTGERAL := V_CONTGERAL + 1;
      END LOOP;
   ELSE
      FOR C_REGISTRO75 IN (SELECT SUBSTR(CDTIPOREG ||
                                  TO_CHAR(DTINICIAL, 'YYYYMMDD')||
                                  TO_CHAR(DTFINAL  , 'YYYYMMDD')||
                                  RPAD(CDARVPROD, 14, ' ') ||
                                  RPAD(NVL(CDCLASFISC, '00000000'), 8, ' ')  ||
                                  RPAD(TRIM(NMPRODUTO), 53, ' ') ||
                                  RPAD(SGUNIDADE, 6, ' ')  ||
                                  RPAD(CDSITUTRIBPR,3, ' ') ||
                                  LTRIM(REPLACE(TO_CHAR(VRALIQIPI, '00.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(VRALIQICMS, '00.00'),'.',''))||
                                  LTRIM(REPLACE(TO_CHAR(VRPERREDBCIT, '00.00'),'.',''))||
                                  '0000000000000', 1, 126) AS DSARQMAG
                             FROM TMP_REGISTRO_PRODUTO
                            ORDER BY CDARVPROD) LOOP

         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('18'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO75.DSARQMAG, P_NRSEQARQMAG);

         V_CONTREG := V_CONTREG + 1;
         V_CONTGERAL := V_CONTGERAL + 1;
      END LOOP;
   END IF;
   
   IF V_CONTREG > 0 THEN
      V_STTOTAL := V_STTOTAL||'75'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
   END IF;

   IF V_CONTGERAL > 0 THEN
      V_TOTALGERAL := RTRIM(LPAD(RTRIM(TO_CHAR(V_CONTGERAL + 1)),8,'0'));
   END IF;

   V_TOT_REG88 := 0;

   V_TOT_REG88 := REGISTRO88_SINTEGRA(P_CDEMPRESA,P_CDINSCESTA,P_DTINICIAL,P_DTFINAL,P_CDIMPOSTO,P_NRSEQARQMAG);

   /* GERACAO DO REGISTRO 90 */
   FOR C_REG_90 IN (SELECT SUBSTR(DSREGARQMAG,1,2)||LPAD(COUNT(*), 8, '0') AS LINHA,
                           COUNT(*) AS TOT_REG
                      FROM ARQMAGNETICO
                     WHERE NRSEQARQMAG = P_NRSEQARQMAG
                       AND SUBSTR(DSREGARQMAG,1,2) NOT IN ('10','11','90')
                     GROUP BY SUBSTR(DSREGARQMAG,1,2)
                     ORDER BY SUBSTR(DSREGARQMAG,1,2)) LOOP

      V_TOTAL_LINHA := NVL(V_TOTAL_LINHA,'') || C_REG_90.LINHA;
      V_TOT_REG     := NVL(V_TOT_REG,0) + C_REG_90.TOT_REG;
      V_TOTAL_REG   :=  C_REG_90.LINHA;
   END LOOP;

   IF V_TOTAL_REG IS NOT NULL THEN
      FOR C_REGISTRO90 IN (SELECT SUBSTR(RPAD('90'||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'.',''),',',''),'/',''),'-',''),'00000000000000'),14,' ')||
                                  RPAD(NVL(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(NVL(P_CDINSCESTAST,P_CDINSCESTA)),1,5),'ISENT','ISENTO',NVL(P_CDINSCESTAST,P_CDINSCESTA)),'.',''),',',''),'-',''),'ISENTO'),14,' ')||
                                  V_TOTAL_LINHA ||
                                  '99'||
                                  LPAD(TO_CHAR(V_TOT_REG+3), 8 ,'0'), 125, ' ') ||
                                  '1' ,1, 126) AS DSARQMAG
                             FROM INSCRESTAD
                            WHERE CDEMPRESA  = P_CDEMPRESA
                              AND CDINSCESTA = P_CDINSCESTA) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('22'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO90.DSARQMAG, P_NRSEQARQMAG);
      END LOOP;
   ELSE
      SELECT COUNT(*)
        INTO V_REGVAZIO
        FROM ARQMAGNETICO
       WHERE NRSEQARQMAG = P_NRSEQARQMAG;

      FOR C_REG90 IN (SELECT SUBSTR(RPAD('90'||
                             RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'.',''),',',''),'/',''),'-',''),'00000000000000'),14,' ')||
                             RPAD(NVL(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(NVL(P_CDINSCESTAST,P_CDINSCESTA)),1,5),'ISENT','ISENTO',NVL(P_CDINSCESTAST,P_CDINSCESTA)),'.',''),',',''),'-',''),'ISENTO'),14,' ')||
                             '99'||
                             LPAD(TO_CHAR(V_REGVAZIO+1), 8 ,'0' ), 125, ' ') ||
                             '1' ,1, 126) AS DSARQMAG
                        FROM INSCRESTAD
                       WHERE CDEMPRESA  = P_CDEMPRESA
                         AND CDINSCESTA = P_CDINSCESTA) LOOP
         INSERT INTO ARQMAGNETICO
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
         VALUES
           ('22'||LPAD(RTRIM('00'), 8, '0'), C_REG90.DSARQMAG, P_NRSEQARQMAG);
      END LOOP;
   END IF;

   DELETE FROM TMP_REGISTRO_PRODUTO;
   COMMIT;
END;
/