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.

Oracle HCM SQL Toolkit – Part 2: Person External Application Identifier Details

Oracle HCM SQL Toolkit – Part 2: Person External Application Identifier Details

Welcome to Part 2 of the Oracle HCM SQL Toolkit, a continuing series of practical SQL queries that Oracle HCM consultants can keep handy for day-to-day work.

In this post, we will look at another frequently useful area:

External Application Identifiers associated with a person.

Oracle HCM frequently integrates with external applications, and those applications may use identifiers that are completely different from the employee's Oracle HCM Person Number.

Knowing how to retrieve these identifiers can be extremely useful when troubleshooting integrations and reconciling employee data between systems.

The Query

SELECT
    ppn.full_name,
    papf.person_number,
    pext.ext_identifier_type,
    flkp.meaning,
    pext.ext_identifier_number,
    pext.date_from,
    pext.date_to
FROM
    per_ext_app_identifiers pext,
    per_all_people_f papf,
    fnd_common_lookups flkp,
    per_person_names_f ppn
WHERE
    TRUNC(SYSDATE) BETWEEN papf.effective_start_date
                       AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_date
                       AND ppn.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pext.date_from
                       AND NVL(
                           pext.date_to,
                           TO_DATE('31/12/4712', 'DD/MM/YYYY')
                       )
AND pext.person_id = papf.person_id
AND pext.ext_identifier_type = flkp.lookup_code
AND flkp.lookup_type = 'ORA_PER_EXT_IDENTIFIER_TYPES'
AND ppn.person_id = papf.person_id
AND ppn.name_type = 'GLOBAL';

Sample Output

Full Name	Person Number	Identifier Type	Meaning	External Identifier
John Smith	100234	BENEFITS_ID	Benefits Vendor ID	BEN29823
Jane Doe	100987	LMS_ID	Learning System ID	LMS83928

Why External Identifiers Matter

Oracle HCM may identify an employee using:

Person Number = 100245

but another application might identify the same employee differently.

Oracle HCM Person Number : 100245
Benefits Vendor ID       : BEN87943
Learning System ID       : LMS45672
Identity System ID       : IAM003482

External Application Identifiers provide a mechanism for maintaining these external references against a person.

Where This Is Useful

This becomes especially helpful when Oracle HCM integrates with:

• Benefits providers
• Payroll vendors
• Learning applications
• Identity-management systems
• Time applications
• Legacy HR systems
• Custom enterprise applications

Identifier Type and Meaning

The technical identifier type comes from:

pext.ext_identifier_type

The query joins this value to:

FND_COMMON_LOOKUPS

using:

flkp.lookup_type = 'ORA_PER_EXT_IDENTIFIER_TYPES'

This gives us both the technical identifier code and the user-readable meaning.

A Common Integration Troubleshooting Scenario

Imagine a support ticket says:

Employee 100245 is missing from the benefits vendor system.

Before investigating middleware or extract logic, one simple check is:

Does employee 100245 have the expected Benefits Vendor external identifier?

If the query returns:

Person Number       : 100245
Identifier Type     : BENEFITS_ID
External Identifier : BEN87943

you now have the value that can be reconciled against the external application.

Oracle HCM SQL Toolkit – Part 2 Recap

This query provides a useful view of:

Person → External Identifier Type → External Identifier

It is particularly valuable when troubleshooting integrations or reconciling worker information between Oracle HCM and another application.

Oracle HCM SQL Toolkit – Part 1: Country, Legal Employer and Employee Count

Oracle HCM SQL Toolkit – Part 1: Country, Legal Employer and Employee Count

Over time, every Oracle HCM consultant builds a personal collection of SQL queries that become part of their everyday toolkit.

Some queries are useful for troubleshooting. Others help with reporting, validation, integrations, payroll, or simply understanding how an Oracle HCM environment is structured.

This series, Oracle HCM SQL Toolkit, is intended to build a practical library of those frequently used queries.

In Part 1, we will look at a simple but very useful query:

How many Legal Employers are configured, which countries do they belong to, and how many active employees are associated with each Legal Employer?

The Query

SELECT
    ple.name AS legal_employer,
    xep.le_information_context AS country_code,
    (
        SELECT COUNT(paaf.assignment_id)
        FROM per_all_assignments_f paaf
        WHERE paaf.legal_entity_id = ple.organization_id
          AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.assignment_status_type_id = 1
    ) AS employee_count
FROM xle_entity_profiles xep
JOIN per_legal_employers ple
    ON xep.legal_entity_id = ple.legal_entity_id
WHERE xep.legal_employer_flag = 'Y'
  AND ple.status = 'A'
--AND xep.le_information_context = 'US'
ORDER BY ple.name;

Sample Output

Legal Employer	Country Code	Employee Count
Vision US LLC	US	12,450
Vision Canada Ltd	CA	2,180
Vision UK Ltd	GB	3,925
Vision India Pvt Ltd	IN	8,740

Why Keep This Query Handy?

This query provides a quick view of the organization's HCM footprint.

• Which Legal Employers exist
• Which country each Legal Employer belongs to
• Employee population by Legal Employer
• Which Legal Employers have large populations
• Whether any Legal Employers have unexpectedly small populations

It is useful during enterprise-structure discussions, Payroll analysis, reporting, data validation, troubleshooting, and general environment discovery.

Oracle HCM SQL Toolkit – Part 1 Recap

This query provides a simple view of:

Country → Legal Employer → Employee Count

It is easy to understand, easy to modify, and useful across many Oracle HCM functional and technical scenarios.