create or replace PROCEDURE USR_IMPORTA_PRODUTO(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);
   --ESPECIFICO TABELA INT
   V_ERRO 			      VARCHAR2(5000);
   --VARIAVEIS LOCAIS MUTAVEIS
   V_CDFILIAL 		  VARCHAR2(4);
   V_IDCTRLESTQ 	 VARCHAR2(1);
   V_IDUTILLOTE 	 VARCHAR2(1);
   V_PRODFILIATIV VARCHAR2(1);
   V_EXISTE_NIVEL 		  NUMBER;
   V_EXISTEPRODFILI	  NUMBER;
   V_JACADASTRADO     NUMBER;
   V_ROWID            RowId;

   V_NVCOMP       INTEGER;
   V_TAMPRINV     INTEGER;

   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
        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
   SELECT LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+'))
     INTO V_TAMPRINV
     FROM PARAMGERAL;

   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;
			   EXIT WHEN C_PRODUTO%NOTFOUND;
      V_ERRO          := NULL;
      P_MENSAGEMERRO  := NULL;
      V_PRODFILIATIV  := 'A';

      V_NVCOMP := V_TAMPRINV;
      V_CDARVPROD := NULL;

      FOR C_NIVEL IN (SELECT ROWNUM CDNVPRODUTO, LENGTH(REGEXP_SUBSTR(CDPICTPROD,'[^.]+', 1, LEVEL)) AS TAMNVPRODUTO,
                             (SELECT MAX(ROWNUM)
                                FROM PARAMGERAL
                               CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL) AS ULTIMONVPRODUTO
                        FROM PARAMGERAL
                        CONNECT BY REGEXP_SUBSTR(CDPICTPROD, '[^.]+', 1, LEVEL) IS NOT NULL) LOOP

         IF (V_NVCOMP <= LENGTH(V_CDARVPRODINT)) OR (C_NIVEL.CDNVPRODUTO = (C_NIVEL.ULTIMONVPRODUTO - 1)) THEN

            IF C_NIVEL.CDNVPRODUTO < C_NIVEL.ULTIMONVPRODUTO THEN
               V_CDARVPROD := V_CDARVPROD||SUBSTR(V_CDARVPRODINT, V_NVCOMP, C_NIVEL.TAMNVPRODUTO);

               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;

               SELECT COUNT(CDPRODUTO)
                 INTO V_EXISTE_NIVEL
                 FROM PRODUTO
                WHERE CDARVPROD = V_CDARVPROD
                  AND CDNVPRODUTO = C_NIVEL.CDNVPRODUTO;
            END IF;

            V_NVCOMP := V_NVCOMP + C_NIVEL.TAMNVPRODUTO;

            SELECT COUNT(PRODFILI.CDPRODUTO)
              INTO V_EXISTEPRODFILI
              FROM PRODFILI, PRODUTO
             WHERE PRODUTO.CDPRODINTE = V_CDPRODINT
               AND PRODUTO.CDPRODUTO = PRODFILI.CDPRODUTO;

            SELECT COUNT(CDPRODUTO)
              INTO V_JACADASTRADO
              FROM PRODUTO
             WHERE CDPRODINTE = V_CDPRODINT;

            IF (V_IDPRODATIVO = 'N') THEN
               V_PRODFILIATIV  := 'I';
            END IF;

            IF(V_JACADASTRADO > 0) THEN
               V_IDACAO := 'ALTERACAO';
            ELSE
               V_IDACAO := 'INCLUSAO';    
            END IF;

            IF (V_IDACAO = 'INCLUSAO') THEN
               BEGIN
                  CASE WHEN V_EXISTE_NIVEL = 0
                       THEN INSERT_PRODUTO(V_CDARVPROD, V_SGUNIDADE, C_NIVEL.CDNVPRODUTO, V_DESCNIVEL, NULL, V_VRFATOCONV, 'S', 'OUTR', 'N', '0', 'N', 'N', 'N', 'N', '2', 'N', 'N', SYSDATE, SYSDATE);
                       ELSE V_EXISTE_NIVEL := NULL;
                  END CASE;

                  V_EXISTE_NIVEL := NULL;

                  IF C_NIVEL.CDNVPRODUTO = C_NIVEL.ULTIMONVPRODUTO 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;

                        IF (V_NVCOMP - V_TAMPRINV) = LENGTH(V_CDARVPRODINT) THEN
                           V_CDARVPROD := V_CDARVPRODINT;
                        ELSE
                           V_CDARVPROD := V_CDARVPROD || '00';
                        END IF;
                     ELSE
                        BEGIN
                        SELECT CDPRODESTO
                          INTO V_CDPRODESTO
                          FROM PRODUTO
                         WHERE CDPRODINTE = V_CDPRODESTOINT;
                        EXCEPTION
                           WHEN OTHERS THEN
                              V_ERRO         := 'Produto de estoque nao encontrado!';
                              P_MENSAGEMERRO := V_ERRO;
                        END;

                        IF V_ERRO IS NOT NULL THEN
                           GOTO BREAK_STP;
                        END IF;

                        IF (V_NVCOMP - V_TAMPRINV) = LENGTH(V_CDARVPRODINT) THEN
                           V_CDARVPROD := V_CDARVPRODINT;
                        ELSE
                           V_CDARVPROD := V_CDARVPROD || '01';
                        END IF;
                     END IF;

                     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)
                     VALUES
                       (V_CDARVPROD,  V_CDPRODUTO, V_SGUNIDADE,  C_NIVEL.CDNVPRODUTO,  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_CDARVPROD := '';

                     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;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_ERRO         := SQLERRM(SQLCODE);
                     P_MENSAGEMERRO := V_ERRO;
               END;
            END IF;

            IF (V_IDACAO = 'ALTERACAO') THEN
               BEGIN
                  IF C_NIVEL.CDNVPRODUTO = C_NIVEL.ULTIMONVPRODUTO THEN
                     UPDATE PRODUTO
                        SET NMPRODUTO  =  V_NMPRODUTO,
                            VRPEFATOCORR = ((V_VRFATCORR -1) * 100),
                            IDPRODATIVPR = V_IDPRODATIVO,
                            DSCOMPLNOME = V_DSCOMPLNOME,
                            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 = V_PRODFILIATIV
                        WHERE CDFILIAL  = V_CDFILIAL
                          AND	CDPRODUTO = (SELECT CDPRODUTO
                                             FROM PRODUTO
                                            WHERE CDPRODINTE = V_CDPRODINT);
                     END LOOP;
                     CLOSE C_FILIAL;
                  ELSE
                     UPDATE PRODUTO
                        SET NMPRODUTO =  V_DESCNIVEL,
                            DTULTATUPROD = SYSDATE,
                            DTULTATU     = SYSDATE
                      WHERE CDARVPROD =  V_CDARVPROD;
                  END IF;

                  UPDATE USR_PRODUTO
                     SET CDPRODUTO  = V_CDPRODUTO,
                         CDPRODESTO = V_CDPRODESTO
                   WHERE CDPRODINT  = V_CDPRODINT
                     AND IDACAO     = 'ALTERACAO'
                     AND ROWID      = V_ROWID;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_ERRO         := SQLERRM(SQLCODE);
                     P_MENSAGEMERRO := V_ERRO;
               END;
            END IF;

            IF (V_IDACAO = 'EXCLUSAO') THEN
               BEGIN
                  UPDATE PRODUTO
                     SET IDPRODATIVPR = 'N',
                         DTULTATUPROD = SYSDATE,
                         DTULTATU     = SYSDATE
                   WHERE CDPRODUTO = (SELECT CDPRODUTO
                                        FROM PRODUTO
                                       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 = V_PRODFILIATIV
                     WHERE CDFILIAL  = V_CDFILIAL
                       AND	CDPRODUTO = (SELECT CDPRODUTO
                                          FROM PRODUTO
                                         WHERE CDPRODINTE = V_CDPRODINT);
                  END LOOP;
                  CLOSE C_FILIAL;

                  UPDATE USR_PRODUTO
                     SET CDPRODUTO    = V_CDPRODUTO,
                         CDPRODESTO   = V_CDPRODESTO
                   WHERE CDPRODINT    = V_CDPRODINT
                     AND IDACAO     = 'EXCLUSAO'
                     AND ROWID      = V_ROWID;
               EXCEPTION
                  WHEN OTHERS THEN
                     V_ERRO         := SQLERRM(SQLCODE);
                     P_MENSAGEMERRO := V_ERRO;
               END;
            END IF;
         END IF;
      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;