Results for “edw_hr_person_m”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDW_HR_PERSON_M is a table owned by the HRI schema (Human Resources Intelligence) within Oracle E-Business Suite 12.1.1 and 12.2.2. It forms part of the Oracle HR Intelligence/HRMS Analytics data model, which supplies denormalized, warehouse-style data for workforce reporting and Oracle Business Intelligence (OBIEE) HR dashboards. Despite its name suggesting a person-centric master, the object is effectively a wide assignment-level fact/dimension table: the surrogate primary key EDW_HR_PERSON_M_PK is defined on ASGN_ASSIGNMENT_PK_KEY, anchoring each row to an assignment rather than to a person record. With 292 documented columns, the table is a flattened "wide row" structure that blends person attributes, assignment attributes, and up to fifteen levels of supervisory hierarchy into a single row.

From a Data Vault modeling perspective, the ETRM metadata classifies this object heuristically as standalone—no foreign key relationships were mined from its constraint structure. This suggests it is best modeled as a self-contained snapshot or derived record rather than a link table joining hubs. In practice, it functions as a materialized aggregation of workforce data, likely populated by concurrent programs in the HR Intelligence product that extract from the transactional HR tables into the HRI reporting layer, rather than being directly maintained by end users.

Key Information Stored

The surrogate primary key, EDW_HR_PERSON_M_PK, is defined on ASGN_ASSIGNMENT_PK_KEY, distinguishing the row at the assignment grain. Two unique indexes act as business-key candidates: EDW_HR_PERSON_M_U1 covers (ASGN_ASSIGNMENT_PK, ASGN_ASSIGNMENT_PK_KEY), and EDW_HR_PERSON_M_U2 covers ASGN_ASSIGNMENT_PK_KEY alone. This confirms that ASGN_ASSIGNMENT_PK is the natural assignment reference and ASGN_ASSIGNMENT_PK_KEY the warehouse surrogate.

The most significant columns include:

• ASGN_PERSON_ID, ASGN_PERSON_NUM, ASGN_GLOBAL_PERSON_ID — identifiers linking to the person record.
• ASGN_ASSIGNMENT_ID (via the S01–S15 assignment columns) and ASGN_BUSINESS_GROUP_ID — assignment and business group context.
• ASGN_FULL_NAME, ASGN_FIRST_NAME, ASGN_LAST_NAME, ASGN_NAME_DISPLAY — personal name attributes.
• ASGN_EMPLOYEE_NUMBER, ASGN_APPLICANT_NUMBER, ASGN_EMPLOYEE_FLAG, ASGN_APPLICANT_FLAG — worker classification.
• ASGN_START_DATE, ASGN_END_DATE, ASGN_EFFECTIVE_START_DATE, ASGN_EFFECTIVE_END_DATE — assignment lifecycle dates.
• ASGN_PERSON_TYPE_ID, ASGN_BUSINESS_GROUP, ASGN_NAME — assignment type and org context.
• S01_SPRVSR_LVL1_PK_KEY through S15_SPRVSR_LVL15_PK_KEY — fifteen levels of supervisory hierarchy pre-resolved into the row.

The S01–S15 block (each containing PERSON_ID, ASSIGNMENT_ID, NAME, INSTANCE, SPRVSR_DP, USER_ATTRIBUTE1–5, and creation/last-update audit columns) is the defining feature of this table, enabling supervisor-chain reporting without recursive joins.

Common Use Cases and Queries

The table is used for HR analytics, supervisor hierarchy reporting, and workforce headcount or diversity dashboards. A typical query returning active employees with their direct manager:

• SELECT ASGN_PERSON_NUM, ASGN_FULL_NAME, ASGN_EMPLOYEE_NUMBER, S01_NAME AS supervisor_name FROM HRI.EDW_HR_PERSON_M WHERE ASGN_EMPLOYEE_FLAG = 'Y' AND SYSDATE BETWEEN ASGN_START_DATE AND NVL(ASGN_END_DATE, SYSDATE);
• Supervisor span-of-control analysis: group by S01_SPRVSR_LVL1_PK_KEY to count direct reports.
• Workforce composition reporting on ASGN_GENDER, ASGN_MARITAL_STATUS, or ASGN_NATIONALITY.
• Hierarchy path extraction using the S01–S15 chain to reconstruct organizational structure up to fifteen levels deep.

Because the table is denormalized, queries avoid joins to PER_ALL_ASSIGNMENTS_F or PER_ALL_PEOPLE_F, making it well suited for OBIEE RPD physical layer sources and ETL staging into downstream marts.

Related Objects

Given the standalone classification, this object has no enforced FK relationships, but functionally it draws from and relates to core HR tables:

• PER_ALL_PEOPLE_F — source of person attributes; join on ASGN_PERSON_ID = PERSON_ID.
• PER_ALL_ASSIGNMENTS_F — source of assignment data; join on ASGN_ASSIGNMENT_PK / ASGN_ASSIGNMENT_ID = ASSIGNMENT_ID.
• HR_ALL_ORGANIZATION_UNITS — business group and organization context via ASGN_BUSINESS_GROUP_ID.
• PER_PERSON_TYPES — classification via ASGN_PERSON_TYPE_ID.
• PER_JOBS and PER_POSITIONS — role context typically carried through related HRI extracts.
• FND_USER and related HRI EDW tables — for reporting security and further analytics joins.

Each of the fifteen S01–S15 supervisory blocks references the same person/assignment universe, so recursive self-joins to EDW_HR_PERSON_M itself can reconstruct multi-level management chains.