Oracle HCM Compliance Audit SQL Queries

1. Why Oracle HCM Audits Are Hard

Oracle Fusion HCM stores data using a date-effective architecture: every change to a person, assignment, or position creates a new row rather than overwriting the old one. This is great for history — but it means that naive SQL returns multiple rows per employee, and most audit reports from consultants contain subtle bugs.

The three most common audit failures:

Wrong effective date: Using SYSDATE returns current state. Auditors need state as of a specific date (quarter-end, fiscal year-end, or date of the incident).
Missing assignment type filter: PER_ALL_ASSIGNMENTS_M contains employee, contractor, and applicant rows. Without ASSIGNMENT_TYPE = 'E', headcount is inflated.
Ignoring EFFECTIVE_LATEST_CHANGE: On _M tables, this flag identifies the most recent effective row for a date range. Omitting it duplicates records.

ℹ️

All queries below use :as_of_date bind variables. Substitute DATE '2026-03-31' (or your target date) in any SQL tool. For Oracle OTBI Logical SQL, replace the date filter with subject area date prompts.

2. Query 1: Point-in-Time Headcount

The foundational audit query. Returns active headcount as of any date, by legal employer and department. This is what auditors ask for on day one of every HR compliance review.

⚠️

Key tables: PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_M, HR_ALL_ORGANIZATION_UNITS_F. The _M suffix is the current-record view that replaces deprecated PER_ALL_ASSIGNMENTS_F in 24B+.

Query 1 — Point-in-Time Headcount by Legal Employer & Department

-- Point-in-time active headcount audit
-- Replace :as_of_date with your target date, e.g. DATE '2026-03-31'
SELECT
    le.name                                        AS legal_employer,
    dept.name                                      AS department,
    COUNT(DISTINCT paam.person_id)               AS headcount
FROM
    per_all_assignments_m      paam
    JOIN per_all_people_f       papf  ON  papf.person_id          = paam.person_id
                                          AND :as_of_date           BETWEEN papf.effective_start_date
                                                                             AND papf.effective_end_date
    JOIN hr_all_organization_units_f le
                                      ON  le.organization_id      = paam.legal_entity_id
                                          AND :as_of_date           BETWEEN le.effective_start_date
                                                                             AND le.effective_end_date
    JOIN hr_all_organization_units_f dept
                                      ON  dept.organization_id   = paam.organization_id
                                          AND :as_of_date           BETWEEN dept.effective_start_date
                                                                             AND dept.effective_end_date
WHERE
    :as_of_date BETWEEN paam.effective_start_date AND paam.effective_end_date
    AND paam.assignment_type        = 'E'           -- employees only, not contractors/applicants
    AND paam.assignment_status_type = 'ACTIVE_ASSIGN'
    AND paam.effective_latest_change = 'Y'          -- one row per assignment per date range
    AND paam.primary_flag           = 'Y'          -- primary assignment only
GROUP BY
    le.name,
    dept.name
ORDER BY
    le.name,
    dept.name;

3. Query 2: Termination Reason Audit

Returns all terminations in a date range with reason codes, manager, and last assignment details. Used by HR auditors to verify voluntary vs. involuntary separation ratios and flag unusual termination patterns.

Query 2 — Termination Reason Audit

-- Terminations with reason codes for a date range
-- :start_date and :end_date bound the termination window
SELECT
    papf.person_number,
    papf.display_name,
    ppos.actual_termination_date,
    ppos.notified_termination_date,
    ppos.last_working_day,
    flv_action.meaning                             AS action_name,
    flv_reason.meaning                             AS action_reason,
    ppos.voluntary_flag,
    dept.name                                      AS department_at_term,
    job.name                                       AS job_at_term,
    mgr.display_name                               AS manager_name
