Introduction
Tables in Oracle Fusion HCM are the backbone of how employee, organizational, and transactional data is stored, processed, and reported. As a consultant working on multiple Oracle Fusion HCM implementations, you quickly realize that understanding tables in Oracle Fusion HCM is not optional—it’s critical for reporting, integrations, data migration, and troubleshooting.
Whether you’re building OTBI reports, extracting data via BI Publisher, designing HDL loads, or integrating with external systems through OIC Gen 3, having a strong grip on table structures gives you a massive advantage.
In this blog, we will break down Oracle Fusion HCM tables from a practical implementation perspective, covering key tables, how they relate, and how you actually use them in real projects.
What are Tables in Oracle Fusion HCM?
In Oracle Fusion HCM, tables are database objects that store structured data related to:
Employees
Assignments
Jobs
Positions
Organizations
Payroll
Absence
Benefits
Unlike legacy systems, Fusion uses a date-effective data model, meaning most tables track historical changes using:
Effective Start Date
Effective End Date
Example
If an employee changes department:
Old record is end-dated
New record is created with updated values
This allows full audit and history tracking.
Key Features of Tables in Oracle Fusion HCM
1. Date Effectivity
Most HCM tables follow date tracking:
| Column Name | Purpose |
|---|---|
| EFFECTIVE_START_DATE | Record start |
| EFFECTIVE_END_DATE | Record end |
2. Surrogate Keys
Instead of natural keys, Fusion uses system-generated IDs:
PERSON_ID
ASSIGNMENT_ID
ORGANIZATION_ID
3. Multi-Language Support
Tables ending with _TL store translated data.
Example:
PER_JOBS_F → Base table
PER_JOBS_F_TL → Translations
4. Secured Views vs Base Tables
As a consultant, you should never directly use base tables in reports.
Instead, use:
Secured views (like PER_PERSONS)
OTBI subject areas
BI Publisher data models
5. Audit and Who Columns
Every table contains audit columns:
CREATED_BY
CREATION_DATE
LAST_UPDATED_BY
Real-World Business Use Cases
Use Case 1: Employee Master Data Extraction
A client needed a daily interface to a third-party payroll system.
We used:
PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_M
to extract:
Employee details
Department
Job
Use Case 2: Building Custom Headcount Report
Using BI Publisher:
Joined PER_ALL_ASSIGNMENTS_M with HR_ORGANIZATION_UNITS
Filtered active employees
Use Case 3: Data Migration via HDL
Understanding table structure helps map HDL files:
| HDL Object | Backend Table |
|---|---|
| Worker.dat | PER_ALL_PEOPLE_F |
| Assignment.dat | PER_ALL_ASSIGNMENTS_M |
Core Tables in Oracle Fusion HCM
1. PER_ALL_PEOPLE_F
Stores employee personal information.
Important Columns:
PERSON_ID
PERSON_NUMBER
DATE_OF_BIRTH
2. PER_ALL_ASSIGNMENTS_M
Stores assignment details.
Important Columns:
ASSIGNMENT_ID
PERSON_ID
JOB_ID
ORGANIZATION_ID
3. PER_PERSON_NAMES_F
Stores employee names.
4. PER_JOBS_F
Stores job definitions.
5. HR_ALL_ORGANIZATION_UNITS_F
Stores departments and organizations.
6. PER_GRADES_F
Stores grades.
7. PER_POSITIONS_F
Stores position details.
Table Relationships (Important for Reporting)
Here is a simplified relationship:
PER_ALL_PEOPLE_F → PERSON_ID
PER_ALL_ASSIGNMENTS_M → PERSON_ID
PER_JOBS_F → JOB_ID
HR_ALL_ORGANIZATION_UNITS_F → ORGANIZATION_ID
Example Join Logic
a.assignment_id,
j.name job_name,
o.name department
FROM per_all_people_f p,
per_all_assignments_m a,
per_jobs_f j,
hr_all_organization_units_f o
WHERE p.person_id = a.person_id
AND a.job_id = j.job_id
AND a.organization_id = o.organization_id;
Architecture / Technical Flow
Data Flow in Oracle Fusion HCM
User enters data in UI
Data stored in base tables
Secured views expose data
Reporting tools access secured layers
Integration Flow (OIC Gen 3)
Extract data via BI Publisher or REST
Transform in OIC
Send to external systems
Prerequisites for Working with Tables
Before working with tables:
Access to BI Publisher
Knowledge of SQL
Understanding of HCM data model
Required roles:
BI Administrator
HCM Data Role
Step-by-Step: Accessing Table Data via BI Publisher
Step 1 – Navigate to BI Publisher
Navigator → Tools → Reports and Analytics
Step 2 – Create Data Model
Click New → Data Model
Select SQL Query
Step 3 – Write Query
Example:
FROM per_all_people_f
WHERE sysdate BETWEEN effective_start_date AND effective_end_date;
Step 4 – Define Parameters
Optional filters:
Business Unit
Department
Step 5 – Save and Run
Save Data Model
Create Report
Run output
Testing the Setup
Example Scenario
Test: Extract active employees
Steps:
Run report
Validate employee count
Cross-check with UI
Expected Results
Only active employees
No duplicate records
Correct department mapping
Validation Checks
Effective date logic
Join accuracy
Null values
Common Implementation Challenges
1. Duplicate Records
Cause:
Multiple effective dated rows
Solution:
Use date filters
2. Missing Data
Cause:
Wrong joins
Solution:
Use proper foreign keys
3. Security Restrictions
Cause:
Using base tables
Solution:
Use secured views
4. Performance Issues
Cause:
Large joins
Solution:
Optimize queries
Best Practices
1. Always Use Effective Date Filters
2. Avoid Direct Table Usage in OTBI
Use:
Subject areas
Secured views
3. Understand Key Relationships
Before writing any query, identify:
Primary key
Foreign key
4. Use Aliases for Clarity
Improves readability and debugging.
5. Validate with UI Data
Always cross-check:
Reports vs UI
6. Use Incremental Extracts for Integrations
Instead of full loads:
Use LAST_UPDATE_DATE
Real Consultant Tips
Always start with PER_ALL_PEOPLE_F + PER_ALL_ASSIGNMENTS_M
80% of HCM reports use these two tables
Maintain your own data model cheat sheet
Use Oracle documentation for latest table structures (26A updates)
Summary
Understanding tables in Oracle Fusion HCM is a foundational skill for any consultant working in reporting, integrations, or data migration. These tables define how data is structured, stored, and accessed across the system.
From employee records to organizational hierarchies, everything flows through a well-designed relational model with date-effectivity at its core. By mastering key tables, relationships, and best practices, you can significantly improve your efficiency in real-world projects.
For deeper technical details, refer to Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html
FAQs
1. What is the most important table in Oracle Fusion HCM?
PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M are the most critical tables used in almost all reports and integrations.
2. Why do we use effective start and end dates?
To track historical changes and maintain auditability of employee data.
3. Can we directly query tables in Oracle Fusion?
No. In most cases, you should use secured views, BI Publisher, or OTBI.