Oracle Fusion HCM Tables Guide

Share

 

Important Tables in Oracle Fusion HCM

Understanding Important Tables in Oracle Fusion HCM is one of the most critical skills for any consultant working on reporting, integrations, or data migration. Even though Oracle Fusion is primarily a cloud-based application with abstracted data access through APIs and OTBI, real-world implementations still demand a strong understanding of backend tables—especially when working with BI Publisher (BIP), HDL validations, or troubleshooting data issues.

In this blog, we will take a practical consultant-level deep dive into the most important tables in Oracle Fusion HCM, where they are used, and how they play a role in real implementations.

What are Important Tables in Oracle Fusion HCM?

In Oracle Fusion HCM, data is stored in a relational structure within the Oracle database. These tables store employee, assignment, payroll, benefits, and organizational data.

Unlike legacy systems like EBS:

  • Direct DB access is restricted
  • Tables are primarily accessed via:
    • BI Publisher (BIP)
    • OTBI (Oracle Transactional Business Intelligence)
    • HCM Extracts
    • REST/SOAP APIs

However, knowing tables helps in:

  • Writing advanced BIP queries
  • Troubleshooting data inconsistencies
  • Understanding HDL data loading
  • Supporting integrations via OIC Gen 3

Why Important Tables Matter in Real Projects

From a consultant’s perspective, you will frequently encounter scenarios like:

  • “Employee not visible in report”
  • “Assignment status mismatch”
  • “Payroll data not syncing with external system”
  • “HDL load failing due to data validation”

In all these cases, understanding backend tables becomes essential.

Key Categories of HCM Tables

Oracle Fusion HCM tables can be grouped into the following major categories:

CategoryDescription
Person TablesStore employee personal information
Assignment TablesJob and employment details
Organization TablesDepartments and business units
Payroll TablesSalary and payroll data
Benefits TablesBenefits enrollment
Security TablesUser roles and access

Core Person Tables in Oracle Fusion HCM

1. PER_ALL_PEOPLE_F

This is the most important table in HCM.

Purpose: Stores core person information.

Key Columns:

  • PERSON_ID
  • PERSON_NUMBER
  • DATE_OF_BIRTH
  • EFFECTIVE_START_DATE / END_DATE

Real Example:

When building a BIP report for employee details, this table is always the starting point.

2. PER_PERSON_NAMES_F

Purpose: Stores employee names.

Key Columns:

  • PERSON_ID
  • FIRST_NAME
  • LAST_NAME
  • FULL_NAME

Consultant Tip:

Always join with PER_ALL_PEOPLE_F using PERSON_ID.

3. PER_EMAIL_ADDRESSES

Purpose: Stores employee email information.

Common Use Case:

Used in integrations where employee communication details are required.

Assignment Tables (Most Critical for HR Processes)

4. PER_ALL_ASSIGNMENTS_M

Purpose: Stores assignment details like job, department, and status.

Key Columns:

  • ASSIGNMENT_ID
  • PERSON_ID
  • JOB_ID
  • DEPARTMENT_ID
  • ASSIGNMENT_STATUS_TYPE

Real Implementation Scenario:

In a client project, payroll was failing because the assignment status was not “Active Payroll Eligible.” This table helped identify incorrect status.

5. PER_ASSIGNMENT_STATUS_TYPES

Purpose: Defines assignment statuses.

Example Values:

  • Active
  • Suspended
  • Terminated

Organization Tables

6. HR_ORGANIZATION_UNITS_F

Purpose: Stores departments and organizations.

Key Columns:

  • ORGANIZATION_ID
  • NAME
  • TYPE

7. HR_ALL_ORGANIZATION_UNITS_F_TL

Purpose: Stores translated organization names.

Use Case:

Needed when working in multi-language environments.

Job and Position Tables

8. PER_JOBS_F

Purpose: Stores job definitions.

Key Columns:

  • JOB_ID
  • JOB_NAME
  • JOB_CODE

9. PER_POSITIONS

Purpose: Stores position details.

Use Case:

Used in position-based organizations.

Payroll Tables

10. PAY_ALL_PAYROLLS_F

Purpose: Payroll definitions.

11. PAY_ELEMENT_ENTRIES_F

Purpose: Stores employee earnings and deductions.

