Master the top 5 SQL queries every HR analyst needs. Bridge legacy PeopleSoft data to Oracle Fusion, ensure data integrity, and boost HRIS process improvement.

Introduction

In today’s global workforce, HR data lives at the intersection of technology and people strategy. We’ve all wrestled with the paradox of powerful HRIS platforms—Oracle Fusion, PeopleSoft, Taleo—yet still spend countless hours untangling data inconsistencies, reconciling legacy reports, and proving the ROI of our HR initiatives. The secret to turning that complexity into a seamless integration is not just a fancy UI; it’s a disciplined blend of SQL mastery, data integrity, and rigorous UAT testing.

When we speak about “bridging” the gap between intricate technical configurations and smooth business processes, we are really talking about continuity of excellence—the ability to carry forward trusted data from on‑premise PeopleSoft environments into Oracle Fusion’s cloud, while preserving the analytical insights that drive strategic decisions. Below are the five SQL queries that form the backbone of that bridge, each paired with a brief why‑it‑matters narrative for HR leaders and a quick tip for UAT or regression testing.

Key Takeaways

  • SQL is the lingua franca that connects legacy PeopleSoft tables to Oracle Fusion’s Core HR schema.
  • Data integrity checks (e.g., row‑level audits) are the first line of defense before any UAT testing strategy.
  • Five core queries—headcount, turnover, recruiting pipeline, compensation variance, and change audit—address the most common HR pain points.
  • Documentation and regression testing ensure that each query continues to deliver accurate results after every cloud upgrade.
  • Strategic HRIS planning starts with reliable data; the queries below are your launchpad for process improvement.

Why it matters

Headcount is the heartbeat of Core HR. Executives need a real‑time snapshot of employees per legal entity, country, and employment type to drive workforce planning, budgeting, and compliance. In the transition from PeopleSoft to Oracle Fusion, mismatched entity codes are a frequent source of reporting errors.

The query


SELECT 

    le.LEGAL_ENTITY_NAME,

    e.COUNTRY,

    e.EMPLOYMENT_TYPE,

    COUNT(*) AS HEADCOUNT

FROM 

    FUSION_PERSONS p

JOIN 

    FUSION_EMPLOYMENT_ASSIGNMENTS e ON p.PERSON_ID = e.PERSON_ID

JOIN 

    LEGAL_ENTITY le ON e.LEGAL_ENTITY_ID = le.LEGAL_ENTITY_ID

WHERE 

    e.EFFECTIVE_END_DATE > SYSDATE   -- active assignments only

GROUP BY 

    le.LEGAL_ENTITY_NAME,

    e.COUNTRY,

    e.EMPLOYMENT_TYPE

ORDER BY 

    le.LEGAL_ENTITY_NAME, e.COUNTRY;

Bridge insight

  • Technical side: The query pulls from the Fusion core tables (`FUSION_PERSONS`, `FUSION_EMPLOYMENT_ASSIGNMENTS`) and joins the `LEGAL_ENTITY` reference.
  • Business side: The result feeds directly into capacity planning dashboards and can be validated during UAT by comparing against the legacy PeopleSoft headcount report.

UAT tip: Run the query in a sandbox and compare totals with the PeopleSoft “Employee Summary” report for the same snapshot date. Document any variance and investigate mismatched entity mappings before go‑live.

2. Monthly Turnover Rate

Why it matters

Turnover is a leading indicator of talent health. HR leaders need a consistent, month‑over‑month calculation that works across on‑premise and cloud data stores.

The query


WITH departures AS (

    SELECT 

        PERSON_ID,

        TRUNC(TERMINATION_DATE, 'MM') AS TERM_MONTH

    FROM 

        FUSION_TERMINATIONS

    WHERE 

        TERMINATION_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -12) 

                              AND TRUNC(SYSDATE, 'MM')

),

headcount AS (

    SELECT 

        TRUNC(EFFECTIVE_START_DATE, 'MM') AS START_MONTH,

        COUNT(DISTINCT PERSON_ID) AS HC

    FROM 

        FUSION_EMPLOYMENT_ASSIGNMENTS

    WHERE 

        EFFECTIVE_END_DATE > SYSDATE

    GROUP BY 

        TRUNC(EFFECTIVE_START_DATE, 'MM')

)

