create or replace FUNCTION ARQUIVO_DMA_ICMS_ST (
   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_REGICMSSUBST IN (SELECT('16'
                                 ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                 ||LPAD(P_CDINSCESTA, 9, '0')
                                 ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(RS.VRENTRSUBTRIB), '9999999999.00'), '.', '')), 12, '0')
                                 ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(RS.VRSAIDSUBTRIB), '9999999999.00'), '.', '')), 12, '0')
                                 ||LPAD('0', 12, '0')
                                 ||LPAD('0', 12, '0')
                                 ||LPAD('0', 12, '0')
                                 ||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(RS.VRIMPORECOL), '9999999999.00'), '.', '')), 12, '0')) AS ARQ_REGICMSSUBST
                            FROM(SELECT SUM(R.VRNORMDEBI)  AS VRENTRSUBTRIB,
                                        SUM(R.VRNORMCRED)  AS VRSAIDSUBTRIB,
                                        SUM(M.VRTOTAIMPOS) AS VRIMPORECOL
                                   FROM MAPAAPURAC M, MAPARECUP R, IMPOSTO I
                                  WHERE M.CDEMPRESA     = P_CDEMPRESA
                                    AND M.CDIMPOSTO     = '80'
                                    AND M.CDINSCESTA    = P_CDINSCESTA
                                    AND M.DTINIPERIODO >= P_DTINICIAL
                                    AND M.DTFIMPERIODO <= P_DTFINAL
                                    AND R.NRSEQUMAPAPU  = M.NRSEQUMAPAPU) RS ) LOOP
      V_CONTADOR := NVL(V_CONTADOR, 0)+1; 
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG) 
      VALUES 
         (V_CONTADOR, C_REGICMSSUBST.ARQ_REGICMSSUBST, P_NRSEQARQMAG); 
   END LOOP; 
    
   FOR C_REGINFOCOMP IN (SELECT('17'
                                ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                ||LPAD(P_CDINSCESTA, 9, '0')
                                ||LPAD('0', 6, '0')
                                ||LPAD('0', 7, '0')
                                ||LPAD('0', 11, '0')/*||LPAD(LTRIM(REPLACE(TO_CHAR(SUM(IC.VRCOFINS), '999999999.00'), '.', '')), 11, '0')*/
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')) AS ARQ_REGINFOCOMP
                           FROM(SELECT SUM(NF.VRIMPOSTONF) AS VRCOFINS
                                  FROM MAPAAPURAC M, IMPOSTONF NF, IMPOSTO I
                                 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 I.CDIMPOSTO     = NF.CDIMPOSTO
                                   AND I.CDIMPOSTONFE  = M.CDIMPOSTO) IC ) LOOP
      V_CONTADOR := NVL(V_CONTADOR, 0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (V_CONTADOR, C_REGINFOCOMP.ARQ_REGINFOCOMP, P_NRSEQARQMAG);
   END LOOP;

   FOR C_REGALIQUOTA IN (SELECT('18'
                                ||TO_CHAR(P_DTINICIAL, 'YYYYMM')
                                ||LPAD(P_CDINSCESTA, 9, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')
                                ||LPAD('0', 12, '0')) AS ARQ_REGALIQUOTA
                           FROM DUAL) LOOP
      V_CONTADOR := NVL(V_CONTADOR, 0)+1;
      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         (V_CONTADOR, C_REGALIQUOTA.ARQ_REGALIQUOTA, P_NRSEQARQMAG);
   END LOOP;

   RETURN(V_CONTADOR);
END;
/