Search Results per_empdir_organizations_pk

Overview

The HR.PER_EMPDIR_ORGANIZATIONS table is a core repository within the Oracle E-Business Suite HR (Human Resources) schema that stores organization records used by the Employee Directory (EmpDir) self-service functionality. It holds directory-specific organization definitions, including representative assignments and location information, that feed the Employee Directory and related self-service pages. The table is registered under the FND Design Data identifier PER.PER_EMPDIR_ORGANIZATIONS and is marked VALID in the 12.1.1 / 12.2.2 releases.

The table integrates with Oracle's trading community and partitioning infrastructure through foreign key relationships. Its Data Vault classification is heuristic and mined from the foreign key structure, which yields a "standalone" classification. In Data Vault modeling terms, this suggests the table behaves more like a reference or descriptive entity rather than a strict hub, link, or satellite, though the unique business key on (ORIG_SYSTEM_ID, ORIG_SYSTEM, LANGUAGE) would typically map to a hub construct. Modelers should treat any hub/satellite assignment as a suggestion rather than a dictated design.

Key Information Stored

The table contains 42 documented columns, with a mix of identifiers, descriptive attributes, and standard EBS audit/WHO columns. The most significant columns include:

• ORIG_SYSTEM (VARCHAR2 30) and ORIG_SYSTEM_ID (NUMBER 15) — Together these identify the source system and its identifier, forming part of the primary key. ORIG_SYSTEM_ID has an FK to HZ_ORIG_SYSTEMS_B.
• NAME (VARCHAR2 240) — The organization directory name; a function-based nonunique index PER_EMPDIR_ORGANIZATIONS_N1 on UPPER("NAME") supports case-insensitive lookups.
• LANGUAGE and SOURCE_LANG — Language and source language attributes; LANGUAGE is part of the unique primary key.
• BUSINESS_GROUP_ID (NUMBER 15) — Identifies the HR business group (operating unit) context.
• LOCATION_ID (NUMBER 15) — Reference to the associated location record.
• REPRESENTATIVE1_ID through REPRESENTATIVE4_ID (NUMBER 15 each) — Up to four representative person identifiers linked to the directory organization.
• OBJECT_VERSION_NUMBER — Optimistic locking version indicator.
• PARTITION_ID — Partitioning key with an FK to JTF_FM_PARTITION_X_REQUEST.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE20 (VARCHAR2 150 each) — Descriptive flexfield (DFF) storage columns.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, REQUEST_ID, and audit program columns — Standard EBS concurrency and audit tracking.

The surrogate uniqueness constraint is captured by the primary key index PER_EMPDIR_ORGANIZATIONS_PK, which enforces uniqueness across ORIG_SYSTEM_ID, ORIG_SYSTEM, and LANGUAGE. There is no separate single-column surrogate key; the composite primary key serves as the business key.

Common Use Cases and Queries

Typical usage centers on the Employee Directory self-service application, which reads organization names, representative assignments, and locations to render directory pages. Reporting queries frequently join this table to the HR business group, location, and person entities.

• Retrieving directory organizations for a business group:
  SELECT name, orig_system, orig_system_id FROM hr.per_empdir_organizations WHERE business_group_id = :p_bg_id;
• Case-insensitive name search leveraging the function-based index:
  SELECT * FROM hr.per_empdir_organizations WHERE UPPER(name) LIKE UPPER(:p_name || '%');
• Identifying representative assignments:
  SELECT representative1_id, representative2_id, representative3_id, representative4_id FROM hr.per_empdir_organizations WHERE orig_system_id = :p_id;
• Auditing recently modified records using the WHO columns and OBJECT_VERSION_NUMBER for optimistic locking validation.

The canonical query text published in the ETRM metadata begins with the projection of ORIG_SYSTEM, ORIG_SYSTEM_ID, BUSINESS_GROUP_ID, LOCATION_ID, the four representative columns, NAME, LANGUAGE, SOURCE_LANG, OBJECT_VERSION_NUMBER, and PARTITION_ID, and is the recommended starting point for custom extracts.

Related Objects

The following objects are most significant to this table based on its documented foreign keys and its role in the HR self-service stack:

• HZ_ORIG_SYSTEMS_B — Referenced by ORIG_SYSTEM_ID; provides the origin system registry entry.
• JTF_FM_PARTITION_X_REQUEST — Referenced by PARTITION_ID; supports the partitioning framework.
• PER_BUSINESS_GROUPS — Logical parent for BUSINESS_GROUP_ID values.
• HR_LOCATIONS_ALL / PER_LOCATIONS — Logical parent for LOCATION_ID.
• PER_ALL_PEOPLE_F — Logical parent for the REPRESENTATIVE1_ID through REPRESENTATIVE4_ID columns.
• FND_FLEX_VALUES / FND_DESCR_FLEX_COL_USAGE_TL — Supporting the ATTRIBUTE1–ATTRIBUTE20 descriptive flexfield columns via ATTRIBUTE_CATEGORY.
• FND_CONCURRENT_REQUESTS — Logical parent for REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID audit columns.
• PER_EMPDIR_ORGANIZATIONS_N1 — The function-based nonunique index on UPPER("NAME") used for directory search performance.