CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R13(
   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_CONTREG13        NUMBER;
V_ORDEM            NUMBER;

BEGIN

 /*----------------------------------R13A-------------------------------------*/
   V_ORDEM := 0;

   FOR C_REG_R13A IN (SELECT   RPAD('R13A',4,' ')                                                                                          ||
                               TO_CHAR(P_DTINICIO,'MM')                                                                                    ||
                               TO_CHAR(P_DTINICIO,'YYYY') AS PARTE1,                                                                       
                               LPAD(' ',14,' ')                                                                                            ||
                               LPAD(' ',2,' ')                                                                                             ||
                               LPAD(' ',8,' ')                                                                                             ||
                               NVL(B.CDTPCREDPICOITE,'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',14,'0')                                                                                            ||
                               LPAD('0',10,'0')AS PARTE2
                         FROM
                               (SELECT (CASE WHEN A.CDTPCREDPICOITE IN ('101','102','103','104','107','109','199')              THEN '1' ELSE
                                       (CASE WHEN A.CDTPCREDPICOITE IN ('201','202','203','204','205','206','207','209','299')  THEN '2' ELSE
                                       (CASE WHEN A.CDTPCREDPICOITE IN ('301','302','303','304','305','306','307','309','399')  THEN '3' ELSE
                                       (CASE WHEN A.CDTPCREDPICOITE = '106'  THEN '4' ELSE (CASE WHEN A.CDTPCREDPICOITE = '105' THEN '5' ELSE
                                       (CASE WHEN A.CDTPCREDPICOITE = '108'  THEN '6' ELSE (CASE WHEN A.CDTPCREDPICOITE = '208' THEN '7' ELSE
                                       (CASE WHEN A.CDTPCREDPICOITE = '308'  THEN '8' ELSE '0' 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     = '50'
                                           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 = '50'
                                 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 'R13A', TO_CHAR(P_DTINICIO,'MM'), TO_CHAR(P_DTINICIO,'YYYY'),B.CDTPCREDPICOITE

                               ) LOOP

      V_CONTREG13 := NVL(V_CONTREG13,0) + 1;
      V_ORDEM := NVL(V_ORDEM,0) + 1;

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('13'||LPAD(RTRIM(V_CONTREG13), 8, '0'), C_REG_R13A.PARTE1||LTRIM(TO_CHAR(V_ORDEM,'000'))||C_REG_R13A.PARTE2 , P_NRSEQARQMAG);

   END LOOP;

   RETURN(V_CONTREG13);

END;
/