Notes

create or replace PROCEDURE "IMPORT_JOB" (
p_recordset OUT SYS_REFCURSOR)
AS

monthly_import NUMBER(6,0);
daily_import NUMBER(6,0);
manual_monthly_import NUMBER(6,0);

BEGIN

SAVEPOINT job_startPoint;

-- check if that should be monthly import
SELECT count(*) INTO monthly_import FROM IMPORT_PUB_CALENDAR WHERE YEAR_MONTH = (SELECT TO_CHAR (SYSDATE, 'YYYYMM') FROM DUAL) AND IMPORT_DAY = (SELECT TO_CHAR (SYSDATE, 'DD') FROM DUAL) AND IMPORT_STATUS = 0;

-- check if that should be daily import
SELECT count(*) INTO daily_import FROM APP_SETTINGS WHERE LAST_DAILY_IMPORT <= (SELECT TO_DATE (SYSDATE, 'DD-MON-YY') FROM DUAL) AND DAILY_IMPORT_HOUR <= (SELECT TO_CHAR (SYSDATE, 'HH24') FROM DUAL) AND IMPORT_RUNNING = 0;

-- check if that should be manual monthly import
SELECT count(*) INTO manual_monthly_import FROM CRON WHERE STATUS = 0;

IF monthly_import=1 THEN

dbms_output.put_line('MONTHLY IMPORT START');
UPDATE IMPORT_PUB_CALENDAR SET IMPORT_STATUS = 3 WHERE YEAR_MONTH = (SELECT TO_CHAR (SYSDATE, 'YYYYMM') FROM DUAL) AND IMPORT_DAY = (SELECT TO_CHAR (SYSDATE, 'DD') FROM DUAL);
-- run procedure
monthly_refresh;

UPDATE IMPORT_PUB_CALENDAR SET IMPORT_STATUS = 1 WHERE YEAR_MONTH = (SELECT TO_CHAR (SYSDATE, 'YYYYMM') FROM DUAL) AND IMPORT_DAY = (SELECT TO_CHAR (SYSDATE, 'DD') FROM DUAL);
dbms_output.put_line('-FINISHED');

import_send_mail(
p_subject => 'Monthly Import Complete',
p_message => 'Import completed successfully and logged in Oracle'
);

ELSIF daily_import=1 THEN

dbms_output.put_line('DAILY IMPORT START');
UPDATE APP_SETTINGS SET IMPORT_RUNNING = 1;
-- run procedure
daily_refresh;

UPDATE APP_SETTINGS SET IMPORT_RUNNING = 0, LAST_DAILY_IMPORT = (SELECT TO_CHAR (SYSDATE + 1, 'DD-MON-YY') FROM DUAL) WHERE IMPORT_RUNNING = 1;
dbms_output.put_line('-FINISH');

import_send_mail(
p_subject => 'Daily Import Complete',
p_message => 'Import completed successfully and logged in Oracle'
);

ELSIF manual_monthly_import=1 THEN

dbms_output.put_line('MANUAL IMPORT START');
UPDATE CRON SET STATUS = 3 WHERE STATUS = 0;
-- run procedure
dbms_output.put_line('-MONTHLY-');
monthly_refresh;

dbms_output.put_line('-DAILY-');
daily_refresh;

UPDATE CRON SET STATUS = 1, END_TIME = systimestamp, MESSAGE = 'The Ad-Hoc import completed successfully (db).' WHERE STATUS = 3;
dbms_output.put_line('-FINISH');

import_send_mail(
p_subject => 'Manual Import Complete',
p_message => 'Import completed successfully and logged in Oracle'
);

ELSE
dbms_output.put_line('nothing to do ...');
END IF;

EXCEPTION
WHEN OTHERS THEN ROLLBACK TO job_startPoint;
import_send_mail(
p_subject => 'Data import error!',
p_message => 'Import error and logged in Oracle : '||SQLERRM
);
RAISE;
END;