CREATE OR REPLACE PACKAGE PKG_ION_JOBS AS
  FUNCTION F_ION_JOB_LIST RETURN ION_JOBS PIPELINED;
  PROCEDURE P_CREATE_JOBS;
  PROCEDURE P_CREATE_JOB(JB_NAME VARCHAR2, P_NAME VARCHAR2, JB_INTERVAL VARCHAR2);
END;

/

CREATE OR REPLACE PACKAGE BODY PKG_ION_JOBS AS
  FUNCTION F_ION_JOB_LIST RETURN ION_JOBS PIPELINED AS
    V_DEFAULT_INTERVAL VARCHAR2(1000) := 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=01; BYMINUTE=0;';
  BEGIN
    PIPE ROW(ION_JOB('JB_ION_SYNC_CONFIGURACOES','IONV_SYNC.IONV_MERGE.SYNC_CONFIGURACOES',V_DEFAULT_INTERVAL));
    PIPE ROW(ION_JOB('JB_ION_SYNC_PRECOS','IONV_SYNC.IONV_MERGE.SYNC_PRECOS',V_DEFAULT_INTERVAL));
    PIPE ROW(ION_JOB('JB_ION_SYNC_PRODUTOS','IONV_SYNC.IONV_MERGE.SYNC_PRODUTOS',V_DEFAULT_INTERVAL));
    PIPE ROW(ION_JOB('JB_ION_SYNC_CLIENTES','IONV_SYNC.IONV_MERGE.SYNC_CLIENTES',V_DEFAULT_INTERVAL));
    PIPE ROW(ION_JOB('JB_ION_SYNC_HISTORICO','IONV_SYNC.IONV_MERGE.SYNC_HISTORICO',V_DEFAULT_INTERVAL));
    RETURN;
  END F_ION_JOB_LIST;

  PROCEDURE P_CREATE_JOBS AS
  BEGIN
    FOR C1 IN (SELECT * FROM TABLE(F_ION_JOB_LIST)) LOOP
      DBMS_OUTPUT.put_line(C1.JOB_NAME);
      P_CREATE_JOB(C1.JOB_NAME, C1.PROC_NAME, C1.JOB_INTERVAL);
    END LOOP;
  END P_CREATE_JOBS;

  PROCEDURE P_CREATE_JOB(JB_NAME VARCHAR2, P_NAME VARCHAR2, JB_INTERVAL VARCHAR2) AS
  BEGIN
    BEGIN
      SYS.DBMS_SCHEDULER.DROP_JOB
        (JOB_NAME  => JB_NAME);
      EXCEPTION
        --WILL FAIL ON FIRST EXECUTION
        WHEN OTHERS THEN
          NULL;
    END;

    SYS.DBMS_SCHEDULER.CREATE_JOB
      (
         JOB_NAME        => JB_NAME
        ,START_DATE      => TO_TIMESTAMP_TZ('2021/07/08 14:00:00.000000 -03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
        ,REPEAT_INTERVAL => JB_INTERVAL
        ,END_DATE        => NULL
        ,JOB_CLASS       => 'DEFAULT_JOB_CLASS'
        ,JOB_TYPE        => 'STORED_PROCEDURE'
        ,JOB_ACTION      => P_NAME
        ,COMMENTS        => 'Importa pedidos feitos fora do horário com o ION Vendas'
      );
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'RESTARTABLE'
       ,VALUE     => FALSE);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'LOGGING_LEVEL'
       ,VALUE     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'MAX_FAILURES');
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'MAX_RUNS');
    BEGIN
      SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
        ( NAME      => JB_NAME
         ,ATTRIBUTE => 'STOP_ON_WINDOW_CLOSE'
         ,VALUE     => FALSE);
    EXCEPTION
      -- COULD FAIL IF PROGRAM IS OF TYPE EXECUTABLE...
      WHEN OTHERS THEN
        NULL;
    END;
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'JOB_PRIORITY'
       ,VALUE     => 3);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'SCHEDULE_LIMIT');
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( NAME      => JB_NAME
       ,ATTRIBUTE => 'AUTO_DROP'
       ,VALUE     => TRUE);
    SYS.DBMS_SCHEDULER.ENABLE
      (NAME  => JB_NAME);
  END P_CREATE_JOB;
END;