Oracle HCM Tables and Views Guide

Share

Tables and Views in Oracle Fusion HCM – Complete Practical Guide

When working with Tables and Views in Oracle Fusion HCM, every consultant eventually realizes that understanding the data model is the real backbone of reporting, integrations, and troubleshooting. Whether you are building OTBI reports, BI Publisher reports, HDL loads, or OIC integrations, your success depends on how well you understand the underlying tables and views.

In this guide, we will break down the Oracle Fusion HCM data model from a real implementation perspective, not just theory, so you can confidently navigate and use it in projects.

Understanding Tables and Views in Oracle Fusion HCM

Oracle Fusion HCM is built on a relational database model, where:

  • Tables store actual data (physical storage)
  • Views provide logical access to data (secured, user-friendly layer)

Unlike legacy systems, direct table access is restricted in Fusion Cloud. Instead, consultants mostly interact with:

  • OTBI Subject Areas
  • BI Publisher (BIP) Data Models
  • HCM Extracts
  • HDL / REST APIs

However, knowing the backend tables and views gives you a huge advantage.

What Are Tables and Views in Oracle Fusion HCM?

Tables

Tables store raw transactional and master data.

Example:

  • PER_ALL_PEOPLE_F → stores employee personal details
  • PER_ALL_ASSIGNMENTS_M → stores assignment data

Views

Views are built on top of tables and provide:

  • Security filtering
  • Simplified joins
  • User-friendly column naming

Example:

  • PER_PERSONS_V → user-friendly version of person data

Key Features of Oracle Fusion HCM Data Model

1. Date Effective Design

Almost all HCM tables are date-effective:

  • EFFECTIVE_START_DATE
  • EFFECTIVE_END_DATE

This allows tracking historical changes.

2. Multi-Language Support

Tables often have _TL suffix:

  • PER_JOBS_TL
  • HR_ORGANIZATION_UNITS_TL

These store translated values.

3. Secured Views Layer

Instead of direct tables, Oracle provides:

  • _V views
  • _VL (View + Language)

4. Audit and Tracking Columns

Common columns:

  • CREATED_BY
  • CREATION_DATE
  • LAST_UPDATED_BY

Core Tables in Oracle Fusion HCM

1. Person and Worker Tables

Table NameDescription
PER_ALL_PEOPLE_FCore person data
PER_PERSON_NAMES_FPerson names
PER_EMAIL_ADDRESSESEmail details
PER_PHONESPhone numbers

2. Assignment Tables

Table NameDescription
PER_ALL_ASSIGNMENTS_MAssignment data
PER_ASSIGNMENT_SUPERVISORS_FManager relationships

3. Organization Tables

Table NameDescription
HR_ORGANIZATION_UNITS_FOrganization data
HR_ORG_UNIT_CLASSIFICATIONS_FOrg classifications

4. Job and Position Tables

Table NameDescription
PER_JOBS_FJob definitions
HR_ALL_POSITIONS_FPosition data

5. Payroll and Compensation Tables

Table NameDescription
PAY_ALL_PAYROLLS_FPayroll definitions
PAY_ELEMENT_ENTRIES_FElement entries

Commonly Used Views in HCM

View NameDescription
PER_PERSONS_VPerson details
PER_ASSIGNMENTS_VAssignment details
PER_JOBS_VLJobs with language
HR_ORGANIZATION_UNITS_VLOrganization details

Real-World Business Use Cases

Use Case 1 – Employee Master Report

A client wants a report showing:

  • Employee Name
  • Department
  • Job
  • Manager

Tables Used:

  • PER_ALL_PEOPLE_F
  • PER_ALL_ASSIGNMENTS_M
  • HR_ORGANIZATION_UNITS_F

Use Case 2 – Integration with Third-Party Payroll

Using OIC Gen 3:

  • Extract employee data from:
    • PER tables
  • Transform data
  • Send to external system

Use Case 3 – Audit Employee Changes

Track:

  • Salary changes
  • Job changes
  • Department transfers

Using date-effective tables.

Architecture / Technical Flow

 
Tables → Views → OTBI / BIP / APIs → End Users
 
  • Tables store raw data
  • Views secure and simplify data
  • Tools consume views

Prerequisites

