CREATE OR REPLACE PROCEDURE ARQUIVO_MAGNETICO_MS(
   P_REGISTRO    IN VARCHAR2,
   P_CDIMPOSTO   IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN EMPRESA.CDINSCESTA%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)
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_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_IPI            VARCHAR2(02);
   V_REGISTRO88     VARCHAR2(20);
BEGIN
   BEGIN
      SELECT CDIMPOSTOLIVRO
      INTO V_IPI
      FROM TPIMPOSLIVRO
      WHERE CDLIVROMAG = P_LIVRO
        AND SGIMPOSTOLIVRO LIKE 'IPI';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IPI := NULL;
   END;
   FOR C_REGISTRO10 IN (SELECT SUBSTR(
                           '10'||
                           RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),',',''),'-',''),'/',''),1,14),14,' ')||
                           RPAD(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(IE.CDINSCESTA),1,5),'ISENT','ISENTO',IE.CDINSCESTA),'.',''),',',''),'-',''),'/',''),14,' ')||
                           RPAD(EP.NMRAZSOCEMPR,35,' ')||
                           RPAD(MU.NMMUNICIPIO,30,' ')||
                           RPAD(IE.SGESTADO,2,' ')||
                           LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(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
                        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(+)) 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(IE.DSENDEIEST, ' '),34,' ')||
                           RPAD('00000s/n',27,' ')||
                           RPAD(NVL(IE.NMBAIRIEST, ' '),15,' ')||
                           LPAD(REPLACE(REPLACE(NVL(IE.NRCEPIEST,'0'),'.',''),'-',''),8,'0')||
                           RPAD(NVL(IE.NMREPRINSEST, ' '),28,' ')||
                           LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(IE.NRTELEIEST,'0'),' ',''),')',''),'(',''),'-',''),'.',''),12,'0')
                           ,1,126) AS DSARQMAG
                        FROM INSCRESTAD IE
                        WHERE IE.CDEMPRESA   = P_CDEMPRESA   AND
                              IE.CDINSCESTA  = P_CDINSCESTA) LOOP
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('01'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO11.DSARQMAG, P_NRSEQARQMAG);
   END LOOP;

   V_CONTGERAL := 2;
   V_CONTREG := 0;
   V_CONTADOR := 0;
   IF SUBSTR(P_REGISTRO,1,1) = 'S' THEN
      FOR C_REGISTRO50 IN (SELECT DATA, SUBSTR('50'        ||
                                               NRINSJUR    ||
                                               NRINSEST    ||
                                               DATA        ||
                                               SGESTADO    ||
                                               CDMODDOCFISC||
                                               SERIE       ||
                                               NRNOTAFISC  ||
                                               CDFISCOPERNF||
                                               IDENTRSAIDOP||
                                               VRCONTABILNF||
                                               VRBASECALCNF||
                                               VRIMPOSTONF ||
                                               VRISENTRIB  ||
                                               VROUTCRED   ||
                                               VRALIQNF    ||
                                               'N' ,1,126) AS DSARQMAG
                           FROM(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,
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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,
                                       'N'
                                FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA,
                                    (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, N.DTENTRSAID,
                                            N.DTEMISSAO, I.VRALIQNF, 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
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD
                                     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       = P_CDIMPOSTO                                  AND
                                           NVL(I.IDIMPOSTONF, '1') <> '3'                                   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
                                           NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'                       AND
                                           NVL(N.CDMODDOCFISC, '01') = MD.CDMODDOCFISC                      AND
                                           MD.CDTPREGFISC  = '50'
                                     GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR,
                                              N.CDCLIENTE, N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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')     AND
                                      EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS)     AND
                                      (INSTR(NVL(P_SGESTADO, EC.SGESTADO), EC.SGESTADO) > 0)
                                UNION ALL
                                SELECT '50',
                                       '00000000000000' AS NRINSJUR,
                                       'ISENTO        ' AS NRINSEST,
                                       TO_CHAR(DECODE(NF.IDENTRSAIDOP,'E', NF.DTENTRSAID, NF.DTEMISSAO),'yyyymmdd') AS DATA,
                                       'EX' 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,
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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, 
                                       'N' 
                                FROM CLIENTE CL, ENDECLIE EC, CLIENTE CA, 
                                    (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, N.DTENTRSAID, 
                                            N.DTEMISSAO, I.VRALIQNF, 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 
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD 
                                     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       = P_CDIMPOSTO                                  AND 
                                           NVL(I.IDIMPOSTONF, '1') <> '3'                                   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 
                                           SUBSTR(I.CDFISCOPERNF,1,1) = '7'                                 AND 
                                           NVL(N.CDMODDOCFISC, '01')  = MD.CDMODDOCFISC                     AND 
                                           MD.CDTPREGFISC  = '50' 
                                     GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, 
                                              N.CDCLIENTE, N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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') AND 
                                      EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS) AND 
                                      (INSTR(NVL(P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0) 
                                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, 
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')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, 
                                       'N' 
                                FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA, 
                                    (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDFORNECED, 
                                            N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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 
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD 
                                     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       = P_CDIMPOSTO                                  AND 
                                           NVL(I.IDIMPOSTONF, '1') <> '3'                                   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 
                                           NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'                       AND 
                                           NVL(N.CDMODDOCFISC, '01')  = MD.CDMODDOCFISC                     AND 
                                           MD.CDTPREGFISC  = '50' 
                                     GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, 
                                              N.CDFORNECED, N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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') AND 
                                      EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS) AND 
                                      (INSTR(NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0) 
                                UNION ALL 
                                SELECT '50', 
                                       '00000000000000' AS NRINSJUR, 
                                       'ISENTO        ' AS NRINSEST, 
                                       TO_CHAR(DECODE(NF.IDENTRSAIDOP, 'S', NF.DTEMISSAO, NF.DTENTRSAID),'yyyymmdd') AS DATA, 
                                       'EX' 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, 
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')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, 
                                       'N' 
                                FROM FORNECEDOR FO, ENDEFORN EF, FORNECEDOR FA, 
                                    (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDFORNECED, 
                                            N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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 
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD 
                                     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       = P_CDIMPOSTO                                  AND 
                                           NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND 
                                           N.CDFILIAL        = I.CDFILIAL                                   AND 
                                           N.NRLANCTONF      = I.NRLANCTONF                                 AND 
                                           NVL(N.IDORIGDESTNF, DECODE(N.IDENTRSAIDOP, 'E', 'F', 'C')) = 'F' AND 
                                           N.IDSTATUS IN ('A','E')                                          AND 
                                           SUBSTR(I.CDFISCOPERNF,1,1) = '3'                                 AND 
                                           NVL(N.CDMODDOCFISC, '01')  = MD.CDMODDOCFISC                     AND 
                                           MD.CDTPREGFISC  = '50' 
                                     GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, 
                                              N.CDFORNECED, N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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') AND 
                                      EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS) AND 
                                      (INSTR( NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0) 
                                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, 
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')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, 
                                       'N' 
                                FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                    (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDFILIEMIT, 
                                            N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, 
                                            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 
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD 
                                     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       = P_CDIMPOSTO                                  AND 
                                           NVL(I.IDIMPOSTONF, '1') <> '3'                                   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 
                                           NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'                       AND 
                                           NVL(N.CDMODDOCFISC, '01') = MD.CDMODDOCFISC                      AND 
                                           MD.CDTPREGFISC  = '50' 
                                     GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDFILIEMIT, N.CDINSCESTA,
                                              N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,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                  AND
                                      NVL(EF.CDPAIS,IE.CDPAIS) = NVL(P_CDPAIS,NVL(EF.CDPAIS,IE.CDPAIS))      AND
                                      (INSTR(NVL(P_SGESTADO, NVL(EF.SGESTADO,IE.SGESTADO)), NVL(EF.SGESTADO,IE.SGESTADO)) > 0)
                                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, 
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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, 
                                       'N' 
                                FROM FILIAL FI, ENDEFILI EF, FILIAL F, INSCRESTAD IE,
                                    (SELECT I.CDFILIAL, I.NRLANCTONF,N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDFILIRECE, 
                                            N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, 
                                            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 
                                     FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD 
                                     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       = P_CDIMPOSTO                                  AND 
                                           NVL(I.IDIMPOSTONF, '1') <> '3'                                   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 
                                           NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'                       AND 
                                           NVL(N.CDMODDOCFISC, '01') = MD.CDMODDOCFISC                      AND 
                                           MD.CDTPREGFISC  = '50' 
                                     GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.IDENTRSAIDOP, N.CDINSCESTA,
                                              N.CDFILIRECE, N.DTEMISSAO, I.VRALIQNF, 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                  AND
                                      NVL(EF.CDPAIS,IE.CDPAIS) = NVL(P_CDPAIS,NVL(EF.CDPAIS,IE.CDPAIS)) AND
                                      (INSTR(NVL(P_SGESTADO, NVL(EF.SGESTADO,IE.SGESTADO)), NVL(EF.SGESTADO,IE.SGESTADO)) > 0) ) NF
                           ORDER BY DATA) LOOP 
         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           ('02'||LPAD(RTRIM(V_CONTADOR), 8, '0'), C_REGISTRO50.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; 
      IF V_CONTREG > 0 THEN 
        V_STTOTAL := V_STTOTAL||'50'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0'); 
      END IF; 
   END IF; 
    
   V_CONTREG := 0; 
   V_CONTADOR := 0; 
   IF SUBSTR(P_REGISTRO,2,1) = 'S' 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, 
                                     LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED, 
                                         N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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, MODDOCFISC MD, 
                                        (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                         FROM MAPAAPURAC M, IMPOSTONF IP 
                                         WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                               M.CDINSCESTA   = P_CDINSCESTA    AND 
                                               M.DTINIPERIODO = P_DTINICIAL     AND 
                                               M.DTFIMPERIODO = P_DTFINAL       AND 
                                               M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                               M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                               IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                   WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND 
                                         N.CDINSCESTA      = P_CDINSCESTA                  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 
                                         I.CDIMPOSTO       = V_IPI                         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),' ') <> '3'        AND 
                                         N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND 
                                         NVL(MD.CDTPREGFISC, '50') IN ('50', '51') 
                                   GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED, 
                                            N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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') AND 
                                    EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS)  AND 
                                    (INSTR(NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0) 
                              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, 
                                     LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED, 
                                         N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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, MODDOCFISC MD, 
                                        (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                         FROM MAPAAPURAC M, IMPOSTONF IP 
                                         WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                               M.CDINSCESTA   = P_CDINSCESTA    AND 
                                               M.DTINIPERIODO = P_DTINICIAL     AND 
                                               M.DTFIMPERIODO = P_DTFINAL       AND 
                                               M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                               M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                               IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                   WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND 
                                         N.CDINSCESTA      = P_CDINSCESTA                  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 
                                         I.CDIMPOSTO       = V_IPI                         AND 
                                         I.CDFILIAL        = IC.CDFILIAL                   AND 
                                         I.NRLANCTONF      = IC.NRLANCTONF                 AND 
                                         NVL(I.IDIMPOSTONF, '1') <> '3'                    AND 
                                         I.NRSEQUMAPAPU IS NOT NULL                        AND 
                                         SUBSTR(I.CDFISCOPERNF,1,1) = '3'                  AND 
                                         N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND 
                                         NVL(MD.CDTPREGFISC, '50') IN ('50', '51') 
                                   GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIEENTR, N.CDSERIE, N.CDFORNECED, 
                                            N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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') AND 
                                    EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS)  AND 
                                    (INSTR(NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0) 
                              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, 
                                     LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, 
                                         N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, 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, MODDOCFISC MD, 
                                        (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                         FROM MAPAAPURAC M, IMPOSTONF IP 
                                         WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                               M.CDINSCESTA   = P_CDINSCESTA    AND 
                                               M.DTINIPERIODO = P_DTINICIAL     AND 
                                               M.DTFIMPERIODO = P_DTFINAL       AND 
                                               M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                               M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                               IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                   WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND 
                                         N.CDINSCESTA      = P_CDINSCESTA                  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 
                                         I.CDIMPOSTO       = V_IPI                         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    = MD.CDMODDOCFISC               AND 
                                         NVL(MD.CDTPREGFISC, '50') IN ('50', '51') 
                                   GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, 
                                            N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, 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') AND 
                                    EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS)  AND 
                                    (INSTR(NVL(P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0) 
                              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, 
                                     LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, 
                                         N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, 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, MODDOCFISC MD, 
                                        (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                         FROM MAPAAPURAC M, IMPOSTONF IP 
                                         WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                               M.CDINSCESTA   = P_CDINSCESTA    AND 
                                               M.DTINIPERIODO = P_DTINICIAL     AND 
                                               M.DTFIMPERIODO = P_DTFINAL       AND 
                                               M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                               M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                               IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                   WHERE N.CDEMPRESA       = P_CDEMPRESA                   AND 
                                         N.CDINSCESTA      = P_CDINSCESTA                  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 
                                         I.CDIMPOSTO       = V_IPI                         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    = MD.CDMODDOCFISC               AND 
                                         NVL(MD.CDTPREGFISC, '50') IN ('50', '51') 
                                   GROUP BY I.NRLANCTONF,N.NRNOTAFISC, N.VRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, 
                                            N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, 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') AND 
                                    EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS) AND 
                                    (INSTR(NVL(P_SGESTADO, EC.SGESTADO),EC.SGESTADO) > 0)) 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; 
    
   V_CONTREG := 0; 
   IF SUBSTR(P_REGISTRO,3,1) = 'S' 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,' ')|| 
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')|| 
                                  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, N.CDSERIE, N.CDSERIEENTR, N.CDCLIENTE, 
                                       N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, 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, MODDOCFISC MD, 
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                      FROM MAPAAPURAC M, IMPOSTONF IP 
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                            M.CDINSCESTA   = P_CDINSCESTA    AND 
                                            M.DTINIPERIODO = P_DTINICIAL     AND 
                                            M.DTFIMPERIODO = P_DTFINAL       AND 
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                            IP.CDIMPOSTO   = P_CDIMPOSTO) IC, 
                                     (SELECT CDIMPOSTRIB 
                                      FROM IMPOSTO 
                                      WHERE CDIMPOSTO = P_CDIMPOSTO) ST 
                                WHERE M.CDEMPRESA       = P_CDEMPRESA                   AND 
                                      M.CDINSCESTA      = P_CDINSCESTA                  AND 
                                      M.DTINIPERIODO    = P_DTINICIAL                   AND 
                                      M.DTFIMPERIODO    = P_DTFINAL                     AND 
                                      M.CDIMPOSTO       = ST.CDIMPOSTRIB                AND 
                                      M.NRSEQUMAPAPU    = I.NRSEQUMAPAPU                AND 
                                      N.CDFILIAL        = I.CDFILIAL                    AND 
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND 
                                      I.CDFILIAL        = IC.CDFILIAL                   AND 
                                      I.NRLANCTONF      = IC.NRLANCTONF                 AND 
                                      I.CDIMPOSTO       = ST.CDIMPOSTRIB                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 
                                      NVL(N.CDMODDOCFISC, '01') = MD.CDMODDOCFISC       AND 
                                      MD.CDTPREGFISC  = '50' 
                                GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, 
                                         N.CDCLIENTE,N.DTEMISSAO, N.DTENTRSAID, I.VRALIQNF, 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')     AND 
                                 EC.CDPAIS          = NVL(P_CDPAIS,EC.CDPAIS)     AND 
                                 (INSTR(NVL(P_SGESTADO, EC.SGESTADO), EC.SGESTADO) > 0) 
                           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,' ')|| 
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')|| 
                                  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, N.CDSERIE, N.CDSERIEENTR, N.CDFORNECED, 
                                       N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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, MODDOCFISC MD, 
                                     (SELECT CDIMPOSTRIB 
                                      FROM IMPOSTO 
                                      WHERE CDIMPOSTO = P_CDIMPOSTO) ST, 
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                      FROM MAPAAPURAC M, IMPOSTONF IP 
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                            M.CDINSCESTA   = P_CDINSCESTA    AND 
                                            M.DTINIPERIODO = P_DTINICIAL     AND 
                                            M.DTFIMPERIODO = P_DTFINAL       AND 
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                            IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                WHERE M.CDEMPRESA       = P_CDEMPRESA                   AND 
                                      M.CDINSCESTA      = P_CDINSCESTA                  AND 
                                      M.DTINIPERIODO    = P_DTINICIAL                   AND 
                                      M.DTFIMPERIODO    = P_DTFINAL                     AND 
                                      M.CDIMPOSTO       = ST.CDIMPOSTRIB                AND 
                                      M.NRSEQUMAPAPU    = I.NRSEQUMAPAPU                AND 
                                      N.CDFILIAL        = I.CDFILIAL                    AND 
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND 
                                      I.CDFILIAL        = IC.CDFILIAL                   AND 
                                      I.NRLANCTONF      = IC.NRLANCTONF                 AND 
                                      I.CDIMPOSTO       = ST.CDIMPOSTRIB                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 
                                      NVL(N.CDMODDOCFISC, '01')  = MD.CDMODDOCFISC      AND 
                                      MD.CDTPREGFISC  = '50' 
                                GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, 
                                         N.CDFORNECED,N.DTENTRSAID, N.DTEMISSAO, I.VRALIQNF, 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') AND 
                                 EF.CDPAIS          = NVL(P_CDPAIS,EF.CDPAIS)  AND 
                                 (INSTR(NVL(P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0) 
                           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,' ')|| 
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')|| 
                                  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, N.CDSERIE, N.CDSERIEENTR, N.CDFILIEMIT, 
                                       N.DTENTRSAID, I.VRALIQNF, 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, MODDOCFISC MD, 
                                     (SELECT CDIMPOSTRIB 
                                      FROM IMPOSTO 
                                      WHERE CDIMPOSTO = P_CDIMPOSTO) ST, 
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                      FROM MAPAAPURAC M, IMPOSTONF IP 
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                            M.CDINSCESTA   = P_CDINSCESTA    AND 
                                            M.DTINIPERIODO = P_DTINICIAL     AND 
                                            M.DTFIMPERIODO = P_DTFINAL       AND 
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                            IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND 
                                      M.CDINSCESTA      = P_CDINSCESTA                                 AND 
                                      M.DTINIPERIODO    = P_DTINICIAL                                  AND 
                                      M.DTFIMPERIODO    = P_DTFINAL                                    AND 
                                      M.CDIMPOSTO       = ST.CDIMPOSTRIB                               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 
                                      I.CDFILIAL        = IC.CDFILIAL                                  AND 
                                      I.NRLANCTONF      = IC.NRLANCTONF                                AND 
                                      I.CDIMPOSTO       = ST.CDIMPOSTRIB                               AND 
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND 
                                      N.IDSTATUS IN ('A','E')                                          AND 
                                      NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '3'                       AND 
                                      NVL(N.CDMODDOCFISC, '01') = MD.CDMODDOCFISC                      AND 
                                      MD.CDTPREGFISC  = '50' 
                                GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDFILIEMIT, 
                                         N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF,N.CDMODDOCFISC, N.IDORIGDESTNF, N.CDINSCESTA) 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                  AND
                                 NVL(EF.CDPAIS,IE.CDPAIS) = NVL(P_CDPAIS,NVL(EF.CDPAIS,IE.CDPAIS)) AND
                                 (INSTR(NVL(P_SGESTADO, NVL(EF.SGESTADO,IE.SGESTADO)), NVL(EF.SGESTADO,IE.SGESTADO)) > 0)
                           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,' ')|| 
                                  LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')|| 
                                  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, N.CDSERIE, N.CDSERIEENTR, N.CDFILIRECE, 
                                       N.DTEMISSAO, I.VRALIQNF, 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, MODDOCFISC MD, 
                                     (SELECT CDIMPOSTRIB 
                                      FROM IMPOSTO 
                                      WHERE CDIMPOSTO = P_CDIMPOSTO) ST, 
                                     (SELECT DISTINCT IP.CDFILIAL, IP.NRLANCTONF 
                                      FROM MAPAAPURAC M, IMPOSTONF IP 
                                      WHERE M.CDEMPRESA    = P_CDEMPRESA     AND 
                                            M.CDINSCESTA   = P_CDINSCESTA    AND 
                                            M.DTINIPERIODO = P_DTINICIAL     AND 
                                            M.DTFIMPERIODO = P_DTFINAL       AND 
                                            M.CDIMPOSTO    = P_CDIMPOSTO     AND 
                                            M.NRSEQUMAPAPU = IP.NRSEQUMAPAPU AND 
                                            IP.CDIMPOSTO   = P_CDIMPOSTO) IC 
                                WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND 
                                      M.CDINSCESTA      = P_CDINSCESTA                                 AND 
                                      M.DTINIPERIODO    = P_DTINICIAL                                  AND 
                                      M.DTFIMPERIODO    = P_DTFINAL                                    AND 
                                      M.CDIMPOSTO       = ST.CDIMPOSTRIB                               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 
                                      I.CDFILIAL        = IC.CDFILIAL                                  AND 
                                      I.NRLANCTONF      = IC.NRLANCTONF                                AND 
                                      I.CDIMPOSTO       = ST.CDIMPOSTRIB                               AND 
                                      N.IDSTATUS IN ('A','E')                                          AND 
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND 
                                      NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'                       AND 
                                      NVL(N.CDMODDOCFISC, '01') = MD.CDMODDOCFISC                      AND 
                                      MD.CDTPREGFISC  = '50' 
                                GROUP BY I.CDFILIAL, I.NRLANCTONF, N.NRNOTAFISC, N.CDSERIE, N.CDSERIEENTR, N.CDINSCESTA,
                                         N.CDFILIRECE, N.DTEMISSAO, I.VRALIQNF, 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                 AND
                                 NVL(EF.CDPAIS,IE.CDPAIS) = NVL(P_CDPAIS,NVL(EF.CDPAIS,IE.CDPAIS)) AND
                                 (INSTR(NVL(P_SGESTADO, NVL(EF.SGESTADO,IE.SGESTADO)), NVL(EF.SGESTADO,IE.SGESTADO)) > 0)) 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; 
    
   V_CONTREG := 0; 
   V_SEQUENCIAL := 0; 
   IF SUBSTR(P_REGISTRO,4,1) = 'S' THEN 
      V_STTOTAL := REGISTRO54_A_B_C_SINTEGRA_MS(P_CDIMPOSTO, P_CDEMPRESA, P_CDINSCESTA, P_DTINICIAL, P_DTFINAL, P_CDPAIS, P_SGESTADO, P_NRSEQARQMAG, V_STTOTAL, V_IPI); 
      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; 
   END IF; 
    
   V_CONTREG := 0; 
   IF SUBSTR(P_REGISTRO,5,1) = 'S' THEN 
      FOR C_REGISTRO55 IN (SELECT SUBSTR('55' || 
                                         REPLACE(REPLACE(REPLACE(I.NRINSJURIEST,'.',''),'-',''),'/','') || 
                                         RPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(M.CDINSCESTA),1,5),'ISENT','ISENTO',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.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; 
   END IF; 
    
   V_CONTREG  := 0; 
   V_CONTADOR := 0;
   IF SUBSTR(P_REGISTRO,6,1) = 'S' THEN
      V_REG60MAIDR := REG60_M_A_D_I_R_SINTEGRA_MS(P_CDEMPRESA, P_CDINSCESTA, P_DTINICIAL, P_DTFINAL, P_NRSEQARQMAG, V_STTOTAL);
      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; 
    
   V_CONTREG  := 0; 
   V_CONTADOR := 0; 
   IF SUBSTR(P_REGISTRO,7,1) = 'S' THEN 
      FOR C_REGISTRO61 IN(SELECT SUBSTR('61'|| 
                                        RPAD(' ', 14, ' ')|| 
                                        RPAD(' ', 14, ' ')|| 
                                        TO_CHAR(NF.DTEMISSAO, 'yyyymmdd')|| 
                                        RPAD('02', 2, ' ')|| 
                                        RPAD(DECODE(TRIM(NVL(NF.CDSERIE,' ')),'-',' ',NVL(NF.CDSERIE,' ')),5,' ')|| 
                                        LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')|| 
                                        LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')|| 
                                        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 I.NRLANCTONF, N.CDSERIE, N.DTEMISSAO, I.VRALIQNF, N.CDMODDOCFISC, N.VRNOTAFISC, 
                                     SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.NRNOTAFISC, 
                                     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 N, IMPOSTONF I, ITEMPRODUTO IP, ITEMVENDA IV, MOVICLIE MC, MODDOCFISC MD 
                                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       = P_CDIMPOSTO                   AND 
                                      NVL(I.IDIMPOSTONF, '1') <> '3'                    AND 
                                      N.CDFILIAL        = I.CDFILIAL                    AND 
                                      N.NRLANCTONF      = I.NRLANCTONF                  AND 
                                      N.IDSTATUS IN ('A','E')                           AND 
                                      N.IDENTRSAIDOP    = 'S'                           AND 
                                      NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'        AND 
                                      N.CDFILIAL        = IP.CDFILIAL                   AND 
                                      N.NRLANCTONF      = IP.NRLANCTONF                 AND 
                                      IP.CDFILIAL       = IV.CDFILIAL(+)                AND 
                                      IP.CDCAIXA        = IV.CDCAIXA  (+)               AND 
                                      IP.NRSEQVENDA     = IV.NRSEQVENDA (+)             AND 
                                      IP.NRSEQUITVEND   = IV.NRSEQUITVEND(+)            AND 
                                      IV.CDFILIAL       = MC.CDFILIAL (+)               AND 
                                      IV.CDCAIXA        = MC.CDCAIXA   (+)              AND 
                                      IV.NRSEQVENDA     = MC.NRSEQVENDA (+)             AND 
                                      N.CDMODDOCFISC    = MD.CDMODDOCFISC               AND 
                                      MD.CDTPREGFISC    = '61' 
                                GROUP BY I.NRLANCTONF, N.CDSERIE, N.DTEMISSAO, I.VRALIQNF, N.CDMODDOCFISC, N.VRNOTAFISC, N.NRNOTAFISC 
                                UNION 
                                SELECT NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC, 
                                       SUM(I.VRCONTABILNF) AS VRCONTABILNF, NF.NRNOTAFISC, 
                                       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, MODDOCFISC MD 
                                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       = P_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.CDMODDOCFISC    = MD.CDMODDOCFISC              AND 
                                      MD.CDTPREGFISC      = '61' 
                                GROUP BY NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC, 
                                         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; 
      IF V_CONTREG > 0 THEN 
         V_STTOTAL := V_STTOTAL||'61'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0'); 
      END IF; 
   END IF; 
    
   V_CONTADOR := 0; 
   V_CONTREG := 0; 
   IF SUBSTR(P_REGISTRO,8,1) = 'S' 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, 
                                       LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0') 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.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, MODDOCFISC MD 
                                     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       = P_CDIMPOSTO)     AND 
                                           (NVL(I.IDIMPOSTONF, '1') <> '3')      AND 
                                           (N.CDFILIAL   = I.CDFILIAL)           AND 
                                           (N.NRLANCTONF = I.NRLANCTONF)         AND 
                                           (N.IDENTRSAIDOP = 'E' )               AND 
                                           (N.IDSTATUS IN ('A', 'E'))            AND 
                                           (N.CDMODDOCFISC    = MD.CDMODDOCFISC) AND 
                                           (MD.CDTPREGFISC    = '70') 
                                     GROUP BY N.CDFILIAL, N.NRLANCTONF, N.NRINSJURNF, N.CDFORNECED, N.DTENTRSAID, 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'))     AND 
                                      (EF.CDPAIS          = NVL( P_CDPAIS,EF.CDPAIS))     AND 
                                      (INSTR(NVL( P_SGESTADO, EF.SGESTADO), EF.SGESTADO) > 0) )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; 
    
   V_CONTREG  := 0; 
   V_CONTADOR := 0; 
   IF SUBSTR(P_REGISTRO,9,1) = 'S' 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, ' ') AS CDCLASFISC, NMPRODUTO, SGUNIDADE, DTINVEFISCAL
                              FROM
                                 (SELECT I.VRTOTINVFISC,I.QTESTINVFISC,I.DTINVEFISCAL AS 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.DTINVEFISCAL <= P_DTPOSICAO
                                                               AND V.CDEMPRESA    = P_CDEMPRESA
                                                               AND V.CDINSCESTA   = P_CDINSCESTA
                                                               AND V.CDPRODUTO    = PR.CDPRODUTO
                                                               AND V.QTESTINVFISC <> 0
                                                               AND I.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; 
      IF V_CONTREG > 0 THEN 
         V_STTOTAL := V_STTOTAL||'74'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
      END IF;
   END IF;

   V_CONTREG := 0;
   IF SUBSTR(P_REGISTRO,10,1) = 'S' THEN
      IF P_VERSAO = '3' THEN
         FOR C_REGISTRO75 IN (SELECT SUBSTR(CDTIPOREG ||
                                            TO_CHAR(DTINICIAL, 'YYYYMMDD')||
                                            TO_CHAR(DTFINAL  , 'YYYYMMDD')||
                                            RPAD(CDARVPROD, 14, ' ')      ||
                                            RPAD(CDCLASFISC, 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(CDCLASFISC, 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;
   END IF;
   IF V_CONTGERAL > 0 THEN
      V_TOTALGERAL := RTRIM(LPAD(RTRIM(TO_CHAR(V_CONTGERAL + 1)),8,'0'));
   END IF;

   V_REGISTRO88 := REGISTRO88_SINTEGRA_MS(P_CDEMPRESA, P_CDINSCESTA, P_DTINICIAL, P_DTFINAL, P_CDIMPOSTO, P_NRSEQARQMAG);

   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;
   END LOOP;
   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(P_CDINSCESTA),1,5),'ISENT','ISENTO',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
        ('20'||LPAD(RTRIM('00'), 8, '0'), C_REGISTRO90.DSARQMAG, P_NRSEQARQMAG);
   END LOOP;
   DELETE FROM TMP_REGISTRO_PRODUTO;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_MAGNETICO_MS','5.67.000'); END;
/
