In this article we will look into how to get the Scheduled process job history from their underlying tables.
Scheduled Processes information is stored in the below tables and views:
Views
select * from FUSION.ESS_REQUEST_HISTORY
select * from FUSION.ESS_REQUEST_PROPERTY
Tables
select * from FUSION.REQUEST_HISTORY
select * from FUSION.REQUEST_PROPERTY
Table of Contents
SQL Query to get BI Report History (Works only for scheduled runs):
SELECT (CASE
WHEN state = 1 THEN 'Wait'
WHEN state = 2 THEN 'Ready'
WHEN state = 3 THEN 'Running'
WHEN state = 4 THEN 'Completed'
WHEN state = 9 THEN 'Cancelled'
WHEN state = 10 THEN 'Error'
WHEN state = 12 THEN 'Succeeded'
WHEN state = 13 THEN 'Paused'
ELSE TO_CHAR (state)
END)
REQUEST_STATE,
erp.value Report_Path,
erh.SUBMITTER,
erh.EXECUTABLE_STATUS,
to_char(PROCESSSTART,'MM/DD/YYYY') PROCESSSTART,
to_char(PROCESSEND,'MM/DD/YYYY') PROCESSEND,
erh.REQUESTID
FROM fusion.ess_request_history erh, fusion.ess_request_property erp
WHERE erh.requestid = erp.requestid
AND erp.name = 'report_url'
--and erp.value like '%Job Report%'
--AND erp.VALUE LIKE '/Custom/Human Capital Management/HCM Integration Report.xdo'
ORDER BY erh.requestid DESC
This query returns all scheduled report runs along with the user who ran it and the start and end dates.
If you want to check the status of specific process:
select * from request_history
where definition like '%LoadBatchFromFile%'
order by PROCESSSTART desc