Introduction:
This blog has ESS Job History Query that can be used to retrieve the ESS Job historical information in Oracle Cloud application.
Cause of the issue:
Business wants to retrieve the ESS Job historical information for Report ESS Job to identify jobs with cancelled status in Oracle Cloud Application.
How do we solve:
We have created below query to retrieve the ESS Job historical information for respective Report ESS Job in Oracle Cloud Application.
Query:
select r.requestid
,absparentid parent_id,
–r.state,
r.application,
r.username,
–r.NAME,
r.TYPE,
r.definition,
r.executable_status,
–r.processstart,
v.value reportname,
to_timestamp(to_char(r.processstart, ‘yyyy-mm-dd hh24:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’) process_start,
to_timestamp(to_char(r.processend, ‘yyyy-mm-dd hh24:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’) process_end,
to_timestamp(to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’) system_date,
to_timestamp(to_char(scheduled, ‘yyyy-mm-dd hh24:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’) scheduled_time
from ess_request_history r,
fusion.ess_request_property v
where 1=1
AND r.requestid = v.requestid
–and r.state in ( 1, 2 , 3 )
–and ( r.definition like ‘%VISION%’ or definition like ‘%EssBipJob%’ )
–and r.username = ‘USER1’
/* Job definition from ESS*/
and r.definition = ‘JobDefinition://oracle/apps/ess/custom/oracle/apps/ess/financials/generalLedger/programs/common/VISIONXXLAACCANALYSISSINGLE’
and v.name = ‘reportID’
–and v.value =:bu_le_etc
–and r.requestid = 13190248
and to_timestamp(to_char(r.processstart, ‘yyyy-mm-dd hh24:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’) >= to_date(to_char(‘2023-08-04 14:00:00’), ‘yyyy-mm-dd hh24:mi:ss’)
/*
and to_timestamp(to_char(r.processstart, ‘yyyy-mm-dd hh24:mi:ss’), ‘yyyy-mm-dd hh24:mi:ss’) <= to_date(to_char(‘2023-08-03 23:59:59’), ‘yyyy-mm-dd hh24:mi:ss’)
*/
