SQL Query to get Scheduled Process Job History
SQL Query to get Scheduled Process Job History

 1,091 total views

In this article we will look into how to get the Scheduled process job history from their underlying tables.

Want to learn Fusion Technical tools? Checkout this article

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

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

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply