/*******************************************************
*PURPOSE: SQL Query to find BIP Report Jobs History *
*AUTHOR: Shailender Thallam *
*******************************************************/
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/Procurement/XX Order Confirmation Report.xdo'
--AND erh.requestid = 140974
ORDER BY erh.requestid DESC;