Search Results assignment_pk

Overview

EDW_HR_PERSON_ASSIGNMENTS_LCV is an Oracle E-Business Suite view belonging to the PER (Human Resources) product family. It is a member of the EDW (Enterprise Data Warehouse) family of "LCV" (Load/Collection View) objects that Oracle seeds for extraction, integration, and reporting purposes. Its defined purpose, reflected in the embedded comment FOR ALL CURRENT AND PAST EMPLOYEES, is to produce a unified person/assignment list by combining employee records with additional person-like assignment entities such as sales representatives and planners.

The view resolves each person or assignment into a composite business key — for example PERSON_ID||'-'||INSTANCE_CODE||'-'||'EMPLOYEE' — which is significant because the user search term assignment_pk relates directly to this design pattern. Rather than exposing a native single-column primary key, the view surfaces a concatenated identity string that functions as the effective assignment primary key for downstream EDW processing. The presence of a secondary key column (the same concatenation minus the trailing type token) reflects the layering convention used across EDW views to separate the person-level key from the assignment-level key.

Underlying Base Objects

The ETRM metadata records no formally documented referenced base objects, and the view itself is annotated "Not implemented in this database," indicating it is a seeded definition that may not exist as a queryable object in every environment. The view text, however, reveals its constituent sources:

• PER_ALL_PEOPLE_F — the dated employee table supplying PERSON_ID, FULL_NAME, EMPLOYEE_NUMBER, EFFECTIVE_START_DATE, CREATION_DATE, and LAST_UPDATE_DATE.
• HR_ALL_ORGANIZATION_UNITS — the organization/business group table joined on BUSINESS_GROUP_ID (or ORG_ID) to return the organization NAME and its audit columns.
• EDW_LOCAL_INSTANCE — supplying INSTANCE_CODE, used as a component of the composite assignment key.
• RA_SALESREPS_ALL — the sales representative table, joined by ORG_ID, contributing salesrep-based assignment rows.
• MTL_PLANNERS — the planner table (referenced in the truncated portion of the view), contributing planner assignment rows.

Because the view is a UNION ALL of these branches, each underlying table contributes a distinct slice of assignment records sharing a common column layout. The join to PER_ALL_PEOPLE_F is filtered to the maximum EFFECTIVE_START_DATE per PERSON_ID, ensuring only the latest dated row is used.

Key Columns

The column projection consists primarily of derived and aliased expressions:

• Assignment key — PERSON_ID (or SALESREP_ID, PLANNER_CODE) concatenated with INSTANCE_CODE and a type token, forming the logical assignment_pk.
• Person/assignment key — the same concatenation without the type suffix.
• Display name — FULL_NAME||'-'||EMPLOYEE_NUMBER annotated "(EMPLOYEE-ASSIGNMENT)", or the equivalent SALESREP/PLANNER labels.
• Organization name — ORG.NAME from HR_ALL_ORGANIZATION_UNITS.
• Employee number — SUBSTR(P.EMPLOYEE_NUMBER, 1, 20), the only branch exposing this value.
• Effective dates — effective start/end dates, populated fully for salesreps via NVL of START_DATE_ACTIVE and END_DATE_ACTIVE.
• Instance code and audit columns — CREATION_DATE and GREATEST(LAST_UPDATE_DATE) for delta extraction.

Common Use Cases and Queries

The view supports EDW extraction of a consolidated assignment dimension and reconciliation of person identity across HR and non-HR assignment sources.

SELECT assignment_key, display_name, organization_name, effective_start_date FROM EDW_HR_PERSON_ASSIGNMENTS_LCV WHERE organization_name = :org;

SELECT * FROM EDW_HR_PERSON_ASSIGNMENTS_LCV WHERE assignment_key LIKE :person_id || '%' ORDER BY effective_start_date DESC;

Audit-based delta extraction uses the greatest last-update column: WHERE greatest_last_update_date > :last_run. Where the view is not implemented, DBAs may reference the view definition to replicate the same UNION ALL logic directly against the base tables.