create or replace PROCEDURE USR_IMPORTA_PRODUTO_DECIO(P_MENSAGEMERRO OUT VARCHAR2)
IS
   --GERAL
   V_CDPRODUTO     VARCHAR2(20);
   V_CDARVPROD     VARCHAR2(20);
   V_CDARVPRODINT  VARCHAR2(20);
   V_CDPRODINT     USR_PRODUTO.CDPRODINT%TYPE;
   V_IDPRODESTO    USR_PRODUTO.IDPRODESTO%TYPE;
   V_CDPRODESTOINT USR_PRODUTO.CDPRODESTOINT%TYPE;
   V_CDNVPRODUTO   USR_PRODUTO.CDNVPRODUTO%TYPE;
   V_CDPRODESTO    USR_PRODUTO.CDPRODESTO%TYPE;
   V_NMPRODUTO     USR_PRODUTO.NMPRODUTO%TYPE;
   V_DSCOMPLNOME   USR_PRODUTO.DSCOMPLNOME%TYPE;
   V_SGUNIDADE     USR_PRODUTO.SGUNIDADE%TYPE;
   V_VRFATCORR     USR_PRODUTO.VRFATCORR%TYPE;
   V_VRFATOCONV    USR_PRODUTO.VRFATOCONV%TYPE;
   V_IDPRODATIVO   USR_PRODUTO.IDPRODATIVO%TYPE;
   V_IDACAO        USR_PRODUTO.IDACAO%TYPE;
   V_LOG_ERRO      USR_PRODUTO.LOG_ERRO%TYPE;
   V_DTGRAVACAO    USR_PRODUTO.DTGRAVACAO%TYPE;
   V_HRGRAVACAO    USR_PRODUTO.HRGRAVACAO%TYPE;
   V_DTIMPORTACAO  USR_PRODUTO.DTIMPORTACAO%TYPE;
   V_HRIMPORTACAO  USR_PRODUTO.HRIMPORTACAO%TYPE;
   TYPE CUR_TYP    IS REF CURSOR;
   C_DESC_NIVEL    CUR_TYP;
   V_QRY_DESCNIVEL VARCHAR2(5000);
   V_DESCNIVEL     VARCHAR2(50);
   V_CDBARPRODUTO  VARCHAR2(30);
   --ESPECIFICO TABELA INT
   V_ERRO 			      VARCHAR2(5000);
   --VARIAVEIS LOCAIS MUTAVEIS
   V_CDFILIAL 		   VARCHAR2(4);
   V_IDCTRLESTQ 	   VARCHAR2(1);
   V_IDUTILLOTE 	   VARCHAR2(1);
   V_EXISTE_NIVEL    NUMBER;
   V_EXISTE_NIVEL4 	NUMBER;
   V_JACADASTRADO    NUMBER;
   V_ROWID           RowId;
   V_ULTARVPROD      VARCHAR2(20);
   V_NEXTCDARVPROD 	PRODUTO.CDARVPROD%TYPE;
   V_NEWCDARVPROD    PRODUTO.CDARVPROD%TYPE;
   V_LENGTHULTNIVEL	NUMBER;
   V_LENGTHARVPROD   NUMBER;
   
   CURSOR C_PRODUTO IS
      SELECT CDPRODINT, IDPRODESTO, CDPRODESTOINT, CDPRODUTO, CDNVPRODUTO, CDPRODESTO, CDARVPRODINT, REGEXP_REPLACE(NMPRODUTO,';|,|:|/',''),
             REGEXP_REPLACE(DSCOMPLNOME,';|,|:|/',''), SGUNIDADE, VRFATCORR, VRFATOCONV, IDPRODATIVO, IDACAO, LOG_ERRO, DTGRAVACAO, HRGRAVACAO, DTIMPORTACAO, HRIMPORTACAO,
             ROWID, CDBARPRODUTO
        FROM USR_PRODUTO
       WHERE DTIMPORTACAO IS NULL
       ORDER BY IDACAO DESC, CDARVPRODINT;
   CURSOR C_FILIAL IS
      SELECT F.CDFILIAL, IDCTRLESTQ, IDUTILLOTE
        FROM PARAMFILIAL P, FILIAL F
       WHERE P.CDFILIAL = F.CDFILIAL;
   PROCEDURE INSERT_PRODUTO(P_CDARVPROD IN VARCHAR2, P_SGUNIDADE IN VARCHAR2,  P_CDNVPRODUTO IN VARCHAR2, P_NMPRODUTO IN VARCHAR2, P_CDPRODESTO IN VARCHAR2, P_VRFATOCONV IN FLOAT,
                            P_IDPRODATIVPR IN VARCHAR2, P_IDSEGMENTOPROD IN VARCHAR2, P_IDPRODUSOCONS IN VARCHAR2, P_CDCSTDIG1PROD IN VARCHAR2,
                            P_IDGERREVPROCFA IN VARCHAR2, P_IDTEMNUTRIENTE IN VARCHAR2, P_IDALTERANUTR IN VARCHAR2, P_IDTEOROBRIG IN VARCHAR2, P_IDVDANALISE IN VARCHAR2,
                            P_IDPRODPERIG IN VARCHAR2, P_IDPRODDIMEVAR IN VARCHAR2, P_DTULTATUPROD IN DATE, P_DTULTATU IN DATE)
   IS
      V_CDPRODUTO  VARCHAR2(20);
   BEGIN
      V_CDPRODUTO := '';
      NOVO_CODIGO_SC('PRODUTO',V_CDPRODUTO);
      V_CDPRODUTO := SUBSTR(V_CDPRODUTO,11,10);
      INSERT INTO PRODUTO
        (CDARVPROD,   CDPRODUTO,   SGUNIDADE,    CDNVPRODUTO,   NMPRODUTO, CDPRODESTO,    VRFATOCONV, IDPRODATIVPR,   IDSEGMENTOPROD,   IDPRODUSOCONS,   CDCSTDIG1PROD,   IDGERREVPROCFA,   IDTEMNUTRIENTE,   IDALTERANUTR,   IDTEOROBRIG,   IDVDANALISE,   IDPRODPERIG,   IDPRODDIMEVAR, DTULTATUPROD, DTULTATU)
      VALUES
         (P_CDARVPROD, V_CDPRODUTO, P_SGUNIDADE,  P_CDNVPRODUTO, P_NMPRODUTO, P_CDPRODESTO, P_VRFATOCONV, P_IDPRODATIVPR, P_IDSEGMENTOPROD, P_IDPRODUSOCONS, P_CDCSTDIG1PROD, P_IDGERREVPROCFA, P_IDTEMNUTRIENTE, P_IDALTERANUTR, P_IDTEOROBRIG, P_IDVDANALISE, P_IDPRODPERIG, P_IDPRODDIMEVAR, P_DTULTATUPROD, P_DTULTATU);
   END;
