In Oracle Fusion HCM, workforce data is structured through multiple foundational entities such as worker, work relationship, assignment, and person records. Among these, the Oracle Fusion HCM Work Relationship table plays a crucial role in representing the legal employment relationship between a worker and an enterprise.
During real implementations, consultants frequently need to access the Work Relationship table when building integrations, reporting solutions, or troubleshooting worker data issues. Whether you are extracting worker employment details using BI Publisher, building integrations using Oracle Integration Cloud (OIC Gen 3), or loading employee data using HDL, understanding the underlying Oracle Fusion HCM Work Relationship table becomes essential.
For example, if an employee moves from contractor to full-time employment or holds multiple legal employer relationships, the work relationship entity captures those details accurately. Without understanding this structure, it becomes difficult to interpret workforce data correctly.
This article explains the Oracle Fusion HCM Work Relationship table, how it works internally, where it fits in the worker data model, and how consultants use it in real implementations.
What is the Oracle Fusion HCM Work Relationship Table?
The Work Relationship in Oracle Fusion represents the legal relationship between a person and a legal employer.
A single worker can have multiple work relationships depending on their employment history or business structure.
For example:
| Scenario | Work Relationship |
|---|---|
| Employee hired by Company A | Work Relationship 1 |
| Same employee hired by Company B | Work Relationship 2 |
| Employee converted from contingent worker to employee | New Work Relationship created |
In the database layer, this relationship is stored in the table:
PER_PERIODS_OF_SERVICE
This table is widely referred to as the Work Relationship table in Oracle Fusion HCM.
It stores key information such as:
Date when employment starts
Legal employer
Worker type (Employee or Contingent Worker)
Termination date
Primary work relationship flag
The Work Relationship table acts as the bridge between the person record and assignment records.
Key Tables Related to Work Relationship
In Oracle Fusion HCM architecture, the Work Relationship table is part of a larger workforce data model.
Below are the most important tables involved.
| Table Name | Purpose |
|---|---|
| PER_ALL_PEOPLE_F | Stores person information |
| PER_PERIODS_OF_SERVICE | Stores work relationship details |
| PER_ALL_ASSIGNMENTS_M | Stores assignments |
| PER_JOBS_F | Job information |
| PER_DEPARTMENTS | Department data |
Relationship Diagram (Conceptual)
|
|
PER_PERIODS_OF_SERVICE
|
|
PER_ALL_ASSIGNMENTS_M
This structure means:
A person exists first
That person gets a work relationship
Assignments are created under the work relationship
Understanding this hierarchy is extremely important when developing reports or integrations.
Important Columns in the Work Relationship Table
The PER_PERIODS_OF_SERVICE table contains multiple columns used by Oracle Fusion HCM.
Below are the most commonly used columns during reporting and integrations.
| Column Name | Description |
|---|---|
| PERIOD_OF_SERVICE_ID | Primary key for work relationship |
| PERSON_ID | Links to the person record |
| LEGAL_ENTITY_ID | Legal employer |
| DATE_START | Start date of employment |
| ACTUAL_TERMINATION_DATE | Termination date |
| BUSINESS_GROUP_ID | Enterprise identifier |
| ADJUSTED_SVC_DATE | Adjusted service date |
| PRIMARY_FLAG | Indicates primary relationship |
Example Data
| PERSON_ID | PERIOD_OF_SERVICE_ID | DATE_START | LEGAL_ENTITY |
|---|---|---|---|
| 100234 | 3001 | 01-JAN-2022 | Vision Corporation |
| 100234 | 3005 | 15-MAR-2024 | Vision Consulting |
This indicates that the worker has two employment relationships.
Why the Work Relationship Table is Important
In real Oracle Fusion HCM implementations, this table is used extensively in multiple areas.
1 Reporting
Reports such as:
Workforce headcount
Active employee reports
Terminated employees
All rely on PER_PERIODS_OF_SERVICE.
2 Integrations
External payroll systems often require employment data including:
Hire date
Termination date
Worker type
Legal employer
This information comes directly from the Work Relationship table.
3 Data Migration
During system implementation, HDL loads create records in the Work Relationship table when workers are loaded.
4 Workforce Lifecycle
Events such as:
Hire
Rehire
Global Transfer
Termination
are tracked through this table.
Real-World Implementation Scenarios
Scenario 1: Worker Rehire
A terminated employee joins the company again.
In Oracle Fusion:
Old work relationship is terminated.
New work relationship record is created.
Example:
| Person | Work Relationship | Start Date |
|---|---|---|
| John | WR001 | 2019 |
| John | WR002 | 2024 |
Consultants must ensure reports fetch the latest active work relationship.
Scenario 2: Global Transfer
An employee moves from India legal entity to USA legal entity.
Oracle creates:
A new Work Relationship
New assignments under the new legal employer
This ensures legal compliance and payroll accuracy.
Scenario 3: Multiple Employment
In some companies, employees can work in multiple legal entities simultaneously.
Example:
| Person | Legal Employer |
|---|---|
| Employee A | Company India |
| Employee A | Company UK |
Each employment relationship will have a separate PERIOD_OF_SERVICE_ID.
Navigation Path to View Work Relationship in Oracle Fusion
Consultants can view Work Relationship data from the Fusion UI.
Navigation
Steps:
Search for the employee.
Open the employee record.
Go to Employment Info.
Select Work Relationship.
Here you will see:
Legal Employer
Worker Type
Start Date
Status
This UI information is stored in PER_PERIODS_OF_SERVICE.
How Work Relationship Is Created in Oracle Fusion
When HR performs a Hire transaction, Oracle automatically creates a work relationship.
Example Hire Process
Step 1 – Navigate
Step 2 – Enter Person Details
Example:
| Field | Value |
|---|---|
| First Name | Ravi |
| Last Name | Kumar |
| National ID | 123456789 |
Step 3 – Enter Work Relationship Details
| Field | Value |
|---|---|
| Legal Employer | Vision India |
| Worker Type | Employee |
| Start Date | 01-Apr-2025 |
Step 4 – Enter Assignment
| Field | Value |
|---|---|
| Business Unit | Vision India BU |
| Department | Finance |
| Job | Financial Analyst |
Step 5 – Submit Transaction
Once the transaction is approved:
Oracle creates a record in PER_PERIODS_OF_SERVICE
Assignment record is created in PER_ALL_ASSIGNMENTS_M
Query Example for Work Relationship Table
Consultants often query this table for reporting or troubleshooting.
Example SQL query:
person_id,
period_of_service_id,
date_start,
actual_termination_date
FROM
per_periods_of_service
WHERE
person_id = 100234;
This query retrieves the worker’s employment relationships.
Testing the Work Relationship Data
After configuration or data load, consultants should validate the data.
Example Test
Scenario: Hire an employee.
Expected Results:
| Validation | Expected Outcome |
|---|---|
| Person created | Record in PER_ALL_PEOPLE_F |
| Work relationship created | Record in PER_PERIODS_OF_SERVICE |
| Assignment created | Record in PER_ALL_ASSIGNMENTS_M |
Validation Query
FROM per_periods_of_service
WHERE person_id = :person_id;
Ensure:
Start date is correct
Legal employer is correct
Status is active
Common Implementation Challenges
1 Incorrect Legal Employer
If the wrong legal employer is selected during hiring:
Payroll integration fails
Compliance issues arise
2 Duplicate Work Relationships
Incorrect HDL loads may create duplicate records.
This causes issues in:
Reporting
Payroll integrations
3 Reporting Confusion
Many reports mistakenly use:
instead of:
This leads to incorrect hire date reporting.
Best Practices Used by Oracle Consultants
Always Use Work Relationship for Employment Dates
Hire date and termination date should be taken from:
not assignment tables.
Filter Active Work Relationships
Example condition:
This ensures only active employees appear.
Use Primary Work Relationship Flag
Some employees have multiple relationships.
Use:
to identify the main employment.
Align Integrations with Work Relationship Events
Integration triggers should consider:
Hire
Rehire
Global Transfer
Termination
These events impact work relationship data.
Frequently Asked Questions (FAQ)
1. What is the Work Relationship table in Oracle Fusion HCM?
The Work Relationship table in Oracle Fusion HCM is PER_PERIODS_OF_SERVICE, which stores employment relationships between a worker and a legal employer.
2. Can a worker have multiple work relationships?
Yes. A worker can have multiple work relationships if they:
Work for multiple legal employers
Are rehired
Have concurrent employment
3. What is the difference between Work Relationship and Assignment?
| Work Relationship | Assignment |
|---|---|
| Legal employment relationship | Job role within organization |
| Stored in PER_PERIODS_OF_SERVICE | Stored in PER_ALL_ASSIGNMENTS_M |
Summary
The Oracle Fusion HCM Work Relationship table is a fundamental component of the workforce data model. Stored in PER_PERIODS_OF_SERVICE, it defines the legal employment relationship between a worker and a legal employer.
Understanding this table is essential for:
Workforce reporting
Data migration
Payroll integrations
Workforce lifecycle management
Oracle consultants frequently interact with this table while developing BI Publisher reports, integrations using OIC Gen 3, and HDL data loads.
When designing solutions in Oracle Fusion HCM, always remember the hierarchy:
A clear understanding of this structure helps avoid reporting errors, integration failures, and data inconsistencies.
For deeper reference and official product documentation, refer to Oracle’s documentation portal:
https://docs.oracle.com/en/cloud/saas/index.html