You are currently viewing SQL Query to get Scheduled Process Job History
SQL Query to get Scheduled Process Job History

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