Oracle HCM Person vs Assignment vs Work Relationship Data Model Explained

Understanding Oracle Fusion HCM's core data model is fundamental for any HCM consultant, developer, or analyst. The three foundational entities—Person, Assignment, and Work Relationship—form the backbone of how Oracle stores and manages worker information. This comprehensive guide explains the differences, relationships, and practical usage patterns for each entity.

Many HCM professionals struggle with when to query PER_ALL_PEOPLE_F versus PER_ALL_ASSIGNMENTS_F versus PER_WORK_RELATIONSHIPS. Each table serves distinct purposes and contains different types of information, making the choice critical for accurate reporting and data analysis.

Core Data Model Overview

Oracle HCM's worker data model is built on three primary entities that work together to provide a complete view of an individual's employment relationship:

This hierarchical relationship reflects real-world employment scenarios:

• A Person represents an individual's identity and personal information
• A Work Relationship represents the legal employment contract with the organization
• An Assignment represents the specific job, role, and organizational position

Person Entity (PER_ALL_PEOPLE_F)

The Person entity represents the individual as a human being, independent of their employment status. It contains biographical information, personal details, and characteristics that remain consistent across employment relationships.

What's Stored in PER_ALL_PEOPLE_F

When to Use PER_ALL_PEOPLE_F

Query the Person entity when you need:

• Employee directory information - Names, contact details, photos
• Demographic reporting - Age, gender, ethnicity analysis
• Compliance reporting - EEO, diversity metrics
• Person-centric views - One row per individual regardless of assignments
• Historical person data - Name changes, address history

Sample Query: Employee Directory

-- Get active employee directory with contact information
SELECT 
    p.PERSON_NUMBER,
    p.FIRST_NAME,
    p.LAST_NAME,
    p.DISPLAY_NAME,
    p.EMAIL_ADDRESS,
    p.PHONE_NUMBER,
    p.DATE_OF_BIRTH,
    p.GENDER
FROM PER_ALL_PEOPLE_F p
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
ORDER BY p.LAST_NAME, p.FIRST_NAME;

Key Considerations for Person Data

Important points about Person entity:

• One person, multiple relationships: A person can have multiple work relationships (rehires, concurrent employment)
• Persistent identity: PERSON_ID remains constant across all employment relationships
• Personal data privacy: Access may be restricted based on data security roles
• Global identifier: PERSON_NUMBER is often used as the employee ID visible to end users

Work Relationship Entity (PER_WORK_RELATIONSHIPS)

The Work Relationship entity represents the legal employment contract between an individual and the organization. It defines the terms and conditions of employment, including start date, employment category, and termination details.

What's Stored in PER_WORK_RELATIONSHIPS

When to Use PER_WORK_RELATIONSHIPS

Query the Work Relationship entity when you need:

• Employment history - Hire dates, termination dates, rehire analysis
• Worker type reporting - Employee vs contractor classification
• Legal employer analysis - Multi-entity organizations
• Contract-level information - One row per employment contract
• Workforce planning - Headcount by legal entity

Sample Query: Employment History

-- Get employment history with work relationship details
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    wr.WORK_RELATIONSHIP_ID,
    wr.START_DATE,
    wr.TERMINATION_DATE,
    wr.WORKER_TYPE,
    wr.LEGAL_EMPLOYER_NAME,
    wr.PRIMARY_FLAG,
    CASE 
        WHEN wr.TERMINATION_DATE IS NULL THEN 'Active'
        ELSE 'Terminated'
    END as EMPLOYMENT_STATUS
FROM PER_ALL_PEOPLE_F p
JOIN PER_WORK_RELATIONSHIPS wr 
  ON p.PERSON_ID = wr.PERSON_ID
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
ORDER BY p.PERSON_NUMBER, wr.START_DATE;

Work Relationship Patterns

Understanding common work relationship patterns is crucial for accurate reporting:

Assignment Entity (PER_ALL_ASSIGNMENTS_F)

The Assignment entity represents the specific job, role, and organizational position within a work relationship. It contains operational details about what the person does, where they work, and how they're positioned in the organization.

