Mastering SQL for Oracle Fusion HCM – Chapter 2 : Oracle Fusion HCM Architecture

 

✅ Chapter 2. Understanding Oracle Fusion HCM Architecture

Oracle Fusion HCM Architecture – SQL for HCM Data

Explore the architecture of Oracle Fusion HCM and understand how SQL interacts with HCM modules, tables, and data models.

🔹 Introduction

Before diving deeper into SQL, it's essential to understand the Oracle Fusion HCM architecture. Knowing how the system is structured, how data is stored, and how modules interact will help you write more effective and optimized SQL queries.

Oracle Fusion HCM is a part of the Oracle Cloud suite and provides a modern, integrated solution for human capital management. It is built on Oracle Fusion Middleware and utilizes a relational database model, making SQL the primary language for querying and reporting.

🔹 Theoretical Concepts

🏗️ Key Layers of Oracle Fusion HCM

LayerDescription
User Interface (UI)Accessed via browser (Responsive UI, HCM Classic)
Application LogicBuilt using Oracle ADF (Application Development Framework)
Database LayerOracle Relational Database (tables, views, indexes)
Integration LayerWeb services, HCM Extracts, BI Publisher, HDL

📦 Core Fusion HCM Modules

  • Global Human Resources (Core HR)

  • Payroll

  • Absence Management

  • Recruiting

  • Compensation

  • Talent Management

  • Workforce Structures

Each of these modules stores data in separate but related tables, which you’ll query using SQL.

🔑 Key Concepts You’ll Use in SQL

ConceptDescription
Effective DatingMost tables (like PER_ALL_PEOPLE_F) store changes over time using effective_start_date and effective_end_date
Legislative Data Group (LDG)Tied to payroll and legal data
Action TypesTrack employee movements (hire, promote, terminate)
LookupsStore user-defined values and codes

🔹 SQL Query Examples (with Context)

📌 Example: List All Legal Employers

SELECT legal_employer_name,
       legal_employer_id
FROM   per_legal_employers_f
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date 

📌 Example: Retrieve Basic Assignment Info


SELECT person_id,
       assignment_number,
       job_id
FROM   per_all_assignments_m
WHERE  assignment_type = 'E'
       AND primary_flag = 'Y' 

🔹 Real-Time Scenario (Fusion HCM Use Case)

📁 Scenario:

“You are asked to prepare a report that lists employees along with their legal employer and current assignment information.”

✅ Solution Approach:

To fulfill this request, you need to:

  • Join PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, and PER_LEGAL_EMPLOYERS_F

  • Use effective dating to ensure current records


SELECT papf.person_number,
       papf.full_name,
       paam.assignment_number,
       ple.legal_employer_name
FROM   per_all_people_f papf,
       per_all_assignments_m paam,
       per_legal_employers_f ple
WHERE  TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND
                              papf.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                                  paam.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN ple.effective_start_date AND
                                  ple.effective_end_date
       AND paam.legal_employer_id = ple.legal_employer_id
       AND papf.person_id = paam.person_id
       AND paam.primary_flag = 'Y' 

🔹 Best Practices and Notes

  • Always use effective date filtering when working with _F (date-tracked) tables.

  • Use primary_flag = 'Y' to fetch the main assignment.

  • Understand foreign key relationships to join tables correctly.

  • Use data security views in OTBI/BI Publisher if using SQL from the front-end.

🔹 Summary

✅ Fusion HCM is built on a modular architecture, each with its own table structure.
✅ Effective dating is critical when writing SQL queries.
✅ Core tables include PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, and PER_LEGAL_EMPLOYERS_F.
✅ Joins between tables are common in real-world reports and extracts.
✅ Understanding architecture improves your SQL design and troubleshooting.

🔹 Next Steps

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)