CREATE OR REPLACE FUNCTION ARQUIVO_10_GIM_PB(
   P_CDEMPRESA   IN EMPRESA.CDEMPRESA%TYPE,
   P_CDINSCESTA  IN NOTAFISCAL.CDINSCESTA%TYPE,
   P_CDINSCESTAST IN NOTAFISCAL.CDINSCESTAST%TYPE,
   P_DTINICIAL   IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_DTFINAL     IN NOTAFISCAL.DTENTRSAID%TYPE,
   P_VERSAO      IN NOTAFISCAL.IDSTATUS%TYPE,
   P_FINALIDADE  IN NOTAFISCAL.IDSTATUS%TYPE,
   P_SEQMAGNET   IN VARCHAR) RETURN NUMBER
AS
   V_CONTADOR   NUMBER;
BEGIN
   V_CONTADOR := 0;
   FOR C_REGISTRO10 IN (SELECT SUBSTR(
                           '10'||
                           RPAD(SUBSTR(REPLACE(REPLACE(REPLACE(REPLACE(NVL(ST.NRINSJURIEST,IE.NRINSJURIEST),'.',''),',',''),'-',''),'/',''),1,14),14,' ')||
                           RPAD(REPLACE(REPLACE(REPLACE(REPLACE(DECODE(SUBSTR(UPPER(IE.CDINSCESTA),1,5),'ISENT','ISENTO',NVL(ST.CDINSCESTAST,IE.CDINSCESTA)),'.',''),',',''),'-',''),'/',''),14,' ')||
                           RPAD(EP.NMRAZSOCEMPR,35,' ')||
                           RPAD(NVL(ST.NMMUNICIPIO,MU.NMMUNICIPIO),30,' ')||
                           RPAD(NVL(ST.SGESTADO,IE.SGESTADO),2,' ')||
                           LPAD(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(NVL(NVL(ST.NRTELEIEST,IE.NRTELEIEST),'0'),' ',''),')',''),'(',''),'-',''),'.',''),10,'0')||
                           TO_CHAR(TO_DATE(P_DTINICIAL),'yyyymmdd')||
                           TO_CHAR(TO_DATE(P_DTFINAL),'yyyymmdd')||
                           P_VERSAO ||'3'||P_FINALIDADE,1,126) AS DSARQMAG
                        FROM EMPRESA EP, INSCRESTAD IE, MUNICIPIO MU,
                            (SELECT I.CDEMPRESA, I.CDINSCESTA, I.NRINSJURIEST, I.CDINSCESTAST,
                                          M.NMMUNICIPIO, I.SGESTADO, I.NRTELEIEST
                                     FROM INSCRESTADST I, MUNICIPIO M
                                    WHERE I.CDEMPRESA    = P_CDEMPRESA
                                      AND I.CDINSCESTA   = P_CDINSCESTA
                                      AND I.CDINSCESTAST = P_CDINSCESTAST
                                      AND I.CDPAIS       = M.CDPAIS(+)
                                      AND I.SGESTADO     = M.SGESTADO(+)
                                      AND I.CDMUNICIPIO  = M.CDMUNICIPIO(+)) ST
                        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(+) AND
                               IE.CDEMPRESA  = ST.CDEMPRESA(+)   AND
                               IE.CDINSCESTA = ST.CDINSCESTA(+)) LOOP

      V_CONTADOR := V_CONTADOR + 1;
      INSERT INTO ARQMAGNETICO
        (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
        ('10'||LPAD(V_CONTADOR, 8, '0'), C_REGISTRO10.DSARQMAG, P_SEQMAGNET);
   END LOOP;
   RETURN(V_CONTADOR);
END;
/