Before working with HCM tables:

  • Basic SQL knowledge
  • Understanding of HCM modules
  • Access to:
    • BI Publisher
    • OTBI
    • SQL Developer (optional)

Step-by-Step: Accessing Data Using BI Publisher

Step 1 – Navigate to BI Publisher

Navigator → Tools → Reports and Analytics

Step 2 – Create Data Model

  • Click “Create” → Data Model
  • Select “SQL Query”

Step 3 – Write Query

Example:

 
SELECT papf.person_number,
ppf.full_name,
paam.assignment_number
FROM per_all_people_f papf,
per_person_names_f ppf,
per_all_assignments_m paam
WHERE papf.person_id = ppf.person_id
AND papf.person_id = paam.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date;
 

Step 4 – Save and Validate

  • Click “Save”
  • Validate query

Step 5 – Create Report

  • Use data model
  • Design layout

Testing the Setup

Example Test Scenario

Run report for:

  • Employee Number: 1001

Expected Output

  • Correct name
  • Active assignment
  • Current department

Validation Checks

  • No duplicate records
  • Correct date-effective data
  • Manager relationship correct

Common Implementation Challenges

1. Duplicate Records

Due to multiple date-effective rows.

Solution:
Use proper date filters.

2. Missing Data

Because of wrong joins.

Solution:
Always join on:

  • PERSON_ID
  • ASSIGNMENT_ID

3. Performance Issues

Large queries on:

  • PER_ALL_ASSIGNMENTS_M

Solution:

  • Use indexed columns
  • Avoid unnecessary joins

4. Security Restrictions

Direct table access not allowed.

Solution:
Use:

  • Views
  • OTBI
  • BIP

Best Practices from Real Projects

1. Always Use Date Filters

 
SYSDATE BETWEEN effective_start_date AND effective_end_date
 

2. Prefer Views Over Tables

  • Better security
  • Easier joins

3. Use Aliases Clearly

Avoid confusion in complex queries.

4. Validate Data with UI

Always cross-check:

  • Report output
  • Fusion UI

5. Understand Relationships

Key joins:

  • PERSON_ID
  • ASSIGNMENT_ID
  • ORGANIZATION_ID

Frequently Asked Interview Questions

1. What is the difference between tables and views?

Tables store data, views provide logical access.

2. What is a date-effective table?

A table that tracks historical data using start and end dates.

3. What is PER_ALL_PEOPLE_F?

Core table storing employee information.

4. Why do we use views in Fusion?

For security and simplified access.

5. What is _TL table?

Translation table for multi-language support.

6. What is _VL view?

View combining base + translation data.

7. How do you avoid duplicate records?

Use date filters and proper joins.

8. What is the key table for assignments?

PER_ALL_ASSIGNMENTS_M.

9. What is PERSON_ID?

Unique identifier for a person.

10. What tools are used to access HCM data?

OTBI, BI Publisher, APIs.

11. Can we directly query database tables?

No, access is restricted.

12. What is effective dating used for?

Tracking history of changes.

Real Implementation Scenario

In a recent project:

A client needed a global employee extract.

Challenges:

  • Multiple assignments
  • Global business units
  • Language translations

Solution:

  • Used:
    • PER_ALL_PEOPLE_F
    • PER_ALL_ASSIGNMENTS_M
    • HR_ORGANIZATION_UNITS_VL
  • Applied date filters
  • Delivered clean report

Expert Tips

  • Learn top 20 tables deeply, not all
  • Practice SQL daily
  • Use BIP Data Model testing
  • Keep a personal cheat sheet

FAQs

1. Which is the most important table in HCM?

PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_M.

2. Why am I getting multiple records for one employee?

Because of date-effective rows.

3. Should I use tables or views?

Always prefer views unless required otherwise.

Summary

Understanding Tables and Views in Oracle Fusion HCM is a must-have skill for any consultant. It directly impacts:

  • Reporting
  • Integrations
  • Troubleshooting

If you master:

  • Key tables
  • Relationships
  • Date-effective logic

You can solve most real-world HCM challenges confidently.

For deeper technical reference, explore Oracle official documentation:
https://docs.oracle.com/en/cloud/saas/index.html


Share

Leave a Reply

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