create or replace PROCEDURE USR_EXPORTA_CLIENTE
AS
   V_CDCLIENTE      VARCHAR2(30);
   V_CGC            VARCHAR2(40);
   V_DSENDECLIECO   VARCHAR2(60);
   V_NMBAIRCLIECO   VARCHAR2(60);
   V_NRCEPCLIECO    VARCHAR2(60);
   V_NMMUNICIPIOCO  VARCHAR2(15);
   V_SGESTADOCO     VARCHAR2(4);
   V_DSENDECLIEEN   VARCHAR2(60);
   V_NMBAIRCLIEEN   VARCHAR2(60);
   V_NMMUNICIPIOEN  VARCHAR2(15);
   V_SGESTADOEN     VARCHAR2(4);
   V_NRCEPCLIEEN    VARCHAR2(20);
   V_CDCLIENTEC     VARCHAR2(30);
   V_BRASIL         VARCHAR2(2);
   V_DSENDECLIEPR   VARCHAR2(60);
   V_NMBAIRCLIEPR   VARCHAR2(60);
   V_NRCEPCLIEPR    VARCHAR2(60);
   V_CDMUNICIPIOPR  VARCHAR2(8);
   V_SGESTADOPR     VARCHAR2(4);
   V_COMPLEMENTO    VARCHAR2(60);
   V_CDMUNICIBGEEN  VARCHAR2(10);
   V_CDMUNICIBGECO  VARCHAR(10);
   V_CONTATOCOBRA   VARCHAR(15);

   V_NOVOCODIGO     INTEGER;