What's Stored in PER_ALL_ASSIGNMENTS_F

When to Use PER_ALL_ASSIGNMENTS_F

Query the Assignment entity when you need:

• Organizational reporting - Who reports to whom, org charts
• Job and position analysis - Role distribution, career progression
• Department/location reporting - Headcount by location or department
• Management hierarchy - Manager-employee relationships
• Operational HR reports - Most day-to-day HR reporting needs

Sample Query: Current Assignments with Hierarchy

-- Get current assignments with manager and organizational details
SELECT 
    p.PERSON_NUMBER as EMPLOYEE_NUMBER,
    p.DISPLAY_NAME as EMPLOYEE_NAME,
    a.ASSIGNMENT_NUMBER,
    j.JOB_CODE,
    j.NAME as JOB_TITLE,
    d.NAME as DEPARTMENT_NAME,
    l.LOCATION_NAME,
    mgr.DISPLAY_NAME as MANAGER_NAME,
    a.EMPLOYMENT_CATEGORY,
    a.ASSIGNMENT_STATUS_TYPE
FROM PER_ALL_ASSIGNMENTS_F a
JOIN PER_ALL_PEOPLE_F p 
  ON a.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE 
                          AND p.EFFECTIVE_END_DATE
LEFT JOIN PER_JOBS j 
  ON a.JOB_ID = j.JOB_ID
  AND TRUNC(SYSDATE) BETWEEN j.EFFECTIVE_START_DATE 
                          AND j.EFFECTIVE_END_DATE
LEFT JOIN HR_ALL_ORGANIZATION_UNITS d 
  ON a.ORGANIZATION_ID = d.ORGANIZATION_ID
LEFT JOIN HR_LOCATIONS_ALL l 
  ON a.LOCATION_ID = l.LOCATION_ID
LEFT JOIN PER_ALL_PEOPLE_F mgr 
  ON a.MANAGER_ID = mgr.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN mgr.EFFECTIVE_START_DATE 
                          AND mgr.EFFECTIVE_END_DATE
WHERE a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
  AND a.PRIMARY_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE 
                          AND a.EFFECTIVE_END_DATE
ORDER BY d.NAME, p.LAST_NAME;

Assignment Types and Patterns

Assignments can have various patterns based on organizational needs:

Entity Relationships and Data Integrity

Understanding the relationships between these entities is crucial for accurate data queries and reporting:

Cardinality Rules

Common Join Patterns

Here are the most frequently used join patterns when working with these entities:

-- Pattern 1: Person + Current Primary Assignment (Most Common)
SELECT p.*, a.*
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON p.PERSON_ID = a.PERSON_ID
  AND a.PRIMARY_FLAG = 'Y'
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE;

-- Pattern 2: Full Three-Entity Join
SELECT p.*, wr.*, a.*
FROM PER_ALL_PEOPLE_F p
JOIN PER_WORK_RELATIONSHIPS wr 
  ON p.PERSON_ID = wr.PERSON_ID
  AND wr.PRIMARY_FLAG = 'Y'
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON wr.WORK_RELATIONSHIP_ID = a.WORK_RELATIONSHIP_ID
  AND a.PRIMARY_FLAG = 'Y'
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE;

-- Pattern 3: Person with All Work Relationships (Historical View)
SELECT p.*, wr.*, 
       CASE WHEN wr.TERMINATION_DATE IS NULL THEN 'Active' ELSE 'Terminated' END
FROM PER_ALL_PEOPLE_F p
JOIN PER_WORK_RELATIONSHIPS wr 
  ON p.PERSON_ID = wr.PERSON_ID
WHERE TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
ORDER BY p.PERSON_NUMBER, wr.START_DATE;

Practical Usage Scenarios

Let's explore common business scenarios and which entity to use for each:

Scenario 1: Employee Headcount Report

Requirement: Count of active employees by department

Best Entity: Assignment (PER_ALL_ASSIGNMENTS_F)

Rationale: Department is an assignment-level attribute

-- Employee headcount by department
SELECT 
    org.NAME as DEPARTMENT,
    COUNT(*) as EMPLOYEE_COUNT
