CREATE OR REPLACE PROCEDURE REPROVA_WORKFLOW_EXPIRADOS
AS
   V_CONT NUMBER;
BEGIN
   FOR CUR_WORK IN
   (
    SELECT W.NRWORKFLOW 
      FROM WORKFLOW W
     WHERE W.IDSITWORKFLOW = '1'
       AND EXISTS (SELECT 1
                     FROM OPERWORKFLOW O
                    WHERE O.NRWORKFLOW = W.NRWORKFLOW
                      AND O.IDSITOPERWFLOW = 'P')
       AND NOT EXISTS(SELECT 1
                        FROM OPERWORKFLOW O
                       WHERE O.NRWORKFLOW = W.NRWORKFLOW
                         AND O.IDSITOPERWFLOW = 'P'
                         AND O.DTLIMITEWFLOW >= TRUNC(SYSDATE))        
   ) LOOP
      FOR CUR_OPER IN
      (
        SELECT CDOPERADOR, IDSITOPERWFLOW
          FROM OPERWORKFLOW
         WHERE NRWORKFLOW = CUR_WORK.NRWORKFLOW
      ) LOOP
         IF CUR_OPER.IDSITOPERWFLOW = 'P' THEN
            UPDATE OPERWORKFLOW
               SET IDSITOPERWFLOW = 'N',
                   DTACAOWFLOW = SYSDATE,
                   DSMOTIVOACAO = 'Encerramento automatico. Termino do prazo limite para aprovacao.'
             WHERE NRWORKFLOW = CUR_WORK.NRWORKFLOW
               AND CDOPERADOR = CUR_OPER.CDOPERADOR;
         END IF;

         UPDATE WORKFLOW
            SET IDSITWORKFLOW = '2'
          WHERE NRWORKFLOW = CUR_WORK.NRWORKFLOW;
      END LOOP;
   END LOOP;
   COMMIT;
END;
/

DECLARE
  V_JOB NUMBER;
BEGIN
  FOR C_JOBS IN (SELECT JOB
                   FROM ALL_JOBS
                  WHERE WHAT LIKE '%REPROVA_WORKFLOW_EXPIRADOS%') LOOP
    DBMS_JOB.REMOVE(C_JOBS.JOB);
  END LOOP;

  DBMS_JOB.SUBMIT(V_JOB, WHAT => 'BEGIN REPROVA_WORKFLOW_EXPIRADOS; END;', 
  NEXT_DATE => TRUNC(SYSDATE + 1), 
  INTERVAL => 'TRUNC(SYSDATE + 1)');
  COMMIT;
END; 
/