FROM
    per_periods_of_service     ppos
    JOIN per_all_people_f       papf  ON  papf.person_id          = ppos.person_id
                                          AND ppos.actual_termination_date
                                              BETWEEN papf.effective_start_date
                                                  AND papf.effective_end_date
    JOIN per_all_assignments_m  paam  ON  paam.period_of_service_id = ppos.period_of_service_id
                                          AND paam.effective_latest_change = 'Y'
                                          AND paam.primary_flag           = 'Y'
    JOIN hr_all_organization_units_f dept
                                      ON  dept.organization_id   = paam.organization_id
                                          AND ppos.actual_termination_date
                                              BETWEEN dept.effective_start_date
                                                  AND dept.effective_end_date
    JOIN per_jobs_f              job   ON  job.job_id              = paam.job_id
                                          AND ppos.actual_termination_date
                                              BETWEEN job.effective_start_date
                                                  AND job.effective_end_date
    LEFT JOIN fnd_lookup_values  flv_action
                                      ON  flv_action.lookup_type = 'HR_TERMINATION_ACTION'
                                          AND flv_action.lookup_code = ppos.leaving_reason
                                          AND flv_action.language    = 'US'
    LEFT JOIN fnd_lookup_values  flv_reason
                                      ON  flv_reason.lookup_type  = 'LEAV_REAS'
                                          AND flv_reason.lookup_code = ppos.leaving_reason_secondary
                                          AND flv_reason.language    = 'US'
    LEFT JOIN per_all_people_f   mgr   ON  mgr.person_id           = paam.manager_id
                                          AND ppos.actual_termination_date
                                              BETWEEN mgr.effective_start_date
                                                  AND mgr.effective_end_date
WHERE
    ppos.actual_termination_date BETWEEN :start_date AND :end_date
ORDER BY
    ppos.actual_termination_date DESC;

4. Query 3: Payroll Variance Detection

Compares gross pay between two consecutive payroll periods and flags employees with variance above a threshold. Compliance teams use this to detect unauthorized pay changes, ghost employees, and data entry errors before external audit.

Query 3 — Payroll Variance Detection (Period-over-Period)

-- Detects pay variance > :variance_pct% between two payroll periods
-- :period1_name = prior period, :period2_name = current period, e.g. '2026-03'
WITH period1 AS (
    SELECT
        paa.assignment_id,
        paa.person_id,
        SUM(prrv.result_value)  AS gross_pay
    FROM
        pay_payroll_actions     ppa
        JOIN pay_assignment_actions paa  ON paa.payroll_action_id = ppa.payroll_action_id
        JOIN pay_run_results       prr  ON prr.assignment_action_id = paa.assignment_action_id
        JOIN pay_run_result_values prrv ON prrv.run_result_id     = prr.run_result_id
        JOIN pay_input_values_f    piv  ON piv.input_value_id     = prrv.input_value_id
                                         AND piv.name = 'Pay Value'
    WHERE
        ppa.action_type   IN ('R', 'Q')     -- Regular run and QuickPay
        AND ppa.action_status  = 'C'          -- Completed only
        AND TO_CHAR(ppa.effective_date, 'YYYY-MM') = :period1_name
    GROUP BY paa.assignment_id, paa.person_id
),
period2 AS (
    SELECT
        paa.assignment_id,
        paa.person_id,
        SUM(prrv.result_value)  AS gross_pay
    FROM
        pay_payroll_actions     ppa
        JOIN pay_assignment_actions paa  ON paa.payroll_action_id = ppa.payroll_action_id
        JOIN pay_run_results       prr  ON prr.assignment_action_id = paa.assignment_action_id
        JOIN pay_run_result_values prrv ON prrv.run_result_id     = prr.run_result_id
        JOIN pay_input_values_f    piv  ON piv.input_value_id     = prrv.input_value_id
                                         AND piv.name = 'Pay Value'
    WHERE
        ppa.action_type   IN ('R', 'Q')
        AND ppa.action_status  = 'C'
        AND TO_CHAR(ppa.effective_date, 'YYYY-MM') = :period2_name
    GROUP BY paa.assignment_id, paa.person_id
)
SELECT
    papf.person_number,
    papf.display_name,
    p1.gross_pay                                   AS prior_period_pay,
    p2.gross_pay                                   AS current_period_pay,
    ROUND((p2.gross_pay - p1.gross_pay) / NULLIF(p1.gross_pay, 0) * 100, 2)
                                                   AS variance_pct,
    p2.gross_pay - p1.gross_pay                    AS variance_amount
FROM
    period2        p2
    JOIN period1   p1     ON  p1.person_id = p2.person_id
    JOIN per_all_people_f papf
                          ON  papf.person_id = p2.person_id
                              AND SYSDATE BETWEEN papf.effective_start_date
                                               AND papf.effective_end_date
WHERE
    ABS((p2.gross_pay - p1.gross_pay) / NULLIF(p1.gross_pay, 0) * 100) > :variance_pct
ORDER BY
    ABS(p2.gross_pay - p1.gross_pay) DESC;

5. Query 4: Rehire Detection

Returns all active employees who have a prior terminated period of service. Used for rehire policy compliance — many organizations require manager or CHRO approval before rehiring terminated employees, especially involuntary terminations.

Query 4 — Active Employees with Prior Termination (Rehire Detection)

