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 automático. Término do prazo limite para aprovação.'
             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_EXISTS NUMBER;
BEGIN
   -- Mantido para apagar resquícios do agendamento anterior
   FOR C_JOBS IN (SELECT JOB
                   FROM ALL_JOBS
                  WHERE WHAT LIKE '%REPROVA_WORKFLOW_EXPIRADOS%') LOOP
      DBMS_JOB.REMOVE(C_JOBS.JOB);
   END LOOP;

   SELECT COUNT(*) INTO V_JOB_EXISTS
     FROM USER_SCHEDULER_JOBS
    WHERE JOB_NAME = 'JOB_REPROVA_WORKFLOW_EXPIRADOS';

   IF V_JOB_EXISTS > 0 THEN
      DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_REPROVA_WORKFLOW_EXPIRADOS');
   END IF;

   DBMS_SCHEDULER.CREATE_JOB (
      job_name => 'JOB_REPROVA_WORKFLOW_EXPIRADOS',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN REPROVA_WORKFLOW_EXPIRADOS(); END;',
      start_date => SYSDATE,
      repeat_interval => 'FREQ=DAILY;BYHOUR=3',
      end_date => NULL,
      enabled => TRUE,
      comments => 'Job criado para fechamento de fluxos de aprovação expirados'
   );
   COMMIT;
END; 
/