CREATE OR REPLACE FUNCTION ARQUIVO_50_DIEF_PI(
   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.CDIMPOSTONFE%TYPE, 
   P_CDPAIS       IN ESTADO.CDPAIS%TYPE, 
   P_SGESTADO     IN ESTADO.SGESTADO%TYPE, 
   P_SEQMAGNET    IN VARCHAR, 
   P_CONTADOR     IN NUMBER) 
RETURN VARCHAR 
AS 
   V_CONTADOR       NUMBER;   
   V_CONTREG        NUMBER;  
   V_CONTGERAL      NUMBER;  
BEGIN 
   V_CONTGERAL := 0; 
   V_CONTREG := 0; 
   V_CONTADOR := P_CONTADOR;
   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, IMPOSTO IM
                                  WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND 
                                        M.CDINSCESTA      = P_CDINSCESTA                                 AND 
                                        M.DTINIPERIODO    = P_DTINICIAL                                  AND 
                                        M.DTFIMPERIODO    = P_DTFINAL                                    AND 
                                        M.CDIMPOSTO       = P_CDIMPOSTO                                  AND 
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        IM.CDIMPOSTONFE   = P_CDIMPOSTO                                  AND
                                        IM.CDIMPOSTO      = I.CDIMPOSTO                                  AND
                                        NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND
                                        N.CDFILIAL        = I.CDFILIAL                                   AND 
                                        N.NRLANCTONF      = I.NRLANCTONF                                 AND 
                                        N.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, IMPOSTO IM
                                  WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND
                                        M.CDINSCESTA      = P_CDINSCESTA                                 AND
                                        M.DTINIPERIODO    = P_DTINICIAL                                  AND
                                        M.DTFIMPERIODO    = P_DTFINAL                                    AND
                                        M.CDIMPOSTO       = P_CDIMPOSTO                                  AND
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        IM.CDIMPOSTONFE   = P_CDIMPOSTO                                  AND
                                        IM.CDIMPOSTO      = I.CDIMPOSTO                                  AND
                                        NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND
                                        N.CDFILIAL        = I.CDFILIAL                                   AND 
                                        N.NRLANCTONF      = I.NRLANCTONF                                 AND 
                                        N.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, IMPOSTO IM 
                                  WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND
                                        M.CDINSCESTA      = P_CDINSCESTA                                 AND
                                        M.DTINIPERIODO    = P_DTINICIAL                                  AND
                                        M.DTFIMPERIODO    = P_DTFINAL                                    AND
                                        M.CDIMPOSTO       = P_CDIMPOSTO                                  AND
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        IM.CDIMPOSTONFE   = P_CDIMPOSTO                                  AND
                                        IM.CDIMPOSTO      = I.CDIMPOSTO                                  AND
                                        NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND
                                        N.CDFILIAL        = I.CDFILIAL                                   AND
                                        N.NRLANCTONF      = I.NRLANCTONF                                 AND
                                        N.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, IMPOSTO IM
                                  WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND
                                        M.CDINSCESTA      = P_CDINSCESTA                                 AND
                                        M.DTINIPERIODO    = P_DTINICIAL                                  AND
                                        M.DTFIMPERIODO    = P_DTFINAL                                    AND
                                        M.CDIMPOSTO       = P_CDIMPOSTO                                  AND
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        IM.CDIMPOSTONFE   = P_CDIMPOSTO                                  AND
                                        IM.CDIMPOSTO      = I.CDIMPOSTO                                  AND
                                        NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND 
                                        N.CDFILIAL        = I.CDFILIAL                                   AND 
                                        N.NRLANCTONF      = I.NRLANCTONF                                 AND
                                        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.CDINSCESTA,
                                         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 
                                  FROM MAPAAPURAC M, NOTAFISCAL N, IMPOSTONF I, MODDOCFISC MD, IMPOSTO IM
                                  WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND
                                        M.CDINSCESTA      = P_CDINSCESTA                                 AND
                                        M.DTINIPERIODO    = P_DTINICIAL                                  AND
                                        M.DTFIMPERIODO    = P_DTFINAL                                    AND
                                        M.CDIMPOSTO       = P_CDIMPOSTO                                  AND
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND
                                        IM.CDIMPOSTONFE   = P_CDIMPOSTO                                  AND
                                        IM.CDIMPOSTO      = I.CDIMPOSTO                                  AND
                                        NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND
                                        N.CDFILIAL        = I.CDFILIAL                                   AND
                                        N.NRLANCTONF      = I.NRLANCTONF                                 AND
                                        N.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
                                   IE.CDINSCESTA      = NF.CDINSCESTA                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.CDINSCESTA,
                                         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, IMPOSTO IM 
                                  WHERE M.CDEMPRESA       = P_CDEMPRESA                                  AND
                                        M.CDINSCESTA      = P_CDINSCESTA                                 AND
                                        M.DTINIPERIODO    = P_DTINICIAL                                  AND
                                        M.DTFIMPERIODO    = P_DTFINAL                                    AND
                                        M.CDIMPOSTO       = P_CDIMPOSTO                                  AND
                                        I.NRSEQUMAPAPU    = M.NRSEQUMAPAPU                               AND 
                                        IM.CDIMPOSTONFE   = P_CDIMPOSTO                                  AND
                                        IM.CDIMPOSTO      = I.CDIMPOSTO                                  AND
                                        NVL(I.IDIMPOSTONF, '1') <> '3'                                   AND 
                                        N.CDFILIAL        = I.CDFILIAL                                   AND 
                                        N.NRLANCTONF      = I.NRLANCTONF                                 AND 
                                        N.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.CDFILIRECE, N.CDINSCESTA, 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
                                   IE.CDINSCESTA      = NF.CDINSCESTA                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
      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 8, '0'), C_REGISTRO50.DSARQMAG, P_SEQMAGNET);
      V_CONTREG   := NVL(V_CONTREG,0) + 1;
      V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
   END LOOP;
   IF V_CONTREG > 0 THEN
     RETURN(TO_CHAR(LPAD(V_CONTGERAL,10,0))||TO_CHAR(LPAD(V_CONTADOR,10,0)));
   END IF;
   IF  V_CONTREG = 0 THEN
   RETURN('0');
   END IF;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_50_DIEF_PI','5.11.026'); END;
/


