CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R19(
   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_CONTREG19        NUMBER;

BEGIN

 /*----------------------------------TIPO R19A-------------------------------------*/

   FOR C_REG_R19A IN (SELECT (RPAD('R19A',4,'0')                                                                                       ||
                               LPAD(B.CDARVPROD,9,'0')                                                                                 ||
                               TO_CHAR(P_DTINICIO,'MM')                                                                                ||
                               LTRIM(LPAD(REPLACE(REPLACE(TO_CHAR(NVL(B.VRCONTITIMP,0),'000000000000.00'),',',''),'.',''),'14','0'))   ||
                               LTRIM(LPAD(REPLACE(REPLACE(TO_CHAR(NVL(B.VRBASECALCIT,0),'000000000000.00'),',',''),'.',''),'14','0'))  ||
                               LPAD('0',12,'0')                                                                                        ||
                               LTRIM(LPAD(REPLACE(REPLACE(TO_CHAR(NVL(B.VRALIQITEM,0),'000.0000'),',',''),'.',''),'7','0'))            ||
                               LTRIM(LPAD(REPLACE(REPLACE(TO_CHAR(NVL(B.VRIMPOSTOIT,0),'000000000000.00'),',',''),'.',''),'14','0'))   ||
                               LPAD('0',10,'0')) AS REG_R19A
                        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     =  '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.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
                     ) LOOP

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

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('20'||LPAD(RTRIM(V_CONTREG19), 8, '0'), C_REG_R19A.REG_R19A, P_NRSEQARQMAG);

   END LOOP;

   RETURN(V_CONTREG19);

END;
/