CREATE OR REPLACE FUNCTION ARQUIVO_DIC_10(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_BENEFICIO   IN NOTAFISCAL.CDEMPRESA%TYPE,
   P_MODELO      IN NOTAFISCAL.IDSTATUS%TYPE,
   P_PORTARIA    IN NOTAFISCAL.CDEMPRESA%TYPE,
   P_SEQUENCIAL  IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER
AS
   V_CONTADOR   NUMBER;
BEGIN
   V_CONTADOR := 0;
   FOR C_DIC_10 IN (SELECT '10'||
                           LPAD(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(IE.CDINSCESTA),1,10),'ISENT','ISENTO',IE.CDINSCESTA),'.',''),',',''),'-',''),'/',''),10,'0')||
                           RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(IE.NRINSJURIEST,'.',''),',',''),'-',''),'/',''),1,14),14,' ')||
                           TO_CHAR(TO_DATE(P_DTINICIAL),'yyyy')||
                           TO_CHAR(TO_DATE(P_DTINICIAL),'mm')||
                           RPAD(NVL(MU.CDMUNIEXPO, '0'), 5, '0')||
                           LPAD(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(IE.CDINSCESTA),1,10),'ISENT','ISENTO',IE.CDINSCESTA),'.',''),',',''),'-',''),'/',''),10,'0')||
                           P_BENEFICIO||
                           P_MODELO||
                           '3'||
                           RPAD(' ', 134, ' ')  AS DSARQMAG
                        FROM EMPRESA EP, INSCRESTAD IE, MUNICIPIO MU
                        WHERE EP.CDEMPRESA   = P_CDEMPRESA
                          AND IE.CDEMPRESA   = EP.CDEMPRESA
                          AND IE.CDINSCESTA  = P_CDINSCESTA
                          AND IE.CDPAIS      = MU.CDPAIS(+)
                          AND IE.SGESTADO    = MU.SGESTADO(+)
                          AND IE.CDMUNICIPIO = MU.CDMUNICIPIO(+) ) LOOP


      V_CONTADOR := NVL(V_CONTADOR,0)+1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        (LPAD('1', 10, '0'), C_DIC_10.DSARQMAG, P_SEQUENCIAL);

   END LOOP;

   RETURN(V_CONTADOR);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DIC_10','5.05.011'); END;
/

