Oracle Fusion/Cloud ERP ESS Job Details

Time taken to complete the process

When dealing with Conversions/Integrations/Reports – Knowing details about ESS(Enterprise Schedule Service) is utmost import in Oracle Fusion Cloud ERP, below are the few queries, which are very much beneficial during your ESS Journey 🙂

Time taken to complete the process

SELECT
REQUESTID,
sysdate,
to_char(PROCESSSTART,’yyyy-mm-dd hh24:mi:ss’) Start_Time,
to_char( PROCESSEND,’yyyy-mm-dd hh24:mi:ss’) End_Time,
PROCESSEND – PROCESSSTART,
(sysdate-PROCESSSTART ) timesofar
FROM ESS_REQUEST_HISTORY
WHERE REQUESTID IN ( )

Logic to get the Process Details

SELECT
erh.REQUESTID,
rp.value,
to_char(erh.PROCESSSTART,’yyyy-mm-dd hh24:mi:ss’) Start_Time,
to_char( erh.PROCESSEND,’yyyy-mm-dd hh24:mi:ss’) End_Time,
erh.PROCESSEND – erh.PROCESSSTART
FROM ESS_REQUEST_HISTORY erh
, request_property rp
WHERE erh.requestid IN (( )
AND rp.requestid = erh.REQUESTID
AND rp.name=’jobDefinitionName’
ORDER BY 2 ;

Open Jobs and their time

SELECT
REQUESTID,
name,
sysdate,
to_char(PROCESSSTART,’yyyy-mm-dd hh24:mi:ss’) Start_Time,
to_char( PROCESSEND,’yyyy-mm-dd hh24:mi:ss’) End_Time,
PROCESSEND – PROCESSSTART,
(sysdate-PROCESSSTART ) timesofar
FROM ESS_REQUEST_HISTORY
WHERE 1=1
–WHERE REQUESTID IN (()
AND PROCESSEND IS NULL
AND PROCESSSTART is not null;

ESS Process Details
select * from request_property WHERE requestid IN (() ;