Query to Get the list of ESS Job Schedules - ERP Cloud

SELECT r.requestid,

r.username,

(SELECT value

FROM ess_request_property v

WHERE v.requestid = r.requestid

AND v.name = 'EXT_PortletContainerWebModule') MODULE,

To_char(r.scheduled, 'yyyy-mm-dd hh24:mi:ss') SCHEDULED,

(SELECT meaning

FROM fnd_lookup_values_vl

WHERE lookup_type = 'BEN_ESS_REQ_STATE'

AND lookup_code = r.state) state,

(SELECT Substr(DEFINITION, -Instr(Reverse(DEFINITION), '/') + 1)

FROM request_history r1

WHERE r1.DEFINITION = r.DEFINITION

AND r.requestid = r1.requestid) name

FROM ess_request_history r

WHERE state IN ( 1, 6)

-- and r.SCHEDULED is not null

AND r.username NOT IN ( 'FUSION_APPS_SEARCH_APPID',

'FUSION_APPS_CRM_ESS_APPID',

'FUSION_APPS_HCM_ADF_LDAP_APPID' )

AND r.submitter NOT IN ( 'FUSION_APPS_SEARCH_APPID',

'FUSION_APPS_CRM_ESS_APPID',

'FUSION_APPS_HCM_ADF_LDAP_APPID'

)

ORDER BY 6,

1 DESC

Hope This Helps !

Happy Learning.