Search Results per_empdir_people




Overview
Explore in AI Chat

Overview

The HR.PER_EMPDIR_PEOPLE table is a denormalized employee directory staging and reporting table within the Oracle E-Business Suite Human Resources (PER) module. Its purpose is to provide a flattened, query-friendly representation of person and assignment attributes, sourced primarily from the core PER_ALL_PEOPLE_F and related directory tables, so that self-service directory screens, search pages, and third-party integrations can retrieve employee information without joining across the normalized HR schema. In EBS 12.1.1 and 12.2.2 the table is owned by the HR schema and exposes 104 columns, combining person identity, name components, contact details, and organizational reporting metrics in a single physical structure.

From a dimensional modeling perspective, the ETRM metadata classifies this object heuristically as standalone, indicating that no dependent foreign-key relationships originate from it. The two declared foreign keys — ORIG_SYSTEM_ID referencing HZ_ORIG_SYSTEMS_B and PARTITION_ID referencing JTF_FM_PARTITION_X_REQUEST — are inbound reference definitions rather than evidence of a hub-and-satellite structure. A reasonable modeling suggestion is therefore to treat PER_EMPDIR_PEOPLE as a satellite-style or reporting-scoped table keyed by the natural business key (ORIG_SYSTEM_ID, ORIG_SYSTEM), rather than as a Data Vault hub or link.

Key Information Stored

Although 104 columns are documented, the columns of primary operational interest fall into several groups.

Common Use Cases and Queries

The table is most often queried for directory lookups and headcount-style reporting. A typical pattern filters on active employees within a legislation or business group:

  • Directory search: SELECT FULL_NAME, EMAIL_ADDRESS, WORK_TELEPHONE FROM PER_EMPDIR_PEOPLE WHERE ACTIVE = 'Y' AND UPPER(LAST_NAME) LIKE 'SM%';
  • Manager rollup reporting: SELECT FULL_NAME, DIRECT_REPORTS, TOTAL_REPORTS FROM PER_EMPDIR_PEOPLE WHERE TOTAL_REPORTS > 0 ORDER BY TOTAL_REPORTS DESC;
  • Integration export: SELECT ORIG_SYSTEM, ORIG_SYSTEM_ID, GLOBAL_PERSON_ID, EMPLOYEE_NUMBER FROM PER_EMPDIR_PEOPLE;
  • Business-key lookup: SELECT * FROM PER_EMPDIR_PEOPLE WHERE ORIG_SYSTEM = :p_system AND ORIG_SYSTEM_ID = :p_person;

Because the table is denormalized, reporting tools such as Oracle Reports, BI Publisher, and OBIEE frequently consume it directly as a source dataset, avoiding joins to PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F.

Related Objects

  • HZ_ORIG_SYSTEMS_B — referenced by ORIG_SYSTEM_ID; defines the registered source system registry used for cross-referencing people and parties.
  • JTF_FM_PARTITION_X_REQUEST — referenced by PARTITION_ID; provides partition metadata for concurrent request scope.
  • PER_ALL_PEOPLE_F — the authoritative person record from which directory attributes and PERSON_KEY values are derived.
  • PER_ALL_ASSIGNMENTS_F — supplies assignment context (business group, legislation, hire date) reflected in the directory row.
  • HZ_PARTIES — the TCA party record aligned to PARTY_ID and GLOBAL_PERSON_ID.
  • FND_USER — relates via USER_NAME for self-service sign-on and directory display.

In summary, PER_EMPDIR_PEOPLE functions as a supporting directory table rather than a transactional core object, and its primary value lies in consolidating person identity, contact, and reporting metrics into a single queryable structure.