create or replace PROCEDURE SDX_IMPORTA_PRODUTO
IS
   --GERAL
   V_CDARVPROD			PRODUTO.CDARVPROD%TYPE;
   V_CDARVPRODOLD		PRODUTO.CDARVPROD%TYPE;
   V_CDULTIMODIGIT		PRODUTO.CDARVPROD%TYPE;
   V_CDCEST			PRODUTO.CDCEST%TYPE;
   V_CDNVPRODUTO		PRODUTO.CDNVPRODUTO%TYPE;
   V_CDPRODUTO			PRODUTO.CDPRODUTO%TYPE;
   V_CDPRODUTOOLD		PRODUTO.CDPRODUTO%TYPE;
   V_ULTARVPROD	          	PRODUTO.CDARVPROD%TYPE;
   V_PENULTARVPROD		PRODUTO.CDARVPROD%TYPE;
   V_NEXTCDARVPROD      	PRODUTO.CDARVPROD%TYPE;
   V_NEXTPENULTPROD 	        PRODUTO.CDARVPROD%TYPE;
   V_EXISTEPRODPAI		PRODUTO.CDARVPROD%TYPE;
   V_NOVOCODIGO	         	VARCHAR2(20);
   V_MENSAGEMERRO		VARCHAR2(1024);
   V_LENGTHULTNIVEL     	NUMBER;
   V_LENGTHARVPROD		NUMBER;
   V_JACADASTRADO               NUMBER;
   V_ERRO		        NUMBER;
   V_ROWNUM			NUMBER;
   V_NVPENULTNIVEL              NUMBER;
   V_LENGTHPENULTNIVEL          NUMBER;
   V_TOTALENGTHPENULT           NUMBER;
   V_EXISTEPRODFILHO            NUMBER;

   P_MAXSEQUENCIAL              NUMBER;

   CURSOR C_PRODUTOS IS
      SELECT NUM_SEQ, COD_ITEM_SAP AS CDPRODINTE, DES_ITEM AS NMPRODUTO, COD_UM AS SGUNIDADE, COD_NBM AS CDPRODNCM, DECODE(IND_INATIVO, '0', 'S', 'N') AS IDATIVO,
             QTD_PESO_LIQ AS VRPESOLIQUPR, QTD_PESO_BRUTO AS VRPESOBRUTPR, DES_GERAL AS CDCSTDIG1PROD, DECODE(IND_PDV, '0', 'N', 'S') AS IDHABTAA, COD_TIPO AS CDTPITEMPROD,
             IDX_CONVERSAO AS VRFATOCONV, COD_CEST AS CDCEST, DESC_CEST AS DSCEST, COD_ARV_PROD AS CDARVPRODPAI, DTA_INCLUSAO, DESC_COD_ARV as CDBARPRODUTO
        FROM SDX_ZFMMBR_MATERI
       WHERE IND_STATUS = 1
         AND DTA_INCLUSAO BETWEEN (SYSDATE - INTERVAL '30' DAY) AND SYSDATE
         AND NUM_SEQ > P_MAXSEQUENCIAL;
