SQL Query to get scheduled process details in Oracle Fusion

 SQL Query to get scheduled process details in Oracle Fusion

Hi friends , we are going to discuss about the SQL Query to get scheduled process details in Oracle Fusion. We will share the detail sql query which helps to extract the complete schedule process details in oracle fusion. Using this query , you will be able to get the complete details including user name who submitted the schedule process as well other important job details too in oracle fusion. In Oracle Fusion , we go call schedule process as a ESS jobs. All ESS job details we do run or submit as a schedule process i oracle fusion. In this post , We will share the schedule process tables too which helps to store the complete schedule process details in oracle fusion. Using these schedule process tables you can develop the custom BIP report which helps to extract the complete schedule process information's in oracle fusion. Please find below the complete detail about SQL Query to get scheduled process details in Oracle Fusion.

2 Important tables about scheduled process details in Oracle Fusion

1.ESS_REQUEST_HISTORY

2.SS_REQUEST_PROPERTY

SQL Query to get scheduled process details in Oracle Fusion

Detail SQL Query to get scheduled process details in Oracle Fusion

SELECT 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,

(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)

             erp.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)

ORDER BY erh.requestid DESC

Second Important Query:-

select * from request_history

where definition like '%LoadBatchFromFile%'

order by PROCESSSTART desc