Oracle Fusion HCM Database Tables

Oracle Fusion HCM Database Tables – A Complete Consultant Guide

Oracle Fusion HCM Database Tables are the backbone of reporting, integrations, and data analysis in Oracle Cloud HCM. Whether you are working on BI reports, HDL loads, OTBI analysis, or OIC integrations, understanding these tables is critical for every consultant.

In real projects, I’ve seen many consultants struggle not because they don’t know HCM functionality—but because they don’t know where the data is stored. This blog solves that gap with a practical, real-world approach.

What are Oracle Fusion HCM Database Tables?

Oracle Fusion HCM Database Tables store all employee, organizational, payroll, and transactional data in structured formats within the Fusion Cloud environment.

Unlike legacy systems like EBS:

Fusion uses date-effective tables
Data is split across multiple normalized tables
Uses secured views (like _VL, _F, _TL tables) instead of direct access

Key Characteristics

Feature	Description
Date Effectivity	Tracks historical changes
Multi-language support	Using _TL tables
Security	Data secured via roles
Normalized Design	Data spread across multiple tables

Key HCM Database Table Types

Understanding table suffixes is crucial for reporting and integrations.

1. _F Tables (Date Effective Tables)

Example:

PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_F

These tables store historical records with effective dates.

Important columns:

EFFECTIVE_START_DATE
EFFECTIVE_END_DATE

2. _TL Tables (Translation Tables)

Example:

HR_ORGANIZATION_UNITS_TL

Used for:

Multi-language support
Storing translated names/descriptions

3. _VL Views (View Layers)

Example:

PER_PERSON_NAMES_VL

These are:

Pre-joined views
Easier to query
Recommended for reporting

4. _B Tables (Base Tables)

Example:

PER_JOBS_B

Stores:

Core transactional data

Core Oracle Fusion HCM Tables Every Consultant Must Know

Employee Core Tables

Table Name	Purpose
PER_ALL_PEOPLE_F	Employee master data
PER_PERSON_NAMES_F	Employee names
PER_EMAIL_ADDRESSES	Email details
PER_PHONES	Phone numbers

Assignment Tables

Table Name	Purpose
PER_ALL_ASSIGNMENTS_F	Employee assignments
PER_ASSIGNMENT_SUPERVISORS_F	Manager relationships

Organization Tables

Table Name	Purpose
HR_ORGANIZATION_UNITS_F	Departments
HR_ALL_ORGANIZATION_UNITS_F	Org hierarchy

Job and Position Tables

Table Name	Purpose
PER_JOBS_F	Job definitions
HR_ALL_POSITIONS_F	Position details

Payroll Tables

Table Name	Purpose
PAY_ELEMENT_ENTRIES_F	Salary components
PAY_PAYROLL_ACTIONS	Payroll processing

Real-World Integration Use Cases

1. Employee Data Integration to Third-Party Systems

Scenario:
A company integrates Fusion HCM with a payroll vendor.

Tables used:

PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_F
PAY_ELEMENT_ENTRIES_F

Outcome:

Extract employee + salary data
Send via OIC Gen 3 integration

2. Custom BI Publisher Reports

Scenario:
HR wants a report with employee + manager + department.

Tables used:

PER_ALL_PEOPLE_F
PER_ASSIGNMENT_SUPERVISORS_F
HR_ORGANIZATION_UNITS_F

3. Data Migration using HDL

Scenario:
Bulk employee upload.

Tables mapped indirectly via HDL:

Worker.dat → PER_ALL_PEOPLE_F
Assignment.dat → PER_ALL_ASSIGNMENTS_F

Architecture / Technical Flow

How Data Flows in Fusion HCM

User enters data via UI
Data stored in base tables (_B)
Date-effective records stored in _F tables
Views (_VL) expose data for reporting
Security applied through roles

Example Flow

Employee Creation:

UI → PER_ALL_PEOPLE_F → PER_PERSON_NAMES_F → PER_ALL_ASSIGNMENTS_F

