Search Results per_empdir_assignments_pk

Overview

HR.PER_EMPDIR_ASSIGNMENTS is an Oracle E-Business Suite table that stores assignment records originating from external or non-native sources, most commonly from an employee directory or an external HR feeder system. The table name reflects its purpose: emp (employee) dir (directory) assignments. Unlike the core HR.PER_ALL_ASSIGNMENTS_F, which holds the full history of an employee's assignments within Oracle HRMS, PER_EMPDIR_ASSIGNMENTS captures a lighter-weight, source-attributed view of assignment data, keyed by the originating system rather than by the native Oracle assignment surrogate key.

The object resides in the HR schema and is exposed through the PER.PER_EMPDIR_ASSIGNMENTS FND design data entity. Its heuristic Data Vault classification is standalone, meaning the mined foreign-key structure does not resolve it into a classic hub, link, or satellite pattern. As a modeling suggestion, it behaves most like a source-attributed staging or integration table whose grain is one row per (ORIG_SYSTEM, ORIG_SYSTEM_ID) pair.

Key Information Stored

The physical schema documents 60 columns. The most operationally significant are the following. The table's unique index, PER_EMPDIR_ASSIGNMENTS_PK, is defined on the composite key ORIG_SYSTEM_ID and ORIG_SYSTEM — this is the business-key candidate rather than a single surrogate column. ORIG_SYSTEM (VARCHAR2 30) names the source application or directory that supplied the record, and ORIG_SYSTEM_ID (NUMBER 15) is the identifier of that record within the source. ORIG_SYSTEM_ID also carries a foreign-key relationship to HZ_ORIG_SYSTEMS_B.

The remaining descriptive columns align closely with core assignment attributes: BUSINESS_GROUP_ID, PERSON_ID, ORGANIZATION_ID, JOB_ID, POSITION_ID, LOCATION_ID, SUPERVISOR_ID, and SUPERVISOR_ASSIGNMENT_ID establish the organizational and reporting relationships. ASSIGNMENT_NUMBER holds the human-readable assignment reference. PRIMARY_FLAG and ACTIVE indicate whether the assignment is the primary one and whether it is currently active. DISCRETIONARY_TITLE stores a free-text title used for directory display. EMPLOYEE_CATEGORY, EMPLOYMENT_CATEGORY, ASSIGNMENT_CATEGORY, and WORK_AT_HOME categorize the assignment. The 30 ASS_ATTRIBUTE1 through ASS_ATTRIBUTE30 columns provide a DFF-style extensibility block, managed by ASS_ATTRIBUTE_CATEGORY. Standard WHO and concurrent-program columns (LAST_UPDATE_DATE, CREATED_BY, REQUEST_ID, PROGRAM_APPLICATION_ID, OBJECT_VERSION_NUMBER, PARTITION_ID) are present for auditing and partition routing.

Common Use Cases and Queries

The table is typically queried to reconcile externally sourced directory records against native HRMS assignments, or to report on assignments by originating system. A representative pattern retrieves active primary assignments for a given source:

• SELECT person_id, assignment_number, discretionary_title FROM per_empdir_assignments WHERE orig_system = :p_source AND active = 'Y' AND primary_flag = 'Y';
• Joining to display the source name: ... JOIN hz_orig_systems_b hos ON hos.orig_system_id = pea.orig_system_id.
• Reporting counts grouped by source to assess integration coverage: SELECT orig_system, COUNT(*) FROM per_empdir_assignments GROUP BY orig_system;

The secondary indexes PER_EMPDIR_ASSIGNMENTS_N1 through N6 (each prefixed by ORIG_SYSTEM, then ORGANIZATION_ID, LOCATION_ID, JOB_ID, POSITION_ID, PERSON_ID, and SUPERVISOR_ID respectively) make these filtered lookups efficient. Index N7 is a function-based index on UPPER(DISCRETIONARY_TITLE) supporting case-insensitive title searches.

Related Objects

The documented foreign keys and keys tie this table to the following significant objects:

• HZ_ORIG_SYSTEMS_B — joined via ORIG_SYSTEM_ID; supplies the originating-system definition.
• JTF_FM_PARTITION_X_REQUEST — referenced through PARTITION_ID for partitioning support.
• PER_ALL_ASSIGNMENTS_F — the native HRMS assignment table, commonly joined by PERSON_ID and ASSIGNMENT_NUMBER for reconciliation.
• PER_ALL_PEOPLE_F — joined by PERSON_ID to resolve the employee behind each assignment.
• HR_ALL_ORGANIZATION_UNITS — joined by ORGANIZATION_ID for organizational context.
• PER_JOBS and PER_POSITIONS — joined by JOB_ID and POSITION_ID respectively for role detail.
• HR_LOCATIONS_ALL — joined by LOCATION_ID for work location.

Because the table is classified as standalone, no dependent child tables inherit its key; it functions as a source-facing endpoint rather than a parent within the HRMS relational hierarchy.