FROM PER_ALL_ASSIGNMENTS_F a
JOIN HR_ALL_ORGANIZATION_UNITS org 
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
WHERE a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
  AND a.PRIMARY_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
GROUP BY org.NAME
ORDER BY org.NAME;

Scenario 2: Diversity Demographics

Requirement: Gender and ethnicity breakdown

Best Entity: Person (PER_ALL_PEOPLE_F)

Rationale: Demographics are person-level attributes

-- Gender and ethnicity breakdown for active employees
SELECT 
    p.GENDER,
    p.ETHNICITY,
    COUNT(*) as EMPLOYEE_COUNT
FROM PER_ALL_PEOPLE_F p
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
GROUP BY p.GENDER, p.ETHNICITY
ORDER BY p.GENDER, p.ETHNICITY;

Scenario 3: Turnover Analysis

Requirement: Employees who terminated in the last year

Best Entity: Work Relationship (PER_WORK_RELATIONSHIPS)

Rationale: Termination dates are stored at the work relationship level

-- Turnover analysis - employees terminated in last 12 months
SELECT 
    p.PERSON_NUMBER,
    p.DISPLAY_NAME,
    wr.START_DATE,
    wr.TERMINATION_DATE,
    wr.LEGAL_EMPLOYER_NAME,
    ROUND((wr.TERMINATION_DATE - wr.START_DATE) / 365, 2) as TENURE_YEARS
FROM PER_WORK_RELATIONSHIPS wr
JOIN PER_ALL_PEOPLE_F p 
  ON wr.PERSON_ID = p.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
WHERE wr.TERMINATION_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE), -12) AND TRUNC(SYSDATE)
  AND wr.PRIMARY_FLAG = 'Y'
ORDER BY wr.TERMINATION_DATE DESC;

Scenario 4: Manager-Employee Relationships

Requirement: Current reporting structure

Best Entity: Assignment (PER_ALL_ASSIGNMENTS_F)

Rationale: Manager relationships are assignment-specific

-- Current manager-employee relationships
SELECT 
    emp.PERSON_NUMBER as EMPLOYEE_NUMBER,
    emp.DISPLAY_NAME as EMPLOYEE_NAME,
    mgr.PERSON_NUMBER as MANAGER_NUMBER,
    mgr.DISPLAY_NAME as MANAGER_NAME,
    j.NAME as JOB_TITLE,
    org.NAME as DEPARTMENT
FROM PER_ALL_ASSIGNMENTS_F a
JOIN PER_ALL_PEOPLE_F emp 
  ON a.PERSON_ID = emp.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN emp.EFFECTIVE_START_DATE AND emp.EFFECTIVE_END_DATE
LEFT JOIN PER_ALL_PEOPLE_F mgr 
  ON a.MANAGER_ID = mgr.PERSON_ID
  AND TRUNC(SYSDATE) BETWEEN mgr.EFFECTIVE_START_DATE AND mgr.EFFECTIVE_END_DATE
LEFT JOIN PER_JOBS j 
  ON a.JOB_ID = j.JOB_ID
  AND TRUNC(SYSDATE) BETWEEN j.EFFECTIVE_START_DATE AND j.EFFECTIVE_END_DATE
LEFT JOIN HR_ALL_ORGANIZATION_UNITS org 
  ON a.ORGANIZATION_ID = org.ORGANIZATION_ID
WHERE a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
  AND a.PRIMARY_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE
ORDER BY org.NAME, mgr.DISPLAY_NAME, emp.DISPLAY_NAME;

Date-Effective vs Non Date-Effective

Understanding the date-effective pattern differences is crucial:

Performance Considerations

When working with these large tables, consider these performance optimization strategies:

Indexing Strategy

Oracle HCM provides standard indexes on these key columns:

• Primary keys: PERSON_ID, ASSIGNMENT_ID, WORK_RELATIONSHIP_ID
• Date filters: EFFECTIVE_START_DATE, EFFECTIVE_END_DATE
• Status flags: PRIMARY_FLAG, CURRENT_EMPLOYEE_FLAG, ASSIGNMENT_STATUS_TYPE
• Lookup columns: ORGANIZATION_ID, JOB_ID, LOCATION_ID

