Query to get scheduled ESS Jobs and its frequency and current status

 Hello All,

By using the below query we can get the list of the ess jobs has been scheduled and what is the current status and frequency etc...

**********************************************************************************

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

**********************************************************************************