create or replace PROCEDURE USR_PROT_IMPORTA_FORNECEDOR(
   P_MENSAGEMERRO OUT VARCHAR2)
IS
   --GERAL
   V_NRINSJURFORN  USR_CISPROT_FORNECEDOR.CGCFORNE%TYPE;
   V_CDFORPROTHEUS USR_CISPROT_FORNECEDOR.CDFORPROTHEUS%TYPE;
   V_NMRAZAOSOC    USR_CISPROT_FORNECEDOR.NMRAZAOSOC%TYPE;
   V_NMFANTFORNE   USR_CISPROT_FORNECEDOR.NMFANTFORNE%TYPE;
   V_DSENDEFORN    USR_CISPROT_FORNECEDOR.DSENDERECO%TYPE;
   V_CDPAIS        USR_CISPROT_FORNECEDOR.CDPAIS%TYPE;
   V_NMPAIS        USR_CISPROT_FORNECEDOR.NMPAIS%TYPE;
   V_SGESTADO      USR_CISPROT_FORNECEDOR.SGESTADO%TYPE;
   V_CDMUNCIPIO    USR_CISPROT_FORNECEDOR.CDMUNCIPIO%TYPE;
   V_NMMUNICIPIO   USR_CISPROT_FORNECEDOR.NMMUNICIPIO%TYPE;
   V_NRCEPFORN     USR_CISPROT_FORNECEDOR.CDCEP%TYPE;
   V_NMBAIRFORN    USR_CISPROT_FORNECEDOR.NMBAIRRO%TYPE;
   V_EMAIL         USR_CISPROT_FORNECEDOR.EMAIL%TYPE;
   V_CDFORMPGTO    USR_CISPROT_FORNECEDOR.CDFORMPGTO%TYPE;
   V_NMFORMPGTO    USR_CISPROT_FORNECEDOR.NMFORMPGTO%TYPE;
   V_IDFORNATIV    USR_CISPROT_FORNECEDOR.IDFORNATIV%TYPE;
   V_IDINTERFACE   USR_CISPROT_FORNECEDOR.IDINTERFACE%TYPE;
   V_CDBAIRRO		 BAIRRO.CDBAIRRO%TYPE;

   --ESPECIFICO TABELA INT
   V_LOG_ERRO      VARCHAR2(250);
   V_DTGRAVACAO    DATE;
   V_HRGRAVACAO    VARCHAR2(5);
   V_DTIMPORTACAO  DATE;
   V_HRIMPORTACAO  VARCHAR2(5);
   V_IDTPIJURFORN  VARCHAR2(1);
   V_ERRO 			 VARCHAR2(5000);
   V_OBS			    VARCHAR2(5000);

   --VARIAVEIS LOCAIS MUTAVEIS
   V_EXISTEPAIS          NUMBER;
   V_EXISTEESTADO        NUMBER;
   V_EXISTEMUNICIPIO     NUMBER;
   V_JACADASTRADO        NUMBER;
   V_JARELACIONADO       NUMBER;
   V_EXISTEPGTO          NUMBER;
   JOB_N                 INT;
   V_TABELA			       VARCHAR2(12);
   V_COMANDO             VARCHAR2(5000);

   CURSOR C_FORNECEDOR IS
      SELECT CGCFORNE AS NRINSJURFORN, CDFORPROTHEUS, NMRAZAOSOC, NMFANTFORNE, DSENDERECO AS DSENDEFORN,
             CDPAIS, NMPAIS, SGESTADO, CDMUNCIPIO, NMMUNICIPIO, CDCEP AS NRCEPFORN,
             NMBAIRRO AS NMBAIRFORN, EMAIL, CDFORMPGTO, NMFORMPGTO, IDFORNATIV, IDINTERFACE,
             LOG_ERRO, DTGRAVACAO, HRGRAVACAO, DTIMPORTACAO, HRIMPORTACAO,
             (CASE WHEN LENGTH(CGCFORNE) = 14 THEN 'J'
                   WHEN LENGTH(CGCFORNE) = 11  THEN 'F'
                   WHEN LENGTH(CGCFORNE) < 11  THEN 'O'
                   WHEN LENGTH(CGCFORNE) BETWEEN 12 AND 13 THEN 'I'
              END) AS IDTPIJURFORN
        FROM USR_CISPROT_FORNECEDOR
       WHERE DTIMPORTACAO IS NULL;
