CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R09(
   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_CONTREG09        NUMBER;

BEGIN

 /*----------------------------------TIPO R09A-------------------------------------*/

   FOR C_REG_R09A IN (SELECT ( RPAD('R09A',4,' ')                                                                        ||
                               LPAD(NVL(B.CDARVPROD,'000000000'),9,'0')                                                  ||
                               TO_CHAR(P_DTINICIO,'MM')                                                                  ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRCONTITIMP,0)),'000000000000.00'),'.')),'14','0')   ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRBASECALCIT,0)),'000000000000.00'),'.')),'14','0')  ||
                               LPAD('0',12,'0')                                                                          ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRALIQITEM,0)),'000.0000'),'.')),'7','0')            ||
                               LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NVL(B.VRIMPOSTOIT,0)),'000000000000.00'),'.')),'14','0')   ||
                               LPAD('0',10,'0')) AS REG_R09A
                        FROM
                           (SELECT SUM(IT.VRCONTITIMP) AS VRCONTITIMP,SUM(IT.VRBASECALCIT) AS VRBASECALCIT , SUM(VRIMPOSTOIT) AS VRIMPOSTOIT,
                                   CDARVPROD, VRALIQITEM
                              FROM ITEMPROIMPOS IT, ITEMPRODUTO IP,PRODUTO P,IMPOSTO IM,
                                 (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.CDFILIAL     = IP.CDFILIAL
                               AND IT.NRLANCTONF   = IP.NRLANCTONF
                               AND IT.NRSEQITPROD  = IP.NRSEQITPROD
                               AND IP.CDPRODUTO    = P.CDPRODUTO
                               AND IT.CDSITUTRIBITNFE = '02'
                           GROUP BY CDARVPROD, VRALIQITEM)B
                        GROUP BY 'R09A', B.CDARVPROD,TO_CHAR(P_DTINICIO,'MM')
                     ) LOOP

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

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('11'||LPAD(RTRIM(V_CONTREG09), 8, '0'), C_REG_R09A.REG_R09A, P_NRSEQARQMAG);

   END LOOP;

   RETURN(V_CONTREG09);

END;
/