BEGIN

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

   FOR C_CL IN (SELECT CL.CDCLIENTE, CL.NMRAZSOCCLIE, CL.IDTPIJURCLIE,
                       SUBSTR(replace(replace(replace(CL.NRINSJURCLIE, '-', ''),'.',''),'/',''),1,14) AS NRINSJURCLIE,
                       CL.NRINSESTCLIE, MU.NMMUNICIPIO, LPAD(CL.CDFORMPGTO,3,'0') AS CDFORMPGTO, CL.IDCLIEATIV,
                       ENP.DSENDECLIE, ENP.NMBAIRCLIE, ENP.NRCEPCLIE, PA.CDPAISEXPFIS,
                       ENP.CDPAIS, ENP.DSEMAILCLIE, ENP.CDMUNICIPIO, ENP.SGESTADO,
                       ENP.NRTELECLIE, ENP.NRTELEXCLIE, ENP.NRFAXCLIE, ENP.NMCONTCLIE,
                       ENP.DSCOMPENDCLI, CL.NMCOMPRZSCLI, MU.CDMUNICIBGE,
                       CL.NMFANTCLIE, GR.NMGRUPCLIE
                  FROM CLIENTE CL, ENDECLIE ENP, MUNICIPIO MU, PAIS PA, GRUPCLIE GR
                 WHERE USR_DTEXPCLI IS NULL
                   AND (CL.CDCLIENTE      = ENP.CDCLIENTE)
                   AND (ENP.IDTPENDECLIE  = 'P')
                   AND (MU.CDPAIS         = ENP.CDPAIS)
                   AND (ENP.SGESTADO      = MU.SGESTADO)
                   AND (MU.CDMUNICIPIO    = ENP.CDMUNICIPIO)
                   AND (PA.CDPAIS         = ENP.CDPAIS)
                   AND (GR.CDGRUPCLIE(+)  = CL.CDGRUPCLIE)
                   AND (CL.IDTPIJURCLIE IN ('F','J'))) LOOP

      BEGIN
         SELECT ENC.DSENDECLIE, ENC.NMBAIRCLIE, ENC.NRCEPCLIE,
                SUBSTR(MU.NMMUNICIPIO,1,15), ENC.SGESTADO, MU.CDMUNICIBGE,
                SUBSTR(ENC.NMCONTCLIE,1,15)
           INTO V_DSENDECLIECO, V_NMBAIRCLIECO, V_NRCEPCLIECO,
                V_NMMUNICIPIOCO, V_SGESTADOCO, V_CDMUNICIBGECO,
                V_CONTATOCOBRA
           FROM ENDECLIE ENC, MUNICIPIO MU
          WHERE ENC.CDCLIENTE = C_CL.CDCLIENTE
            AND ENC.IDTPENDECLIE   = 'C'
            AND (MU.CDPAIS         = ENC.CDPAIS)
            AND (ENC.SGESTADO      = MU.SGESTADO)
            AND (MU.CDMUNICIPIO    = ENC.CDMUNICIPIO);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_DSENDECLIECO   := NULL;
            V_NMBAIRCLIECO   := NULL;
            V_NRCEPCLIECO    := NULL;
            V_NMMUNICIPIOCO  := NULL;
            V_SGESTADOCO     := NULL;
            V_CONTATOCOBRA   := NULL;
         END;
      END;

      BEGIN
         SELECT ENC.DSENDECLIE,  ENC.NMBAIRCLIE, ENC.NRCEPCLIE,
                SUBSTR(MU.NMMUNICIPIO,1,15), ENC.SGESTADO,   MU.CDMUNICIBGE
           INTO V_DSENDECLIEEN,  V_NMBAIRCLIEEN, V_NRCEPCLIEEN,
                V_NMMUNICIPIOEN, V_SGESTADOEN,   V_CDMUNICIBGEEN
           FROM ENDECLIE ENC, MUNICIPIO MU
          WHERE ENC.CDCLIENTE = C_CL.CDCLIENTE
            AND ENC.IDTPENDECLIE  = 'T'
            AND (MU.CDPAIS         = ENC.CDPAIS)
            AND (ENC.SGESTADO      = MU.SGESTADO)
            AND (MU.CDMUNICIPIO    = ENC.CDMUNICIPIO);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_NRCEPCLIEEN    := NULL;
            V_DSENDECLIEEN   := NULL;
            V_NMBAIRCLIEEN   := NULL;
            V_NMMUNICIPIOEN  := NULL;
            V_SGESTADOEN     := NULL;
         END;
      END;

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

      INSERT INTO USR_ZW2
      (W2_IDCLI, W2_EMPRESA, W2_FILIAL, W2_NOME, W2_PESSOA, W2_TIPO,
       W2_END, W2_BAIRRO, W2_CEP,
       W2_PAIS, W2_IBGE, W2_MUN, W2_EST,
       W2_TEL, W2_TELEX, W2_FAX, W2_CONTATO,
       W2_ENDCOB, W2_BAIRROC, W2_CEPC,
       W2_MUNC, W2_ESTC, W2_ENDENT, W2_BAIRROE,
       W2_CEPE,
       W2_MUNE, W2_ESTE, W2_CGC,
       W2_INSCR, W2_COND, W2_EMAIL, W2_FLATIVO, W2_TPEDI,
       W2_HRGRV, W2_DTGRV, W2_COMPLEM, W2_RG,
       W2_NREDUZ, W2_COD_MUN,
       W2_CCCRED, W2_CONTC)
       VALUES
      (V_NOVOCODIGO, '01', '01', SUBSTR(C_CL.NMRAZSOCCLIE, 1, 40), C_CL.IDTPIJURCLIE, SUBSTR(C_CL.NMGRUPCLIE, 1, 1),
       SUBSTR(C_CL.DSENDECLIE, 1, 40), SUBSTR(C_CL.NMBAIRCLIE, 1, 20), SUBSTR(replace(replace(C_CL.NRCEPCLIE, '-', ''),'.',''),1,8),
       NVL(SUBSTR(C_CL.CDPAISEXPFIS, 3, 5), NVL(SUBSTR(C_CL.CDPAIS, 2, 4), V_BRASIL)), SUBSTR(C_CL.CDMUNICIBGE, 1, 5), SUBSTR(C_CL.NMMUNICIPIO, 1, 15), C_CL.SGESTADO,
       SUBSTR(C_CL.NRTELECLIE, 1, 20), SUBSTR(C_CL.NRTELEXCLIE,1,10), SUBSTR(C_CL.NRFAXCLIE,1,20), SUBSTR(C_CL.NMCONTCLIE,1,15),
       SUBSTR(V_DSENDECLIECO, 1, 40), SUBSTR(V_NMBAIRCLIECO, 1, 20), SUBSTR(replace(replace(V_NRCEPCLIECO, '-', ''),'.',''),1,8),
       V_NMMUNICIPIOCO, V_SGESTADOCO, SUBSTR(V_DSENDECLIEEN, 1, 40), SUBSTR(V_NMBAIRCLIEEN, 1, 20),
       SUBSTR(replace(replace(V_NRCEPCLIEEN, '-', ''),'.',''),1,8),
       V_NMMUNICIPIOEN, V_SGESTADOEN, SUBSTR(C_CL.NRINSJURCLIE, 1,14),
       SUBSTR(C_CL.NRINSESTCLIE,1,18), C_CL.CDFORMPGTO, SUBSTR(C_CL.DSEMAILCLIE, 1, 30), C_CL.IDCLIEATIV, '1',
       TO_CHAR(SYSDATE, 'HH24MI'), TRUNC(SYSDATE), SUBSTR(C_CL.DSCOMPENDCLI, 1, 50), SUBSTR(C_CL.NMCOMPRZSCLI, 1, 15),
       SUBSTR(C_CL.NMFANTCLIE, 1, 40), SUBSTR(V_CDMUNICIBGEEN, 1, 5),
       NULL, V_CONTATOCOBRA);

          UPDATE CLIENTE
             SET USR_DTEXPCLI  = SYSDATE
           WHERE SUBSTR(NRINSJURCLIE, 1,14) = SUBSTR(C_CL.NRINSJURCLIE, 1,14);
   END LOOP;


   FOR C_CLUPD IN (SELECT CL.CDCLIENTE, CL.NMRAZSOCCLIE, CL.IDTPIJURCLIE,
                          SUBSTR(replace(replace(replace(CL.NRINSJURCLIE, '-', ''),'.',''),'/',''),1,14) AS NRINSJURCLIE,
                          CL.NRINSESTCLIE, MU.NMMUNICIPIO, LPAD(CL.CDFORMPGTO,3,'0') AS CDFORMPGTO, CL.IDCLIEATIV,
                          ENP.DSENDECLIE, ENP.NMBAIRCLIE, ENP.NRCEPCLIE, PA.CDPAISEXPFIS,
                          ENP.CDPAIS, ENP.DSEMAILCLIE, ENP.CDMUNICIPIO, ENP.SGESTADO,
                          ENP.NRTELECLIE, ENP.NRTELEXCLIE, ENP.NRFAXCLIE, ENP.NMCONTCLIE,
                          ENP.DSCOMPENDCLI, CL.NMCOMPRZSCLI, MU.CDMUNICIBGE,
                          CL.NMFANTCLIE, GR.NMGRUPCLIE
                     FROM CLIENTE CL, ENDECLIE ENP, MUNICIPIO MU, PAIS PA, GRUPCLIE GR,
                          (SELECT CDCLIENTE, MAX(DTULTATUECLI) AS DTULTATUECLI
                             FROM ENDECLIE
                            WHERE IDTPENDECLIE IN ('C', 'T')
                            GROUP BY CDCLIENTE) ENC
                    WHERE (CL.CDCLIENTE      = ENP.CDCLIENTE)
                      AND (CL.CDCLIENTE      = ENC.CDCLIENTE(+))
                      AND (MU.CDPAIS         = ENP.CDPAIS)
                      AND (ENP.IDTPENDECLIE  = 'P')
                      AND (ENP.SGESTADO      = MU.SGESTADO)
                      AND (MU.CDMUNICIPIO    = ENP.CDMUNICIPIO)
                      AND ((CL.DTULTATUCLIE >= CL.USR_DTEXPCLI) 
                        OR (ENP.DTULTATUECLI >= CL.USR_DTEXPCLI)
                        OR (ENC.DTULTATUECLI >= CL.USR_DTEXPCLI))
                      AND (PA.CDPAIS         = ENP.CDPAIS )
                      AND (GR.CDGRUPCLIE(+)  = CL.CDGRUPCLIE)
                      AND (CL.IDTPIJURCLIE IN ('F','J'))) LOOP

      BEGIN
         SELECT ENC.DSENDECLIE, ENC.NMBAIRCLIE, ENC.NRCEPCLIE,
                SUBSTR(MU.NMMUNICIPIO,1,15), ENC.SGESTADO, MU.CDMUNICIBGE,
                SUBSTR(ENC.NMCONTCLIE,1,15)
           INTO V_DSENDECLIECO, V_NMBAIRCLIECO, V_NRCEPCLIECO,
                V_NMMUNICIPIOCO, V_SGESTADOCO, V_CDMUNICIBGECO,
                V_CONTATOCOBRA
           FROM ENDECLIE ENC, MUNICIPIO MU
          WHERE ENC.CDCLIENTE = C_CLUPD.CDCLIENTE
            AND ENC.IDTPENDECLIE  = 'C'
            AND (MU.CDPAIS         = ENC.CDPAIS)
            AND (ENC.SGESTADO      = MU.SGESTADO)
            AND (MU.CDMUNICIPIO    = ENC.CDMUNICIPIO);
      EXCEPTION
          WHEN NO_DATA_FOUND THEN BEGIN
             V_DSENDECLIECO   := NULL;
             V_NMBAIRCLIECO   := NULL;
             V_NRCEPCLIECO    := NULL;
             V_NMMUNICIPIOCO  := NULL;
             V_SGESTADOCO     := NULL;
             V_CONTATOCOBRA   := NULL;
          END;
      END;

      BEGIN
         SELECT ENC.DSENDECLIE, ENC.NMBAIRCLIE, ENC.NRCEPCLIE,
                SUBSTR(MU.NMMUNICIPIO,1,15), ENC.SGESTADO, MU.CDMUNICIBGE
           INTO V_DSENDECLIEEN, V_NMBAIRCLIEEN, V_NRCEPCLIEEN,
                V_NMMUNICIPIOEN, V_SGESTADOEN, V_CDMUNICIBGEEN
           FROM ENDECLIE ENC, MUNICIPIO MU
          WHERE ENC.CDCLIENTE = C_CLUPD.CDCLIENTE
            AND ENC.IDTPENDECLIE  = 'T'
            AND (MU.CDPAIS         = ENC.CDPAIS)
            AND (ENC.SGESTADO      = MU.SGESTADO)
            AND (MU.CDMUNICIPIO    = ENC.CDMUNICIPIO);
      EXCEPTION
          WHEN NO_DATA_FOUND THEN BEGIN
             V_NRCEPCLIEEN    := NULL;
             V_DSENDECLIEEN   := NULL;
             V_NMBAIRCLIEEN   := NULL;
             V_NMMUNICIPIOEN  := NULL;
             V_SGESTADOEN     := NULL;
          END;
      END;

      SELECT ENC.DSENDECLIE, ENC.NMBAIRCLIE, ENC.NRCEPCLIE,
             ENC.CDMUNICIPIO, ENC.SGESTADO, ENC.DSCOMPENDCLI
        INTO V_DSENDECLIEPR, V_NMBAIRCLIEPR, V_NRCEPCLIEPR,
             V_CDMUNICIPIOPR, V_SGESTADOPR, V_COMPLEMENTO
        FROM ENDECLIE ENC
       WHERE ENC.CDCLIENTE = C_CLUPD.CDCLIENTE
         AND ENC.IDTPENDECLIE  = 'P';

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

      INSERT INTO USR_ZW2
      (W2_IDCLI, W2_NOME, W2_PESSOA, W2_TIPO,
       W2_END, W2_BAIRRO, W2_CEP,
       W2_PAIS, W2_IBGE, W2_MUN, W2_EST,
       W2_TEL, W2_TELEX, W2_FAX, W2_CONTATO,
       W2_ENDCOB, W2_BAIRROC, W2_CEPC,
       W2_MUNC, W2_ESTC, W2_ENDENT, W2_BAIRROE,
       W2_CEPE,
       W2_MUNE, W2_ESTE, W2_CGC,
       W2_INSCR, W2_COND, W2_EMAIL, W2_FLATIVO, W2_TPEDI,
       W2_HRGRV, W2_DTGRV, W2_COMPLEM, W2_RG,
       W2_NREDUZ, W2_COD_MUN,
       W2_CCCRED, W2_CONTC)
      VALUES
      (V_NOVOCODIGO, SUBSTR(C_CLUPD.NMRAZSOCCLIE, 1, 40), C_CLUPD.IDTPIJURCLIE, SUBSTR(C_CLUPD.NMGRUPCLIE, 1, 1),
       SUBSTR(C_CLUPD.DSENDECLIE, 1, 40), SUBSTR(C_CLUPD.NMBAIRCLIE, 1, 20), SUBSTR(replace(replace(C_CLUPD.NRCEPCLIE, '-', ''),'.',''),1,8),
       NVL(SUBSTR(C_CLUPD.CDPAISEXPFIS, 3, 5), NVL(SUBSTR(C_CLUPD.CDPAIS, 2, 4), V_BRASIL)), SUBSTR(C_CLUPD.CDMUNICIBGE, 1, 5), SUBSTR(C_CLUPD.NMMUNICIPIO, 1, 15), C_CLUPD.SGESTADO,
       SUBSTR(C_CLUPD.NRTELECLIE, 1, 20), SUBSTR(C_CLUPD.NRTELEXCLIE,1,10), SUBSTR(C_CLUPD.NRFAXCLIE,1,20), SUBSTR(C_CLUPD.NMCONTCLIE,1,15),
       SUBSTR(V_DSENDECLIECO, 1, 40), SUBSTR(V_NMBAIRCLIECO, 1, 20), SUBSTR(replace(replace(V_NRCEPCLIECO, '-', ''),'.',''),1,8),
       V_NMMUNICIPIOCO, V_SGESTADOCO, SUBSTR(V_DSENDECLIEEN, 1, 40), SUBSTR(V_NMBAIRCLIEEN, 1, 20),
       SUBSTR(replace(replace(V_NRCEPCLIEEN, '-', ''),'.',''),1,8),
       V_NMMUNICIPIOEN, V_SGESTADOEN, SUBSTR(C_CLUPD.NRINSJURCLIE, 1,14),
       SUBSTR(C_CLUPD.NRINSESTCLIE,1,18), C_CLUPD.CDFORMPGTO, SUBSTR(C_CLUPD.DSEMAILCLIE, 1, 30), C_CLUPD.IDCLIEATIV, '3',
       TO_CHAR(SYSDATE, 'HH24MI'), TRUNC(SYSDATE), SUBSTR(C_CLUPD.DSCOMPENDCLI, 1, 50), SUBSTR(C_CLUPD.NMCOMPRZSCLI, 1, 15),
       SUBSTR(C_CLUPD.NMFANTCLIE, 1, 40), SUBSTR(V_CDMUNICIBGEEN, 1, 5),
       NULL, V_CONTATOCOBRA);

      UPDATE CLIENTE
         SET USR_DTEXPCLI = SYSDATE
       WHERE SUBSTR(NRINSJURCLIE, 1,14) = SUBSTR(C_CLUPD.NRINSJURCLIE, 1,14);
   END LOOP;
   COMMIT;
END;
/

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