BEGIN
   SELECT NVL(MAX(NUM_SEQ),0) INTO P_MAXSEQUENCIAL FROM SDX_ZFMMBR_MAT_RE
    WHERE DTA_IMPORTACAO BETWEEN (SYSDATE - INTERVAL '30' DAY) AND SYSDATE;

   FOR C_PRODUTO IN C_PRODUTOS
   LOOP
      V_ERRO          := 2;
      V_MENSAGEMERRO  := NULL;

      IF C_PRODUTO.CDCEST IS NOT NULL THEN
         BEGIN
            SELECT CDCEST INTO V_CDCEST FROM CODESPSUBTRI WHERE REPLACE(CDCEST, '.', '') = C_PRODUTO.CDCEST;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               INSERT INTO CODESPSUBTRI
                  (CDSEGMENTO, CDITEM, CDCEST, DSCEST)
               VALUES
                  (SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 1, 2),
                   SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 3, 3)||'.'||SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 6, 1),
                   SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 1, 2)||'.'||SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 3, 3)||'.'||SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 6, 2),
                   C_PRODUTO.DSCEST);

               V_CDCEST := SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 1, 2)||'.'||SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 3, 3)||'.'||SUBSTR(REPLACE(C_PRODUTO.CDCEST, '.', ''), 6, 2);
         END;
      ELSE
         V_CDCEST := NULL;
      END IF;

      BEGIN
         SELECT CDARVPROD INTO V_EXISTEPRODPAI FROM PRODUTO WHERE CDARVPROD = C_PRODUTO.CDARVPRODPAI;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_ERRO := 3;
            V_MENSAGEMERRO := 'Código do produto PAI (Hierarquia) não existe.';
            GOTO ERROR_HANDLER;
      END;

      --DESCOBRE TAMANHO TOTAL DO CDARVPROD
      SELECT SUM(LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL))) INTO V_LENGTHARVPROD FROM PARAMGERAL
             CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL;

      --DESCOBRE O TAMANHO DO PENULTIMO NIVEL
      SELECT NIVEL, TAMANHO INTO V_NVPENULTNIVEL, V_LENGTHPENULTNIVEL FROM (
         SELECT ROWNUM AS NIVEL, LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL)) AS TAMANHO FROM PARAMGERAL
                CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL
          ORDER BY ROWNUM DESC)
       WHERE NIVEL = (SELECT MAX(ROWNUM) -1 FROM PARAMGERAL
                            CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL);

      --DESCOBRE QUANTOS DIGITOS ATÉ O PENULTIMO NIVEL
      SELECT SUM(TAMANHO) INTO V_TOTALENGTHPENULT FROM (
         SELECT ROWNUM AS NIVEL, LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL)) AS TAMANHO FROM PARAMGERAL
                CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL
          ORDER BY ROWNUM DESC)
       WHERE NIVEL <= (SELECT MAX(ROWNUM) -1 FROM PARAMGERAL
                             CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL);

      --DESCOBRE O TAMANHO DO ULTIMO NIVEL
      SELECT ROWNUM, LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL)) INTO V_ROWNUM, V_LENGTHULTNIVEL FROM PARAMGERAL
             CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL
       ORDER BY ROWNUM DESC
       FETCH FIRST ROW ONLY;

      --DESCOBRRE O NIVEL MAXIMO DE PRODUTO
      SELECT COUNT(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL)) INTO V_CDNVPRODUTO FROM PARAMGERAL
             CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL;

      BEGIN
         SELECT CDPRODUTO, CDARVPROD, COUNT(CDPRODUTO) INTO V_CDPRODUTOOLD, V_CDARVPRODOLD, V_JACADASTRADO FROM PRODUTO WHERE CDPRODINTE = C_PRODUTO.CDPRODINTE GROUP BY CDPRODUTO, CDARVPROD;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_JACADASTRADO := 0;
            V_CDPRODUTO := NULL;
      END;

      IF (V_JACADASTRADO > 0) THEN -- UPDATING
         DBMS_OUTPUT.PUT_LINE('V_CDARVPRODOLD= '||SUBSTR(V_CDARVPRODOLD, 1, (V_TOTALENGTHPENULT - V_LENGTHPENULTNIVEL))||' CDARVPRODPAI= '|| C_PRODUTO.CDARVPRODPAI);

         IF(SUBSTR(V_CDARVPRODOLD, 1, (V_TOTALENGTHPENULT - V_LENGTHPENULTNIVEL)) != SUBSTR(C_PRODUTO.CDARVPRODPAI, 1, V_TOTALENGTHPENULT - V_LENGTHPENULTNIVEL)) THEN
            BEGIN -- DESCOBRE PROXIMO SEQUENCIAL DO PENULTIMO NIVEL
               SELECT CDARVPROD
                 INTO V_PENULTARVPROD
                 FROM PRODUTO
                WHERE CDARVPROD LIKE SUBSTR(C_PRODUTO.CDARVPRODPAI, 1, V_TOTALENGTHPENULT)||'%'
                  AND CDNVPRODUTO = V_NVPENULTNIVEL
                ORDER BY CDARVPROD DESC
                FETCH FIRST ROW ONLY;

               V_NEXTPENULTPROD := (LPAD((TO_NUMBER(TRIM(SUBSTR(V_PENULTARVPROD, (V_TOTALENGTHPENULT+1-V_LENGTHPENULTNIVEL), V_LENGTHPENULTNIVEL)))+1), V_LENGTHPENULTNIVEL, '0'));
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_NEXTPENULTPROD := LPAD('0', V_LENGTHPENULTNIVEL, '0');
            END;

            V_CDARVPROD := C_PRODUTO.CDARVPRODPAI||V_NEXTPENULTPROD;
            NOVO_CODIGO_SC('PRODUTO', V_NOVOCODIGO);
            V_CDPRODUTO := SUBSTR(V_NOVOCODIGO, 11, 10);
            DBMS_OUTPUT.PUT_LINE('insert novo nivel= '||V_CDARVPROD||' cdproduto= '||V_CDPRODUTO);

            INSERT INTO PRODUTO
               (CDARVPROD, CDPRODUTO, CDNVPRODUTO, NMPRODUTO,
                IDPRODATIVPR, IDSEGMENTOPROD, IDPRODUSOCONS,
                CDCSTDIG1PROD, IDGERREVPROCFA, IDTEMNUTRIENTE, IDALTERANUTR, IDTEOROBRIG,
                IDVDANALISE, IDPRODPERIG, IDPRODDIMEVAR, DTULTATUPROD, DTULTATU)
            VALUES
               (V_CDARVPROD,  V_CDPRODUTO, V_NVPENULTNIVEL, C_PRODUTO.NMPRODUTO,
                C_PRODUTO.IDATIVO, 'OUTR', 'N',
                '0', 'N', 'N', 'N', 'N',
                '2', 'N', 'N', SYSDATE, SYSDATE);

            BEGIN --DESCOBRE PROXIMO SEQUENCIAL ARVPROD ultimo nivel.
               SELECT CDARVPROD
                 INTO V_ULTARVPROD
                 FROM PRODUTO
                WHERE CDARVPROD LIKE V_CDARVPROD||'%'
                  AND CDNVPRODUTO = V_CDNVPRODUTO
                ORDER BY CDARVPROD DESC
                FETCH FIRST ROW ONLY;

               V_NEXTCDARVPROD := (LPAD((TO_NUMBER(TRIM(SUBSTR(V_ULTARVPROD, (V_LENGTHARVPROD+1-V_LENGTHULTNIVEL), V_LENGTHULTNIVEL)))+1), V_LENGTHULTNIVEL, '0'));
            EXCEPTION WHEN NO_DATA_FOUND THEN
               V_NEXTCDARVPROD := LPAD('0', V_LENGTHULTNIVEL, '0');
            END;

            V_CDARVPROD := V_CDARVPROD||V_NEXTCDARVPROD;
         ELSE
            V_CDARVPROD := V_CDARVPRODOLD;

            UPDATE PRODUTO
               SET NMPRODUTO = C_PRODUTO.NMPRODUTO
             WHERE CDARVPROD = SUBSTR(V_CDARVPRODOLD, 1, V_TOTALENGTHPENULT);
         END IF;

         BEGIN
            UPDATE PRODUTO
               SET NMPRODUTO =  C_PRODUTO.NMPRODUTO,
                   IDPRODATIVPR = C_PRODUTO.IDATIVO,
                   CDPRODNCM = C_PRODUTO.CDPRODNCM,
                   VRPESOLIQUPR = C_PRODUTO.VRPESOLIQUPR,
                   VRPESOBRUTPR = C_PRODUTO.VRPESOBRUTPR,
                   IDHABTAA = C_PRODUTO.IDHABTAA, -- CONFERIR O VALOR QUE ESSE CAMPO RECEBE.
                   CDTPITEMPROD = C_PRODUTO.CDTPITEMPROD,
                   CDCEST = V_CDCEST,
                   CDARVPROD = V_CDARVPROD,
                   CDBARPRODUTO = C_PRODUTO.CDBARPRODUTO,
                   CDCLASFISC = C_PRODUTO.CDPRODNCM
             WHERE CDPRODUTO =  V_CDPRODUTOOLD;
         EXCEPTION WHEN OTHERS THEN
            ROLLBACK;
            V_ERRO := 3;
            V_MENSAGEMERRO := SQLERRM(SQLCODE);
            GOTO ERROR_HANDLER;
         END;

         BEGIN
            SELECT COUNT(*)
              INTO V_EXISTEPRODFILHO
              FROM PRODUTO
             WHERE CDARVPROD LIKE SUBSTR(V_CDARVPRODOLD, 1, V_TOTALENGTHPENULT)||'%';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               ROLLBACK;
               V_ERRO := 3;
               V_MENSAGEMERRO := 'Produto filho não encontrado';
               GOTO ERROR_HANDLER;
         END;

         IF (V_EXISTEPRODFILHO <= 1) THEN
            DELETE PRODUTO
             WHERE CDARVPROD LIKE SUBSTR(V_CDARVPRODOLD, 1, V_TOTALENGTHPENULT)||'%';
         END IF;
      ELSE
      --PROCESSO DE NAO CADASTRADO

         IF(LENGTH(C_PRODUTO.CDARVPRODPAI) < (V_LENGTHARVPROD-V_LENGTHULTNIVEL)) THEN
            BEGIN -- DESCOBRE PROXIMO SEQUENCIAL DO PENULTIMO NIVEL
               SELECT CDARVPROD
                 INTO V_PENULTARVPROD
                 FROM PRODUTO
                WHERE CDARVPROD LIKE SUBSTR(C_PRODUTO.CDARVPRODPAI, 1, V_TOTALENGTHPENULT)||'%'
                  AND CDNVPRODUTO = V_NVPENULTNIVEL
                ORDER BY CDARVPROD DESC
                FETCH FIRST ROW ONLY;

                V_NEXTPENULTPROD := (LPAD((TO_NUMBER(TRIM(SUBSTR(V_PENULTARVPROD, (V_TOTALENGTHPENULT+1-V_LENGTHPENULTNIVEL), V_LENGTHPENULTNIVEL)))+1), V_LENGTHPENULTNIVEL, '0'));
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                  V_NEXTPENULTPROD := LPAD('0', V_LENGTHPENULTNIVEL, '0');
            END;

            V_CDARVPROD := C_PRODUTO.CDARVPRODPAI||V_NEXTPENULTPROD;
            NOVO_CODIGO_SC('PRODUTO', V_NOVOCODIGO);
            V_CDPRODUTO := SUBSTR(V_NOVOCODIGO, 11, 10);

            INSERT INTO PRODUTO
               (CDARVPROD, CDPRODUTO, CDNVPRODUTO, NMPRODUTO,
                IDPRODATIVPR, IDSEGMENTOPROD, IDPRODUSOCONS,
                CDCSTDIG1PROD, IDGERREVPROCFA, IDTEMNUTRIENTE, IDALTERANUTR, IDTEOROBRIG,
                IDVDANALISE, IDPRODPERIG, IDPRODDIMEVAR, DTULTATUPROD, DTULTATU
                )
            VALUES
               (V_CDARVPROD,  V_CDPRODUTO, V_NVPENULTNIVEL, C_PRODUTO.NMPRODUTO,
                C_PRODUTO.IDATIVO, 'OUTR', 'N',
                '0', 'N', 'N', 'N', 'N',
                '2', 'N', 'N', SYSDATE, SYSDATE);
         ELSE
             V_CDARVPROD := C_PRODUTO.CDARVPRODPAI;
         END IF;

         BEGIN --DESCOBRE PROXIMO SEQUENCIAL ARVPROD ultimo nivel.
            SELECT CDARVPROD
              INTO V_ULTARVPROD FROM PRODUTO
             WHERE CDARVPROD LIKE V_CDARVPROD||'%'
               AND CDNVPRODUTO = V_CDNVPRODUTO
             ORDER BY CDARVPROD DESC
             FETCH FIRST ROW ONLY;

            V_NEXTCDARVPROD := (LPAD((TO_NUMBER(TRIM(SUBSTR(V_ULTARVPROD, (V_LENGTHARVPROD+1-V_LENGTHULTNIVEL), V_LENGTHULTNIVEL)))+1), V_LENGTHULTNIVEL, '0'));
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_NEXTCDARVPROD := LPAD('0', V_LENGTHULTNIVEL, '0');
         END;

         V_CDARVPROD := V_CDARVPROD||V_NEXTCDARVPROD;

         NOVO_CODIGO_SC('PRODUTO', V_NOVOCODIGO);
         V_CDPRODUTO := SUBSTR(V_NOVOCODIGO, 11, 10);

         DBMS_OUTPUT.PUT_LINE('produto V_CDARVPROD= '||V_CDARVPROD);

         BEGIN
            INSERT INTO PRODUTO
               (CDARVPROD, CDPRODUTO, SGUNIDADE, CDNVPRODUTO, NMPRODUTO, CDPRODESTO,
                VRFATOCONV, VRPEFATOCORR, IDPRODATIVPR, IDSEGMENTOPROD, IDPRODUSOCONS,
                CDCSTDIG1PROD, IDGERREVPROCFA, IDTEMNUTRIENTE, IDALTERANUTR, IDTEOROBRIG,
                IDVDANALISE, IDPRODPERIG, IDPRODDIMEVAR, DTULTATUPROD, DTULTATU,
                CDPRODNCM, VRPESOLIQUPR, VRPESOBRUTPR, IDHABTAA, CDTPITEMPROD,
                CDCEST, CDPRODINTE, CDBARPRODUTO, CDCLASFISC)
            VALUES
               (V_CDARVPROD,  V_CDPRODUTO, C_PRODUTO.SGUNIDADE, V_CDNVPRODUTO, C_PRODUTO.NMPRODUTO, V_CDPRODUTO,
                C_PRODUTO.VRFATOCONV, 0, C_PRODUTO.IDATIVO, 'OUTR', 'N',
                '0', 'N', 'N', 'N', 'N',
                '2', 'N', 'N', SYSDATE, SYSDATE,
                C_PRODUTO.CDPRODNCM, C_PRODUTO.VRPESOLIQUPR, C_PRODUTO.VRPESOBRUTPR, C_PRODUTO.IDHABTAA, C_PRODUTO.CDTPITEMPROD,
                V_CDCEST, C_PRODUTO.CDPRODINTE, C_PRODUTO.CDBARPRODUTO, C_PRODUTO.CDPRODNCM);
         EXCEPTION WHEN OTHERS THEN
            ROLLBACK;
            V_ERRO := 3;
            V_MENSAGEMERRO := SQLERRM(SQLCODE);
            GOTO ERROR_HANDLER;
         END;
      END IF; -- V_JACADASTRADO

      <<ERROR_HANDLER>>

      V_CDULTIMODIGIT := SUBSTR(V_CDARVPROD, (1 + V_LENGTHARVPROD - V_LENGTHULTNIVEL), V_LENGTHULTNIVEL);

      INSERT INTO SDX_ZFMMBR_MAT_RE
         (NUM_SEQ, COD_ITEM_SAP, DES_ITEM, COD_UM, COD_NBM, NUM_SEQ_ORIGEM,
          QTD_PESO_LIQ, QTD_PESO_BRUTO, DES_GERAL, IND_PDV, COD_TIPO, TXT_LOG,
          IDX_CONVERSAO, COD_CEST, DESC_CEST, COD_ARV_PROD, COD_ITEM_TK, DTA_IMPORTACAO, IND_INATIVO, DESC_COD_ARV, IND_STATUS)
      VALUES
         (C_PRODUTO.NUM_SEQ, C_PRODUTO.CDPRODINTE, C_PRODUTO.NMPRODUTO, C_PRODUTO.SGUNIDADE, C_PRODUTO.CDPRODNCM, V_CDULTIMODIGIT,
          C_PRODUTO.VRPESOLIQUPR, C_PRODUTO.VRPESOBRUTPR, C_PRODUTO.CDCSTDIG1PROD, C_PRODUTO.IDHABTAA, C_PRODUTO.CDTPITEMPROD, V_MENSAGEMERRO,
          C_PRODUTO.VRFATOCONV, C_PRODUTO.CDCEST, C_PRODUTO.DSCEST, V_CDARVPROD, V_CDARVPROD, SYSDATE, C_PRODUTO.IDATIVO, C_PRODUTO.CDBARPRODUTO, V_ERRO);

      COMMIT;
   END LOOP; -- END C_PRODUTO
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('SDX_IMPORTA_PRODUTO','6.49.014'); END;
/