-- Identifies active employees who were previously terminated
SELECT
    papf.person_number,
    papf.display_name,
    papf.date_of_birth,
    curr_pos.date_start                            AS rehire_date,
    prior_pos.actual_termination_date              AS prior_term_date,
    prior_pos.leaving_reason,
    flv.meaning                                    AS prior_term_reason,
    prior_pos.voluntary_flag                       AS prior_voluntary,
    MONTHS_BETWEEN(curr_pos.date_start,
                   prior_pos.actual_termination_date)
                                                   AS gap_months
FROM
    per_all_people_f            papf
    JOIN per_periods_of_service curr_pos
                                      ON  curr_pos.person_id     = papf.person_id
                                          AND curr_pos.actual_termination_date IS NULL  -- current active POS
    JOIN per_periods_of_service prior_pos
                                      ON  prior_pos.person_id    = papf.person_id
                                          AND prior_pos.period_of_service_id
                                              != curr_pos.period_of_service_id
                                          AND prior_pos.actual_termination_date IS NOT NULL
    LEFT JOIN fnd_lookup_values flv
                                      ON  flv.lookup_type        = 'LEAV_REAS'
                                          AND flv.lookup_code     = prior_pos.leaving_reason
                                          AND flv.language        = 'US'
WHERE
    SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
ORDER BY
    curr_pos.date_start DESC;

6. Query 5: Job Change Audit Trail

Returns the full history of job, grade, and salary changes for a person or population. Auditors use this to verify that promotions and transfers followed the correct approval workflow and compensation bands.

Query 5 — Job & Salary Change History Audit Trail

-- Full job + salary change history for audit period
-- :start_date / :end_date filter by when the change became effective
SELECT
    papf.person_number,
    papf.display_name,
    paam.effective_start_date                      AS change_date,
    job.name                                       AS job_name,
    grade.name                                     AS grade,
    pos.name                                       AS position_name,
    dept.name                                      AS department,
    paam.normal_hours,
    sal.proposed_salary_n                          AS proposed_salary,
    sal.change_amount_n                            AS salary_change_amount,
    sal.change_percent                             AS salary_change_pct,
    flv.meaning                                    AS salary_change_reason
FROM
    per_all_assignments_m        paam
    JOIN per_all_people_f        papf  ON  papf.person_id        = paam.person_id
                                          AND paam.effective_start_date
                                              BETWEEN papf.effective_start_date
                                                  AND papf.effective_end_date
    LEFT JOIN per_jobs_f         job   ON  job.job_id            = paam.job_id
                                          AND paam.effective_start_date
                                              BETWEEN job.effective_start_date
                                                  AND job.effective_end_date
    LEFT JOIN per_grades_f       grade ON  grade.grade_id        = paam.grade_id
                                          AND paam.effective_start_date
                                              BETWEEN grade.effective_start_date
                                                  AND grade.effective_end_date
    LEFT JOIN hr_all_positions_f pos   ON  pos.position_id       = paam.position_id
                                          AND paam.effective_start_date
                                              BETWEEN pos.effective_start_date
                                                  AND pos.effective_end_date
    LEFT JOIN hr_all_organization_units_f dept
                                      ON  dept.organization_id = paam.organization_id
                                          AND paam.effective_start_date
                                              BETWEEN dept.effective_start_date
                                                  AND dept.effective_end_date
    LEFT JOIN cmp_salary          sal   ON  sal.assignment_id     = paam.assignment_id
                                          AND paam.effective_start_date
                                              BETWEEN sal.date_from
                                                  AND NVL(sal.date_to, DATE '4712-12-31')
    LEFT JOIN fnd_lookup_values  flv   ON  flv.lookup_type       = 'SALARY_CHANGE_REASON'
                                          AND flv.lookup_code    = sal.change_reason
                                          AND flv.language       = 'US'
WHERE
    paam.assignment_type         = 'E'
    AND paam.primary_flag        = 'Y'
    AND paam.effective_start_date BETWEEN :start_date AND :end_date
ORDER BY
    papf.display_name,
    paam.effective_start_date;

7. Query 6: Absence Accrual Balance Audit

Returns current accrual balances by plan and employee as of a specific date. Used for year-end liability calculations, audit of excess carry-forward balances, and SOX controls around accrued PTO liability.

Query 6 — Absence Accrual Balance Audit

-- Absence accrual balances as of :as_of_date
SELECT
    papf.person_number,
    papf.display_name,
    aap.name                                       AS absence_plan,
    anr.accrual_date,
    anr.net_accrual_value                          AS balance_days,
    anr.accrued_value,
    anr.taken_value,
    anr.adjusted_value,
    anr.ceiling_value,
    anr.carry_over_value,
    dept.name                                      AS department
