Search Results per_empdir_phones_pk

Overview

HR.PER_EMPDIR_PHONES is a transactional table in the Oracle E-Business Suite HR schema that stores telephone contact information used by the Employee Directory (EmpDir) feature set. The table is classified as VALID under both EBS 12.1.1 and 12.2.2 and is registered in FND Design Data as PER.PER_EMPDIR_PHONES. Its purpose is to persist phone numbers associated with directory entities, where each row is bound to a parent record through the PARENT_ID and PARENT_TABLE pair. This polymorphic parent linkage allows the same physical table to serve multiple directory consumers (for example, employee, organization, or location directory entries) without requiring a dedicated table per parent type. The presence of DATE_FROM and DATE_TO date-effective columns indicates that the table supports historical, date-tracked phone records rather than a single mutable value per parent.

From a Data Vault modeling perspective, the metadata does not declare a formal classification; however, the heuristic analysis suggests this object behaves as a satellite. It carries descriptive, time-variant attributes (phone type, phone number, date ranges) keyed by a business key composed of ORIG_SYSTEM and ORIG_SYSTEM_ID, which aligns with the satellite pattern rather than a pure hub or link.

Key Information Stored

The table contains 20 documented columns. The most significant are summarized below.

• ORIG_SYSTEM / ORIG_SYSTEM_ID — The composite business key. Together they form the unique index PER_EMPDIR_PHONES_PK, identifying the source system and the record identifier within that system. ORIG_SYSTEM_ID carries a foreign key to HZ_ORIG_SYSTEMS_B.
• PHONE_TYPE — Classifies the number (for example, work, mobile, fax), enabling filtered directory output.
• PHONE_NUMBER / PHONE_KEY — The stored number and a normalized lookup key. PHONE_KEY is indexed by PER_EMPDIR_PHONES_N2 for efficient search.
• PARENT_ID / PARENT_TABLE — The polymorphic link to the owning directory record. Both columns are indexed by PER_EMPDIR_PHONES_N1 (NONUNIQUE), which supports fast retrieval of all phones for a given parent.
• DATE_FROM / DATE_TO — Effective dating of the phone record, supporting as-of and historical reporting.
• OBJECT_VERSION_NUMBER — Optimistic locking column used by the Oracle Application Framework (OAF) and PL/SQL DML to detect concurrent updates.
• PARTITION_ID — Foreign key to JTF_FM_PARTITION_X_REQUEST, used for partition-aware data distribution.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, plus concurrent program context columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical usage centers on the Employee Directory UI and related reporting. A common pattern retrieves all phone records for a specific parent directory entry:

• SELECT PHONE_TYPE, PHONE_NUMBER FROM HR.PER_EMPDIR_PHONES WHERE PARENT_ID = :p_id AND PARENT_TABLE = :p_table AND SYSDATE BETWEEN NVL(DATE_FROM, SYSDATE) AND NVL(DATE_TO, SYSDATE+1);
• Lookup by normalized key: SELECT * FROM HR.PER_EMPDIR_PHONES WHERE PHONE_KEY = :key;
• Lookup by business key: SELECT * FROM HR.PER_EMPDIR_PHONES WHERE ORIG_SYSTEM = :sys AND ORIG_SYSTEM_ID = :id;

In reporting, the table is frequently joined with HZ_ORIG_SYSTEMS_B for source-system descriptions and used to build telephone listings filtered by PHONE_TYPE. Because PARENT_TABLE is a discriminator, integrations often join dynamically to the appropriate parent table via application logic rather than a static foreign key. Date-effective queries should always account for DATE_FROM and DATE_TO to avoid returning superseded numbers.

Related Objects

• HZ_ORIG_SYSTEMS_B — Referenced by this table on ORIG_SYSTEM_ID; supplies source-system definitions.
• JTF_FM_PARTITION_X_REQUEST — Referenced by this table on PARTITION_ID; supports partition assignment.
• HR.PER_EMPDIR_PHONES# — The internal table referenced by HR.PER_EMPDIR_PHONES, indicating the dependency relationship between the base object and its internal variant.
• PER.PER_EMPDIR_PHONES (FND Design Data) — The design-data registration that governs the object's business rules.
• Parent directory tables — The set of possible PARENT_TABLE values that reference this table logically via PARENT_ID and PARENT_TABLE rather than through a declared foreign key.

The absence of outgoing foreign keys beyond ORIG_SYSTEM_ID and PARTITION_ID, together with its sole referencing object HR.PER_EMPDIR_PHONES#, confirms that this table is a leaf-level satellite within the directory data model.