create or replace PROCEDURE USR_EXPORTA_FORNECEDOR
AS
   V_CDFORNCEDOR   VARCHAR2(20);
   V_BRASIL        VARCHAR2(4);

   V_NOVOCODIGO    INTEGER;
BEGIN
   SELECT MAX(SUBSTR(CDPAIS, 3,4))
     INTO V_BRASIL
     FROM PAIS
    WHERE NMPAIS = 'BRASIL';

   FOR C_FORN IN (SELECT FR.CDFORNECED, FR.NMRAZSOCFORN, FR.IDTPIJURFORN,
                         SUBSTR(replace(replace(replace(FR.NRINSJURFORN, '-', ''),'.',''),'/',''),1,14) AS NRINSJURFORN,
                         FR.NRINSESTFORN, FR.NRINSMUNFORN, MU.NMMUNICIPIO, LPAD(FR.CDFORMPGTO,3,'0') AS CDFORMPGTO, EF.DSENDEFORN,
                         EF.NMBAIRFORN, EF.NRCEPFORN, EF.CDPAIS, EF.DSEMAILFORN, PA.CDPAISEXPFIS,
                         EF.CDMUNICIPIO, EF.SGESTADO, EF.NRTELEFORN, EF.NRCELULARFOR AS NRTELEXFORN, EF.NRFAXFORN, EF.NMCONTFORN,
                         FR.NMFANTFORN, EF.DSCOMPENDFOR, MU.CDMUNICIBGE, CC.CDBANCFORN, CC.CDAGENFORN,
                         FR.VRPERPIFORN, FR.DTINIVRPDESC, FR.CDTIPCOB, CC.CDCCORFORN
                    FROM FORNECEDOR FR, ENDEFORN EF, MUNICIPIO MU, CCORFORN CC, PAIS PA
                   WHERE FR.USR_DTEXPFORN IS NULL
                     AND (FR.CDFORNECED    = EF.CDFORNECED)
                     AND MU.CDPAIS         = EF.CDPAIS
                     AND (EF.SGESTADO      = MU.SGESTADO)
                     AND MU.CDMUNICIPIO    = EF.CDMUNICIPIO
                     AND CC.CDFORNECED(+)  = FR.CDFORNECED
                     AND (PA.CDPAIS        = EF.CDPAIS)
                     AND (FR.IDTPIJURFORN IN ('F','J'))) LOOP

      /* Retorna o sequencial para a tabela */
      BEGIN
         SELECT W3_IDFORN.NEXTVAL
           INTO V_NOVOCODIGO
           FROM DUAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NOVOCODIGO := 1;
      END;

      INSERT INTO USR_ZW3
         (W3_IDFORN, W3_EMPRESA, W3_FILIAL, W3_NOME, W3_TIPO,
          W3_END, W3_BAIRRO, W3_CEP,
          W3_PAIS, W3_MUN, W3_EST,
          W3_TEL, W3_TELEX, W3_FAX, W3_CONTATO,
          W3_CGC, W3_INSCR, W3_INSCRM, W3_COND, W3_EMAIL, W3_TPEDI,
          W3_NREDUZ, W3_COD_MUN, W3_HRGRV, W3_DTGRV,
          W3_ENDCOMP, W3_IBGE, W3_BANCO, W3_AGENCIA,
          W3_DESCCOM, W3_PERVIGE, W3_TIPPAG, W3_COMPLEM, W3_NUMCON)
       VALUES
         (V_NOVOCODIGO, '01', '01', SUBSTR(C_FORN.NMRAZSOCFORN, 1, 40), C_FORN.IDTPIJURFORN,
          SUBSTR(C_FORN.DSENDEFORN, 1, 40), SUBSTR(C_FORN.NMBAIRFORN, 1, 20), SUBSTR(replace(replace(C_FORN.NRCEPFORN, '-', ''),'.',''),1,8),
          NVL(SUBSTR(C_FORN.CDPAISEXPFIS, 3, 3), NVL(SUBSTR(C_FORN.CDPAIS, 2, 4), V_BRASIL)), SUBSTR(C_FORN.NMMUNICIPIO, 1, 15), C_FORN.SGESTADO,
          SUBSTR(C_FORN.NRTELEFORN, 1, 15), SUBSTR(C_FORN.NRTELEXFORN,1,10), SUBSTR(C_FORN.NRFAXFORN, 1, 15), SUBSTR(C_FORN.NMCONTFORN, 1, 15), SUBSTR(C_FORN.NRINSJURFORN, 1,14),
          SUBSTR(C_FORN.NRINSESTFORN,1,18), SUBSTR(C_FORN.NRINSMUNFORN,1,18), C_FORN.CDFORMPGTO, SUBSTR(C_FORN.DSEMAILFORN, 1, 30), '1',
          SUBSTR(C_FORN.NMFANTFORN, 1, 20), SUBSTR(C_FORN.CDMUNICIBGE, 1, 5), TO_CHAR(SYSDATE, 'HH24MI'), TRUNC(SYSDATE),
          SUBSTR(C_FORN.DSCOMPENDFOR, 1, 21), SUBSTR(C_FORN.CDMUNICIBGE, 1, 11), SUBSTR(C_FORN.CDBANCFORN, 1, 3), SUBSTR(C_FORN.CDAGENFORN, 1, 5),
          C_FORN.VRPERPIFORN, C_FORN.DTINIVRPDESC, SUBSTR(C_FORN.CDTIPCOB ,1 ,3), SUBSTR(C_FORN.DSCOMPENDFOR ,1 ,50),
          SUBSTR(C_FORN.CDCCORFORN,1,10));

       UPDATE FORNECEDOR
          SET USR_DTEXPFORN  = SYSDATE
        WHERE SUBSTR(NRINSJURFORN, 1,14) = SUBSTR(C_FORN.NRINSJURFORN, 1,14);
   END LOOP;

   FOR C_FORNUPD IN (SELECT FR.CDFORNECED, FR.NMRAZSOCFORN, FR.IDTPIJURFORN,
                            SUBSTR(replace(replace(replace(FR.NRINSJURFORN, '-', ''),'.',''),'/',''),1,14) AS NRINSJURFORN,
                            FR.NRINSESTFORN, FR.NRINSMUNFORN, MU.NMMUNICIPIO, LPAD(FR.CDFORMPGTO,3,'0') AS CDFORMPGTO, EF.DSENDEFORN,
                            EF.NMBAIRFORN, EF.NRCEPFORN, EF.CDPAIS, EF.DSEMAILFORN, PA.CDPAISEXPFIS,
                            EF.CDMUNICIPIO, EF.SGESTADO, EF.NRTELEFORN, EF.NRCELULARFOR AS NRTELEXFORN, EF.NRFAXFORN, EF.NMCONTFORN,
                            FR.NMFANTFORN, EF.DSCOMPENDFOR, MU.CDMUNICIBGE, CC.CDBANCFORN, CC.CDAGENFORN,
                            FR.VRPERPIFORN, FR.DTINIVRPDESC, FR.CDTIPCOB, CC.CDCCORFORN
                       FROM FORNECEDOR FR, ENDEFORN EF, MUNICIPIO MU, CCORFORN CC, PAIS PA
                      WHERE (FR.CDFORNECED       = EF.CDFORNECED)
                        AND (MU.CDPAIS           = EF.CDPAIS)
                        AND (EF.SGESTADO         = MU.SGESTADO)
                        AND (MU.CDMUNICIPIO      = EF.CDMUNICIPIO)
                        AND ((FR.DTULTATUFORN   >= FR.USR_DTEXPFORN) OR (EF.DTULTATUENFO >= FR.USR_DTEXPFORN))
                        AND  CC.CDFORNECED(+)    = FR.CDFORNECED
                        AND (PA.CDPAIS           = EF.CDPAIS)
                        AND (FR.IDTPIJURFORN IN ('F','J'))) LOOP

      /* Retorna o sequencial para a tabela */
      BEGIN
         SELECT W3_IDFORN.NEXTVAL
           INTO V_NOVOCODIGO
           FROM DUAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_NOVOCODIGO := 1;
      END;

      INSERT INTO USR_ZW3
         (W3_IDFORN, W3_NOME, W3_TIPO,
          W3_END, W3_BAIRRO, W3_CEP,
          W3_PAIS, W3_MUN, W3_EST,
          W3_TEL, W3_TELEX, W3_FAX, W3_CONTATO,
          W3_CGC, W3_INSCR, W3_INSCRM, W3_COND, W3_EMAIL, W3_TPEDI,
          W3_NREDUZ, W3_COD_MUN, W3_HRGRV, W3_DTGRV,
          W3_ENDCOMP, W3_IBGE, W3_BANCO, W3_AGENCIA,
          W3_DESCCOM, W3_PERVIGE, W3_TIPPAG, W3_COMPLEM, W3_NUMCON)
       VALUES
         (V_NOVOCODIGO,SUBSTR(C_FORNUPD.NMRAZSOCFORN, 1, 40), C_FORNUPD.IDTPIJURFORN,
          SUBSTR(C_FORNUPD.DSENDEFORN, 1, 40), SUBSTR(C_FORNUPD.NMBAIRFORN, 1, 20), SUBSTR(replace(replace(C_FORNUPD.NRCEPFORN, '-', ''),'.',''),1,8),
          NVL(SUBSTR(C_FORNUPD.CDPAISEXPFIS, 3, 3), NVL(SUBSTR(C_FORNUPD.CDPAIS, 2, 4), V_BRASIL)), SUBSTR(C_FORNUPD.NMMUNICIPIO, 1, 15), C_FORNUPD.SGESTADO,
          SUBSTR(C_FORNUPD.NRTELEFORN, 1, 15), SUBSTR(C_FORNUPD.NRTELEXFORN,1,10), SUBSTR(C_FORNUPD.NRFAXFORN, 1, 15), SUBSTR(C_FORNUPD.NMCONTFORN, 1, 15), SUBSTR(C_FORNUPD.NRINSJURFORN, 1,14),
          SUBSTR(C_FORNUPD.NRINSESTFORN,1,18), SUBSTR(C_FORNUPD.NRINSMUNFORN,1,18), C_FORNUPD.CDFORMPGTO, SUBSTR(C_FORNUPD.DSEMAILFORN, 1, 30), '3',
          SUBSTR(C_FORNUPD.NMFANTFORN, 1, 20), SUBSTR(C_FORNUPD.CDMUNICIBGE, 1, 5), TO_CHAR(SYSDATE, 'HH24MI'), TRUNC(SYSDATE),
          SUBSTR(C_FORNUPD.DSCOMPENDFOR, 1, 21), SUBSTR(C_FORNUPD.CDMUNICIBGE, 1, 11), SUBSTR(C_FORNUPD.CDBANCFORN, 1, 3), SUBSTR(C_FORNUPD.CDAGENFORN, 1, 5),
          C_FORNUPD.VRPERPIFORN, C_FORNUPD.DTINIVRPDESC, SUBSTR(C_FORNUPD.CDTIPCOB ,1 ,3), SUBSTR(C_FORNUPD.DSCOMPENDFOR ,1 ,50),
          SUBSTR(C_FORNUPD.CDCCORFORN,1,10));

       UPDATE FORNECEDOR
          SET USR_DTEXPFORN = SYSDATE
        WHERE SUBSTR(NRINSJURFORN, 1,14) = SUBSTR(C_FORNUPD.NRINSJURFORN, 1,14);
   END LOOP;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_EXPORTA_FORNECEDOR','5.09.000'); END;
/
