Mastering SQL for Oracle Fusion HCM – Chapter 20. Advanced SQL Use Cases for Oracle Fusion HCM

 

✅Chapter 20. Advanced SQL Use Cases for Oracle Fusion HCM

Advanced SQL Use Cases in Oracle Fusion HCM

Explore expert-level SQL use cases for Oracle Fusion HCM including CTEs, analytics functions, and real-time business reporting scenarios.

🔹 Introduction

Advanced SQL is a must-have skill for Fusion HCM consultants, BI developers, and HR tech leads. It helps in handling large volumes of data, writing efficient queries, and building complex reports with accuracy and performance.

This chapter explores real-world advanced use cases using:

  • CTEs (Common Table Expressions)

  • Analytic Functions

  • CASE Statements

  • Date-based calculations

  • Subqueries and nesting

  • Optimized conditional joins

🔹 1. Use Case: Latest Active Assignment per Employee


WITH latest_assignment
     AS (SELECT a.*,
                ROW_NUMBER()
                  over (
                    PARTITION BY a.person_id
                    ORDER BY a.effective_start_date DESC) rn
         FROM   per_all_assignments_m a
         WHERE  TRUNC(SYSDATE) BETWEEN a.effective_start_date AND
                                       a.effective_end_date)
SELECT *
FROM   latest_assignment
WHERE  rn = 1 

✅ Purpose: Retrieve most recent assignment info (for dashboards, analytics, etc.)

🔹 2. Use Case: Employees Without Email Address


SELECT p.person_number,
       p.full_name
FROM   per_all_people_f p,
       per_email_addresses e
WHERE  e.email_address IS NULL
       AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND
                                  p.effective_end_date
       AND p.person_id = e.person_id 

✅ Purpose: Identify data gaps in onboarding or master data

🔹 3. Use Case: Employee Movement History (Using LAG)

SELECT person_id,
       assignment_id,
       effective_start_date,
       department_id,
       LAG(department_id)
         over (
           PARTITION BY person_id
           ORDER BY effective_start_date) AS previous_dept
FROM   per_all_assignments_m
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date 

✅ Purpose: Track department changes, role transitions

🔹 4. Use Case: Calculate Employee Age and Years of Service


SELECT person_number,
       full_name,
       FLOOR(MONTHS_BETWEEN(SYSDATE, date_of_birth) / 12) AS age,
       ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 2)  AS years_of_service
FROM   per_all_people_f
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date 

✅ Purpose: Build tenure dashboards and age-related segmentation

🔹 5. Use Case: Department-Wise Headcount Summary

SELECT d.department_name,
       COUNT(DISTINCT p.person_id) AS headcount
FROM   per_all_people_f p,
       per_all_assignments_m a,
       hr_all_departments d
WHERE  TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND
                                  p.effective_end_date
       AND a.department_id = d.department_id
       AND p.person_id = a.person_id
GROUP  BY d.department_name 

✅ Purpose: Useful for HR scorecards and BI dashboards

🔹 6. Use Case: Payroll Element Report with Conditional Logic

SELECT ee.assignment_id,
       ev.input_value_id,
       CASE
         WHEN ev.entry_value < 0 THEN 'Deduction'
         ELSE 'Earning'
       END AS value_type,
       ev.entry_value
FROM   pay_element_entries_f ee,
       pay_element_entry_values_f ev
WHERE  TRUNC(SYSDATE) BETWEEN ee.effective_start_date AND ee.effective_end_date
       AND ee.element_entry_id = ev.element_entry_id 

✅ Purpose: Analyze salary structure and payroll components

🔹 7. Use Case: Identify Rehired Employees

SELECT person_id,
       person_number,
       hire_date
FROM   per_all_people_f
WHERE  person_id IN (SELECT person_id
                     FROM   per_periods_of_service
                     GROUP  BY person_id
                     HAVING COUNT(*) > 1)
       AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date 

✅ Purpose: Track rehires for attrition or rehiring strategy

🔹 8. Use Case: Multiple Assignments for Same Person

SELECT person_id,
       COUNT(*) AS assignment_count
FROM   per_all_assignments_m
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
GROUP  BY person_id
HAVING COUNT(*) > 1 

✅ Purpose: Check for concurrent jobs or duplicate assignment data

🔹 9. Use Case: Absence Days Calculation (If absence tables available)

SELECT person_id,
       absence_type,
       TO_DATE(actual_end_date) - TO_DATE(actual_start_date) + 1 AS absence_days
FROM   per_absence_entries
WHERE  actual_start_date >= ADD_MONTHS(SYSDATE, -6) 

✅ Purpose: Absence trend analysis and leave audits

🔹 10. Use Case: Use of CASE in Custom Grade Display

SELECT person_number,
       CASE
         WHEN grade_code = 'G1' THEN 'Junior'
         WHEN grade_code = 'G2' THEN 'Mid-Level'
         ELSE 'Senior'
       END AS grade_description
FROM   per_all_assignments_m a,
       per_grades g
WHERE  a.grade_id = g.grade_id
       AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND
                                  a.effective_end_date 

✅ Purpose: Customize raw data into meaningful HR categories

🔹 Best Practices for Advanced SQL

  • ✅ Use CTEs for readability and modularity

  • ✅ Use analytic functions for historical tracking

  • ✅ Always filter by effective dates

  • ✅ Write reusable logic using CASE, LAG, LEAD

  • ✅ Validate subqueries with small samples

🔹 Summary

✅ CTEs simplify layered queries
✅ Analytics functions enhance trend and history reporting
✅ CASE statements help in business rule translation
✅ These patterns scale well in Fusion HCM's BI or OTBI layers

🔹 Next Steps

 Pages 
























No comments:

Post a Comment

Subscribe to: Post Comments (Atom)