SELECT 

    d.TERM_MONTH,

    COUNT(d.PERSON_ID) AS DEPARTURES,

    COALESCE(SUM(h.HC),0) AS AVERAGE_HEADCOUNT,

    ROUND( (COUNT(d.PERSON_ID) / NULLIF(COALESCE(SUM(h.HC),0),0)) * 100, 2) AS TURNOVER_PCT

FROM 

    departures d

LEFT JOIN 

    headcount h ON h.START_MONTH = d.TERM_MONTH

GROUP BY 

    d.TERM_MONTH

ORDER BY 

    d.TERM_MONTH DESC;

Bridge insight

  • Technical side: Uses CTE (Common Table Expressions) to isolate departures and calculate average headcount, ensuring the logic works regardless of schema changes.
  • Business side: Provides a turnover percentage that can be embedded in HRIS process improvement scorecards.

Regression testing tip: After any Oracle Fusion patch, re‑run this query for the last 12 months and verify that the turnover percentages match the pre‑patch baseline. Any drift may indicate a change in date handling or assignment logic.

3. Recruiting Funnel – From Open Reqs to Hired Employees

Why it matters

Connecting Oracle Recruiting Cloud data to Core HR is essential for measuring time‑to‑fill and for aligning recruiting spend with actual hires.

The query


SELECT 

    r.REQUISITION_ID,

    r.JOB_TITLE,

    COUNT(DISTINCT a.APPLICATION_ID) AS APPS_RECEIVED,

    COUNT(DISTINCT h.HIRE_ID)      AS HIRES,

    ROUND( (COUNT(DISTINCT h.HIRE_ID) / NULLIF(COUNT(DISTINCT a.APPLICATION_ID),0)) * 100, 2) 

        AS CONVERSION_RATE_PCT

FROM 

    ORACLE_RECRUITING_REQUISITIONS r

LEFT JOIN 

    ORACLE_RECRUITING_APPLICATIONS a ON r.REQUISITION_ID = a.REQUISITION_ID

LEFT JOIN 

    FUSION_HIRES h ON a.APPLICATION_ID = h.APPLICATION_ID

WHERE 

    r.OPEN_DATE >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6)   -- last 6 months

GROUP BY 

    r.REQUISITION_ID,

    r.JOB_TITLE

ORDER BY 

    CONVERSION_RATE_PCT DESC;

Bridge insight

  • Technical side: Joins Recruiting Cloud tables (`ORACLE_RECRUITING_REQUISITIONS`, `APPLICATIONS`) with the Core HR hire table (`FUSION_HIRES`).
  • Business side: Shows the conversion rate from applications to hires, a metric that drives both recruiting strategy and onboarding workload planning.

Documentation tip: Capture the join logic in the HRIS knowledge base, noting any custom fields (e.g., `APPLICATION_SOURCE`) that may be added later. This safeguards the query during future schema extensions.

4. Compensation Variance – Spotting Anomalies

Why it matters

Compensation data is a frequent source of audit findings. A quick variance check helps us verify that salary grades, bonuses, and currency conversions are applied consistently across regions.

The query


SELECT 

    p.PERSON_ID,

    p.FULL_NAME,

    c.COMPONENT_NAME,

    c.AMOUNT_LOCAL,

    c.AMOUNT_USD,

    ROUND(c.AMOUNT_USD - (c.AMOUNT_LOCAL * cr.EXCHANGE_RATE), 2) AS USD_VARIANCE

FROM 

    FUSION_PERSONS p

JOIN 

    FUSION_COMPENSATION_COMPONENTS c ON p.PERSON_ID = c.PERSON_ID

JOIN 

    FX_RATES cr ON c.CURRENCY_CODE = cr.CURRENCY_CODE

WHERE 

    c.EFFECTIVE_DATE = (SELECT MAX(EFFECTIVE_DATE) FROM FUSION_COMPENSATION_COMPONENTS WHERE PERSON_ID = p.PERSON_ID)

    AND ABS(USD_VARIANCE) > 1000   -- flag > $1,000 variance

