CREATE OR REPLACE PROCEDURE ATUALIZA_PRODFILI_BKS
AS
  V_DSPARTHREAD  VARCHAR2(20);
  V_DSVALPTHREAD VARCHAR2(200);

  V_IDSTATUS     VARCHAR2(1);
  V_PRODUTO      VARCHAR2(10);
  V_NVPRODUTO    VARCHAR2(1);
  V_IDCNTRESTOQ  VARCHAR2(1);

  V_CLOB         CLOB;

  /*Função utilizada para converter BLOB para CLOB*/
  PROCEDURE BLOBTOCLOB(P_BLOB IN BLOB,
                       P_CLOB OUT CLOB)

  AS
    V_FILE_CLOB    CLOB;
    V_FILE_SIZE    INTEGER := DBMS_LOB.LOBMAXSIZE;
    V_DEST_OFFSET  INTEGER := 1;
    V_SRC_OFFSET   INTEGER := 1;
    V_BLOB_CSID    NUMBER  := DBMS_LOB.DEFAULT_CSID;
    V_LANG_CONTEXT NUMBER  := DBMS_LOB.DEFAULT_LANG_CTX;
    V_WARNING      INTEGER;
    V_LENGTH       NUMBER;
  BEGIN
    IF NVL(LENGTH(P_BLOB), 0) > 0 THEN
      DBMS_LOB.CREATETEMPORARY(V_FILE_CLOB, TRUE);
      DBMS_LOB.CONVERTTOCLOB(V_FILE_CLOB,
                             P_BLOB,
                             V_FILE_SIZE,
                             V_DEST_OFFSET,
                             V_SRC_OFFSET,
                             V_BLOB_CSID,
                             V_LANG_CONTEXT,
                             V_WARNING);
      P_CLOB := V_FILE_CLOB;
    ELSE
      P_CLOB := '';
    END IF;
  END;
BEGIN
  FOR C_BKGTHREAD IN (  SELECT BKGTHREAD.IDTHREAD
                          FROM BKGTHREAD
                         WHERE BKGTHREAD.IDTIPOTHREAD        = '1'
                           AND BKGTHREAD.IDSTATTHREAD        = '4'
                           AND BKGTHREAD.DSNOBJTHREAD        = 'ATUALIZA_PRODFILI'
                           AND TRUNC(BKGTHREAD.DTINCLTHREAD) = TRUNC(SYSDATE))
  LOOP
    /* Thread >>>> Executando */
    UPDATE BKGTHREAD
       SET IDSTATTHREAD = '1'
     WHERE IDTHREAD = C_BKGTHREAD.IDTHREAD;
    COMMIT;

    V_IDSTATUS    := NULL;
    V_PRODUTO     := NULL;
    V_NVPRODUTO   := NULL;
    V_IDCNTRESTOQ := NULL;
    FOR C_BKGTHREADPARAM IN ( SELECT BKGTHREADPARAM.DSVALPTHREAD,
                                     BKGTHREADPARAM.DSPARTHREAD
                                FROM BKGTHREADPARAM
                               WHERE /*NVL(LENGTH(BKGTHREADPARAM.DSVALPTHREAD), 0) > 0
                                  AND*/ BKGTHREADPARAM.IDTHREAD = C_BKGTHREAD.IDTHREAD)
    LOOP
      BLOBTOCLOB(C_BKGTHREADPARAM.DSVALPTHREAD, V_CLOB);
      IF V_CLOB <> 'ATUALIZA_PRODFILI' THEN
        IF C_BKGTHREADPARAM.DSPARTHREAD = 'P_IDSTATUS' THEN
          V_IDSTATUS := V_CLOB;
        ELSIF C_BKGTHREADPARAM.DSPARTHREAD = 'P_PRODUTO' THEN
          V_PRODUTO := V_CLOB;
        ELSIF C_BKGTHREADPARAM.DSPARTHREAD = 'P_NVPRODUTO' THEN
          V_NVPRODUTO := V_CLOB;
        ELSIF C_BKGTHREADPARAM.DSPARTHREAD = 'P_IDCNTRESTOQ' THEN
          V_IDCNTRESTOQ := V_CLOB;
        END IF;
      END IF;
    END LOOP;
    IF ((V_IDSTATUS IS NOT NULL) AND (V_PRODUTO IS NOT NULL) AND (V_NVPRODUTO IS NOT NULL)) THEN
      BEGIN
        ATUALIZA_PRODFILI(V_IDSTATUS, V_PRODUTO, V_NVPRODUTO, NVL(V_IDCNTRESTOQ, 'N'));
      EXCEPTION
        WHEN OTHERS THEN
          /* Thread >>>> Fila */
          UPDATE BKGTHREAD
             SET IDSTATTHREAD = '4' /*Retorna para a Fila*/
           WHERE IDTHREAD = C_BKGTHREAD.IDTHREAD;
          COMMIT;
      END;
      /* Thread >>>> Terminado */
      UPDATE BKGTHREAD
         SET IDSTATTHREAD = '2',
             DTTERMTHREAD = SYSDATE
       WHERE IDTHREAD = C_BKGTHREAD.IDTHREAD;
      COMMIT;
    END IF;
  END LOOP;
END;
/

DECLARE
   L_JOB_EXISTS NUMBER;
BEGIN
   SELECT COUNT(*) INTO L_JOB_EXISTS
     FROM USER_SCHEDULER_JOBS
    WHERE JOB_NAME = 'JOB_PRODFILI';
   IF L_JOB_EXISTS = 1 THEN
      DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_PRODFILI');
   END IF;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   JOB_NAME    =>  'JOB_PRODFILI',
   JOB_TYPE               =>  'PLSQL_BLOCK',
   JOB_ACTION   =>  'BEGIN ATUALIZA_PRODFILI_BKS; 
                                commit; END;',
   START_DATE   =>  systimestamp,
   repeat_interval => 'FREQ=MINUTELY;INTERVAL=5', 
   JOB_CLASS              =>  '"DEFAULT_JOB_CLASS"',
   ENABLED => TRUE,
   AUTO_DROP => FALSE,
   COMMENTS   =>  '');
END;
/