FROM
    anc_accrual_plan_enrollments  aape
    JOIN anc_accrual_plans         aap   ON  aap.accrual_plan_id    = aape.accrual_plan_id
    JOIN anc_net_accruals          anr   ON  anr.accrual_plan_enrollment_id
                                               = aape.accrual_plan_enrollment_id
                                           AND anr.accrual_date      = (
                                                   SELECT MAX(anr2.accrual_date)
                                                   FROM   anc_net_accruals anr2
                                                   WHERE  anr2.accrual_plan_enrollment_id
                                                          = aape.accrual_plan_enrollment_id
                                                     AND  anr2.accrual_date <= :as_of_date
                                               )
    JOIN per_all_people_f          papf  ON  papf.person_id         = aape.person_id
                                           AND :as_of_date BETWEEN  papf.effective_start_date
                                                                    AND papf.effective_end_date
    LEFT JOIN per_all_assignments_m paam ON  paam.person_id         = papf.person_id
                                           AND :as_of_date BETWEEN  paam.effective_start_date
                                                                    AND paam.effective_end_date
                                           AND paam.primary_flag    = 'Y'
                                           AND paam.effective_latest_change = 'Y'
    LEFT JOIN hr_all_organization_units_f dept
                                       ON  dept.organization_id  = paam.organization_id
                                           AND :as_of_date BETWEEN  dept.effective_start_date
                                                                    AND dept.effective_end_date
WHERE
    aape.enrollment_status = 'A'
ORDER BY
    aap.name,
    papf.display_name;

8. Query 7: Position Change Audit

Tracks all position movements across the organization for a period: promotions, transfers, reclassifications. Useful for workforce planning audits and verifying that position budget controls were respected.

Query 7 — Position Change Audit (FTE Movements)

-- All assignment-to-position changes in a date range
SELECT
    papf.person_number,
    papf.display_name,
    paam.effective_start_date                      AS movement_date,
    pos_new.name                                   AS new_position,
    pos_new.full_part_time                         AS new_fte_type,
    dept_new.name                                  AS new_department,
    pos_old.name                                   AS prior_position,
    dept_old.name                                  AS prior_department,
    CASE
        WHEN dept_new.organization_id != dept_old.organization_id THEN 'TRANSFER'
        WHEN pos_new.position_id     != pos_old.position_id       THEN 'POSITION CHANGE'
        ELSE 'OTHER'
    END                                            AS movement_type,
    mgr.display_name                               AS approving_manager
FROM
    per_all_assignments_m        paam
    JOIN per_all_people_f        papf     ON  papf.person_id        = paam.person_id
                                              AND paam.effective_start_date
                                                  BETWEEN papf.effective_start_date
                                                      AND papf.effective_end_date
    LEFT JOIN hr_all_positions_f pos_new  ON  pos_new.position_id   = paam.position_id
                                              AND paam.effective_start_date
                                                  BETWEEN pos_new.effective_start_date
                                                      AND pos_new.effective_end_date
    LEFT JOIN hr_all_organization_units_f dept_new
                                          ON  dept_new.organization_id = paam.organization_id
                                              AND paam.effective_start_date
                                                  BETWEEN dept_new.effective_start_date
                                                      AND dept_new.effective_end_date
    LEFT JOIN per_all_assignments_m paam_prior
                                          ON  paam_prior.assignment_id = paam.assignment_id
                                              AND paam_prior.effective_end_date
                                                  = paam.effective_start_date - 1
                                              AND paam_prior.effective_latest_change = 'Y'
    LEFT JOIN hr_all_positions_f pos_old  ON  pos_old.position_id   = paam_prior.position_id
                                              AND paam_prior.effective_end_date
                                                  BETWEEN pos_old.effective_start_date
                                                      AND pos_old.effective_end_date
    LEFT JOIN hr_all_organization_units_f dept_old
                                          ON  dept_old.organization_id = paam_prior.organization_id
                                              AND paam_prior.effective_end_date
                                                  BETWEEN dept_old.effective_start_date
                                                      AND dept_old.effective_end_date
    LEFT JOIN per_all_people_f   mgr      ON  mgr.person_id         = paam.manager_id
                                              AND paam.effective_start_date
                                                  BETWEEN mgr.effective_start_date
                                                      AND mgr.effective_end_date
