Introduction
When working with Oracle Fusion HCM Tables and Views, consultants often need to access backend data for reporting, integrations, data migration, and troubleshooting. Although most business users interact with the application through the UI, every transaction in Oracle Fusion HCM is stored in a structured set of database tables and views.
Understanding Oracle Fusion HCM tables and views is extremely valuable for technical consultants, integration developers, and reporting specialists. Whether you are building a BI Publisher report, OTBI analysis, HCM Extract, or HDL validation process, knowledge of the underlying data model helps you identify where the data originates and how it is related.
In modern Oracle Cloud implementations (including Fusion Applications 26A), Oracle provides secure access to HCM data primarily through views rather than base tables. These views ensure security, enforce business rules, and support role-based access.
This article provides a detailed consultant-level understanding of Oracle Fusion HCM tables and views, including real-world examples, data model concepts, key objects, navigation, and best practices used in real implementations.
What are Oracle Fusion HCM Tables and Views?
In Oracle Fusion HCM, data is stored in database tables, while views provide controlled and secure access to that data.
Tables
Tables are the physical storage structures where application data resides.
Examples include:
| Table Name | Description |
|---|---|
| PER_ALL_PEOPLE_F | Stores person information |
| PER_ALL_ASSIGNMENTS_M | Stores worker assignment details |
| PER_PERSON_NAMES_F | Stores worker name details |
| PER_EMAIL_ADDRESSES | Stores email addresses |
| PER_PERIODS_OF_SERVICE | Stores employment periods |
These tables often contain date-effective data, meaning records include effective start date and end date columns.
Typical columns:
PERSON_ID
EFFECTIVE_START_DATE
EFFECTIVE_END_DATE
BUSINESS_GROUP_ID
LAST_UPDATE_DATE
Views
Views provide secure access layers built on top of tables.
Examples:
| View Name | Description |
|---|---|
| PER_PERSONS | Person data view |
| PER_ASSIGNMENTS_F | Assignment information |
| PER_ALL_PEOPLE_F_V | Secure people view |
| PER_PERSON_NAMES_F_V | Names data view |
Views ensure:
Data security enforcement
Role-based filtering
Consistent reporting access
Protection of base tables
Oracle strongly recommends using views instead of tables for reporting and integrations.
Key Components of the Oracle Fusion HCM Data Model
The Fusion HCM data model revolves around three primary entities.
1. Person
Represents an individual in the system.
Examples:
Employees
Contingent workers
Applicants
Contacts
Main tables:
PER_ALL_PEOPLE_F
PER_PERSON_NAMES_F
PER_EMAIL_ADDRESSES
2. Work Relationship
Represents the employment relationship between a worker and an enterprise.
Examples:
Employee relationship
Contingent worker relationship
Main table:
PER_PERIODS_OF_SERVICE
3. Assignment
Represents the job or position assigned to the worker.
Examples:
Job
Department
Location
Manager
Main tables:
PER_ALL_ASSIGNMENTS_M
PER_ASSIGNMENTS_F
Assignments are date-effective and frequently updated during employee lifecycle changes.
Important Oracle Fusion HCM Tables
Below are some of the most frequently used tables by Oracle consultants.
Core Person Tables
| Table | Description |
|---|---|
| PER_ALL_PEOPLE_F | Core person details |
| PER_PERSON_NAMES_F | Worker name information |
| PER_EMAIL_ADDRESSES | Email details |
| PER_PHONES | Phone numbers |
Example columns:
| Column | Description |
|---|---|
| PERSON_ID | Unique identifier |
| PERSON_NUMBER | Worker number |
| DATE_OF_BIRTH | Date of birth |
| SEX | Gender |
Assignment Tables
| Table | Description |
|---|---|
| PER_ALL_ASSIGNMENTS_M | Master assignment table |
| PER_ASSIGNMENTS_F | Assignment details |
| PER_ASSIGNMENT_SUPERVISORS_F | Manager relationship |
Example fields:
ASSIGNMENT_ID
JOB_ID
DEPARTMENT_ID
LOCATION_ID
Organization Tables
| Table | Description |
|---|---|
| HR_ALL_ORGANIZATION_UNITS_F | Organization units |
| HR_ORGANIZATION_INFORMATION_F | Organization attributes |
These tables are used when building organizational hierarchy reports.
Job and Position Tables
| Table | Description |
|---|---|
| PER_JOBS_F | Job definitions |
| HR_ALL_POSITIONS_F | Position data |
These tables are commonly used in workforce structure reports.
Payroll Tables (if Payroll enabled)
| Table | Description |
|---|---|
| PAY_PAYROLL_RELATIONSHIPS_F | Payroll relationship |
| PAY_ELEMENT_ENTRIES_F | Payroll elements |
| PAY_RUN_RESULTS | Payroll results |
Real-World Implementation Use Cases
Understanding Oracle Fusion HCM tables and views becomes critical in many real projects.
Use Case 1 – BI Publisher Employee Report
A customer needs a complete employee master report including:
Person name
Job
Department
Manager
Location
Required tables:
PER_ALL_PEOPLE_F
PER_PERSON_NAMES_F
PER_ALL_ASSIGNMENTS_M
HR_ALL_ORGANIZATION_UNITS_F
Join example:
ASSIGNMENT_ID
DEPARTMENT_ID
Use Case 2 – Integration with External Payroll System
In many projects, companies integrate Fusion HCM with third-party payroll systems using Oracle Integration Cloud (OIC Gen3).
Required data:
Employee details
Salary information
Bank accounts
Tables typically used:
PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_M
PAY_ELEMENT_ENTRIES_F
Integration flow:
Use Case 3 – Workforce Analytics Dashboard
Organizations build analytics dashboards using OTBI or external BI tools.
Data sources include:
Employee count
Attrition
Department distribution
Job hierarchy
Tables used:
PER_ALL_PEOPLE_F
PER_ALL_ASSIGNMENTS_M
PER_PERIODS_OF_SERVICE
Oracle Fusion HCM Data Security Through Views
Oracle Cloud enforces data security using secure views.
Reasons views are used:
Row-level security
Role-based access
Protection of sensitive HR data
For example:
If a manager runs a report, the view may only show:
Their direct reports
Their organizational hierarchy
This filtering happens automatically through:
Data roles
security profiles
database views
Prerequisites for Accessing Tables and Views
Before accessing HCM data objects, the following access is typically required.
Required Roles
Examples:
HCM Application Administrator
BI Administrator
HCM Integration Specialist
Access Tools
Consultants usually access data through:
| Tool | Purpose |
|---|---|
| BI Publisher | Data model queries |
| OTBI | Subject area reporting |
| HCM Extract | Data extraction |
| OIC integrations | APIs and services |
Direct SQL access is usually performed through BI Publisher Data Model queries.
Step-by-Step Example – Querying Employee Data
Below is a practical example used in many projects.
Step 1 – Navigate to BI Publisher
Navigator → Tools → Reports and Analytics
Step 2 – Create Data Model
Select:
Choose:
Step 3 – Write SQL Query
Example:
PAPF.PERSON_NUMBER,
PPNF.FIRST_NAME,
PPNF.LAST_NAME,
PAAM.ASSIGNMENT_NUMBER,
PAAM.JOB_ID,
PAAM.DEPARTMENT_ID
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
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
This query retrieves:
Employee number
Name
Assignment details
Step 4 – Save the Data Model
Save the data model.
Next:
Create a report layout.
Step 5 – Run the Report
Execute the report and validate:
Employee list
Assignment data
Department information
Testing the Data Extraction
Testing is an important step before deploying reports or integrations.
Example validation:
| Test Scenario | Expected Result |
|---|---|
| New employee created | Appears in report |
| Assignment change | New assignment visible |
| Terminated employee | Status updated |
Always test with:
Multiple employees
Multiple assignments
Historical records
Common Implementation Challenges
1. Date Effective Data Confusion
Many tables include:
EFFECTIVE_END_DATE
If filters are incorrect, reports may return:
Duplicate rows
Historical records
Solution:
Use proper date filters.
2. Multiple Assignments
Employees may have:
Primary assignment
Secondary assignment
Always filter using:
3. Security Restrictions
Some users cannot access data due to:
Security profiles
Data roles
Always validate:
Role assignments
Security profiles
4. Incorrect Joins
Many tables use different keys:
| Table | Key |
|---|---|
| People | PERSON_ID |
| Assignment | ASSIGNMENT_ID |
| Organization | ORGANIZATION_ID |
Incorrect joins produce inconsistent data.
Best Practices for Working with Oracle Fusion HCM Tables
Experienced consultants typically follow these practices.
Always Use Secure Views
Instead of base tables, prefer:
PER_ASSIGNMENTS_F
This ensures compliance with Oracle security model.
Use Date Filters
Example:
This ensures current records only.
Avoid Hard Coding Values
Use parameters instead of:
Document Data Models
Always document:
Table relationships
Join keys
Business logic
This simplifies future maintenance.
Use Oracle Documentation
The official documentation provides detailed information on data objects and reporting structures.
Refer to the Oracle documentation index:
https://docs.oracle.com/en/cloud/saas/index.html
Consultants working with workforce data should also review the Time and Labor documentation available within the same documentation library for deeper insights into workforce data structures and reporting capabilities.
Frequently Asked Questions (FAQ)
1. What is the difference between tables and views in Oracle Fusion HCM?
Tables store the actual application data, while views provide secure access to that data with built-in security and filtering.
2. Which table stores employee assignment information?
The main table storing assignment information is:
It contains job, department, and assignment details.
3. Why does Oracle recommend using views instead of tables?
Views enforce:
Security policies
Role-based filtering
Data access controls
This protects sensitive HR information.
Summary
Understanding Oracle Fusion HCM tables and views is essential for consultants working with reporting, integrations, and analytics.
Key takeaways:
Tables store core application data.
Views provide secure and filtered access.
Important tables include PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, and PER_PERSON_NAMES_F.
Most real-world use cases involve BI Publisher reports, integrations, and analytics dashboards.
Proper handling of date-effective records and joins is critical for accurate reporting.
Consultants who master the Fusion HCM data model can build more reliable reports, integrations, and workforce analytics solutions in real implementations.