Introduction
Oracle Fusion HCM SQL Queries are one of the most critical skills for any technical consultant working on reporting, integrations, or data extraction in Fusion Cloud. Even though Oracle Fusion is a SaaS application with controlled database access, SQL still plays a major role behind the scenes—especially in BI Publisher (BIP), OTBI extensions, and data validation scenarios.
In real-world projects, I’ve seen teams struggle not because they don’t know SQL—but because they don’t understand Fusion HCM data model complexities, secured views, and effective-dated tables. This is where mastering Oracle Fusion HCM SQL queries becomes a game changer.
This blog explains everything from fundamentals to advanced query patterns, with real project examples and consultant-level tips aligned with Fusion Cloud 26A standards.
What are Oracle Fusion HCM SQL Queries?
Oracle Fusion HCM SQL queries are used to extract, validate, and transform HR data from the Fusion database using secured views (not base tables).
Unlike on-premise systems:
You cannot directly query base tables
You must use secured views like PER_PERSON_NAMES_F_V, PER_ALL_PEOPLE_F, etc.
Data is date-effective (start/end dates matter)
Security is applied through data roles and session context
Key Point from Implementation
In one of my projects, a simple “Employee List” report failed because the developer queried without considering effective dates, resulting in duplicate records. Fixing the query using TRUNC(SYSDATE) with date filters resolved it.
Real-World Integration Use Cases
Use Case 1 – Employee Master Data Extraction
Used in integrations where employee data is sent to external payroll or identity systems.
Extract from:
PER_ALL_PEOPLE_F,PER_PERSON_NAMES_FFilter: Active employees only
Output: XML/CSV via BI Publisher
Use Case 2 – Manager Hierarchy Reporting
Used for approvals, dashboards, or audit reports.
Tables involved:
PER_ASSIGNMENT_SUPERVISORS_FPER_ALL_ASSIGNMENTS_M
Logic:
Fetch direct manager
Build hierarchy
Use Case 3 – Compensation Analytics
Used for salary audits or HR dashboards.
Tables:
CMP_SALARYPER_ALL_ASSIGNMENTS_M
Join conditions:
Assignment ID
Effective dates
Architecture / Technical Flow
Understanding how SQL fits into Fusion HCM architecture is critical.
Data Flow:
Fusion Application UI
Application Layer
Secured Views (accessible)
BI Publisher / OTBI
Output (Report / Integration)
Important Note
Always use _F (date-effective) and _V (secured views).
Avoid using base tables like
PER_ALL_PEOPLE_Fdirectly unless exposed via views.
Prerequisites
Before writing SQL queries in Oracle Fusion HCM:
Access Requirements
BI Publisher Data Model access
Required roles:
BI Administrator
HCM Data Role
Knowledge Requirements
Fusion HCM data model
Effective dating logic
SQL joins and filters
Tools
BI Publisher (BIP)
OTBI (for validation)
SQL Developer (for offline understanding)
Step-by-Step Build Process (BI Publisher SQL Query)
Let’s walk through creating a SQL-based report.
Step 1 – Navigate to BI Publisher
Navigation:
Navigator → Tools → Reports and Analytics → Browse Catalog
Step 2 – Create Data Model
Click: New → Data Model
Select: SQL Query
Step 3 – Write SQL Query
Example: Employee Basic Details
papf.person_number,
pnf.full_name,
paam.assignment_number,
paam.assignment_status_type
FROM
per_all_people_f papf,
per_person_names_f pnf,
per_all_assignments_m paam
WHERE
papf.person_id = pnf.person_id
AND papf.person_id = paam.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pnf.effective_start_date AND pnf.effective_end_date
AND paam.primary_flag = ‘Y’;
Step 4 – Configure Parameters (Optional)
Example:
Business Unit
Department
Employee Number
Step 5 – Save Data Model
Save in shared folder
Assign appropriate roles
Step 6 – Create Report
Use Data Model
Choose layout (RTF/Excel)
Key Fusion HCM Tables and Views
| Object Name | Description |
|---|---|
| PER_ALL_PEOPLE_F | Person core details |
| PER_PERSON_NAMES_F | Employee names |
| PER_ALL_ASSIGNMENTS_M | Assignment data |
| PER_ASSIGNMENT_SUPERVISORS_F | Manager details |
| PER_EMAIL_ADDRESSES | Email information |
| PER_JOBS | Job definitions |
Important SQL Concepts in Fusion HCM
1. Effective Dating
Almost every HCM table is date-effective.
Real Scenario
If you don’t apply this filter:
You may get multiple historical rows
Reports become inaccurate
2. Primary Assignment Filter
Used to ensure:
Only main assignment is fetched
Avoid duplicates
3. Language Filtering
Ensures:
Correct name format
Avoid multiple translations
4. Joins in Fusion
Always use proper joins:
Avoid Cartesian joins—common mistake in beginners.
Testing the SQL Query
Test Scenario
Run report for:
Employee Number: 1001
Expected Output
Correct name
Active assignment
No duplicate rows
Validation Checks
Compare with UI:
Navigator → My Client Groups → Person Management
Validate:
Name
Assignment status
Common Errors and Troubleshooting
1. Duplicate Records
Cause:
Missing effective date filter
Solution:
Add
TRUNC(SYSDATE)condition
2. Missing Data
Cause:
Security restrictions
Solution:
Check data roles
Validate access
3. Slow Performance
Cause:
Unoptimized joins
Large dataset
Solution:
Use filters
Avoid unnecessary tables
4. Invalid Objects
Cause:
Using base tables instead of views
Solution:
Use secured views (_V)
Best Practices from Real Projects
1. Always Use Secured Views
Avoid direct base table queries.
2. Apply Effective Date Logic Everywhere
Even if you think it’s not needed—it is.
3. Limit Data Volume
Use filters like:
Business Unit
Department
Date range
4. Use Aliases Properly
Improves readability:
5. Validate with OTBI First
Before writing SQL:
Build report in OTBI
Understand joins
Then replicate in SQL
6. Document Your Query Logic
In real implementations:
Always explain joins
Helps future maintenance
Real Implementation Scenario (End-to-End)
Scenario: Employee Integration to External Payroll
Requirement
Extract:
Employee ID
Name
Salary
Manager
Approach
Use BI Publisher SQL query
Join:
People
Assignment
Salary
Supervisor
Apply filters:
Active employees
Output:
XML file
Result
Scheduled report runs daily
File sent to external system
FAQs
1. Can we directly query Oracle Fusion database?
No. Direct database access is restricted. You must use:
BI Publisher
OTBI
Secured views
2. Why do we get duplicate records in HCM SQL queries?
Because Fusion uses effective-dated tables. Without date filters, multiple records appear.
3. What is the difference between _F and _M tables?
_F → Date-effective tables
_M → Multiple rows for assignments (non-date-effective in same way)
Summary
Oracle Fusion HCM SQL Queries are not just about writing SELECT statements—they require a deep understanding of:
Fusion data model
Effective dating
Security layers
Business logic
In real implementations, the difference between an average and expert consultant is how well they:
Handle joins
Filter data correctly
Optimize queries for performance
If you master these concepts, you can confidently work on:
Reports
Integrations
Data validations
Analytics solutions
For deeper understanding, refer to official Oracle documentation:
https://docs.oracle.com/en/cloud/saas/index.html