ORDER BY 

    USD_VARIANCE DESC;

Bridge insight

  • Technical side: Leverages a foreign‑exchange rate table (`FX_RATES`) to normalize local amounts to USD, exposing discrepancies.
  • Business side: The flagged rows become the basis for compensation audits, ensuring data integrity before payroll processing.

UAT testing strategy: Include a test case where a known salary conversion error is injected (e.g., wrong exchange rate). Verify that the query surfaces the anomaly, confirming that the validation rule works end‑to‑end.

5. Change Audit – Who Modified What and When

Why it matters

Governance, Risk, and Compliance (GRC) teams demand a clear audit trail for any Core HR data change. Whether it’s a title update or a cost‑center reassignment, a change audit query provides the evidence needed for internal controls and external audits.

The query


SELECT 

    audit.AUDIT_ID,

    audit.PERSON_ID,

    p.FULL_NAME,

    audit.COLUMN_NAME,

    audit.OLD_VALUE,

    audit.NEW_VALUE,

    audit.CHANGE_DATE,

    u.USER_NAME AS CHANGED_BY

FROM 

    FUSION_AUDIT_TRAIL audit

JOIN 

    FUSION_PERSONS p ON audit.PERSON_ID = p.PERSON_ID

JOIN 

    FUSION_USERS u ON audit.CHANGED_BY = u.USER_ID

WHERE 

    audit.CHANGE_DATE >= TRUNC(SYSDATE, 'MM')   -- current month

    AND audit.TABLE_NAME = 'FUSION_EMPLOYMENT_ASSIGNMENTS'

ORDER BY 

    audit.CHANGE_DATE DESC;

Bridge insight

  • Technical side: Pulls from the system‑generated audit tables (`FUSION_AUDIT_TRAIL`) and enriches the result with user-friendly names.
  • Business side: Enables process improvement by identifying frequent change patterns (e.g., repeated cost‑center updates) that may signal a need for workflow automation.

Documentation tip: Store the query definition and a sample output in the HRIS change‑management SOP. Reference it during UAT sign‑off to demonstrate that audit capture meets regulatory requirements.

Why UAT Is the Safety Net of Global Rollouts

UAT (User Acceptance Testing) is not a checkbox; it’s the safety net that validates our bridge between technical configuration and business reality. When we run the five queries above in a UAT environment, we are essentially asking:

1. Does the data we migrated from PeopleSoft match the cloud view?

2. Are our business rules (e.g., headcount thresholds, turnover calculations) still accurate after the migration?

3. Can end‑users trust the reports that drive their decisions?

A robust UAT plan includes regression test scripts that execute each query against a known data set, compare results, and log any deviations. This systematic approach protects us from “silent” data corruption that can otherwise erode stakeholder confidence.

Bridging the Gap Between Recruiting and Onboarding

The recruiting‑to‑onboarding handoff is a classic pain point. By using the Recruiting Funnel query (Query 3) together with a new‑hire onboarding status report, we can create a single source of truth that tracks each candidate from requisition to day‑one. This eliminates duplicate data entry, reduces time‑to‑productivity, and showcases the continuity of excellence that modern HRIS platforms promise.

Conclusion

Data is the foundation of every HR transformation. Whether you are migrating legacy PeopleSoft tables to Oracle Fusion’s cloud, fine‑tuning Oracle Recruiting Cloud, or simply striving for HRIS process improvement, the five SQL queries outlined here act as bridges—connecting complex technical configurations to the seamless business processes our stakeholders expect.

We invite you to:

  • Integrate these queries into your regular health‑check routine.
  • Document the logic, expected results, and UAT test cases in a shared repository.
  • Plan a strategic HRIS roadmap that prioritizes data integrity, rigorous testing, and continuous process optimization.

If you’re ready to elevate your HR analytics and ensure a smooth, future‑proof HRIS journey, let’s start a conversation. Reach out to our team for a free HRIS health assessment and discover how a disciplined, techno‑functional approach can turn data challenges into strategic advantage.

Keywords: Oracle Fusion, Core HR, UAT testing strategies, Oracle Recruiting Cloud, Data Integrity, HRIS Process Improvement