WHERE
    paam.assignment_type         = 'E'
    AND paam.primary_flag        = 'Y'
    AND paam.effective_latest_change = 'Y'
    AND paam.effective_start_date BETWEEN :start_date AND :end_date
    AND paam_prior.assignment_id  IS NOT NULL   -- must have a prior row (not a new hire)
    AND (  pos_new.position_id  != pos_old.position_id
        OR dept_new.organization_id != dept_old.organization_id)
ORDER BY
    paam.effective_start_date DESC;

9. Query 8: Costing Distribution Audit

Returns payroll cost allocations by GL account, cost center, and employee. Used to reconcile payroll journals against the general ledger and verify that costing overrides were properly authorized during the audit period.

Query 8 — Payroll Costing Distribution Audit

-- Payroll costing by GL segment for a period
-- :payroll_period_name = e.g. 'Monthly Payroll 2026-03'
SELECT
    papf.person_number,
    papf.display_name,
    pet.element_name,
    pca.cost_allocation_keyflex_id,
    -- GL segments — adjust segment numbers to your chart of accounts
    gcc.segment1                                   AS company,
    gcc.segment2                                   AS cost_center,
    gcc.segment3                                   AS account,
    gcc.segment4                                   AS project,
    pca.debit_or_credit,
    pca.costed_value,
    ppa.effective_date                             AS payroll_date,
    ppa.payroll_id
FROM
    pay_payroll_actions           ppa
    JOIN pay_assignment_actions   paa    ON  paa.payroll_action_id  = ppa.payroll_action_id
    JOIN pay_costs                pca    ON  pca.assignment_action_id = paa.assignment_action_id
    JOIN pay_run_results          prr    ON  prr.run_result_id      = pca.run_result_id
    JOIN pay_element_types_f      pet    ON  pet.element_type_id    = prr.element_type_id
                                            AND ppa.effective_date
                                                BETWEEN pet.effective_start_date
                                                    AND pet.effective_end_date
    JOIN gl_code_combinations     gcc    ON  gcc.code_combination_id
                                               = pca.gl_code_combination_id
    JOIN per_all_people_f         papf   ON  papf.person_id         = paa.person_id
                                            AND ppa.effective_date
                                                BETWEEN papf.effective_start_date
                                                    AND papf.effective_end_date
WHERE
    ppa.action_type        IN ('R', 'Q')
    AND ppa.action_status  = 'C'
    AND ppa.payroll_period_name = :payroll_period_name
ORDER BY
    gcc.segment2,          -- cost center
    papf.display_name,
    pet.element_name;

10. Audit Query Best Practices

Date-Effective Filtering Rules

Scenario	Filter to Use	Why
Point-in-time state	`:date BETWEEN eff_start AND eff_end`	Returns state at a specific moment
Changes in a period	`eff_start BETWEEN :start AND :end`	Returns rows that became effective in the window
Current state	`SYSDATE BETWEEN eff_start AND eff_end`	Use only for operational reports, not audits
Latest row in range	`effective_latest_change = 'Y'`	Deduplicates _M tables; one row per assignment per date range

Common Gotchas

Ghost employees: Workers with no PER_PERIODS_OF_SERVICE row but an active assignment — possible if migrated via HDL with incomplete data. Add a JOIN to PER_PERIODS_OF_SERVICE with a null-check on ACTUAL_TERMINATION_DATE to validate.
Multiple assignments: Some workers have more than one active assignment (common in education and healthcare). Always filter PRIMARY_FLAG = 'Y' for headcount; omit it when auditing compensation by assignment.
Payroll variance false positives: Salary increases, back-pay, and QuickPay supplements all appear as variance. Add an element name filter or cross-reference PAY_ELEMENT_TYPES_F.CLASSIFICATION_NAME to isolate regular pay vs. one-time payments.
Costing with overrides: PAY_COSTS contains both default and override allocations. If a cost override was entered at the assignment or element level, both rows appear. Check DEBIT_OR_CREDIT and sum to net.

✅

Audit-ready tip: Run Query 1 (headcount) at three dates — prior quarter-end, current quarter-end, and today. If they diverge from HR system headcount reports, the delta reveals data quality issues before your external auditors find them.

Need More Oracle HCM SQL Patterns?

The HCM Query Cookbook includes 30 production SQL queries covering headcount, compensation, absence, turnover, payroll, and recruiting — with every EFFECTIVE_LATEST_CHANGE pattern and date-effective gotcha documented. Used by Oracle HCM consultants billing $150–$350/hr.

Get the HCM Query Cookbook ($197) → OTBI Template Pack ($97) →

Table of Contents