CREATE OR REPLACE PROCEDURE EXPORTA_PESSOA_SAGA
IS
   V_CODPSS       VARCHAR2(20);
   V_NOMPSS       VARCHAR2(125);
   V_NOMAPEPSS    VARCHAR2(125);
   V_CODSTE       VARCHAR2(20);
   V_CODNATPSS    NUMBER;
   V_CODTRN       NUMBER;
   BITBOLMND      NUMBER;
   V_DATCAD       DATE;
   V_BOLPDRCRP    NUMBER;
   V_CODMTV       NUMBER;
   V_CODPPSEXT    VARCHAR2(20);
   V_CODTIPENDPTL NUMBER;
   V_DATHORBLQ    DATE;
   V_CEP          VARCHAR2(20);
   V_NOMPAI       VARCHAR2(80);
   V_NOMUFE       VARCHAR2(80);
   V_NOMLOC       VARCHAR2(125);
   V_NOMBRO       VARCHAR2(80);
   V_NOMRUA       VARCHAR2(125);
   V_NUMCMP       VARCHAR2(20);
   V_NOMCMP       VARCHAR2(40);
   V_NOMEML       VARCHAR2(80);
   V_NOMWEBSIT    VARCHAR2(80);
   V_NUMCEL1      VARCHAR2(15);
   V_NUMCEL2      VARCHAR2(15);
   V_NUMFAX       VARCHAR2(15);
   V_NUMIDPSSPRM  VARCHAR2(20);
   V_NUMIDPSSSCG  VARCHAR2(20);
   V_NUMTEL1      VARCHAR2(15);
   V_NUMTEL2      VARCHAR2(15);
   V_FLAPRC       VARCHAR2(5);
   V_DSCERR       VARCHAR2(300);
   V_DATULTPRC    DATE;
   V_CODSITPSJ    NUMBER;
   V_CODUNICO     VARCHAR2(21);
   V_IDENT        VARCHAR2(2);
   
   CURSOR C_PESSOA IS
      SELECT F.NRINSJURFORN, F.NMRAZSOCFORN, F.NMFANTFORN, 10 CODNATPSS, EF.NRCEPFORN, P.NMPAIS, E.NMESTADO, M.NMMUNICIPIO, EF.NMBAIRFORN, 
             EF.DSENDEFORN, SUBSTR(EF.DSCOMPENDFOR, 1, 40) DSCOMPENDFOR, EF.DSEMAILFORN, SUBSTR(REGEXP_REPLACE(EF.NRCELULARFOR, '[[:space:]]*'), 1, 15) NRCELULARFOR, 
             SUBSTR(EF.NRFAXFORN, 1, 15) NRFAXFORN, F.NRINSESTFORN, SUBSTR(REGEXP_REPLACE(EF.NRTELEFORN, '[[:space:]]*'), 1, 15) NRTELEFORN, 
             SUBSTR(REGEXP_REPLACE(EF.NRTELEFORN1, '[[:space:]]*'), 1, 15) NRTELEFORN1, F.CDFORNECED, 'FO' IDENT
        FROM FORNECEDOR F, ENDEFORN EF, PAIS P, ESTADO E, MUNICIPIO M
       WHERE F.CDFORNECED = EF.CDFORNECED
         AND EF.CDPAIS = P.CDPAIS
         AND EF.CDPAIS = E.CDPAIS
         AND EF.SGESTADO = E.SGESTADO
         AND EF.CDPAIS   = M.CDPAIS
         AND EF.SGESTADO = M.SGESTADO
         AND EF.CDMUNICIPIO = M.CDMUNICIPIO
         AND P.CDPAIS = E.CDPAIS
         AND P.CDPAIS = M.CDPAIS
         AND E.CDPAIS = M.CDPAIS
         AND E.SGESTADO = M.SGESTADO
         AND EF.IDTPENDEFORN = 'P'
         AND ((F.DTULTATUFORN >= F.DTEXPFORNSAGA) OR (EF.DTULTATUENFO >= F.DTEXPFORNSAGA) OR (F.DTEXPFORNSAGA IS NULL))
       UNION ALL
      SELECT F.CDFILIAL, F.NMRAZSOCFILI, F.NMFILIAL, 9 CODNATPSS, EF.NRCEPFILI, P.NMPAIS, E.NMESTADO, M.NMMUNICIPIO, EF.NMBAIRFILI, 
             EF.DSENDEFILI, SUBSTR(EF.DSCOMPENDFIL, 1, 40) DSCOMPENDFIL, EF.DSEMAILFILI, '' NRCELULARFIL, 
             SUBSTR(EF.NRFAXFILI, 1, 15), F.CDINSCESTA, SUBSTR(REGEXP_REPLACE(EF.NRTELEFILI, '[[:space:]]*'), 1, 15) NRTELEFILI, 
             '' NRTELEFILI1, F.CDFILIAL, 'FI' IDENT
        FROM FILIAL F, ENDEFILI EF, PAIS P, ESTADO E, MUNICIPIO M, (SELECT DISTINCT CDFILIAL 
                                                                      FROM PARAMPRODSUP P
                                                                     UNION ALL
                                                                    SELECT DISTINCT F.CDFILIAL 
                                                                      FROM FILIAL F, PARAMPRODSUP P
                                                                     WHERE F.CDFILIAL = P.CDFILIOPELOG) PA
       WHERE F.CDFILIAL = EF.CDFILIAL
         AND EF.CDPAIS = P.CDPAIS
         AND EF.CDPAIS = E.CDPAIS
         AND EF.SGESTADO = E.SGESTADO
         AND EF.CDPAIS   = M.CDPAIS
         AND EF.SGESTADO = M.SGESTADO
         AND EF.CDMUNICIPIO = M.CDMUNICIPIO
         AND P.CDPAIS = E.CDPAIS
         AND P.CDPAIS = M.CDPAIS
         AND E.CDPAIS = M.CDPAIS
         AND E.SGESTADO = M.SGESTADO
         AND F.CDFILIAL = PA.CDFILIAL
         AND EF.IDTPENDEFILI = 'P'
         AND ((F.DTULTATUFILI >= F.DTEXPFILISAGA) OR (EF.DTULTATUEFIL >= F.DTEXPFILISAGA) OR (F.DTEXPFILISAGA IS NULL));
