Search Results sprvsr_lvl1_fk

Overview

EDW_HR_PERM_ASSIGN_LCV is an Oracle E-Business Suite 12.1.1 / 12.2.2 view owned by the APPS schema. It is one of the "LCV" (logical current view) family of objects built for the Oracle HRMS and Enterprise Data Warehouse (EDW) reporting layer, which materializes curated HR and assignment information for downstream analytics, extracts, and integrations. The view presents a permanent assignment record (a "perm assign") joined to person-level attributes, exposing a flattened, denormalized projection that combines assignment identity, supervisor hierarchy keys, employment status flags, personal identifiers, and demographic data.

The name suffix "LCV" indicates this is a logical view intended to present the "current" or effective-dated picture of permanent assignments, rather than a transactional base table. Because it is a reporting-oriented object, it abstracts joins between HR person and assignment entities so that query writers do not have to reconstruct the association themselves. The presence of GLOBAL_PERSON_ID, PERSON_ID, and PERSON_DP reflects support for global (multi-organization) person modeling, which is significant in global HR deployments where a single worker may hold assignments across multiple business groups and legal employers. For users searching on global_person_id, this view is a natural access path because it exposes both the local PERSON_ID and the cross-business-group GLOBAL_PERSON_ID.

Underlying Base Objects

The documented definition of APPS.EDW_HR_PERM_ASSIGN_LCV is a straight SELECT of 53 columns from a single referenced object, EDWBV_HR_PERM_ASSIGN_LCV, with no documented base tables beyond that source view. The dependency chain therefore runs from EDW_HR_PERM_ASSIGN_LCV to EDWBV_HR_PERM_ASSIGN_LCV, which in the EDW naming convention is itself a "BV" (business view) object. In practice, EDW business views typically resolve to the HRMS base tables PER_ALL_PEOPLE_F (person data) and PER_ALL_ASSIGNMENTS_F (assignment data), along with supporting lookups, but the ETRM metadata for this object documents only the immediate parent view, not its ultimate tables.

The five trailing NULL columns in the SELECT list are placeholder positions retained to keep the column count and ordinal positions aligned with the logical model, a common technique in EDW view definitions that must remain compatible across versions. Five columns are therefore always null when queried directly.

Key Columns

• ASSIGNMENT_PK — Primary/surrogate key that uniquely identifies the assignment record; the leading column in the view and the logical primary key for joins.
• PERSON_ID — The worker's person identifier within the local business group; the standard HRMS key used across PER tables.
• GLOBAL_PERSON_ID — The cross-business-group person identifier enabling global HR reporting, particularly relevant for workers with assignments in multiple legal entities.
• PERSON_DP, BUSINESS_GROUP, PERSON_NUM — Descriptive person display name, the owning business group, and the employee/applicant number.
• FULL_NAME, LAST_NAME, FIRST_NAME, MIDDLE_NAMES, KNOWN_AS, PREVIOUS_LAST_NAME, NAME_PREFIX, NAME_SUFFIX, TITLE — Name components supporting formatted display and matching.
• START_DATE, END_DATE, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE — Assignment and person record date boundaries used for effective-dating filters.
• EMPLOYEE_FLAG, APPLICANT_FLAG, BUYER_FLAG, PLANNER_FLAG, SALES_REP_FLAG, SYS_GEN_FLAG — System person-type flags that classify the worker for different functional roles.
• SPRVSR_LVL1_FK, ALL_FK — Supervisor hierarchy and organizational foreign keys supporting reporting structures.
• ORGANIZATION_ID, SALESREP_ID, PLANNER_CODE — Organization and sales/planner identifiers linking the assignment to inventory or sales structures.
• EMAIL_ADDRESS, MARITAL_STATUS, GENDER, DATE_OF_BIRTH, NATIONAL_IDENTIFIER, NATIONALITY, TOWN_OF_BIRTH, REGION_OF_BIRTH, COUNTRY_OF_BIRTH, DISABILITY_FLAG, DATE_EMP_DATA_VRFD — Personal and demographic attributes carried for analytics and compliance reporting.
• RESUME_EXISTS, RESUME_UPDATED_DATE, REHIRE_RCMMNDTN, CRRSPNDNC_LANGUAGE, FTE_CAPACITY, STUDENT_STATUS, INTERNAL_LOCATION, MAILSTOP, FAST_PATH_EMPLOYEE — Supplementary HR attributes used in workforce and recruiting analysis.
• INSTANCE, CREATION_DATE, LAST_UPDATE_DATE — Instance and audit columns for multi-instance extracts and incremental loads.

Common Use Cases and Queries

Typical scenarios include global headcount reporting, cross-business-group worker reconciliation, extraction feeds into the enterprise data warehouse, and person/assignment lookups by global person identifier. Because the view is denormalized, a single query can retrieve assignment, person, and demographic attributes without additional joins to PER_ALL_PEOPLE_F or PER_ALL_ASSIGNMENTS_F.

Retrieving all assignments for a known global person identifier:

SELECT global_person_id, person_id, assignment_pk,
       full_name, person_num, business_group,
       effective_start_date, effective_end_date
FROM   apps.edw_hr_perm_assign_lcv
WHERE  global_person_id = :p_global_person_id
ORDER  BY effective_start_date;

Current effective permanent assignments across business groups:

SELECT business_group, person_num, full_name,
       organization_id, employee_flag
FROM   apps.edw_hr_perm_assign_lcv
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
AND    employee_flag = 'Y';

Because the five placeholder columns are always null, they should be excluded from selective column lists. Effective-dating predicates should always be applied when the query intends to represent a point-in-time picture, since the underlying assignment source is date-tracked and may return multiple rows per assignment key. Joins to other EDW or HRMS views should use ASSIGNMENT_PK for assignment-level grain and GLOBAL_PERSON_ID or PERSON_ID for person-level grain, as appropriate to the reporting requirement.