Results for “per_empdir_people_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

HR.PER_EMPDIR_PEOPLE is an employee directory staging and denormalized access table in Oracle E-Business Suite, owned by the HR schema and registered under FND Design Data as PER.PER_EMPDIR_PEOPLE. Its role is to provide a flattened, query-optimized projection of person records for the Employee Directory (a self-service directory used by employees to locate colleagues by name, e-mail, telephone, or username). Rather than requiring the directory to join the full PER_ALL_PEOPLE_F and PER_PERSON_NAMES_F stack on every search, this table materializes the frequently searched attributes together with the originating system identifiers, allowing indexed lookups against searchable columns.

The table is populated by concurrent program processing that reads source person data and writes it into the directory with request tracking columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE). From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone: it is not a classic hub, link, or satellite because it carries no enforced foreign keys that would give it a foreign-key relationship to other tables in the Data Vault sense. A pragmatic modeling suggestion is to treat it as a satellite-style descriptive snapshot keyed on the natural business key (ORIG_SYSTEM, ORIG_SYSTEM_ID), since it holds descriptive directory attributes about a person rather than relationships between persons.

Key Information Stored

The documented schema contains 104 columns. The unique business-key candidate is defined by the unique index PER_EMPDIR_PEOPLE_PK on the composite (ORIG_SYSTEM_ID, ORIG_SYSTEM), which identifies the source system and the person's identifier within that system. The most significant stored attributes are:

• ORIG_SYSTEM and ORIG_SYSTEM_ID — the source system code and the person's identifier within it; these form the effective business key rather than a surrogate primary key.
• PERSON_KEY and PARTY_ID — the person's application-level key and trading community party identifier used to link back to core HR and TCA records.
• DISPLAY_NAME, FULL_NAME, and FULL_NAME_ALTERNATE — the primary and alternate directory display names.
• LAST_NAME, FIRST_NAME, and their _ALTERNATE variants — the person's name components used by function-based indexes.
• KNOWN_AS, MIDDLE_NAMES, PREVIOUS_LAST_NAME, and PRE_NAME_ADJUNCT — supplementary naming attributes surfaced in directory searches.
• EMAIL_ADDRESS, WORK_TELEPHONE, MAILSTOP, and OFFICE_NUMBER — contact details indexed for lookup.
• USER_NAME — the application login name used for directory identity matching.
• BUSINESS_GROUP_ID, LEGISLATION_CODE, and PERSON_TYPE — organizational scoping and person classification.
• ACTIVE and START_DATE / ORIGINAL_DATE_OF_HIRE — status and employment timing used to filter current personnel.

Additional descriptive fields include ORDER_NAME, GLOBAL_PERSON_ID, and DIRECT_REPORTS / TOTAL_REPORTS counts, plus standard WHO columns and 30 DFF (ATTRIBUTE) and 30 descriptive flexfield (PER_INFORMATION) columns.

Common Use Cases and Queries

The primary use case is employee directory search, where the function-based indexes on UPPER(LAST_NAME), UPPER(FIRST_NAME), UPPER(EMAIL_ADDRESS), and UPPER(WORK_TELEPHONE) support case-insensitive lookups. A typical pattern joins by business key to reconcile directory entries with source HR data:

• Lookup by name: filter on UPPER(LAST_NAME) or UPPER(FIRST_NAME) using the function-based indexes.
• Retrieve a specific directory entry: WHERE ORIG_SYSTEM = :sys AND ORIG_SYSTEM_ID = :id, which exercises the unique PER_EMPDIR_PEOPLE_PK index.
• Resolve a person's identity via USER_NAME using the PER_EMPDIR_PEOPLE_N6 index.
• Reporting on active directory membership: filter ACTIVE and scope by BUSINESS_GROUP_ID.
• Contact reports grouping by EMAIL_ADDRESS or WORK_TELEPHONE.

Because the table is refreshed by a loader, queries should account for REQUEST_ID and PROGRAM_UPDATE_DATE when validating freshness of directory data.

Related Objects

Although the object is classified as standalone, the documented foreign-key relationships anchor it to two supporting tables:

• HZ_ORIG_SYSTEMS_B — referenced through PER_EMPDIR_PEOPLE.ORIG_SYSTEM_ID, identifying the source system definition used to interpret ORIG_SYSTEM and ORIG_SYSTEM_ID.
• JTF_FM_PARTITION_X_REQUEST — referenced through PER_EMPDIR_PEOPLE.PARTITION_ID, supporting partitioned directory refresh processing.

Conceptual dependencies, established through shared person identifiers rather than enforced foreign keys, include PER_ALL_PEOPLE_F and PER_PERSON_NAMES_F via PERSON_ID / PARTY_ID, and TCA's HZ_PARTIES via PARTY_ID. The directory is normally maintained indirectly through the HR person update process and consumed by Employee Directory (SSHR) self-service pages; any direct manipulation should be avoided and staged through the standard loader or APIs.