Introduction
Oracle Fusion HCM Queries are a core part of any real-time implementation, especially when consultants need to extract, validate, or troubleshoot employee and HR data across modules. In Oracle Fusion Cloud (Release 26A), querying capabilities are not limited to just reporting—they extend into OTBI, BI Publisher (BIP), HCM Extracts, and even REST APIs.
From a practical consulting standpoint, queries are used daily—for validating HDL loads, reconciling payroll data, auditing security roles, or supporting integrations via OIC Gen 3. If you don’t understand how queries work in Fusion HCM, you will struggle in real projects.
This article walks you through everything—from concepts to real-world usage, configuration approaches, and troubleshooting tips.
What are Oracle Fusion HCM Queries?
Oracle Fusion HCM Queries refer to methods used to retrieve data from the Fusion HCM database layer using different tools provided by Oracle Cloud.
Unlike traditional Oracle EBS where direct SQL access was common, Fusion Cloud restricts direct DB access. Instead, Oracle provides controlled query tools, such as:
OTBI (Oracle Transactional Business Intelligence)
BI Publisher (BIP)
HCM Extracts
Fast Formulas (limited querying)
REST APIs (for integration-based querying)
Each tool serves a different purpose, and as a consultant, choosing the right one is critical.
Key Query Tools in Oracle Fusion HCM
1. OTBI (Oracle Transactional BI)
Real-time reporting
Subject-area-based querying
No SQL required
2. BI Publisher (BIP)
SQL-based reporting
Used for complex joins and large datasets
Supports bursting and scheduling
3. HCM Extracts
Used for outbound integrations
Structured data extraction for payroll/vendors
4. REST APIs
Used in integrations (especially with OIC Gen 3)
Supports GET operations for querying data
5. Fast Formula (Indirect Querying)
Used in payroll, benefits
Can fetch data using database items
Real-World Integration Use Cases
Use Case 1: Employee Data Validation After HDL Load
After loading employee data via HDL:
Consultants use OTBI to verify employee creation
Use BIP queries to validate missing attributes
Example:
Query PER_ALL_PEOPLE_F equivalent via secured views
Use Case 2: Payroll Reconciliation
Payroll teams need:
Salary, deductions, tax details
Solution:
Use BI Publisher with SQL joins
Combine payroll tables like:
PAY_PAYROLL_ACTIONS
PAY_RUN_RESULTS
Use Case 3: Integration with Third-Party System
A company integrates HCM with a vendor system.
Approach:
Use REST API queries via OIC Gen 3
Fetch worker details using:
/hcmRestApi/resources/latest/workers
Architecture / Technical Flow
In Fusion HCM, queries are layered and secured.
Data Flow:
Fusion Database (Oracle Managed)
Secured Views (No direct DB access)
Data Access Layer:
OTBI Subject Areas
BIP Data Models
REST APIs
User Access via Roles
Key Concept:
You never query base tables directly
You use:
Secured views (e.g., PER_PERSON_SECURED_LIST_V)
Subject areas (for OTBI)
Prerequisites
Before working on queries, ensure:
Proper role assignment:
BI Author Role
Human Capital Management Integration Specialist
Access to:
BI Catalog
OTBI subject areas
Understanding of:
HCM data model basics
Effective dating concept
Step-by-Step Build Process
Option 1: Creating OTBI Query
Step 1 – Navigate
Navigator → Tools → Reports and Analytics
Step 2 – Create Analysis
Click “Create” → Analysis
Select Subject Area:
Workforce Management – Worker Assignment Real Time
Step 3 – Select Columns
Example:
Person Number
Assignment Status
Department Name
Step 4 – Apply Filters
Example:
Assignment Status = Active
Step 5 – Save Report
Save under:
Shared Folders → Custom → HCM Reports
Option 2: Creating BI Publisher Query
Step 1 – Navigate
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:
papf.person_number,
papf.full_name,
paaf.assignment_status_type
FROM
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
Step 4 – Define Parameters (Optional)
Person Number
Department
Step 5 – Save Data Model
Step 6 – Create Report Template
Use RTF or Excel template
Option 3: Using REST API Query
Step 1 – Endpoint
Step 2 – Add Filters
Step 3 – Authentication
OAuth 2.0 or Basic Auth
Step 4 – Test via Postman
Testing the Technical Component
Example Test Case
Scenario: Validate employee data after HDL load
Step 1:
Run OTBI report for employee
Step 2:
Run BIP query
Step 3:
Compare results
Expected Results:
Employee appears in both reports
Data matches
Validation Checks:
Effective dates
Assignment status
Department mapping
Common Errors and Troubleshooting
1. No Data Returned
Cause:
Security roles missing
Solution:
Assign BI roles properly
2. Incorrect Data in OTBI
Cause:
OTBI uses cached or real-time logic
Solution:
Validate with BIP query
3. SQL Errors in BIP
Cause:
Using base tables instead of secured views
Solution:
Use Oracle-supported views
4. REST API Authentication Failure
Cause:
Incorrect token or credentials
Solution:
Validate OAuth configuration
Best Practices
1. Choose the Right Tool
OTBI → Quick reports
BIP → Complex queries
REST API → Integrations
2. Always Handle Effective Dates
Fusion is date-effective.
Always include:
3. Avoid Heavy Queries in Peak Time
Schedule BIP reports during off-hours
4. Use Secured Views Only
Never assume base table access.
5. Validate Data Across Tools
OTBI vs BIP vs UI
6. Follow Naming Conventions
Example:
HCM_EMPLOYEE_DETAILS_BIP
HCM_ACTIVE_WORKERS_OTBI
Real Implementation Scenarios
Scenario 1: Data Migration Validation
During implementation:
Load 10,000 employees via HDL
Use BIP query to validate missing fields
Scenario 2: Audit Requirement
Client needs:
List of terminated employees in last 30 days
Solution:
OTBI report with filters
Scenario 3: Integration with Payroll Vendor
Extract employee salary data
Use HCM Extract + BIP query
Frequently Asked Interview Questions
1. What are different query tools in Fusion HCM?
Answer: OTBI, BIP, HCM Extracts, REST APIs.
2. Why can’t we access DB directly?
Answer: Fusion is SaaS; Oracle restricts DB access for security.
3. Difference between OTBI and BIP?
Answer:
OTBI → Real-time, no SQL
BIP → SQL-based, complex reporting
4. What is a subject area in OTBI?
Answer: Predefined data model for reporting.
5. What is secured view?
Answer: Oracle-provided view with security filters.
6. How do you handle effective dating?
Answer: Use effective start and end date conditions.
7. What is HCM Extract?
Answer: Tool for outbound data extraction.
8. What is REST API used for?
Answer: Integration and data retrieval.
9. How do you debug missing data?
Answer:
Check security roles
Validate effective dates
Cross-check with UI
10. What is BI Catalog?
Answer: Repository for reports and data models.
11. Can OTBI handle large data?
Answer: Limited; use BIP for large datasets.
12. What is bursting in BIP?
Answer: Distributing reports automatically.
FAQs
1. Which query tool is best for beginners?
OTBI is best since it doesn’t require SQL knowledge.
2. Can we use joins in OTBI?
Limited joins via subject areas; complex joins require BIP.
3. Is REST API mandatory for integrations?
Yes, especially in OIC Gen 3-based integrations.
Summary
Oracle Fusion HCM Queries are not just about writing SQL—they are about choosing the right tool, understanding the data model, and ensuring security compliance.
In real projects:
OTBI is used for quick validations
BIP is used for complex reporting
REST APIs are used for integrations
HCM Extracts are used for structured outbound data
If you master these query techniques, you will handle:
Data validation
Integration debugging
Reporting requirements
Client audits
For deeper reference, always refer to Oracle’s official documentation:
https://docs.oracle.com/en/cloud/saas/index.html