Oracle HCM SQL Toolkit – Part 3: ESS Scheduled Process Details and Status

Oracle HCM SQL Toolkit – Part 3: ESS Scheduled Process Details and Status

Welcome to Part 3 of the Oracle HCM SQL Toolkit, a continuing series of practical Oracle HCM SQL queries every consultant should keep handy.

In this post, we move to another area that comes up frequently in Oracle HCM troubleshooting:

Scheduled Processes and ESS request history.

Oracle HCM performs a significant amount of background processing through Enterprise Scheduler Service (ESS).

When investigating a Scheduled Process, it is often useful to know:

• What all processes are scheduled, and you don’t have access to see from the UI
• What process was submitted?
• Who submitted it?
• When was it submitted?
• When did execution actually start?
• When did it finish?
• What is its current status?
• What is the technical process name?

The Query

SELECT
    erh.requestid AS "Process ID",
    SUBSTR(
        erh.definition,
        INSTR(erh.definition, '/', -1) + 1
    ) AS "Process Name",
    erh.username AS "Submitted By",
    TO_CHAR(
        erh.submission,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "Submission Time",
    TO_CHAR(
        erh.processstart,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "Start Time",
    TO_CHAR(
        erh.processend,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "End Time",
    DECODE(
        erh.state,
        1,  'Wait',
        2,  'Ready',
        3,  'Running',
        4,  'Completed',
        5,  'Blocked',
        6,  'Hold',
        7,  'Canceling',
        9,  'Canceled',
        10, 'Error',
        11, 'Warning',
        12, 'Succeeded',
        erh.state
    ) AS "Status",
    erh.definition AS "Full Definition Path"
FROM
    fusion.ess_request_history erh
WHERE 1 = 1
  AND erh.state IN (1,6)
ORDER BY
    erh.submission DESC;

In this example, the query returns requests in Wait and Hold status because of:

erh.state IN (1,6)

ESS State Values

State	Status
1	Wait
2	Ready
3	Running
4	Completed
5	Blocked
6	Hold
7	Canceling
9	Canceled
10	Error
11	Warning
12	Succeeded

Error and Warning Requests

AND erh.state IN (10,11)

Currently Running Requests

AND erh.state = 3

Technical Process Name vs. Scheduled Process Display Name

One particularly useful detail when working with ESS is that the internal technical process name may be different from the Scheduled Process name that functional users recognize in the application.

Technical Process Name	Scheduled Process / Display Name
TCDEventInternalizingProcessJob	Generate Time Cards from Time Collection Devices
HrcCommunicationManager	Manage Communication Responses
DataSecurityAclRefresh	Compute Users ACL by Events
SyncRolesJob	Retrieve Latest LDAP Changes
ACRPLENR	Update Accrual Plan Enrollments
ACRPRC	Calculate Accruals and Balances
BENDSGEL	Reevaluate Designee Eligibility
BENMNGLE	Evaluate Life Event Participation
FlowEssJobDefn	HCM Flow Secured
PurgeSpreadsheetLoaderHistoryJob	Delete HCM Spreadsheet Data Loader Stage Table Data
PurgeExtractPayrollActionsJob	Purge Extracts Archive Data
PurgeExtractsPartitionsAuto	Purge Extracts Archive Partitions
CloudMetricsNumberOfNamedUsers	Generate Cloud Usage Metrics
TimeComplianceRulesCheckJob	Generate Time Exceptions from Compliance Rules
BENCLENR	Close Enrollment
BENCLLPSLE	Collapse Life Events
BENTEMOD	Evaluate Temporal Event Participation
BENASLFE	Assign Corrective Potential Life Event
BENMNGBENREL	Assign and Update Benefits Relationships
HcmAtomFeedPurgeJob	Purge Atom Feed Entries from Oracle Fusion Schema
PurgeObsoletedSmartActions	Purge Obsoleted Smart Action Records
HcmAlertRunPurgeJob	Purge Alert Processing and Log Entries
GenericFeatureUpgrade	Generic Feature Upgrade
SendCommunication	Send Communication
HcmPurgeSensitiveDataAccessAuditJob	Purge Sensitive Data Access Audit
GroupsEvalJob	Evaluate Group Membership
SynchronizeManagerHierarchyJob	Synchronize Person Assignments from Position

for example-

The technical ESS process name is:

TimeComplianceRulesCheckJob

You can filter ESS history using:

AND erh.definition LIKE '%TimeComplianceRulesCheckJob%'

and review when requests were submitted, when they started, how long they ran, whether requests remained waiting, and whether they ended with errors or warnings.

Oracle HCM SQL Toolkit – Part 3 Recap

This query provides a useful view of:

Scheduled Process → Technical ESS Job → Status and Execution Details

It is particularly useful when troubleshooting background processing or trying to connect the functional Scheduled Process name with the underlying ESS technical process.