CREATE OR REPLACE FUNCTION ARQUIVO_DIC_61(
   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_DTPOSICAO    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_CONTADOR     NUMBER;
   V_CONTREG      NUMBER;
   V_CONTGERAL    NUMBER;
   V_STTOTAL      VARCHAR2(4000);
BEGIN
   V_CONTADOR := P_CONTADOR;
   V_CONTREG := 0;
   FOR C_REGISTRO61 IN(SELECT '61'||
                              RPAD(' ', 14, ' ')||
                              RPAD(' ', 14, ' ')||
                              TO_CHAR(NF.DTEMISSAO, 'yyyymmdd')||
                              RPAD(NF.CDMODDOCFISC, 2, ' ')||
                              RPAD(DECODE(TRIM(NVL(NF.CDSERIE,' ')),'-',' ',NVL(NF.CDSERIE,' ')),3,' ')||
                              RPAD(' ', 2, ' ')||
                              LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')||
                              LPAD(SUBSTR(NF.NRNOTAFISC,LENGTH(NF.NRNOTAFISC)-5,6),6,'0')||
                              LTRIM(REPLACE(TO_CHAR(NF.VRNOTAFISC, '00000000000.00'),'.',''))||
                              LTRIM(REPLACE(TO_CHAR(ABS(NF.VRBASECALCNF),'00000000000.00'),'.',''))||
                              LTRIM(REPLACE(TO_CHAR(ABS(NF.VRIMPOSTONF),'0000000000.00'),'.',''))||
                              LTRIM(REPLACE(TO_CHAR(ABS(NF.VRISENTRIB),'00000000000.00'),'.',''))||
                              LTRIM(REPLACE(TO_CHAR(ABS(NF.VROUTRCRED),'00000000000.00'),'.',''))||
                              LTRIM(REPLACE(TO_CHAR(ABS(NF.VRALIQNF),'00.00'),'.',''))||
                              RPAD(' ', 1, ' ')||
                              CDMUNIEXPO||
                              RPAD(' ', 35, ' ')||
                              IDSTATUS||
                              ANOEMIS||
                              '00000'||
                              RPAD(' ', 08, ' ') AS DSARQMAG, NF.DTEMISSAO
                       FROM(SELECT NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC, NF.VRNOTAFISC,
                                   SUM(I.VRCONTABILNF) AS VRCONTABILNF, NF.NRNOTAFISC,
                                   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,DECODE(SUBSTR(I.CDSITUTRIBNF,2,2),'20',I.VRCONTABILNF-I.VRBASECALCNF,0))) AS VRISENTRIB,
                                   ORIGEM_NOTA(NF.CDFILIAL, NF.NRLANCTONF, 'O') AS CDMUNIEXPO,
                                   DECODE(IDSTATUS, 'C', 'S', 'N') AS IDSTATUS,
                                   TO_CHAR(DTEMISSAO, 'yyyy') AS ANOEMIS
                            FROM MAPAAPURAC M, NOTAFISCAL NF, 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
                                  NF.IDENTRSAIDOP     = 'S'                         AND
                                  NF.IDSTATUS IN ('A','E')                          AND
                                  NVL(SUBSTR(I.CDFISCOPERNF,1,1),' ') <> '7'        AND
                                  NF.CDFILIAL       = I.CDFILIAL                   AND
                                  NF.NRLANCTONF     = I.NRLANCTONF                 AND
                                  NF.CDMODDOCFISC   = MD.CDMODDOCFISC              AND
                                  MD.CDTPREGFISC    = '61'
                            GROUP BY NF.NRLANCTONF, NF.CDSERIE, NF.DTEMISSAO, I.VRALIQNF, NF.CDMODDOCFISC,
                                     NF.VRNOTAFISC, NF.NRNOTAFISC, DECODE(IDSTATUS, 'C', 'S', 'N'),
                                     ORIGEM_NOTA(NF.CDFILIAL, NF.NRLANCTONF, 'O'), TO_CHAR(DTEMISSAO, 'yyyy') ) NF
                       ORDER BY NF.DTEMISSAO) LOOP

      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 10, '0'), C_REGISTRO61.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 := P_CONTTOTAL||'61'||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_61','5.30.xxx'); END;
/

