CREATE OR REPLACE FUNCTION ARQUIVO_90_DIEF_MA (
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN EMPRESA.CDINSCESTA%TYPE,
   P_CONTGERAL   IN VARCHAR2,
   P_CONTADOR    IN NUMBER,
   P_SEQMAGNET   IN VARCHAR) RETURN VARCHAR
AS
   V_CONTADOR    NUMBER;
   V_TOTAL       VARCHAR2(1000);
BEGIN
   V_CONTADOR := P_CONTADOR;
   FOR C_TOTAL IN (SELECT SUBSTR(DSREGARQMAG,1,2)||LPAD(COUNT(*),8,0) AS CONTADOR
                   FROM ARQMAGNETICO
                   WHERE NRSEQARQMAG = P_SEQMAGNET
                     AND SUBSTR(DSREGARQMAG,1,2) NOT IN('10','11')
                   GROUP BY SUBSTR(DSREGARQMAG,1,2)
                   ORDER BY SUBSTR(DSREGARQMAG,1,2) ) LOOP
      V_TOTAL := V_TOTAL||C_TOTAL.CONTADOR;
   END LOOP;
   FOR C_REGISTRO90 IN (SELECT RPAD('90'||
                               LPAD(NVL(REPLACE(REPLACE(REPLACE(REPLACE(NRINSJURIEST,'.',''),',',''),'/',''),'-',''),'00000000000000'),14,'0')||
                               RPAD(NVL(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(P_CDINSCESTA),1,5),'ISENT','ISENTO',P_CDINSCESTA),'.',''),',',''),'-',''),'ISENTO'),14,' ')||
                               V_TOTAL||'99'||LPAD(P_CONTGERAL,8,'0'),125,' ')||
                               '1'  AS DSARQMAG
                        FROM INSCRESTAD
                        WHERE CDEMPRESA  = P_CDEMPRESA AND
                              CDINSCESTA = P_CDINSCESTA) LOOP
      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD(V_CONTADOR, 8, '0'), C_REGISTRO90.DSARQMAG, P_SEQMAGNET);
   END LOOP;
   RETURN(TO_CHAR(V_CONTADOR));
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_90_DIEF_MA','5.11.026'); END;
/
