Â
Introduction
In any Oracle Fusion HCM implementation, understanding Oracle Fusion HCM Tables is critical for both functional consultants and technical developers. Whether you are working on reporting, integrations, or data migration, these tables form the backbone of how data is stored and retrieved in Oracle Fusion Cloud HCM.
In real projects, consultants often face situations where UI-based tools like OTBI or BI Publisher are not enough. That’s when a solid understanding of backend tables helps in troubleshooting, custom reporting, and integrations.
This blog provides a practical, implementation-focused deep dive into Oracle Fusion HCM tables based on real-world consulting experience.
What are Oracle Fusion HCM Tables?
Oracle Fusion HCM tables are database objects that store employee, organizational, payroll, and transactional data within the Fusion Cloud environment.
Unlike legacy systems:
- Direct database access is restricted
- Tables are accessed via:
- BI Publisher (BIP)
- OTBI (logical layer)
- HCM Extracts
- HDL / REST APIs
Key Concept
Fusion uses a secured data model with:
- Date-effective tables
- Multi-language support (TL tables)
- Audit and history tracking
Key Categories of Oracle Fusion HCM Tables
From an implementation perspective, tables are broadly divided into the following categories:
1. Core HR Tables
These tables store employee and assignment data.
| Table Name | Description |
|---|---|
| PER_ALL_PEOPLE_F | Person details (date effective) |
| PER_ALL_ASSIGNMENTS_M | Assignment information |
| PER_PERSON_NAMES_F | Employee names |
| PER_EMAIL_ADDRESSES | Email details |
👉 Real-time usage: Employee master data reporting
2. Organization Tables
| Table Name | Description |
|---|---|
| HR_ORGANIZATION_UNITS_F | Organization structure |
| HR_ALL_ORGANIZATION_UNITS_F_TL | Org names (translated) |
👉 Used in: Department hierarchy reports
3. Payroll Tables
| Table Name | Description |
|---|---|
| PAY_PAYROLL_RELATIONSHIPS | Payroll relationships |
| PAY_RUN_RESULTS | Payroll results |
| PAY_ELEMENT_ENTRIES_F | Element entries |
👉 Used in: Payslip and payroll reconciliation reports
4. Absence Management Tables
| Table Name | Description |
|---|---|
| ANC_PER_ABS_ENTRIES | Absence records |
| ANC_ABSENCE_PLANS_F | Absence plans |
5. Talent Management Tables
| Table Name | Description |
|---|---|
| HRT_PROFILES_B | Talent profiles |
| HRT_EVALUATIONS | Performance evaluations |
6. Time and Labor Tables
| Table Name | Description |
|---|---|
| HWM_TM_REC | Time records |
| HWM_TM_REC_GRP | Time group data |
Important Table Concepts in Fusion HCM
1. Date Effectivity
Most tables end with _F:
Example:
- PER_ALL_PEOPLE_F
This means:
- Records are stored with:
- EFFECTIVE_START_DATE
- EFFECTIVE_END_DATE
👉 Real-world scenario:
If an employee changes department, a new row is created instead of updating the old one.
2. Translation Tables (_TL)
Example:
- HR_ALL_ORGANIZATION_UNITS_F_TL
These store:
- Language-specific values
👉 Important in global implementations.
3. Multi-Row Tables (_M)
Example:
- PER_ALL_ASSIGNMENTS_M
These handle:
- Multiple assignments per employee
Real-World Business Use Cases
Use Case 1: Employee Master Report
A client needs a complete employee report with department, email, and job details.
Tables used:
- PER_ALL_PEOPLE_F
- PER_ALL_ASSIGNMENTS_M
- HR_ORGANIZATION_UNITS_F
- PER_EMAIL_ADDRESSES
👉 Built using BI Publisher data model.
Use Case 2: Payroll Validation Report
Finance team wants to validate payroll results before processing.
Tables:
- PAY_RUN_RESULTS
- PAY_ELEMENT_ENTRIES_F
👉 Helps identify incorrect salary components.
Use Case 3: Absence Tracking Dashboard
HR wants to track employee leaves.
Tables:
- ANC_PER_ABS_ENTRIES
- ANC_ABSENCE_PLANS_F
👉 Integrated with OTBI for dashboards.
How Tables are Accessed in Oracle Fusion
Unlike on-premise systems, you don’t directly query tables.
Available Methods:
| Method | Usage |
|---|---|
| BI Publisher | SQL-based reporting |
| OTBI | Subject areas (logical model) |
| HCM Extracts | Data extraction |
| REST APIs | Integration |
| HDL | Data loading |
Step-by-Step: Using Tables in BI Publisher Report
Step 1 – Navigate to BI Publisher
Navigator → Tools → Reports and Analytics
Step 2 – Create Data Model
- Choose: SQL Query
- Example query:
p.person_number,
n.full_name,
a.assignment_number
FROM
per_all_people_f p,
per_person_names_f n,
per_all_assignments_m a
WHERE
p.person_id = n.person_id
AND p.person_id = a.person_id
AND SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
Step 3 – Apply Filters
- Always use:
- Effective date filters
- Business group filters
Step 4 – Create Report Layout
- Use RTF template or Excel template
Step 5 – Save and Run
- Validate output
Testing the Setup
Example Test Case
Scenario: Fetch employee details
Input:
- Employee Number: 1001
Expected Output:
- Name
- Assignment
- Department
Validation Checks
- Check date-effective records
- Validate joins
- Ensure no duplicate records
Common Implementation Challenges
1. Missing Data Due to Date Filters
👉 Always include:
2. Duplicate Records
Cause:
- Multiple assignments
Solution:
- Filter by:
- PRIMARY_FLAG = ‘Y’
3. Security Restrictions
- Fusion applies data security policies
- You may not see all data
4. Complex Joins
- Tables are highly normalized
- Requires strong understanding of relationships
Best Practices from Real Projects
1. Always Use Effective Date Filters
Without this:
- Reports will be incorrect
2. Use Aliases for Readability
Example:
3. Avoid Direct Table Dependency for Integrations
Instead use:
- REST APIs
- HCM Extracts
4. Use OTBI for Business Users
- Avoid exposing SQL-based reports to HR teams
5. Maintain a Table Mapping Document
In every project:
- Maintain documentation of:
- Tables used
- Joins
- Filters
Advanced Insight: Table Relationships
Example flow:
- PER_ALL_PEOPLE_F → Person
- PER_ALL_ASSIGNMENTS_M → Assignment
- HR_ORGANIZATION_UNITS_F → Department
👉 This forms the core of most HCM reports.
Summary
Understanding Oracle Fusion HCM Tables is a must-have skill for consultants working on reporting, integrations, and data validation.
Key takeaways:
- Tables are date-effective and secured
- Direct access is not allowed — use tools like BI Publisher
- Strong knowledge of joins and relationships is essential
- Real-world reporting depends heavily on these tables
For deeper reference, consult the official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html
FAQs
1. Can we directly access Oracle Fusion HCM tables?
No, direct database access is restricted. Tables are accessed via BI Publisher, OTBI, or APIs.
2. What is the most important table in HCM?
PER_ALL_PEOPLE_F is the most commonly used table for employee data.
3. Why do we use effective dates in queries?
Because Fusion stores historical data, and effective dates ensure you fetch the correct record.