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.