CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R24(
   P_CDEMPRESA              IN EMPRESA.CDEMPRESA%TYPE,
   P_DTINICIO               IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFIM                  IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_NRSEQARQMAG            IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER

AS
V_CONTREG24        NUMBER;

BEGIN

 /*----------------------------------R24-------------------------------------*/

   FOR C_REG_R24 IN (SELECT (RPAD('R24',4,' ')                                                                         ||
                             NVL(CDTPCREDPICOITE,'00')                                                                 ||
                             TO_CHAR(P_DTINICIO,'MM')                                                                  ||
                             '01'                                                                                      ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRIMPOSTOIT,0)),'000000000000.00'),'.')),'14','0')   ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRIMPOSTOIT,0)),'000000000000.00'),'.')),'14','0')   ||
                             LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRIMPOSTOIT,0)),'000000000000.00'),'.')),'14','0')   ||
                             LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRIMPOSTOIT,0)),'000000000000.00'),'.')),'14','0')   ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',14,'0')                                                                          ||
                             LPAD('0',302,'0')                                                                         ||
                             LPAD('0',10,'0')
                   ) AS REG_R24
                       FROM
                             (SELECT (CASE WHEN A.CDTPCREDPICOITE IN ('101','102','103','104','107','109','199')               THEN '01' ELSE
                                     (CASE WHEN A.CDTPCREDPICOITE IN ('201','202','203','204','205','206','207','209','299')   THEN '02' ELSE
                                     (CASE WHEN A.CDTPCREDPICOITE IN ('301','302','303','304','305','306','307','309','399')   THEN '03' ELSE
                                     (CASE WHEN A.CDTPCREDPICOITE = '106'  THEN '04' ELSE (CASE WHEN A.CDTPCREDPICOITE = '105' THEN '05' ELSE
                                     (CASE WHEN A.CDTPCREDPICOITE = '108'  THEN '06' ELSE (CASE WHEN A.CDTPCREDPICOITE = '208' THEN '07' ELSE
                                     (CASE WHEN A.CDTPCREDPICOITE = '308'  THEN '08' ELSE '00' END)END) END) END) END) END) END) END) AS CDTPCREDPICOITE, VRIMPOSTOIT
                              FROM
                                (SELECT CDTPCREDPICOITE, VRIMPOSTOIT
                                   FROM ITEMPROIMPOS IT,IMPOSTO IM,NOTAFISCAL N, FILIAL F,
                                     (SELECT DISTINCT I.CDFILIAL, I.NRLANCTONF
                                        FROM IMPOSTONF I, MAPAAPURAC M
                                       WHERE M.CDIMPOSTO     = '60'
                                         AND M.CDEMPRESA     =  P_CDEMPRESA
                                         AND M.DTINIPERIODO  >= P_DTINICIO
                                         AND M.DTFIMPERIODO  <= P_DTFIM
                                         AND M.NRSEQUMAPAPU  = I.NRSEQUMAPAPU) NOTA
                             WHERE IT.CDFILIAL     = NOTA.CDFILIAL
                               AND IT.NRLANCTONF   = NOTA.NRLANCTONF
                               AND IT.CDIMPOSTO    = IM.CDIMPOSTO
                               AND IM.CDIMPOSTONFE = '60'
                               AND IT.CDSITUTRIBITNFE BETWEEN '50' AND '67'
                               AND N.CDFILIAL      = IT.CDFILIAL
                               AND N.NRLANCTONF    = IT.NRLANCTONF
                               AND N.CDFILIAL      = NOTA.CDFILIAL
                               AND N.NRLANCTONF    = NOTA.NRLANCTONF
                               AND DECODE(IDENTRSAIDOP,'S',N.CDFILIEMIT,N.CDFILIRECE) = F.CDFILIAL
                               AND F.IDCUMUPISCOFIL   = 'S')A)B
                             GROUP BY 'R24',CDTPCREDPICOITE,TO_CHAR(P_DTINICIO,'MM')
                     )LOOP

      V_CONTREG24 := NVL(V_CONTREG24,0) + 1;


      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('23'||LPAD(RTRIM(V_CONTREG24), 8, '0'), C_REG_R24.REG_R24, P_NRSEQARQMAG);

   END LOOP;

   RETURN(V_CONTREG24);

END;
/