create or replace FUNCTION ARQUIVO_DMA_APURACAO (
   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_NRSEQARQMAG  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER
   
AS
   V_CONTADOR      NUMBER;
BEGIN
   V_CONTADOR := 0;

   FOR C_REG14APUIMP IN (SELECT ('14'
                                ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                ||LPAD(P_CDINSCESTA, 9, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRSAIDTRIBUTDS), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VROUTRDEBI), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRESTORCREDI), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRENTRTRIBUTDS), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VROUTRCREDI), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRESTORDEBI), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(NF.VRCREDORPERANTE), '9999999999.00'), '.', '')), 12, '0')) AS ARQ_REG14APUIMP
                           FROM(SELECT SUM(R.VRNORMDEBI)  AS VRSAIDTRIBUTDS,
                                       SUM(R.VROUTRDEBI)  AS VROUTRDEBI,
                                       SUM(R.VRESTOCRED)  AS VRESTORCREDI,
                                       SUM(R.VRNORMCRED)  AS VRENTRTRIBUTDS,
                                       SUM(R.VROUTRCRED)  AS VROUTRCREDI,
                                       SUM(R.VRESTODEBI)  AS VRESTORDEBI,
                                       SUM(R.VRSLDCRANTE) AS VRCREDORPERANTE
                                  FROM MAPARECUP R, MAPAAPURAC M
                                 WHERE M.CDEMPRESA     = P_CDEMPRESA
                                   AND M.CDIMPOSTO     = P_CDIMPOSTO
                                   AND M.CDINSCESTA    = P_CDINSCESTA
                                   AND M.DTINIPERIODO >= P_DTINICIAL
                                   AND M.DTFIMPERIODO <= P_DTFINAL
                                   AND R.NRSEQUMAPAPU  = M.NRSEQUMAPAPU) NF ) LOOP
   V_CONTADOR := NVL(V_CONTADOR, 0)+1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (V_CONTADOR, C_REG14APUIMP.ARQ_REG14APUIMP, P_NRSEQARQMAG);
   END LOOP;
   FOR C_REG15APUIMP IN (SELECT('15'
                                ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                ||LPAD(P_CDINSCESTA, 9, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRDEDUCAO), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRIMPORECOL+VRDIFALIQ), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRTOTDEBITO+VRDIFALIQ), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRTOTCRED+VRCREDORPERANTE), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRSALDEVEDOR+AP.VRDEDUCAO+VRDIFALIQ), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRSALCREDOR), '9999999999.00'), '.', '')), 12, '0')
                                ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(AP.VRDIFALIQ), '9999999999.00'), '.', '')), 12, '0')) AS ARQ_REG15APUIMP
                           FROM(SELECT SUM(VRDEDUCAO)   AS VRDEDUCAO,
                                       SUM(VRTOTAIMPOS) AS VRIMPORECOL,
                                       SUM(VRTOTADEBI)  AS VRTOTDEBITO,
                                       SUM(VRTOTACRED)  AS VRTOTCRED,
                                       SUM(VRSALDODEBI) AS VRSALDEVEDOR,
                                       SUM(VRSALDOCRED) AS VRSALCREDOR,
                                       SUM(VRDIFALIQU)  AS VRDIFALIQ,
                                       SUM(VRSLDCRANTE) AS VRCREDORPERANTE
                                  FROM MAPAAPURAC M, MAPARECUP MR
                                 WHERE M.CDEMPRESA      = P_CDEMPRESA
                                   AND M.CDIMPOSTO      = P_CDIMPOSTO
                                   AND M.CDINSCESTA     = P_CDINSCESTA
                                   AND M.DTINIPERIODO  >= P_DTINICIAL
                                   AND M.DTFIMPERIODO  <= P_DTFINAL
                                   AND M.NRSEQUMAPAPU   = MR.NRSEQUMAPAPU) AP) LOOP
   V_CONTADOR := NVL(V_CONTADOR, 0)+1;
   INSERT INTO ARQMAGNETICO
      (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
   VALUES
      (V_CONTADOR, C_REG15APUIMP.ARQ_REG15APUIMP, P_NRSEQARQMAG);
   END LOOP;
   RETURN(V_CONTADOR);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DMA_APURACAO','6.49.010'); END;
/