BEGIN
   OPEN C_FORNECEDOR;
   LOOP
      FETCH C_FORNECEDOR INTO V_NRINSJURFORN,
                              V_CDFORPROTHEUS,
                              V_NMRAZAOSOC,
                              V_NMFANTFORNE,
                              V_DSENDEFORN,
                              V_CDPAIS,
                              V_NMPAIS,
                              V_SGESTADO,
                              V_CDMUNCIPIO,
                              V_NMMUNICIPIO,
                              V_NRCEPFORN,
                              V_NMBAIRFORN,
                              V_EMAIL,
                              V_CDFORMPGTO,
                              V_NMFORMPGTO,
                              V_IDFORNATIV,
                              V_IDINTERFACE,
                              V_LOG_ERRO,
                              V_DTGRAVACAO,
                              V_HRGRAVACAO,
                              V_DTIMPORTACAO,
                              V_HRIMPORTACAO,
                              V_IDTPIJURFORN;
      EXIT WHEN C_FORNECEDOR%NOTFOUND;

      V_ERRO := NULL;
      V_OBS  := NULL;
      P_MENSAGEMERRO := NULL;
      V_TABELA := 'CLIENTE';

      V_CDMUNCIPIO := SUBSTR(V_CDMUNCIPIO, 4, 4);
      V_CDPAIS := LPAD(V_CDPAIS, 4, '0');


      SELECT COUNT(CDPAIS) INTO V_EXISTEPAIS FROM PAIS WHERE CDPAIS = V_CDPAIS;
      SELECT COUNT(SGESTADO) INTO V_EXISTEESTADO FROM ESTADO WHERE CDPAIS = V_CDPAIS AND SGESTADO = V_SGESTADO;
      SELECT COUNT(CDMUNICIPIO) INTO V_EXISTEMUNICIPIO FROM MUNICIPIO WHERE CDMUNICIPIO = V_CDMUNCIPIO AND SGESTADO = V_SGESTADO AND CDPAIS = V_CDPAIS;
      SELECT COUNT(CDFORNECED) INTO V_JACADASTRADO FROM FORNECEDOR WHERE CDFORNECED = V_IDTPIJURFORN||V_NRINSJURFORN;
      SELECT COUNT(CDFORMPGTO) INTO V_EXISTEPGTO FROM FORMPGTO WHERE CDFORMPGTO = V_CDFORMPGTO;
      SELECT CDBAIRRO INTO V_CDBAIRRO FROM BAIRRO WHERE CDPAIS = V_CDPAIS AND SGESTADO = V_SGESTADO AND CDMUNICIPIO = V_CDMUNCIPIO AND NMBAIRRO = V_NMBAIRFORN;

      IF (V_EXISTEPAIS = 0) THEN
         INSERT INTO PAIS
            (CDPAIS, NMPAIS, CDPAISBACEN)
         VALUES
            (V_CDPAIS, V_NMPAIS, V_CDPAIS);

         COMMIT;
      END IF;

      IF (V_EXISTEESTADO = 0) THEN
         INSERT INTO ESTADO
            (CDPAIS, SGESTADO, NMESTADO)
         VALUES
            (V_CDPAIS, V_SGESTADO, V_SGESTADO);

         COMMIT;
      END IF;

      IF (V_EXISTEMUNICIPIO = 0) THEN
         INSERT INTO MUNICIPIO
            (CDPAIS, SGESTADO, CDMUNICIPIO, NMMUNICIPIO, IDMUNIUTCONVNFE)
         VALUES
            (V_CDPAIS, V_SGESTADO, V_CDMUNCIPIO, V_NMMUNICIPIO, 'N');

         COMMIT;
      END IF;

      IF (V_EXISTEPGTO > 0) THEN
         UPDATE FORMPGTO
            SET NMFORMPGTO = V_NMFORMPGTO
          WHERE CDFORMPGTO = V_CDFORMPGTO;
      ELSE
         IF (V_CDFORMPGTO IS NOT NULL) THEN
            INSERT INTO FORMPGTO
               (CDFORMPGTO, NMFORMPGTO, IDTIPODTPGTO, IDTPPERIODO, IDREFECALCPARC, IDCONSDIAUTIL)
            VALUES
               (V_CDFORMPGTO, V_NMFORMPGTO, 'EMIN', 'M', 'DIA_DE_REFERENCIA', 'N');
         END IF;
      END IF;

      COMMIT;

      IF (V_JACADASTRADO = 1) THEN
         UPDATE FORNECEDOR
            SET CDEXFORNECED = V_CDFORPROTHEUS
          WHERE CDFORNECED = IDTPIJURFORN||V_NRINSJURFORN;

         IF (V_IDINTERFACE <> 3) THEN
            V_IDINTERFACE := '2';
         END IF;
      END IF;

      IF (V_IDINTERFACE = '1') THEN
         BEGIN
            INSERT INTO FORNECEDOR
               (CDFORNECED, IDTPIJURFORN, NRINSJURFORN, NMRAZSOCFORN, NMFANTFORN,
                IDTPDESCFORN, IDTIPOFORN, IDTPFRETFORN, DTCADAFORN, IDSITUCADA,
                IDUTDIAPGTFO, IDENDEMISFOR, IDENDCFOPFOR, IDPRIPUBFORN, IDAGRUBOLFOR,
                IDLIGEXTFORN, DSMAILCOTCOP, IDTPENVAFFOR, IDUTILEDINF, IDOBRIGAEAN, IDUTQTDISPFORN,
                IDBLOQCMPSAFFOR, IDLOCALFORNCAD, IDCONSFINAL, IDTPENVAFNORMAL, IDCONTRIBICMS,
                IDTPTITULOCRED, CDEXFORNECED, CDFORMPGTO)
            VALUES
                (V_IDTPIJURFORN||V_NRINSJURFORN, V_IDTPIJURFORN, V_NRINSJURFORN, V_NMRAZAOSOC, V_NMFANTFORNE,
                 'S', 'N', 'C', SYSDATE, V_IDFORNATIV,
                 'I', 'P', 'P', 'V', 'S',
                 'N', V_EMAIL, '1', 'N', 'N', 'N',
                 'N', 'F', 'N', 'E', 'N',
                 'XX', V_CDFORPROTHEUS, V_CDFORMPGTO);

            INSERT INTO ENDEFORN
               (CDFORNECED, IDTPENDEFORN, CDPAIS, SGESTADO, CDMUNICIPIO, DSENDEFORN,
                NMBAIRFORN, NRCEPFORN, DSEMAILFORN ,DSEMAILFORNNFE, DSEMAILFORNCTE, DSEMAILFORNMDFE)
            VALUES
               (V_IDTPIJURFORN||V_NRINSJURFORN, 'P', V_CDPAIS, V_SGESTADO, V_CDMUNCIPIO, V_DSENDEFORN,
                V_NMBAIRFORN, V_NRCEPFORN, SUBSTR(V_EMAIL, 0, 40), V_EMAIL, SUBSTR(V_EMAIL, 0, 60), SUBSTR(V_EMAIL, 0, 60));
         EXCEPTION
            WHEN OTHERS THEN
               V_ERRO := SQLERRM(SQLCODE);
               P_MENSAGEMERRO := V_ERRO;
         END;
      END IF;

      IF (V_IDINTERFACE = '2') THEN
         BEGIN
            UPDATE FORNECEDOR
               SET NMRAZSOCFORN = V_NMRAZAOSOC,
                   NMFANTFORN   = V_NMFANTFORNE,
                   IDSITUCADA   = V_IDFORNATIV,
                   CDFORMPGTO	  = V_CDFORMPGTO,
                   DSMAILCOTCOP = V_EMAIL
             WHERE CDFORNECED = V_IDTPIJURFORN||V_NRINSJURFORN;

            UPDATE ENDEFORN
               SET CDPAIS          = V_CDPAIS,
                   SGESTADO        = V_SGESTADO,
                   CDMUNICIPIO     = V_CDMUNCIPIO,
                   DSENDEFORN      = V_DSENDEFORN,
                   NMBAIRFORN      = V_NMBAIRFORN,
                   NRCEPFORN       = V_NRCEPFORN,
                   DSEMAILFORN     = SUBSTR(V_EMAIL, 0, 40),
                   DSEMAILFORNNFE  = V_EMAIL,
                   DSEMAILFORNCTE  = SUBSTR(V_EMAIL, 0, 60),
                   DSEMAILFORNMDFE = SUBSTR(V_EMAIL, 0, 60)
             WHERE CDFORNECED = V_IDTPIJURFORN||V_NRINSJURFORN;
         EXCEPTION
            WHEN OTHERS THEN
               V_ERRO := SQLERRM(SQLCODE);
               P_MENSAGEMERRO := V_ERRO;
         END;
      END IF;

      IF (V_IDINTERFACE = '3') THEN
         BEGIN
            UPDATE FORNECEDOR
               SET IDSITUCADA   = 'I'
             WHERE CDFORNECED = V_IDTPIJURFORN||V_NRINSJURFORN;

         EXCEPTION
            WHEN OTHERS THEN
               V_ERRO := SQLERRM(SQLCODE);
               P_MENSAGEMERRO := V_ERRO;
         END;
      END IF;

      UPDATE USR_CISPROT_FORNECEDOR
         SET DTIMPORTACAO = SYSDATE,
             HRIMPORTACAO = TO_CHAR(SYSDATE, 'HH:MM'),
             LOG_ERRO = V_ERRO
      WHERE CGCFORNE = V_NRINSJURFORN;

      COMMIT;

   END LOOP;
   CLOSE C_FORNECEDOR;

   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_PROT_IMPORTA_FORNECEDOR','6.35.038'); END;
/
