Search Results edwbv_hr_perm_assign_lcv

Overview

EDWBV_HR_PERM_ASSIGN_LCV is a database view belonging to the HRI — Human Resources Intelligence product family within Oracle E-Business Suite. In ETRM (E-Business Suite Technical Reference Manual) documentation this module is flagged as Obsolete, meaning Oracle no longer actively supports or ships it in current 12.1.1 / 12.2.2 environments. ETRM explicitly notes for this object: "Not implemented in this database." Consequently, in a stock or standard 12.1.1 / 12.2.2 installation the view will typically not exist; it appears only in older deployments where the HRI product or its associated EDW (Enterprise Data Warehouse) staging components were licensed and installed.

The view is a "permanent assignment" (PERM_ASSIGN) low-level conformed view, denoted by the LCV suffix (Load Conformed View). It exposes a person-centric assignment dimension intended for dimensional reporting and ETL into warehouse schemas. It converts transactional HR person and assignment records into a flattened, denormalised row suitable for star-schema loading.

Underlying Base Objects

ETRM documents no referenced base objects for this view. This is consistent with the view being defined entirely through inline SQL over synonyms and package calls rather than through a formal object dependency list. Inspection of the view text reveals references to core HR person data (aliased PEO, referencing PERSON_ID, FULL_NAME, EMPLOYEE_NUMBER, APPLICANT_NUMBER, BUSINESS_GROUP_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, etc.), a period-of-service style source (PPS, supplying DATE_START and ACTUAL_TERMINATION_DATE), a business group source (BGR.NAME), and an instance registry (INST.INSTANCE_CODE). It also calls the HRI_EDW_DIM_PERSON package functions GET_NEAREST_PARENT, IS_A_BUYER, IS_A_PLANNER, and IS_A_SALES_REP, plus HR_GENERAL.DECODE_LOOKUP.

Key Columns

• ASSIGNMENT_PK — composite key of PERSON_ID, INSTANCE_CODE, and a literal type tag ("EMPLOYEE-PERS").
• ALL_FK — constant 'ALL' pseudo-key used to drive an "all values" outer dimension row.
• SPRVSR_LVL1_FK — supervisor hierarchy key derived from TREE.SUB_PERSON_ID and TREE.SUB_LEVEL, with nearest-parent fallback.
• NAME / PERSON_DP / FULL_NAME / LAST_NAME / FIRST_NAME / MIDDLE_NAMES — person descriptive attributes.
• PERSON_NUM — employee or applicant number.
• BUSINESS_GROUP / ORGANIZATION_ID — legal employer context.
• START_DATE / END_DATE — assignment period boundaries.
• EMPLOYEE_FLAG / APPLICANT_FLAG / BUYER_FLAG / PLANNER_FLAG / SALES_REP_FLAG — role indicators decoded through the YES_NO lookup.
• SYS_GEN_FLAG — system-generated marker, hard-coded 'N'.
• SALESREP_ID / PLANNER_CODE — null placeholders for downstream role-specific keys.
• INSTANCE — source instance code for multi-instance consolidation.

Common Use Cases and Queries

The view supports HR workforce dimensional reporting and historical ETL. Typical usage filters on the instance and role flags, or joins the surrogate keys to supervisor or business-group dimensions.

SELECT person_id,
       person_num,
       full_name,
       business_group,
       start_date,
       end_date,
       employee_flag
FROM   edwbv_hr_perm_assign_lcv
WHERE  instance = :p_instance
AND    employee_flag = 'Yes'
ORDER BY full_name;

Because ETRM classifies HRI as obsolete, teams migrating to 12.2.2 should plan to replace this view with standard HRMS extracts (PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F) or Oracle BI Applications HR subject areas before upgrading.