Search Results person_num

Overview

EDW_HR_PERSON_PERSONS_LCV is a reporting view in the PER (Human Resources) module of Oracle E-Business Suite, intended for use by the Oracle E-Business Suite Embedded Data Warehouse (EDW) and associated HR analytics. It consolidates person-centric records from multiple source populations — employees, sales representatives, and planners — into a single, denormalized row set suitable for dimensional reporting. The "_LCV" suffix indicates a "List of Values / Consolidated View" style construct used by EDW extraction routines rather than by core application forms.

The view is documented as not implemented in the baseline database. It is therefore a metadata artifact defined for EDW/ETRM reference purposes and is materialized only in environments where the EDW schemas have been deployed. Its role is to present a unified person picture — with display names, instance codes, and role classification flags — for downstream warehouse loading and HR LOV population.

Underlying Base Objects

The ETRM metadata documents no explicit base object list, but the view text reveals its dependents through its UNION ALL structure. The primary sources are:

• PER_ALL_PEOPLE_F — the effective-dated person master, filtered to the latest effective start date per person and to rows with a non-null employee number.
• HR_ALL_ORGANIZATION_UNITS — supplies the business group / organization name and last update date.
• RA_SALESREPS_ALL — contributes the sales representative population.
• MTL_PLANNERS (via the ALL PLANNERS branch) — contributes the planner population keyed by PLANNER_CODE.
• EDW_LOCAL_INSTANCE — supplies INSTANCE_CODE for multi-instance identification.

The view also calls EDW_HR_PERSON_PKG functions — BUYER_FLAG, PLANNER_FLAG, and SALES_REP_FLAG — to derive role indicators per person.

Key Columns

• PERSON_ID / SOURCE_ID — the underlying person or role identifier, concatenated with instance code and a role suffix (EMPLOYEE, SALESREP, PLANNER) to form a unique key.
• PERSON_NAME / DISPLAY_NAME — typically FULL_NAME truncated to 85 characters and appended with the employee number for readability.
• ORGANIZATION_NAME — the business group or organization associated with the record.
• EMPLOYEE_NUMBER — truncated to 20 characters; NULL for sales rep and planner rows.
• BUYER_FLAG, PLANNER_FLAG, SALES_REP_FLAG — single-character ('Y'/'N') role classification derived from EDW_HR_PERSON_PKG.
• EFFECTIVE_START_DATE / END_DATE — validity bounds, with sales rep dates taken from START_DATE_ACTIVE/END_DATE_ACTIVE.
• INSTANCE_CODE, CREATION_DATE, LAST_UPDATE_DATE — provenance and audit columns.

Common Use Cases and Queries

The view is typically queried when building HR person LOVs, warehouse extracts, or role-based population reports. It is especially relevant to searches for planner_flag, since PLANNER_FLAG identifies persons designated as planners.

Example — list all planners in the current instance:

SELECT PERSON_ID, PERSON_NAME, ORGANIZATION_NAME
FROM   EDW_HR_PERSON_PERSONS_LCV
WHERE  PLANNER_FLAG = 'Y';

Example — count persons by role:

SELECT SUM(CASE WHEN BUYER_FLAG      = 'Y' THEN 1 END) AS buy,
       SUM(CASE WHEN PLANNER_FLAG    = 'Y' THEN 1 END) AS plan,
       SUM(CASE WHEN SALES_REP_FLAG  = 'Y' THEN 1 END) AS sales
FROM   EDW_HR_PERSON_PERSONS_LCV;

Planners are joined via MTL_PLANNERS.PLANNER_CODE, so the PLANNER branch yields rows where the planner code, organization, instance code, and literal 'PLANNER' form the composite key. Querying this view avoids writing the multi-branch UNION manually and ensures consistent role flags across HR and inventory reporting.