BEGIN
   --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 ULTIMO NIVEL
   SELECT LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL)) INTO V_LENGTHULTNIVEL FROM PARAMGERAL
   CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL
   ORDER BY ROWNUM DESC
   FETCH FIRST ROW ONLY;
   
   OPEN C_PRODUTO;
   LOOP
		    FETCH C_PRODUTO INTO V_CDPRODINT    ,
                           V_IDPRODESTO   ,
                           V_CDPRODESTOINT,
                           V_CDPRODUTO    ,
                           V_CDNVPRODUTO  ,
                           V_CDPRODESTO   ,
                           V_CDARVPRODINT ,
                           V_NMPRODUTO    ,
                           V_DSCOMPLNOME  ,
                           V_SGUNIDADE    ,
                           V_VRFATCORR    ,
                           V_VRFATOCONV   ,
                           V_IDPRODATIVO  ,
                           V_IDACAO       ,
                           V_LOG_ERRO     ,
                           V_DTGRAVACAO   ,
                           V_HRGRAVACAO   ,
                           V_DTIMPORTACAO ,
                           V_HRIMPORTACAO ,
                           V_ROWID,
                           V_CDBARPRODUTO;
			   EXIT WHEN C_PRODUTO%NOTFOUND;
      V_ERRO          := NULL;
      P_MENSAGEMERRO  := NULL;
      V_CDARVPROD := NULL;
      FOR C_NIVEL IN (SELECT A.NUMROW AS CDNVPRODUTO, A.CODE AS CODNIVEL,  --210401000000027
                     (SELECT LISTAGG(CODE)  WITHIN GROUP (ORDER BY NUMROW) AS CONCATE FROM (
                     SELECT ROWNUM AS NUMROW, DECODE(ROWNUM, 4, SUBSTR(REGEXP_SUBSTR(APLICA_MASCARA(V_CDPRODINT, 'AA.AA.AA.AAAAAAAAA'),'[^.]+', 1, LEVEL), 6, 9), REGEXP_SUBSTR(APLICA_MASCARA(V_CDPRODINT, 'AA.AA.AA.AAAAAAAAA'),'[^.]+', 1, LEVEL)) AS CODE
                     FROM DUAL
                     CONNECT BY REGEXP_SUBSTR(APLICA_MASCARA(V_CDPRODINT, 'AA.AA.AA.AAAAAAAAA'), '[^.]+', 1, LEVEL) IS NOT NULL) B
                     WHERE B.NUMROW <= A.NUMROW) AS CODCONCAT
                     FROM 
                     (SELECT ROWNUM AS NUMROW, REGEXP_SUBSTR(APLICA_MASCARA(V_CDPRODINT, 'AA.AA.AA.AAAAAAAAA'),'[^.]+', 1, LEVEL) AS CODE 
                     FROM DUAL
                     CONNECT BY REGEXP_SUBSTR(APLICA_MASCARA(V_CDPRODINT, 'AA.AA.AA.AAAAAAAAA'), '[^.]+', 1, LEVEL) IS NOT NULL) A) LOOP
                        
         V_CDARVPROD := C_NIVEL.CODCONCAT;
         V_QRY_DESCNIVEL := NULL;
         V_QRY_DESCNIVEL := 'SELECT DSNIVEL'||C_NIVEL.CDNVPRODUTO||' FROM USR_PRODUTO WHERE CDPRODINT = '||CHR(39)||V_CDPRODINT||CHR(39)||' AND DTIMPORTACAO IS NULL';
         OPEN C_DESC_NIVEL FOR V_QRY_DESCNIVEL; LOOP
            FETCH C_DESC_NIVEL INTO V_DESCNIVEL;--PEGANDO A DESCRICAO DO TIPO
           EXIT  WHEN C_DESC_NIVEL%NOTFOUND;
         END LOOP;
         
         IF C_NIVEL.CDNVPRODUTO < 4 THEN
            SELECT COUNT(CDPRODUTO)
              INTO V_EXISTE_NIVEL
              FROM PRODUTO
            WHERE CDARVPROD = V_CDARVPROD
               AND CDNVPRODUTO = C_NIVEL.CDNVPRODUTO;
               
            IF V_EXISTE_NIVEL = 0 THEN
               INSERT_PRODUTO(V_CDARVPROD, NULL, C_NIVEL.CDNVPRODUTO, V_DESCNIVEL, NULL, NULL, 'S', 'OUTR', 'N', '0', 'N', 'N', 'N', 'N', '2', 'N', 'N', SYSDATE, SYSDATE);
            ELSE
               BEGIN
                  UPDATE PRODUTO
                  SET NMPRODUTO =  V_DESCNIVEL,
                      DTULTATUPROD = SYSDATE,
                      DTULTATU     = SYSDATE
                  WHERE CDARVPROD =  V_CDARVPROD;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_ERRO         := 'Produto Sem nome ou descrição do nível';
                     P_MENSAGEMERRO := V_ERRO;
                     GOTO BREAK_STP;
               END;
            END IF;
         ELSE -- C_NIVEL.CDNVPRODUTO >= 4
            
            SELECT COUNT(CDPRODUTO) --CONFERE SE EXISTE O NIVEL 4
              INTO V_EXISTE_NIVEL4
              FROM PRODUTO
            WHERE CDARVPROD = V_CDARVPROD
               AND CDNVPRODUTO = 4;
            
            IF V_EXISTE_NIVEL4 = 0 THEN
               INSERT_PRODUTO(V_CDARVPROD, NULL, C_NIVEL.CDNVPRODUTO, V_DESCNIVEL, NULL, NULL, 'S', 'OUTR', 'N', '0', 'N', 'N', 'N', 'N', '2', 'N', 'N', SYSDATE, SYSDATE);
            ELSE
               UPDATE PRODUTO
               SET NMPRODUTO =  V_DESCNIVEL,
                   DTULTATUPROD = SYSDATE,
                   DTULTATU     = SYSDATE
               WHERE CDARVPROD =  V_CDARVPROD;
            END IF;
            
            BEGIN  --PRODUTO JÁ CADASTRADO
          	   SELECT CDPRODUTO, COUNT(CDPRODUTO) INTO V_CDPRODUTO, V_JACADASTRADO FROM PRODUTO WHERE CDPRODINTE = V_CDPRODINT GROUP BY CDPRODUTO;
          	EXCEPTION WHEN NO_DATA_FOUND THEN
               V_JACADASTRADO := 0;
               V_CDPRODUTO := NULL;
            END;
            
            IF V_JACADASTRADO > 0 THEN
               UPDATE PRODUTO
               SET NMPRODUTO  =  V_NMPRODUTO,
                   VRPEFATOCORR = ((V_VRFATCORR -1) * 100),
                   IDPRODATIVPR = V_IDPRODATIVO,
                   DSCOMPLNOME = V_DSCOMPLNOME,
                   CDBARPRODUTO = V_CDBARPRODUTO,
                   SGUNIDADE = V_SGUNIDADE,
                   DTULTATUPROD = SYSDATE,
                   DTULTATU     = SYSDATE
               WHERE CDPRODINTE =  V_CDPRODINT;
               
               OPEN C_FILIAL;
               LOOP
                  FETCH C_FILIAL INTO V_CDFILIAL, V_IDCTRLESTQ, V_IDUTILLOTE;
                  EXIT WHEN C_FILIAL%NOTFOUND;
                     UPDATE PRODFILI
                     SET IDPRODATIV = DECODE(V_IDPRODATIVO, 'S', 'A', 'N', 'I')
                     WHERE CDFILIAL  = V_CDFILIAL
                     AND	CDPRODUTO = (SELECT CDPRODUTO
                                          FROM PRODUTO
                                          WHERE CDPRODINTE = V_CDPRODINT);
               END LOOP;
               CLOSE C_FILIAL;
            ELSE -- V_JACADASTRADO > 0 THEN
               V_CDPRODUTO := '';
               NOVO_CODIGO_SC('PRODUTO',V_CDPRODUTO);
               V_CDPRODUTO := SUBSTR(V_CDPRODUTO,11,10);
               IF V_IDPRODESTO = 'EST' THEN
                  V_CDPRODESTO := V_CDPRODUTO;
               ELSE
                  BEGIN
                     SELECT CDPRODESTO
                     INTO V_CDPRODESTO
                     FROM PRODUTO
                     WHERE CDPRODINTE = V_CDPRODESTOINT;
                  EXCEPTION
                     WHEN OTHERS THEN
                        V_ERRO         := 'Produto de estoque não encontrado!';
                        P_MENSAGEMERRO := V_ERRO;
                        GOTO BREAK_STP;
                  END;
               END IF;
               
               --DESCOBRE SEQUENCIAL ARVPROD DO ULTIMO NIVEL.
               BEGIN --DESCOBRE PROXIMO SEQUENCIAL ARVPROD ultimo nivel.
   		    		SELECT CDARVPROD INTO V_ULTARVPROD FROM PRODUTO  
   					WHERE CDARVPROD LIKE V_CDARVPROD||'%'
   					AND CDNVPRODUTO = C_NIVEL.CDNVPRODUTO+1
   					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_NEWCDARVPROD := V_CDARVPROD||V_NEXTCDARVPROD;
               
               INSERT INTO PRODUTO
                 (CDARVPROD, CDPRODUTO, SGUNIDADE, CDNVPRODUTO, NMPRODUTO, CDPRODESTO,
                  VRFATOCONV, VRPEFATOCORR, IDPRODATIVPR, IDSEGMENTOPROD, IDPRODUSOCONS,
                  CDCSTDIG1PROD, IDGERREVPROCFA, IDTEMNUTRIENTE, IDALTERANUTR, IDTEOROBRIG,
                  IDVDANALISE, IDPRODPERIG, IDPRODDIMEVAR, DTULTATUPROD, DTULTATU, CDPRODINTE, DSCOMPLNOME,
                  CDBARPRODUTO)
               VALUES
                 (V_NEWCDARVPROD,  V_CDPRODUTO, V_SGUNIDADE,  C_NIVEL.CDNVPRODUTO+1,  V_NMPRODUTO, V_CDPRODESTO,
                  V_VRFATOCONV, ((V_VRFATCORR -1) * 100), V_IDPRODATIVO, 'OUTR', 'N',
                  '0', 'N', 'N', 'N', 'N',
                  '2', 'N', 'N', SYSDATE, SYSDATE, V_CDPRODINT, V_DSCOMPLNOME,
                  V_CDBARPRODUTO);
               
               OPEN C_FILIAL;
               LOOP
                  FETCH C_FILIAL INTO V_CDFILIAL, V_IDCTRLESTQ, V_IDUTILLOTE;
                  EXIT WHEN C_FILIAL%NOTFOUND;
                     INSERT INTO PRODFILI (CDFILIAL, CDPRODUTO, IDCNTRESTOQ, IDUTCTRLLOTE)
                     VALUES (V_CDFILIAL, V_CDPRODUTO, V_IDCTRLESTQ, V_IDUTILLOTE);
                 END LOOP;
               CLOSE C_FILIAL;
               
               UPDATE USR_PRODUTO
               SET CDPRODUTO  = V_CDPRODUTO,
                   CDPRODESTO = V_CDPRODESTO
               WHERE CDPRODINT  = V_CDPRODINT
               AND IDACAO     = 'INCLUSAO'
               AND ROWID      = V_ROWID;
               
            END IF; --V_JACADASTRADO > 0 THEN
            
         END IF; -- C_NIVEL.CDNVPRODUTO < 4

      END LOOP;--C_NIVEL
      <<BREAK_STP>>
      UPDATE USR_PRODUTO
	     	  SET DTIMPORTACAO = SYSDATE,
	     		     HRIMPORTACAO = TO_CHAR(SYSDATE, 'HH:MM'),
	     		     LOG_ERRO     = V_ERRO
	      WHERE CDPRODINT    = V_CDPRODINT
         AND DTIMPORTACAO IS NULL
         AND ROWID        = V_ROWID;
      COMMIT;
   END LOOP;
   CLOSE C_PRODUTO;
   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_IMPORTA_PRODUTO_DECIO','6.49.025'); END;
/
