Mastering SQL for Oracle Fusion HCM –Chapter 9. SQL JOINS – Combining Tables in Fusion HCM

 

✅ Chapter 8. SQL JOINS – Combining Tables in Fusion HCM

SQL JOINS in Oracle Fusion HCM – INNER, LEFT, OUTER with Real-Time Examples

Learn how to use SQL JOINS in Oracle Fusion HCM to combine data across tables like people, assignments, jobs, and departments. Includes real-time use cases and BI Publisher reporting scenarios.

---

🔹 Introduction

In Oracle Fusion HCM, data is normalized across multiple tables like PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, PER_JOBS, and HR_ALL_DEPARTMENTS. To build meaningful reports, you must join these tables.

SQL JOINs allow you to combine data across related tables using common keys like person_id, assignment_id, job_id, or department_id.

---

🔹 Types of SQL JOINs

JOIN Type	Description	Use Case
INNER JOIN	Returns matching records in both tables	Most common in HCM reports
LEFT JOIN or LEFT OUTER JOIN	Returns all from the left table and matching from the right	Include people with or without assignments
RIGHT JOIN or RIGHT OUTER JOIN	Rarely used	Not common in Fusion HCM
FULL OUTER JOIN	Combines all records from both sides	For exceptional audit/reporting
CROSS JOIN	Cartesian product	Avoid in HCM queries

---

🔹 Basic JOIN Syntax

SELECT a.column1,
       b.column2
FROM   table_a a
       JOIN table_b b
         ON a.common_column = b.common_column; 

🔹 Common Joins in Fusion HCM

✅ Join People with Assignments

SELECT papf.person_number,
       papf.full_name,
       paam.assignment_number
FROM   per_all_people_f papf
       JOIN per_all_assignments_m paam
         ON papf.person_id = paam.person_id
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; 

✅ Join Assignments with Jobs

SELECT paam.assignment_number,
       pj.job_name
FROM   per_all_assignments_m paam
       JOIN per_jobs pj
         ON paam.job_id = pj.job_id
WHERE  Trunc(sysdate) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
       AND Trunc(sysdate) BETWEEN pj.effective_start_date AND
                                  pj.effective_end_date; 

✅ Join Assignments with Departments

SELECT paam.assignment_number,
       hd.department_name
FROM   per_all_assignments_m paam
       JOIN hr_all_departments hd
         ON paam.department_id = hd.department_id
WHERE  Trunc(sysdate) BETWEEN hd.effective_start_date AND hd.effective_end_date; 

---

🔹 Real-Time Scenario (Fusion HCM BI Publisher Report)

🧾 Scenario:

“You are asked to create a report displaying full name, assignment number, department name, and job name for all active employees.”

✅ SQL Query:

SELECT papf.full_name,
       paam.assignment_number,
       hd.department_name,
       pj.job_name
FROM   per_all_people_f papf
       JOIN per_all_assignments_m paam
         ON papf.person_id = paam.person_id
       JOIN hr_all_departments hd
         ON paam.department_id = hd.department_id
       JOIN per_jobs pj
         ON paam.job_id = pj.job_id
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 hd.effective_start_date AND
                                  hd.effective_end_date
       AND Trunc(sysdate) BETWEEN pj.effective_start_date AND
                                  pj.effective_end_date; 

🔹 LEFT JOIN Example – Include People Without Assignments

SELECT papf.person_number,
       papf.full_name,
       paam.assignment_number
FROM   per_all_people_f papf
       LEFT JOIN per_all_assignments_m paam
              ON papf.person_id = paam.person_id
                 AND Trunc(sysdate) BETWEEN paam.effective_start_date AND
                                            paam.effective_end_date
WHERE  Trunc(sysdate) BETWEEN papf.effective_start_date AND
                              papf.effective_end_date; 

This shows people even if they don't currently have assignments (e.g., future-dated hires or terminated).

---

🔹 Practice Exercise

💡 Task: Write a query to join assignment data with job and department names for only primary employees.

SELECT paam.assignment_number,
       pj.job_name,
       hd.department_name
FROM   per_all_assignments_m paam
       JOIN per_jobs pj
         ON paam.job_id = pj.job_id
       JOIN hr_all_departments hd
         ON paam.department_id = hd.department_id
WHERE  paam.primary_flag = 'Y'
       AND Trunc(sysdate) BETWEEN paam.effective_start_date AND
                                  paam.effective_end_date
       AND Trunc(sysdate) BETWEEN pj.effective_start_date AND
                                  pj.effective_end_date
       AND Trunc(sysdate) BETWEEN hd.effective_start_date AND
                                  hd.effective_end_date; 

---

🔹 Best Practices

• Always filter date-effective tables using TRUNC(SYSDATE) between effective_start_date and effective_end_date.

• Use table aliases (papf, paam) for readability.

• Avoid CROSS JOIN unless explicitly needed.

• Use LEFT JOIN to include unmatched records (e.g., reporting missing data).

• Ensure all joins are on proper keys (person_id, assignment_id, job_id, etc.).

---

🔹 Summary

✅ SQL JOINs are essential for combining data from multiple HCM tables.
✅ Use INNER JOIN for exact matches, LEFT JOIN for optional/missing records.
✅ In Fusion HCM, common joins include People ↔ Assignments, Assignments ↔ Jobs, Assignments ↔ Departments.
✅ BI reports, value sets, and Extracts heavily rely on correctly written joins.

---

🔹 Next Steps

• Previous Chapter:  Mastering SQL for Oracle Fusion HCM –Chapter 8. Handling NULLs in SQL for Fusion HCM

• Next Chapter: Mastering SQL for Oracle Fusion HCM –Chapter 10. SQL GROUP BY and Aggregations in Fusion HCM