ESS Job Definition tables in Oracle Fusion

In the last article, we discussed how to define and schedule ESS Jobs in Oracle Fusion. Wondering which tables stores these ESS Job definition?

Let’s try to find out this.

As per this Oracle Support article, there are no tables that store ESS Job Definition. It is actually an XML document stored in MDS DB which you can’t query using SQL. There is a form available in Oracle fusion using which you can see the definition of the job. Refer to this article for details.

Once the job is submitted, you can see details about the parameter, run time using the tables given below.

select * from FUSION_ORA_ESS.REQUEST_HISTORY
select * from FUSION_ORA_ESS.REQUEST_PROPERTY

Query to get run time details of ESS Job

SELECT 
requestid,
PRODUCT,
SUBSTR(DEFINITION,INSTR(DEFINITION,'/', -1)+1) short_name,
name,
DEFINITION,
count(1) number_of_exe,
ROUND(Min(((cast(processend as Date) - cast(processstart as date)) * 24*60 )),2)min_time,
ROUND(Max(((cast(processend as Date) - cast(processstart as date)) * 24*60 )),2)max_time,
ROUND(Avg(((cast(processend as Date) - cast(processstart as date)) * 24*60)),2)avg_time
from fusion_ora_ess.request_history_view rh
where PROCESSEND is not null
AND TRUNC(rh.processstart) between NVL(:P_START_DATE,SYSDATE-90) AND NVL(:P_END_DATE,SYSDATE)
group by requestid, PRODUCT,name, SUBSTR(DEFINITION,INSTR(DEFINITION,'/', -1)+1),DEFINITION
ORDER BY Max(((cast(processend as Date) - cast(processstart as date)) * 24*60 )) desc