create or replace
FUNCTION           ARQUIVO_70_DIEF_PI (
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTONFE%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_CDPAIS       IN ESTADO.CDPAIS%TYPE,
   P_SGESTADO     IN ESTADO.SGESTADO%TYPE, 
   P_SEQMAGNET    IN VARCHAR, 
   P_CONTADOR     IN NUMBER)
RETURN VARCHAR 
AS 
   V_CONTGERAL      NUMBER;  
   V_CONTREG        NUMBER; 
   V_CONTADOR       NUMBER; 
BEGIN 
   V_CONTADOR := P_CONTADOR; 
   V_CONTREG  := 0; 
      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(NF.NRINSJURNF,'00000000000000'),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(NVL(NF.IDPGTOFRETE, '1'), 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, 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 
                                           (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 
         V_CONTADOR  := NVL(V_CONTADOR,0)+1; 
         INSERT INTO ARQMAGNETICO 
           (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
         VALUES 
           (LPAD(V_CONTADOR, 8, '0'), C_REGISTRO70.DSARQMAG, P_SEQMAGNET); 
         V_CONTREG   := V_CONTREG + 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_70_DIEF_PI','5.11.026'); END;
/

 
