SQL Query to Extract ESS jobs submitted in Oracle Cloud: ESS Request Tables in Oracle Fusion

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)