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

   SELECT COUNT(*) INTO L_JOB_EXISTS
   FROM USER_SCHEDULER_JOBS
   WHERE JOB_NAME = 'JOB_ETL_PLADIA';

   IF L_JOB_EXISTS = 1 THEN
       DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_ETL_PLADIA');
   END IF;

   BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
         JOB_NAME => 'JOB_ETL_PLADIA',
         JOB_TYPE => 'PLSQL_BLOCK',
         JOB_ACTION => 'BEGIN ETL_PLADIA(TRUNC(SYSDATE-90), TRUNC(SYSDATE+120) ); END;',
         START_DATE => SYSDATE,
         REPEAT_INTERVAL => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=30',
         JOB_CLASS => '"DEFAULT_JOB_CLASS"',
         ENABLED => TRUE,
         AUTO_DROP => FALSE,
         COMMENTS => 'JOB PARA PREENCHER A TABELA DM_PLADIARIO');
   END;
   COMMIT;
END;
/
