Search Results per_empdir_positions_pk

Overview

HR.PER_EMPDIR_POSITIONS is a translation and directory staging table within the Oracle E-Business Suite HR (Human Resources) schema. It supports the Employee Directory and related position-directory functionality by holding position records keyed by an external (originating) system, so that positions defined in a source system can be mapped, localized, and published into the Oracle HRMS directory model. The table carries translated position names and attributes across multiple languages, which is why LANGUAGE forms part of the unique key rather than a simple row identifier.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone. This suggests it is best treated as a satellite-like reference table rather than a hub or link. It does not act as a central business entity hub, but rather as a dependent descriptor store whose grain is defined by the combination of ORIG_SYSTEM, ORIG_SYSTEM_ID, and LANGUAGE.

Key Information Stored

The surrogate identifier is ORIG_SYSTEM_ID, a NUMBER(15) column representing the position identifier in the originating system. The business-key candidate is defined by the unique index PER_EMPDIR_POSITIONS_PK, which spans ORIG_SYSTEM_ID, ORIG_SYSTEM, and LANGUAGE. This composite uniqueness ensures a single position translation per source system and language.

• ORIG_SYSTEM / ORIG_SYSTEM_ID — The external source system code and its position identifier; together with LANGUAGE they form the primary key.
• LANGUAGE / SOURCE_LANG — The language of the translated NAME and its source language, enabling multilingual directory display.
• NAME — VARCHAR2(240) translated position name; indexed through the function-based index PER_EMPDIR_POSITIONS_N1 on UPPER(NAME) for case-insensitive lookups.
• BUSINESS_GROUP_ID — The HR business group that owns the position definition.
• JOB_ID, LOCATION_ID, ORGANIZATION_ID — Normalized references to job, location, and organization, each indexed with ORIG_SYSTEM for filtered reporting.
• POSITION_DEFINITION_ID — Links back to the internal position definition record.
• OBJECT_VERSION_NUMBER, PARTITION_ID — Concurrency control and partitioning attributes used by the EMPDIR framework.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–20 — Descriptive flexfield segments for extensibility.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATE_BY, CREATED_BY, CREATION_DATE, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE support concurrent-program and audit tracking.

Common Use Cases and Queries

Typical usage centers on synchronizing external position data into Oracle HRMS and reporting directory content by language or organization. A frequent pattern is joining to HZ_ORIG_SYSTEMS_B via ORIG_SYSTEM_ID to resolve the source system, and filtering by LANGUAGE for a specific locale.

• Listing translated positions for a source system: SELECT name, language FROM hr.per_empdir_positions WHERE orig_system = :p AND orig_system_id = :id;
• Case-insensitive name search using the function-based index: SELECT * FROM hr.per_empdir_positions WHERE UPPER(name) LIKE UPPER(:term||'%');
• Reporting by organization or location: filter on ORGANIZATION_ID or LOCATION_ID using indexes N3 and N4.
• Auditing which concurrent program loaded rows via REQUEST_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Related Objects

• HZ_ORIG_SYSTEMS_B — Referenced by ORIG_SYSTEM_ID; provides the originating system definition.
• JTF_FM_PARTITION_X_REQUEST — Referenced by PARTITION_ID; supports the EMPDIR partition framework.
• PER_POSITION_DEFINITIONS — Logical parent for POSITION_DEFINITION_ID.
• PER_JOBS / PER_LOCATIONS / HR_ORGANIZATION_UNITS — Joined via JOB_ID, LOCATION_ID, and ORGANIZATION_ID.
• HR.PER_EMPDIR_POSITIONS indexes (PER_EMPDIR_POSITIONS_PK, N1–N4) — Essential for efficient lookup and reporting access paths.