BEGIN 
   V_CODTRN       := 201;
   V_CODSITPSJ    := 1;
   V_BOLPDRCRP    := 1;
   V_CODMTV       := NULL;
   V_CODPPSEXT    := NULL;
   V_CODTIPENDPTL := 1;
   V_DATHORBLQ    := NULL;
   V_NUMCMP       := NULL;
   V_NOMWEBSIT    := NULL;
   V_NUMCEL2      := NULL;
   V_FLAPRC       := '0';
   V_DSCERR       := NULL;
   V_DATULTPRC    := NULL;
   V_CODUNICO     := NULL;
   V_IDENT        := NULL;
   
   SELECT MAX(CDFILIALSAGA)
     INTO V_CODSTE
     FROM PARAMFILIAL;
   
   OPEN C_PESSOA;
   LOOP
      FETCH C_PESSOA INTO V_CODPSS, V_NOMPSS, V_NOMAPEPSS, V_CODNATPSS, V_CEP, V_NOMPAI, V_NOMUFE, V_NOMLOC, V_NOMBRO,
                          V_NOMRUA, V_NOMCMP, V_NOMEML, V_NUMCEL1, 
                          V_NUMFAX, V_NUMIDPSSSCG, V_NUMTEL1, 
                          V_NUMTEL2, V_CODUNICO, V_IDENT;
      EXIT WHEN C_PESSOA%NOTFOUND;
      
      V_DATCAD      := SYSDATE;
      V_NUMIDPSSPRM := V_CODPSS;
      
      INSERT INTO INT_CADPSJ(CODPSS, NOMPSS, NOMAPEPSS, CODSTE, CODNATPSS, CODTRN, CODSITPSJ, DATCAD, BOLPDRCRP, CODMTV, CODPPSEXT, CODTIPENDPTL,
                             DATHORBLQ, CEP, NOMPAI, NOMUFE, NOMLOC, NOMBRO, NOMRUA, NUMCMP, NOMCMP, NOMEML, NOMWEBSIT, NUMCEL1, NUMCEL2, NUMFAX,
                             NUMIDPSSPRM, NUMIDPSSSCG, NUMTEL1, NUMTEL2, FLAPRC, DSCERR, DATULTPRC)
      VALUES(V_CODPSS, V_NOMPSS, V_NOMAPEPSS, V_CODSTE, V_CODNATPSS, V_CODTRN, V_CODSITPSJ, V_DATCAD, V_BOLPDRCRP, V_CODMTV, V_CODPPSEXT, V_CODTIPENDPTL,
             V_DATHORBLQ, V_CEP, V_NOMPAI, V_NOMUFE, V_NOMLOC, V_NOMBRO, V_NOMRUA, V_NUMCMP, V_NOMCMP, V_NOMEML, V_NOMWEBSIT, V_NUMCEL1, V_NUMCEL2, V_NUMFAX,
             V_NUMIDPSSPRM, V_NUMIDPSSSCG, V_NUMTEL1, V_NUMTEL2, V_FLAPRC, V_DSCERR, V_DATULTPRC);
      
      IF (V_IDENT = 'FO') THEN
         UPDATE FORNECEDOR 
            SET DTEXPFORNSAGA = SYSDATE
          WHERE CDFORNECED = V_CODUNICO;
      ELSE
         UPDATE FILIAL
            SET DTEXPFILISAGA = SYSDATE
          WHERE CDFILIAL = V_CODUNICO;
      END IF;
   END LOOP;
   
   COMMIT;
   CLOSE C_PESSOA;
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      CLOSE C_PESSOA;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('EXPORTA_PESSOA_SAGA','5.45.XXX'); END;
/