Work Relationship Table in Fusion HCM

Introduction

In Oracle Fusion HCM, understanding the Work Relationship Table is critical for any consultant working on Core HR, Payroll, Benefits, or integrations. The concept of a work relationship defines the legal and employment connection between a worker and an employer, and it is one of the foundational pillars of the Fusion HCM data model.

From an implementation perspective, if you don’t understand how the Work Relationship Table behaves, you will face issues in HDL loads, reporting (OTBI/BIP), integrations, and even employee lifecycle transactions.

This article provides a deep, consultant-level understanding of the Work Relationship Table in Oracle Fusion HCM, including structure, usage, real scenarios, and practical tips.

What is Work Relationship in Oracle Fusion HCM?

A Work Relationship represents the legal association between a person and a legal employer. It determines:

• Employment type (Employee, Contingent Worker, Non-worker)
• Legal employer assignment
• Payroll and statutory eligibility
• Employment lifecycle tracking

Each worker in Oracle Fusion must have at least one work relationship, but can have multiple depending on business scenarios.

Key Point (Consultant Insight)

Think of Work Relationship as the container that holds employment details, while assignments define the job/role inside that container.

What is the Work Relationship Table?

From a technical standpoint, the Work Relationship data is stored primarily in:

Core Table

This is one of the most important tables in Fusion HCM.

Key Columns in PER_PERIODS_OF_SERVICE

Below are the most commonly used columns you’ll encounter in real projects:

Why Work Relationship Table is Important in Oracle Cloud

The Work Relationship Table plays a central role in:

• Employee lifecycle management
• Payroll eligibility
• Security roles and access
• Benefits eligibility
• Reporting and analytics
• Integrations using HDL and REST APIs

Consultant Reality

In most projects, data issues in reports or integrations often trace back to incorrect Work Relationship data.

Key Features of Work Relationship

1. Supports Multiple Work Relationships

A person can have multiple work relationships with different legal employers.

2. Lifecycle Tracking

Tracks:

• Hire
• Rehire
• Transfer
• Termination

3. Legal Entity Mapping

Each work relationship is tied to a Legal Employer, which drives compliance.

4. Integration Anchor

Used in:

• HDL (WorkRelationship.dat)
• REST APIs
• BIP reports

Real-World Business Use Cases

Use Case 1: Multi-Country Employment

An employee works in:

• India (Legal Employer A)
• US (Legal Employer B)

➡ Two work relationships are created.

Use Case 2: Rehire Scenario

An employee:

• Resigns in 2022
• Rejoins in 2024

➡ New Work Relationship is created with a new PERIOD_OF_SERVICE_ID.

Use Case 3: Concurrent Employment

An employee works:

• Full-time in one role
• Part-time in another legal entity

➡ Two active work relationships exist simultaneously.

Work Relationship vs Assignment (Common Confusion)

Consultant Tip

If your issue is related to legal employer or termination → check Work Relationship

If it’s about job, department, salary → check Assignment

Configuration Overview

Before working with Work Relationships, ensure the following setups are completed:

• Enterprise Structure
• Legal Employers
• Business Units
• Job and Position setup
• Worker types configuration

Step-by-Step: Creating Work Relationship in Oracle Fusion

Step 1 – Navigate to Hire Process

Navigator → My Client Groups → Hiring → Hire an Employee

Step 2 – Enter Basic Details

• Name
• Date of Birth
• National ID

Step 3 – Enter Work Relationship Details

Here is where the Work Relationship is created:

Important fields:

• Legal Employer
• Worker Type
• Start Date
• Primary Flag

Step 4 – Add Assignment Details

• Job
• Department
• Location

Step 5 – Submit

Once submitted:

• Work Relationship record is created in PER_PERIODS_OF_SERVICE
• Assignment record is created in PER_ALL_ASSIGNMENTS_M

How Work Relationship is Used in HDL

In HDL, Work Relationship is loaded using:

Object Name

Sample HDL Data

Consultant Tip

Always ensure:

• Legal employer exists
• Person record is already created
• Dates are consistent

Testing the Work Relationship Setup

Test Scenario

Hire a new employee and validate:

Step 1 – Check UI

• Navigate to Person Management
• Verify Work Relationship

Step 2 – Validate in Table

Run query:

Step 3 – Validate Assignment Link

Expected Result

• Work relationship exists
• Assignment linked correctly
• Legal employer mapped

Architecture / Data Flow

Work Relationship sits between:

Flow Explanation

1. Person is created
2. Work Relationship is created
3. Assignment is created under Work Relationship

Common Implementation Challenges

1. Multiple Active Work Relationships

Problem:

• Incorrect reporting
• Payroll conflicts

Solution:

• Validate PRIMARY_FLAG

2. Incorrect Legal Employer

Problem:

• Payroll errors
• Benefits issues

Solution:

• Validate LEGAL_ENTITY_ID

3. Termination Issues

Problem:

• Employee still active in system

Solution:

• Check ACTUAL_TERMINATION_DATE

4. HDL Errors

Common errors:

• Invalid legal employer
• Missing person ID

Best Practices from Real Projects

1. Always Use Primary Flag Carefully

Only one primary work relationship should exist per worker.

2. Avoid Manual Data Fixes

Never update PER_PERIODS_OF_SERVICE directly in DB.

3. Validate Dates Strictly

• Start date < Termination date
• No overlapping relationships

4. Use Proper Naming in HDL

Use meaningful SourceSystemId values.

5. Reporting Optimization

Always join:

• PER_PERSONS
• PER_PERIODS_OF_SERVICE
• PER_ALL_ASSIGNMENTS_M

Advanced Consultant Insight

When to Create Multiple Work Relationships?

Only when:

• Legal employer changes
• Rehire scenario
• Concurrent employment

Do NOT create multiple relationships for:

• Job changes
• Department changes

Frequently Asked Interview Questions

1. What is Work Relationship in Oracle Fusion HCM?

It defines the legal employment relationship between a worker and employer.

2. Which table stores Work Relationship data?

PER_PERIODS_OF_SERVICE.

3. Can a person have multiple work relationships?

Yes, based on business scenarios.

4. What is PERIOD_OF_SERVICE_ID?

Unique identifier for each work relationship.

5. Difference between Work Relationship and Assignment?

Work Relationship = employment
Assignment = job details

6. What happens during rehire?

New Work Relationship is created.

7. What is PRIMARY_FLAG?

Indicates main work relationship.

8. How is termination stored?

ACTUAL_TERMINATION_DATE column.

9. Which HDL object is used?

WorkRelationship.dat

10. What is link between Work Relationship and Assignment?

PERIOD_OF_SERVICE_ID.

11. Can two work relationships be active?

Yes (in concurrent employment scenarios).

12. What is Worker Type?

Employee, Contingent Worker, Non-worker.

13. What causes duplicate records?

Improper HDL loads or data migration errors.

14. How to validate work relationship?

UI + SQL query validation.

15. Why is it critical for payroll?

Because payroll eligibility depends on legal employer.

Real Implementation Scenario Walkthrough

Scenario: Global Organization

Company operates in:

• India
• US
• UK

Employee:

• Joins India entity
• Transfers to US

Solution:

• Terminate India work relationship
• Create new US work relationship

Expert Tips

• Always validate work relationship before debugging payroll issues
• Use OTBI subject area: Workforce Management – Worker Assignment Real Time
• Keep track of rehire scenarios carefully
• Use effective dating properly

Summary

The Work Relationship Table (PER_PERIODS_OF_SERVICE) is one of the most critical components in Oracle Fusion HCM. It defines how a worker is legally associated with an organization and drives everything from payroll to reporting.

From a consultant’s perspective, mastering this concept ensures:

• Accurate data modeling
• Smooth integrations
• Correct reporting
• Successful implementations

Additional Reference

For more detailed documentation, refer to:

https://docs.oracle.com/en/cloud/saas/index.html

FAQs

1. Can we update Work Relationship directly in database?

No, always use UI or HDL.

2. What happens if Work Relationship is missing?

Employee cannot be processed in payroll or assignments.

3. Is Work Relationship mandatory?

Yes, every worker must have at least one.