Query Optimization Tips

-- Good: Filter early and use specific criteria
SELECT p.PERSON_NUMBER, a.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON p.PERSON_ID = a.PERSON_ID
  AND a.PRIMARY_FLAG = 'Y'                    -- Filter in JOIN
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'     -- Filter in JOIN
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'          -- Filter early
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE;

-- Bad: Filter after joining large result sets
SELECT p.PERSON_NUMBER, a.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON p.PERSON_ID = a.PERSON_ID
WHERE p.CURRENT_EMPLOYEE_FLAG = 'Y'
  AND a.PRIMARY_FLAG = 'Y'                    -- Filter in WHERE (late)
  AND a.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'     -- Filter in WHERE (late)
  AND TRUNC(SYSDATE) BETWEEN p.EFFECTIVE_START_DATE AND p.EFFECTIVE_END_DATE
  AND TRUNC(SYSDATE) BETWEEN a.EFFECTIVE_START_DATE AND a.EFFECTIVE_END_DATE;

Common Data Model Pitfalls

Avoid these common mistakes when working with Oracle HCM's data model:

1. Forgetting Date-Effective Filters

-- Wrong: Missing date filters (returns historical data)
SELECT COUNT(*) FROM PER_ALL_PEOPLE_F WHERE CURRENT_EMPLOYEE_FLAG = 'Y';

-- Correct: Include date filters for current data
SELECT COUNT(*) 
FROM PER_ALL_PEOPLE_F 
WHERE CURRENT_EMPLOYEE_FLAG = 'Y'
  AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

2. Mixing Entity Levels

-- Wrong: Trying to get department from Person table
SELECT p.PERSON_NUMBER, p.DEPARTMENT_NAME  -- DEPARTMENT_NAME not in Person!
FROM PER_ALL_PEOPLE_F p;

-- Correct: Get department from Assignment
SELECT p.PERSON_NUMBER, org.NAME as DEPARTMENT_NAME
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F a ON p.PERSON_ID = a.PERSON_ID
JOIN HR_ALL_ORGANIZATION_UNITS org ON a.ORGANIZATION_ID = org.ORGANIZATION_ID;

3. Incorrect Primary Assignment Logic

-- Wrong: Not filtering for primary assignments
SELECT p.PERSON_NUMBER, COUNT(a.ASSIGNMENT_ID) as ASSIGNMENT_COUNT
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F a ON p.PERSON_ID = a.PERSON_ID
GROUP BY p.PERSON_NUMBER;  -- May return multiple assignments per person

-- Correct: Focus on primary assignments for headcount
SELECT p.PERSON_NUMBER, a.ASSIGNMENT_NUMBER
FROM PER_ALL_PEOPLE_F p
JOIN PER_ALL_ASSIGNMENTS_F a 
  ON p.PERSON_ID = a.PERSON_ID
  AND a.PRIMARY_FLAG = 'Y';

Best Practices Summary

Follow these best practices when working with Oracle HCM's core data model:

• Always include date filters for Person and Assignment tables
• Use PRIMARY_FLAG = 'Y' to get the main assignment for each work relationship
• Choose the right entity based on what data you need:
  • Person-level attributes → PER_ALL_PEOPLE_F
  • Employment contracts → PER_WORK_RELATIONSHIPS
  • Job/organizational data → PER_ALL_ASSIGNMENTS_F
• Understand cardinality - a person can have multiple work relationships and assignments
• Join efficiently by including filters in the JOIN conditions
• Test your queries with known data to verify accuracy

Mastering these three core entities is fundamental to successful Oracle HCM development and reporting. Whether you're building REST API integrations, creating OTBI reports, or writing custom SQL queries, understanding the Person-Work Relationship-Assignment model will make your work more accurate and efficient.

Remember that this data model reflects real-world employment complexity—people can be rehired, hold multiple positions, and change roles over time. The three-entity model provides the flexibility to handle these scenarios while maintaining data integrity and auditability.