CREATE OR REPLACE FUNCTION ARQUIVO_DIC_50(
   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_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_CDPAIS       IN ESTADO.CDPAIS%TYPE,
   P_SGESTADO     IN ESTADO.SGESTADO%TYPE,
   P_SEQUENCIAL   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CONTADOR     IN NUMBER) RETURN VARCHAR
AS
   V_CONTADOR       NUMBER;
   V_CONTREG        NUMBER;
   V_CONTGERAL      NUMBER;
   V_STTOTAL        VARCHAR2(4000);
   V_MUNICIPIO      VARCHAR2(20);
BEGIN

   BEGIN
      SELECT M.CDMUNIEXPO
      INTO V_MUNICIPIO
      FROM INSCRESTAD I, MUNICIPIO M
      WHERE I.CDEMPRESA = P_CDEMPRESA
        AND I.CDINSCESTA = P_CDINSCESTA
        AND I.CDPAIS = M.CDPAIS
        AND I.SGESTADO = M.SGESTADO
        AND I.CDMUNICIPIO = M.CDMUNICIPIO;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
        V_MUNICIPIO := NULL;
   END;

   V_CONTADOR := P_CONTADOR;
   V_CONTREG := 0;
   FOR C_REGISTRO50 IN (SELECT DATA, '50'        ||
                                     NRINSJUR    ||
                                     NRINSEST    ||
                                     DATA        ||
                                     SGESTADO    ||
                                     CDMODDOCFISC||
                                     SERIE       ||
                                     NRNOTAFISC  ||
                                     CDFISCOPERNF||
                                     IDENTRSAIDOP||
                                     VRCONTABILNF||
                                     VRBASECALCNF||
                                     VRIMPOSTONF ||
                                     VRISENTRIB  ||
                                     VROUTCRED   ||
                                     VRALIQNF    ||
                                     SITUNF      ||
                                     V_MUNICIPIO ||
                                     ANOAIDF     ||
                                     NRAIDF      ||
                                     CFOPESTEND  ||
                                     DTENTRSAID  ||
                                     BRANCOS 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,
                                    DECODE(IDSTATUS, 'C', 'S', 'N') AS SITUNF,
                                    '2008' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    TO_CHAR(DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(' ', 34, ' ') AS BRANCOS

                             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, I.CDSUFXCFOPNF,
                                         N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, N.IDSTATUS,
                                         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, IMPOSTO 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
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        I.CDIMPOSTO       = IP.CDIMPOSTO                                 AND
                                        IP.CDIMPOSTONFE   = 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, I.CDSUFXCFOPNF,
                                           N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, N.IDSTATUS) 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,
                                    DECODE(IDSTATUS, 'C', 'S', 'N') AS SITUNF,
                                    '2008' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    TO_CHAR(DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(' ', 34, ' ') AS BRANCOS
                             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, I.CDSUFXCFOPNF, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                         SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.IDSTATUS,
                                         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, IMPOSTO 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
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        I.CDIMPOSTO       = IP.CDIMPOSTO                                 AND
                                        IP.CDIMPOSTONFE   = 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, I.CDSUFXCFOPNF,
                                           N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, N.IDSTATUS) 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,
                                    DECODE(IDSTATUS, 'C', 'S', 'N') AS SITUNF,
                                    '2008' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    TO_CHAR(DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(' ', 34, ' ') AS BRANCOS
                             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, I.CDSUFXCFOPNF, N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP,
                                         SUM(I.VRCONTABILNF) AS VRCONTABILNF, N.IDSTATUS,
                                         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, IMPOSTO 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
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        I.CDIMPOSTO       = IP.CDIMPOSTO                                 AND
                                        IP.CDIMPOSTONFE   = 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, I.CDSUFXCFOPNF,
                                           N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, N.IDSTATUS) 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,
                                    DECODE(IDSTATUS, 'C', 'S', 'N') AS SITUNF,
                                    '2008' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    TO_CHAR(DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(' ', 34, ' ') AS BRANCOS
                             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, I.CDSUFXCFOPNF, N.CDMODDOCFISC, N.IDORIGDESTNF,
                                         N.IDENTRSAIDOP, N.IDSTATUS,
                                         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, IMPOSTO 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
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        I.CDIMPOSTO       = IP.CDIMPOSTO                                 AND
                                        IP.CDIMPOSTONFE   = 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, I.CDSUFXCFOPNF,
                                           N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDENTRSAIDOP, N.IDSTATUS) 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,
                                    DECODE(IDSTATUS, 'C', 'S', 'N') AS SITUNF,
                                    '2008' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    TO_CHAR(DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(' ', 34, ' ') AS BRANCOS
                             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, I.CDSUFXCFOPNF, N.CDMODDOCFISC, N.IDORIGDESTNF,
                                         N.IDENTRSAIDOP, N.IDSTATUS, N.CDINSCESTA,
                                         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, IMPOSTO 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
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        I.CDIMPOSTO       = IP.CDIMPOSTO                                 AND
                                        IP.CDIMPOSTONFE   = 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.CDINSCESTA,
                                           N.CDFILIEMIT, N.DTENTRSAID, I.VRALIQNF, I.CDFISCOPERNF, I.CDSUFXCFOPNF, N.CDMODDOCFISC,
                                           N.IDORIGDESTNF, N.IDENTRSAIDOP, N.IDSTATUS) 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,
                                    DECODE(IDSTATUS, 'C', 'S', 'N') AS SITUNF,
                                    '2008' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    TO_CHAR(DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(' ', 34, ' ') AS BRANCOS
                             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, I.CDSUFXCFOPNF, N.CDMODDOCFISC, N.IDORIGDESTNF,
                                         N.IDENTRSAIDOP, N.IDSTATUS, N.DTENTRSAID, N.CDINSCESTA,
                                         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, IMPOSTO 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
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        I.CDIMPOSTO       = IP.CDIMPOSTO                                 AND
                                        IP.CDIMPOSTONFE   = 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.CDINSCESTA,
                                           N.IDENTRSAIDOP, N.CDFILIRECE, N.DTEMISSAO, I.VRALIQNF, I.CDFISCOPERNF, I.CDSUFXCFOPNF,
                                           N.CDMODDOCFISC, N.IDORIGDESTNF, N.IDSTATUS, N.DTENTRSAID) 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
        (LPAD(V_CONTADOR, 10, '0'), C_REGISTRO50.DSARQMAG, P_SEQUENCIAL);

      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 := '50'||LPAD(RTRIM(TO_CHAR(V_CONTREG)),8,'0');
      RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0))||V_STTOTAL);
   END IF;

   IF V_CONTREG = 0 THEN
      RETURN('0');
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DIC_50','5.30.xxx'); END;
/

