CREATE OR REPLACE FUNCTION ARQUIVO_DIC_71(
   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_REGISTRO71 IN (SELECT '71'          ||
                               NRINSJUREMPR  ||
                               NRINSCESTA    ||
                               DTEMISSAO     ||
                               SGESTADO      ||
                               CDMODDOCFISC  ||
                               SERIE         ||
                               --SUBSERIE      ||
                               NRNOTAFISC    ||
                               SGESTADOREM   ||
                               NRINSJURNF    ||
                               CDINSCESTA    ||
                               DTEMISSAOACOB ||
                               MODDOCACOB    ||
                               SERIEACOB     ||
                               NRNOTAFISCACOB||
                               VRNOTAFISC    ||
                               BRANCOS  AS DSARQMAG, ORDEM
                        FROM(SELECT RPAD(REPLACE(REPLACE(REPLACE(NRINSJUREMPR,'.',''),'/',''),'-',''),14,' ') AS NRINSJUREMPR,
                                    RPAD(REPLACE(REPLACE(REPLACE(REPLACE(NRINSCESTA,'.',''),'*',''),'-',''),'/',''),14,' ') AS NRINSCESTA,
                                    TO_CHAR(N.DTEMISSAO,'yyyymmdd') AS DTEMISSAO,
                                    RPAD(NVL(E.SGESTADO,' '),2,' ') AS SGESTADO,
                                    NVL(N.CDMODDOCFISC,'  ') AS CDMODDOCFISC,
                                    RPAD(REPLACE(NVL(N.CDSERIEENTR,' '),'-',' '),3,' ') AS SERIE,
                                    --RPAD(' ',2,' ') AS SUBSERIE,
                                    LPAD(SUBSTR(N.NRNOTAFISC,LENGTH(N.NRNOTAFISC)-5,6),6,'0') AS NRNOTAFISC,
                                    RPAD(NVL(EF.SGESTADO,' '),2,' ') AS SGESTADOREM,
                                    RPAD(REPLACE(REPLACE(REPLACE(N.NRINSJURNF,'.',''),'/',''),'-',''),14,' ') AS NRINSJURNF,
                                    RPAD(REPLACE(REPLACE(REPLACE(REPLACE(FO.NRINSESTFORN,'.',''),'*',''),'-',''),'/',''),14,' ') AS CDINSCESTA,
                                    TO_CHAR(N.DTEMISSAO,'yyyymmdd') AS DTEMISSAOACOB,
                                    NVL(N.CDMODDOCFISC,'  ') AS MODDOCACOB,
                                    RPAD(REPLACE(NVL(N.CDSERIEENTR,' '),'-',' '),3,' ') AS SERIEACOB,
                                    LPAD(SUBSTR(N.NRNOTAFISC,LENGTH(N.NRNOTAFISC)-5,6),6,'0') AS NRNOTAFISCACOB,
                                    LTRIM(REPLACE(TO_CHAR(N.VRNOTAFISC,'00000000000.00'),'.','')) AS VRNOTAFISC,
                                    RPAD(' ',70,' ') AS BRANCOS, N.DTEMISSAO AS ORDEM
                             FROM (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                   FROM MAPAAPURAC M, IMPOSTONF I, 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') I,
                                  NOTAFISCAL N, MODDOCFISC MD, EMPRESA E,
                                  FORNECEDOR FO, ENDEFORN EF
                             WHERE 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'
                               AND EF.IDTPENDEFORN = 'P'
                               AND EF.SGESTADO = 'SE'
                               AND N.CDFORNECED = FO.CDFORNECED
                               AND FO.CDFORNECED = EF.CDFORNECED
                               AND E.CDEMPRESA = N.CDEMPRESA)NF
                        ORDER BY NF.ORDEM) LOOP

      V_CONTADOR  := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 10, '0'), C_REGISTRO71.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||'71'||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_71','5.30.xxx'); END;
/
