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.