Example:

  • Basic Salary
  • Bonus
  • Allowances

12. PAY_RUN_RESULTS

Purpose: Stores payroll calculation results.

Real Scenario:

Used in reporting salary slips via BIP.

Benefits Tables

13. BEN_PRTT_ENRT_RSLT

Purpose: Stores benefit enrollment results.

User and Security Tables

14. PER_USERS

Purpose: Stores application user details.

15. PER_USER_ROLES

Purpose: Maps users to roles.

Real-World Integration Use Cases

Use Case 1: Employee Data Integration with External System

  • Source Tables:
    • PER_ALL_PEOPLE_F
    • PER_ALL_ASSIGNMENTS_M
    • PER_EMAIL_ADDRESSES
  • Integration Tool:
    • OIC Gen 3

Use Case 2: Payroll Report Generation

  • Tables Used:
    • PAY_RUN_RESULTS
    • PAY_ELEMENT_ENTRIES_F
  • Output:
    • Payslip via BI Publisher

Use Case 3: Organizational Hierarchy Reporting

  • Tables Used:
    • HR_ORGANIZATION_UNITS_F
    • PER_ALL_ASSIGNMENTS_M

Table Relationships (Important for Reporting)

Typical join structure:

  • PER_ALL_PEOPLE_F → PER_PERSON_NAMES_F
  • PER_ALL_PEOPLE_F → PER_ALL_ASSIGNMENTS_M
  • PER_ALL_ASSIGNMENTS_M → HR_ORGANIZATION_UNITS_F

Consultant Insight:

Always use PERSON_ID and ASSIGNMENT_ID as primary join keys.

Prerequisites for Working with Tables

Before accessing these tables:

  • BI Publisher access
  • Data model creation privileges
  • SQL knowledge
  • Understanding of effective dating

Step-by-Step: Creating a BIP Report Using Tables

Step 1 – Navigate

Navigator → Tools → Reports and Analytics

Step 2 – Create Data Model

  • Choose SQL Query
  • Enter query:
 
SELECT papf.person_number,
ppnf.full_name,
paam.assignment_status_type
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam
WHERE papf.person_id = ppnf.person_id
AND papf.person_id = paam.person_id;
 

Step 3 – Define Parameters

  • Person Number (Optional)

Step 4 – Create Report Layout

  • Use RTF Template

Step 5 – Save and Run

Testing the Setup

Test Scenario

  • Input: Employee Number
  • Expected Output:
    • Name
    • Assignment Status

Validation Checks

  • Check active records (effective dates)
  • Validate assignment status

Common Implementation Challenges

1. Effective Dating Confusion

Most tables are date-effective.

Solution: Always filter using:

 
SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
 

2. Duplicate Records

Due to multiple assignments.

3. Missing Data in Reports

Cause:

  • Wrong join condition
  • Incorrect table selection

Best Practices from Real Projects

1. Always Use Views When Available

Prefer secured views like:

  • PER_PERSON_SECURED_LIST_V

2. Avoid Hardcoding IDs

Use joins instead of static values.

3. Use Effective Date Filters

Critical for accurate reporting.

4. Validate with UI

Always cross-check report data with Fusion UI.

5. Use Aliases for Readability

Improves maintainability of SQL queries.

Frequently Asked Questions (FAQ)

1. Can we directly access Oracle Fusion tables?

No. Access is restricted. You can use BIP, OTBI, or APIs.

2. Which table is most important in HCM?

PER_ALL_PEOPLE_F is the core table for employee data.

3. How do we handle effective-dated tables?

Always use date filters to fetch current records.

Summary

Understanding Important Tables in Oracle Fusion HCM is essential for any consultant working on reporting, integrations, or troubleshooting.

In real implementations, tables like:

  • PER_ALL_PEOPLE_F
  • PER_ALL_ASSIGNMENTS_M
  • PAY_RUN_RESULTS

are used almost daily.

A strong grasp of these tables allows you to:

  • Build accurate reports
  • Debug production issues
  • Design efficient integrations

For deeper reference, always consult Oracle’s official documentation:
https://docs.oracle.com/en/cloud/saas/human-resources/index.html

Also refer to the uploaded reference for structured guidance


Share

Leave a Reply

Your email address will not be published. Required fields are marked *