SQL Query to Extract ESS jobs submitted in Oracle Cloud: ESS Request Tables in Oracle Fusion
In this post , We are going to discuss about the ESS jobs. As we know , in Oracle apps we uses the Concurrent requests to run any report , procedure in oracle applications. We do register concurrent request as an concurrent programs then we runs these concurrent program as a requests. In the same way in Oracle Cloud/Fusion , We have the ESS programs like concurrent program and we runs these ESS programs as a ESS jobs. We do runs these programs from Schedules process. If we want to extract all the ESS jobs history for an particular ESS program then we can use this below SQL script or query. This below SQL query helps to extract all the submitted ESS jobs in oracle cloud application.
ESS Request/Jobs Tables in Oracle Fusion
1.fusion.ess_request_history
2.fusion.ess_request_property
Detail SQL Query to Extract ESS jobs submitted in Oracle Cloud
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, FROM fusion.ess_request_history erh, fusion.ess_request_property
erp
WHERE
1 = 1
AND erh.requestid = erp.requestid
AND erp.name = 'report_url'
AND erp.VALUE LIKE '/Custom/XXTEST/XX Purchase Report.xdo'
AND erh.requestid =NVL(:P_REQUEST_ID, erh.requestid)
