CREATE OR REPLACE FUNCTION ARQUIVO_DIC_70(
   P_LIVRO        IN LIVROMAGNETICO.CDLIVROMAG%TYPE,
   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_CDPAIS       IN ESTADO.CDPAIS%TYPE,
   P_SGESTADO     IN ESTADO.SGESTADO%TYPE,
   P_SEQUENCIAL   IN ARQMAGNETICO.NRSEQARQMAG%TYPE,
   P_CONTADOR     IN NUMBER,
   P_CONTTOTAL    IN VARCHAR2) RETURN VARCHAR2
AS
   V_IPI            VARCHAR2(02);
   V_STTOTAL        VARCHAR2(4000);
   V_CONTGERAL      NUMBER;
   V_CONTREG        NUMBER;
   V_CONTADOR       NUMBER;
BEGIN
   V_CONTADOR := P_CONTADOR;
   V_CONTREG := 0;
   FOR C_REGISTRO70 IN (SELECT '70'          ||
                               NRINSJURIEST  ||
                               CDINSCESTA    ||
                               DTENTRSAID    ||
                               SGESTADO      ||
                               CDMODDOCFISC  ||
                               CDSERIEENTR   ||
                               NRNOTAFISC    ||
                               CDFISCOPERNF  ||
                               VRNOTAFISC    ||
                               VRBASECALCNF  ||
                               VRIMPOSTONF   ||
                               VRISENTRIB    ||
                               VROUTRCRED    ||
                               IDPGTOFRETE   ||
                               IDSTATUS      ||
                               CDMUNIEXPO    ||
                               ANOAIDF       ||
                               NRAIDF        ||
                               CFOPESTEND    ||
                               DTENTRSAID    ||
                               OBSERVACAO AS DSARQMAG, DTENTRSAID
                        FROM(SELECT '70',
                                    RPAD(NVL(IE.NRINSJURIEST,'00000000000000'),14,' ') AS NRINSJURIEST,
                                    RPAD(NVL(REPLACE(REPLACE(REPLACE(NF.CDINSCESTA,'.',''),',',''),'-',''),'ISENTO'),14,' ') AS CDINSCESTA,
                                    TO_CHAR(NF.DTENTRSAID,'yyyymmdd') AS DTENTRSAID,
                                    RPAD(NVL(IE.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,
                                    RPAD(' ',2,' ') AS SUBSERIE,
                                    LPAD(NVL(CDMUNIEXPO,'0'), 5, '0') AS CDMUNIEXPO,
                                    '0000' AS ANOAIDF,
                                    '00000' AS NRAIDF,
                                    NVL(LPAD(RTRIM(LTRIM(NF.CDSUFXCFOPNF)),2,'0'),'00') AS CFOPESTEND,
                                    RPAD(' ',34,' ') AS OBSERVACAO
                             FROM INSCRESTAD IE, MUNICIPIO MU,
                                (SELECT N.CDFILIAL, N.NRLANCTONF, N.CDINSCESTA, N.CDEMPRESA, N.DTENTRSAID, N.CDSERIEENTR,
                                        N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE, I.CDFISCOPERNF, I.CDSUFXCFOPNF, 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 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
                                        (N.CDMODDOCFISC    = MD.CDMODDOCFISC) AND
                                        (MD.CDTPREGFISC    = '70')
                                  GROUP BY N.CDFILIAL, N.NRLANCTONF, N.CDINSCESTA, N.CDEMPRESA, N.DTENTRSAID, N.CDSERIEENTR,
                                        N.NRNOTAFISC, N.VRNOTAFISC, N.IDSTATUS, N.IDPGTOFRETE,I.CDFISCOPERNF, I.CDSUFXCFOPNF, N.CDMODDOCFISC) NF
                             WHERE IE.CDINSCESTA      = NF.CDINSCESTA
                               AND IE.CDEMPRESA       = NF.CDEMPRESA
                               AND IE.CDPAIS          = NVL(P_CDPAIS, IE.CDPAIS)
                               AND INSTR(NVL(P_SGESTADO, IE.SGESTADO), IE.SGESTADO) > 0
                               AND IE.CDPAIS          = MU.CDPAIS
                               AND IE.SGESTADO        = MU.SGESTADO
                               AND IE.CDMUNICIPIO     = MU.CDMUNICIPIO)NF
                        ORDER BY DTENTRSAID) LOOP

      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 10, '0'), C_REGISTRO70.DSARQMAG, P_SEQUENCIAL);

      V_CONTREG   := NVL(V_CONTREG,0) + 1;
      V_CONTGERAL := NVL(V_CONTGERAL,0) + 1;
   END LOOP;
   IF V_CONTREG > 0 THEN
      V_STTOTAL := P_CONTTOTAL||'70'||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_70','5.30.000'); END;
/

