Query to find out ESS job history for a report

When a report is run throough ESS or when we run it by clicking Schedule option, then below query can be used to find out the job history.


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,
         erh.*
    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/Financials/XX AP Payment Information Email Notification Report.xdo'
ORDER BY erh.requestid DESC