In this post you’ll see query to get submitted ESS job details in Oracle Fusion Cloud, this will help you to extract complete information of ESS job with following columns like requested person , template name, request date, submitter, status
SELECT ERH.REQUESTID
,(SELECT template_name
FROM ap_payment_templates
WHERE template_id = ERP1.VALUE
) Template_name
,erh.requestedstart
,erh.SUBMISSION
,erh.SCHEDULED
,erh.submitter
,erh.executable_status
FROM ess_request_history erh
,ess_request_property erp1
WHERE ERH.requestid = ERP1.requestid
AND ERP1.name = ‘submit.argument2’
— AND erh.executable_status IS NULL
AND ERH.DEFINITION = ‘JobDefinition://oracle/apps/ess/financials/payables/payments/AutoSelect’
–AND erh.SUBMISSION LIKE ‘2018-02-01%’
–AND erh.SCHEDULED LIKE ‘2018-02-08%’
Query to get ESS Job request status in Oracle Fusion
SELECT erh.requestid requestid
,TO_CHAR(ERH.requestedstart,’DD-MM-YYYY:HH24:MI:SS PM’) requestedstart
,(
SELECT TO_CHAR(erh1.scheduled,’DD-MM-YYYY:HH24:MI:SS PM’)
FROM fusion.ess_request_history erh1
WHERE erh1.absparentid=erh.absparentid
AND erh1.state = 1
AND scheduled IS NOT NULL
AND ROWNUM=1
) daily_scheduled_time
,TO_CHAR(erh.completedtime,’DD-MM-YYYY:HH24:MI:SS PM’) current_request_completedtime
,TO_CHAR(erh.requestedend,’DD-MM-YYYY:HH24:MI:SS PM’) requestedend
,(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’
WHEN state = 14 THEN ‘Pending validation’
WHEN state = 15 THEN ‘Validation failed’
WHEN state = 16 THEN ‘Scheduled Ended’
WHEN state = 17 THEN ‘Finished’
WHEN state = 18 THEN ‘Error Auto-Retry’
WHEN state = 19 THEN ‘Error Manual Recovery’
ELSE TO_CHAR (state)
END)
request_state, erh.username
/* ,(SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’username’) username1 */
,(SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’jobid’) jobid
,nvl((SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’ujobname’),nvl(CASE WHEN erh.NAME IS NULL THEN (
SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’CustomDatacontrol’)
else
(SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND upper(NAME)=upper(‘jobDefinitionName’))
END,substr(erh.DEFINITION,instr(erh.DEFINITION,’/’,-1)+1))) ujobname
,(SELECT substr(VALUE,instr(VALUE,’/’,-1)+1) FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’report_url’) report_NAME
,(SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’report_url’) report_path
,nvl((SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’report_params’),(select
listagg (value, ‘,’)
WITHIN GROUP
(ORDER BY value) value1
FROM
REQUEST_property rp4
where rp4.REQUESTID = erh.requestid
and rp4.name like ‘%attributeName%’
GROUP BY
REQUESTID)) report_params
,nvl((SELECT VALUE FROM fusion_ora_ess.request_property rp4
WHERE rp4.REQUESTID = erh.requestid
AND NAME=’report_param_displays’),(select
listagg (value, ‘,’)
WITHIN GROUP
(ORDER BY value) value1
FROM
REQUEST_property rp4
where rp4.REQUESTID = erh.requestid
and rp4.name like ‘%attributeValue%’
GROUP BY
REQUESTID)) report_param_displays
,(
SELECT substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘TEMPLATE’,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘TEMPLATE’,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘TEMPLATE’,1,1,1,’i’)+1))
FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’
) TEMPLATE
,(select substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘DEL_CHANNEL’,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘DEL_CHANNEL’,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘DEL_CHANNEL’,1,1,1,’i’)+1))
FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’
) DEL_CHANNEL
,(SELECT substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘OUTPUT_NAME’,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘OUTPUT_NAME’,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘OUTPUT_NAME’,1,1,1,’i’)+1))
FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’) OUTPUT_NAME
, (SELECT substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘OUTPUT_FORMAT’,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘OUTPUT_FORMAT’,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘OUTPUT_FORMAT’,1,1,1,’i’)+1))
FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’) OUTPUT_FORMAT
,( SELECT substr(rp1.VALUE,regexp_instr (rp1.VALUE, ‘PARAMETER2′,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘PARAMETER2′,1,2,0,’i’)-2)-(regexp_instr (rp1.VALUE, ‘PARAMETER2′,1,1,1,’i’)+1))
FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’) cc_email_address
,(SELECT (CASE WHEN (substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘DEL_CHANNEL’,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘DEL_CHANNEL’,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘DEL_CHANNEL’,1,1,1,’i’)+1)))=’EMAIL’ THEN substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘PARAMETER1′,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘PARAMETER1′,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘PARAMETER1′,1,1,1,’i’)+1)) ELSE NULL END) FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’)
TO_email_address
,(SELECT substr(rp1.VALUE,REGEXP_INSTR (rp1.VALUE, ‘PARAMETER3′,1,1,1,’i’)+1,(REGEXP_INSTR (rp1.VALUE, ‘PARAMETER3′,1,2,0,’i’)-2)-(REGEXP_INSTR (rp1.VALUE, ‘PARAMETER3′,1,1,1,’i’)+1)) from_email_address
FROM fusion_ora_ess.request_property rp1
WHERE rp1.requestid=erh.requestid
AND rp1.NAME=’CONTROL_XML’) SUBMITTER
FROM FUSION_ORA_ESS.REQUEST_HISTORY erh
where state=1
—and erh.username=’ABCD’
–AND erh.requestid=12345