Introduction
When working with Oracle Fusion HCM Table Names, one of the first challenges consultants face is understanding where data is actually stored in the cloud environment. Unlike legacy systems where direct database access was common, Oracle Fusion Cloud restricts backend access, making it essential to understand table structures for reporting, integrations, HDL loads, and troubleshooting.
From my real project experience, whether you are building OTBI reports, BI Publisher reports, or integrations using OIC Gen 3, knowing the correct HCM tables can save hours of debugging and data validation.
In this blog, we will break down Oracle Fusion HCM table names in a practical, implementation-focused way, so you can confidently work on real-time projects.
What are Oracle Fusion HCM Table Names?
Oracle Fusion HCM table names represent the underlying database structures where employee, organization, payroll, and transactional data is stored.
Even though direct database access is restricted in Fusion, these tables are:
- Used in BI Publisher (BIP) reports
- Referenced in HDL (HCM Data Loader)
- Mapped in OTBI subject areas
- Used in OIC integrations via REST/SOAP payloads
Important Understanding
Fusion uses a secure data layer, but internally:
- Tables exist in schemas like:
- PER (Person)
- PAY (Payroll)
- HRC (Core HR)
- BEN (Benefits)
Key Oracle Fusion HCM Table Names
Below are the most commonly used and must-know tables for any consultant.
Core HR Tables
| Table Name | Description |
|---|---|
| PER_ALL_PEOPLE_F | Stores basic person details |
| PER_PERSON_NAMES_F | Stores employee names |
| PER_ALL_ASSIGNMENTS_M | Stores assignment data |
| PER_PERSON_TYPES_TL | Person types (Employee, Contractor) |
| PER_EMAIL_ADDRESSES | Email details |
Assignment & Employment Tables
| Table Name | Description |
|---|---|
| PER_ALL_ASSIGNMENTS_M | Assignment details |
| PER_PERIODS_OF_SERVICE | Employment duration |
| PER_ASSIGNMENT_STATUS_TYPES | Assignment statuses |
Organization & Department Tables
| Table Name | Description |
|---|---|
| HR_ORGANIZATION_UNITS_F | Organization details |
| HR_ORG_UNIT_CLASSIFICATIONS | Organization classification |
| PER_DEPARTMENTS | Department details |
Payroll Tables
| Table Name | Description |
|---|---|
| PAY_ALL_PAYROLLS_F | Payroll definitions |
| PAY_RUN_RESULTS | Payroll calculation results |
| PAY_ELEMENT_ENTRIES_F | Element entries |
| PAY_INPUT_VALUES_F | Input values for elements |
Benefits Tables
| Table Name | Description |
|---|---|
| BEN_PLANS_F | Benefit plans |
| BEN_ENROLLMENTS | Employee enrollments |
Time & Labor Tables
| Table Name | Description |
|---|---|
| HWM_TM_REC | Time records |
| HWM_TM_REC_GRP | Time record groups |
Real-World Integration Use Cases
1. Employee Data Extraction for Data Warehouse
In one implementation, a client needed to build a data warehouse for HR analytics.
We used:
- PER_ALL_PEOPLE_F → Employee master data
- PER_ALL_ASSIGNMENTS_M → Job and department
- HR_ORGANIZATION_UNITS_F → Organization hierarchy
👉 Combined using BIP and scheduled extract.
2. Payroll Integration with Third-Party Vendor
A client integrated Fusion Payroll with an external payroll processor.
Key tables used:
- PAY_RUN_RESULTS
- PAY_ELEMENT_ENTRIES_F
- PAY_INPUT_VALUES_F
👉 Data was extracted via BI Publisher and pushed using OIC Gen 3 REST integration
3. Employee Directory Application
For a mobile app:
- PER_PERSON_NAMES_F → Names
- PER_EMAIL_ADDRESSES → Emails
- PER_ALL_ASSIGNMENTS_M → Department
👉 Exposed via REST APIs and consumed externally.
Architecture / Technical Flow
Understanding how table names fit into Fusion architecture is critical.
Flow Overview
- Data stored in backend tables (PER, PAY, BEN schemas)
- Exposed via:
- OTBI subject areas
- BI Publisher data models
- REST/SOAP services
- Consumed by:
- Reports
- Integrations (OIC Gen 3)
- External systems
Key Insight
You never directly query tables in production—but you use them in:
- BI Publisher SQL queries
- HDL file structures
- Data extraction logic
Prerequisites
Before working with HCM tables, ensure:
- Access to BI Publisher (BIP)
- Knowledge of SQL joins
- Understanding of effective dating (very important in Fusion)
- Awareness of secured views vs base tables
Step-by-Step: Using Table Names in BI Publisher
Step 1 – Navigate to BI Publisher
Navigator → Tools → Reports and Analytics → Browse Catalog
Step 2 – Create Data Model
- Click New → Data Model
- Select SQL Query
Step 3 – Write Query Using Table Names
Example:
papf.person_number,
ppnf.full_name,
paam.assignment_number,
hou.name department_name
FROM
per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
hr_organization_units_f hou
WHERE
papf.person_id = ppnf.person_id
AND papf.person_id = paam.person_id
AND paam.organization_id = hou.organization_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date;
Step 4 – Save Data Model
- Validate SQL
- Save and create report
Testing the Setup
Example Test Case
Scenario: Fetch active employee list
Steps:
- Run report
- Validate:
- Employee number
- Name
- Department
- Assignment status
Expected Output
- Only active employees
- Correct department mapping
- No duplicate records
Common Errors and Troubleshooting
1. Missing Effective Date Filter
❌ Issue: Duplicate or incorrect records
✅ Solution:
2. Incorrect Joins
❌ Issue: Missing data
✅ Solution:
- Always join using:
- PERSON_ID
- ASSIGNMENT_ID
3. Using Base Tables Instead of Secured Views
❌ Issue: Data security problems
✅ Solution:
- Use secured views where applicable
4. Performance Issues
❌ Issue: Slow reports
✅ Solution:
- Use filters
- Avoid unnecessary joins
- Use indexed columns
Best Practices
1. Always Handle Effective Dating
Almost every HCM table is date-effective.
2. Use Aliases Properly
Improves readability:
paam → assignment
3. Avoid Hardcoding Values
Use parameters in reports.
4. Validate Data with UI
Always cross-check with Fusion UI screens.
5. Use Latest Fusion Model (26A)
- Updated structures
- Enhanced security
- Improved reporting capabilities
Real Consultant Tips
From real implementations:
- Always start with PER_ALL_PEOPLE_F + PER_ALL_ASSIGNMENTS_M
- 80% of reporting issues come from wrong joins
- Learn person_id vs assignment_id difference
- Use OTBI first, then move to BIP if needed
Frequently Asked Questions (FAQ)
1. Can we directly access Oracle Fusion HCM tables?
No. Direct DB access is restricted. You use tables via:
- BI Publisher
- OTBI
- APIs
2. What is the most important table in HCM?
PER_ALL_PEOPLE_F is the core table for employee data.
3. Why do we use _F tables?
_F indicates date-effective tables, storing historical data.
Summary
Understanding Oracle Fusion HCM Table Names is a critical skill for any consultant working on reporting, integrations, or data migration.
Key takeaways:
- Tables are organized by modules (PER, PAY, BEN)
- Effective dating is crucial
- Used mainly in BI Publisher and integrations
- Correct joins and filters are essential for accurate data
If you master these tables, you significantly improve your ability to:
- Build reports
- Debug issues
- Design integrations
- Work efficiently in real-time projects
For deeper reference, always refer to Oracle official documentation:
https://docs.oracle.com/en/cloud/saas/index.html