Prerequisites Before Working with Tables

Before querying or integrating:

Access to BI Publisher / OTBI
SQL knowledge (joins, date filters)
Understanding of HCM data model
Role-based access (important!)

Step-by-Step: Querying Employee Data

Step 1 – Identify Required Data

Example:

Employee Name
Employee Number
Department

Step 2 – Identify Tables

PER_ALL_PEOPLE_F
PER_PERSON_NAMES_F
PER_ALL_ASSIGNMENTS_F
HR_ORGANIZATION_UNITS_F

Step 3 – Sample Query

SELECT 
papf.person_number,
ppnf.full_name,
haouf.name AS department_name
FROM 
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
hr_all_organization_units_f haouf
WHERE 
papf.person_id = ppnf.person_id
AND papf.person_id = paaf.person_id
AND paaf.organization_id = haouf.organization_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date;

Step 4 – Key Points

Always use effective date filters
Use joins carefully
Avoid direct base tables when possible

Testing the Technical Component

Test Scenario

Run report for active employees.

Expected Output

Only current employees
Correct department mapping
No duplicate records

Validation Checklist

Check effective dates
Validate joins
Verify data security

Common Errors and Troubleshooting

1. Duplicate Records

Cause:
Missing effective date condition

Solution:
Always use:

SYSDATE BETWEEN effective_start_date AND effective_end_date

2. Missing Data

Cause:
Wrong joins

Solution:
Verify foreign keys like:

PERSON_ID
ASSIGNMENT_ID

3. Performance Issues

Cause:
Querying base tables directly

Solution:
Use:

_VL views
Indexed columns

Best Practices from Real Projects

1. Always Use _VL Views for Reporting

They simplify joins and improve readability.

2. Never Ignore Date Effectivity

This is the #1 mistake beginners make.

3. Understand Table Relationships

Example:

PERSON → ASSIGNMENT → ORGANIZATION

4. Avoid Hardcoding Values

Always use dynamic filters like:

SYSDATE
PARAMETERS

5. Document Your Queries

Helps in debugging and future reuse.

Frequently Asked Interview Questions

1. What is PER_ALL_PEOPLE_F?

Stores employee master data with date-effectivity.

2. Difference between _F and _TL tables?

_F → Date effective data
_TL → Translated data

3. What is PER_ALL_ASSIGNMENTS_F?

Stores employee assignment details.

4. Why use effective dates?

To track historical changes.

5. What is a _VL view?

A pre-joined view for reporting.

6. How to get current employee records?

Use SYSDATE filter.

7. What is PERSON_ID?

Unique identifier for employees.

8. Difference between JOB and POSITION?

Job = Role definition
Position = Specific instance

9. What is HDL mapping?

Mapping files to database tables.

10. How to improve query performance?

Use indexed columns and views.

11. What is ORGANIZATION_ID?

Links employee to department.

12. What is ASSIGNMENT_ID?

Unique identifier for employee assignments.

Real Implementation Insights

In one real project:

Client needed real-time employee sync
Issue: Duplicate records
Root cause: Missing effective date filters

Fix:

Applied date logic
Used PER_PERSON_NAMES_VL

Result:

Clean, accurate data integration

Expert Tips

Always start with PER_ALL_PEOPLE_F
Then join assignments
Then add organization data
Build queries step by step

Summary

Oracle Fusion HCM Database Tables are essential for:

Reporting
Integrations
Data analysis

Mastering these tables:

Improves your technical confidence
Helps in real-time projects
Makes you interview-ready

For deeper reference, always review official documentation:
https://docs.oracle.com/en/cloud/saas/index.html

FAQs

1. Which table stores employee data in Fusion HCM?

PER_ALL_PEOPLE_F stores core employee information.

2. Why are multiple tables used instead of one?

Fusion uses normalized design for flexibility and performance.

3. Can we directly query base tables?

Not recommended—use views like _VL for better performance and security.