CREATE OR REPLACE FUNCTION ARQUIVO_DACON_R02(
   P_CDEMPRESA              IN EMPRESA.CDEMPRESA%TYPE,
   P_NRSEQARQMAG            IN ARQMAGNETICO.NRSEQARQMAG%TYPE) RETURN NUMBER

AS
   V_CONTREG02        NUMBER;

BEGIN

 /*----------------------------------R02-------------------------------------*/

   FOR C_REG_R02 IN (SELECT (RPAD('R02',4,' ')                                                                                  ||
                            LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.NRINSJUREMPR,' '),'.'),'-'),'/'),' '),14,0)              ||
                            RPAD(NVL(E.NMRAZSOCEMPR,' '),150,' ')                                                               ||
                            LPAD(NVL(CDNATUJURI,'0'),4,'0')                                                                     ||
                            '0000000'                                                                                           ||
                            RPAD(SUBSTR(E.DSENDEEMPR,1,INSTR(E.DSENDEEMPR,' ')),20,' ') /*TIPO DE LOGRADOURO*/                  ||
                            RPAD(NVL((SUBSTR(E.DSENDEEMPR,INSTR(E.DSENDEEMPR,' ')+1,150)),' '),150,' ')                         ||
                            RPAD(' ',6,' ')                                                                                     ||
                            RPAD(NVL(E.DSCOMPENDEMP,' '),50,' ')                                                                ||
                            RPAD(NVL(B.NMBAIRRO,' '),50,' ')                                                                    ||
                            RPAD(NVL(E.SGESTADO,' '),2,' ')                                                                     ||
                            RPAD(NVL(M.NMMUNICIPIO,' '),50,' ')                                                                 ||
                            LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.NRCEPEMPR,' '),'.'),'('),'-'),')'),8,' ')                ||
                            SUBSTR(LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.NRTELEFOEMPR,' '),'-'),'.'),'('),')'),12,' '),1,4)||
                            SUBSTR(LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.NRTELEFOEMPR,' '),'-'),'.'),'('),')'),12,' '),5,8)||
                            --LPAD(SUBSTR(LPAD(REPLACE(REPLACE(REPLACE(REPLACE(NVL(E.NRTELEFOEMPR,' '),'-'),'.'),'('),')'),12,' '),5,8),9,' ')||
                            RPAD(' ',4,' ')                                                                                     ||
                            RPAD(' ',8,' ')                                                                                     ||
                            --RPAD(' ',9,' ')                                                                                     ||
                            RPAD(' ',6,' ')                                                                                     ||
                            RPAD(' ',2,' ')                                                                                     ||
                            RPAD(' ',8,' ')                                                                                     ||
                            RPAD(NVL(E.DSEMAILEMPRESA,' '),115,' ')                                                             ||
                            '0000000000'                                                                               ) AS REG_R02
                        FROM EMPRESA E, MUNICIPIO M, BAIRRO B
                       WHERE E.CDEMPRESA   = P_CDEMPRESA
                         AND M.CDMUNICIPIO = E.CDMUNICIPIO
                         AND M.CDPAIS      = E.CDPAIS
                         AND M.SGESTADO    = E.SGESTADO
                         AND B.CDPAIS      = E.CDPAIS
                         AND B.SGESTADO    = E.SGESTADO
                         AND B.CDMUNICIPIO = E.CDMUNICIPIO
                         AND B.CDBAIRRO    = E.CDBAIRRO)  LOOP

      V_CONTREG02 := NVL(V_CONTREG02,0) + 1;

      INSERT INTO ARQMAGNETICO
         (NRORDREGARQ, DSREGARQMAG, NRSEQARQMAG)
      VALUES
         ('02'||LPAD(RTRIM(V_CONTREG02), 8, '0'), C_REG_R02.REG_R02, P_NRSEQARQMAG);

   END LOOP;

   RETURN(V_CONTREG02);

END;
/

-- VERSAO --
BEGIN ATUVEROBJ('ARQUIVO_DACON_R02','5.60.000'); END;
/