Mastering SQL for Oracle Fusion HCM – Chapter 19. Creating Reusable SQL Snippets and Templates for HCM Reporting

 

✅Chapter 19. Creating Reusable SQL Snippets and Templates for HCM Reporting

Reusable SQL Snippets & Templates for Fusion HCM Reporting

Boost productivity with ready-to-use, reusable SQL snippets and templates tailored for Oracle Fusion HCM reports, BI Publisher, and OTBI custom logic.

🔹 Introduction

In a typical Oracle Fusion HCM reporting environment, certain SQL patterns are repeated often — like pulling current assignments, department details, or payroll info. Instead of rewriting these queries each time, use modular and reusable SQL snippets.

This chapter presents:

  • Commonly used SQL blocks

  • Modular query structures

  • Templates for BI Publisher reports

  • Snippets for HCM business logic

🔹 1. Template: Get Current Employee Assignment


SELECT a.assignment_id,
       a.person_id,
       a.assignment_status_type
FROM   per_all_assignments_m a
WHERE  TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
       AND a.assignment_status_type = 'ACTIVE' 

✅ Use Case: Any active employee-related report

🔹 2. Template: Get Full Name and Person Number


SELECT p.person_id,
       p.person_number,
       p.full_name
FROM   per_all_people_f p
WHERE  TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date 

✅ Use Case: Standard identity for employees in BI reports

🔹 3. Template: Join Employees and Jobs


SELECT p.person_number,
       p.full_name,
       j.job_name
FROM   per_all_people_f p,
       per_all_assignments_m a,
       per_jobs j
WHERE  TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN a.effective_start_date AND
                                  a.effective_end_date
       AND a.job_id = j.job_id
       AND p.person_id = a.person_id 

✅ Use Case: Combine job titles with employee info

🔹 4. Snippet: Add Department Name


SELECT d.department_name
FROM   hr_all_departments d
WHERE  d.department_id = :DEPARTMENT_ID 

✅ Use Case: Reuse in subqueries or joins to get department names

🔹 5. Template: Fetch Element Entry Values (Payroll)


SELECT ee.assignment_id,
       ee.element_type_id,
       ee.input_value_id,
       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 

🔹 6. Snippet: Calculate Employee Tenure


ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 2) AS years_of_service

✅ Use Case: HR analytics for service awards or attrition reports

🔹 7. BI Publisher Snippet: Use of Parameters


WHERE department_id = :P_DEPT_ID
AND trunc(SYSDATE) BETWEEN effective_start_date AND effective_end_date

✅ Tip: Parameter names should match BIP data model prompts

🔹 8. Snippet: Handle NULL Email Addresses


NVL(e.email_address, 'Not Available') AS email_address

✅ Use Case: Display fallback values in reports

🔹 9. Snippet: Show Only Latest Assignment Row


ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY effective_start_date DESC) AS rn

Use this inside a CTE or subquery to filter only latest records:


WITH latest
     AS (SELECT a.*,
                ROW_NUMBER()
                  over (
                    PARTITION BY person_id
                    ORDER BY effective_start_date DESC) rn
         FROM   per_all_assignments_m a)
SELECT *
FROM   latest
WHERE  rn = 1 

🔹 10. Template: Salary and Grade Info (If accessible)


SELECT s.salary_amount,
       g.grade_code
FROM   per_all_assignments_m a,
       per_grades g
WHERE  TRUNC(SYSDATE) BETWEEN a.effective_start_date AND a.effective_end_date
       AND a.grade_id = g.grade_id
       AND a.assignment_id = s.assignment_id 

✅ Use Case: Compensation reports, band analysis, etc.

🔹 Tips for Using Snippets

  • ✅ Maintain a SQL Snippet Library in your local Notion/OneNote

  • ✅ Use CTEs to wrap snippets for modular logic

  • ✅ In BIP, save commonly used filters and conditions in global templates

  • ✅ Always include TRUNC(SYSDATE) for date-effective logic

🔹 Summary

✅ Modular SQL snippets improve speed, consistency, and quality
✅ Templates reduce errors and support multiple report types
✅ Use clean joins, aliases, and formatting for reusability
✅ Keep snippets versioned for easy rollback and testing

🔹 Next Steps

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)