Oracle HCM SOX Audit SQL Queries

1. SOX Controls That Touch Oracle HCM

Sarbanes-Oxley Section 404 requires management to assess the effectiveness of internal controls over financial reporting (ICFR). For Oracle HCM, that means three categories of controls that external auditors will test every year:

IT General Controls (ITGC): Who has access, how that access is granted, and whether terminated employees still have system access.
Payroll Process Controls: Is payroll processed by authorized users only? Are pay run changes within expected variance thresholds?
Change Management Controls: Are changes to sensitive data (compensation, bank accounts, roles) authorized, logged, and reviewed?

Every query below is tagged to its SOX control category. The bind variable :as_of_date should be set to the quarter-end or fiscal year-end date under review.

⚠️

Key join gotcha: Oracle HCM uses date-effective tables. Without the correct BETWEEN effective_start_date AND effective_end_date filter, you will pull historical role assignments that were revoked years ago — and falsely flag them as active SoD conflicts.

2. Query 1: Segregation of Duties Role Conflict

The most critical SOX-HCM query. Returns users who hold two or more roles from a defined conflict matrix. Customize the role_pairs CTE with your organization's SoD ruleset. Common conflicts: payroll entry + payroll approval, HR data entry + user provisioning, compensation change + payroll release.

Query 1 — SOX SoD Role Conflict Detection

-- SOX ITGC: Segregation of Duties conflict report
-- Customize the role_pairs CTE with your SoD ruleset
WITH role_pairs AS (
    SELECT 'ORA_PAY_PAYROLL_ADMIN'    AS role_a, 'ORA_PAY_PAYROLL_MANAGER'     AS role_b, 'Payroll Entry + Approval'       AS conflict FROM dual
    UNION ALL
    SELECT 'ORA_HCM_HR_SPECIALIST',       'ORA_PER_MANAGE_USER_ACCOUNT', 'HR Data Entry + User Provisioning' FROM dual
    UNION ALL
    SELECT 'ORA_CMP_COMPENSATION_ANALYST', 'ORA_PAY_PAYROLL_MANAGER',     'Comp Change + Payroll Release'     FROM dual
)
SELECT
    fu.user_name,
    fu.email_address,
    ra_a.role_name     AS conflicting_role_a,
    ra_b.role_name     AS conflicting_role_b,
    rp.conflict        AS sod_violation,
    ra_a.start_date    AS role_a_granted,
    ra_b.start_date    AS role_b_granted
FROM
    role_pairs rp
    JOIN per_user_roles ra_a
         ON  ra_a.role_name   = rp.role_a
         AND :as_of_date     BETWEEN ra_a.start_date AND NVL(ra_a.end_date, DATE '9999-12-31')
    JOIN per_user_roles ra_b
         ON  ra_b.role_name   = rp.role_b
         AND ra_b.user_id     = ra_a.user_id
         AND :as_of_date     BETWEEN ra_b.start_date AND NVL(ra_b.end_date, DATE '9999-12-31')
    JOIN fnd_user fu
         ON  fu.user_id       = ra_a.user_id
ORDER BY
    rp.conflict,
    fu.user_name;

3. Query 2: Privileged Access Review

Returns all users with administrator-level or privileged roles as of the audit date. SOX requires this list to be reviewed and certified by a control owner each quarter. Run this against every quarter-end date and compare the delta.

Query 2 — SOX ITGC Privileged Access Review

-- SOX ITGC: Privileged access review as of audit date
-- Extend the role_name IN list with your org's privileged roles
SELECT
    fu.user_name,
    fu.email_address,
    pur.role_name,
    pur.start_date                                  AS access_granted_date,
    NVL(pur.end_date, DATE '9999-12-31')           AS access_end_date,
    papf.display_name                               AS employee_name,
    dept.name                                       AS department,
    mgr.display_name                                AS manager_name
FROM
    per_user_roles              pur
    JOIN fnd_user               fu    ON  fu.user_id          = pur.user_id
    LEFT JOIN per_all_people_f  papf  ON  papf.person_id      = fu.person_party_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.assignment_type         = 'E'
                                          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
    LEFT JOIN per_all_people_f  mgr   ON  mgr.person_id       = paam.manager_id
                                          AND :as_of_date      BETWEEN mgr.effective_start_date
                                                                         AND mgr.effective_end_date
WHERE
    pur.role_name IN (
        'ORA_FND_APPLICATION_ADMINISTRATOR_JOB',
        'ORA_PER_MANAGE_USER_ACCOUNT',
        'ORA_PAY_PAYROLL_ADMIN',
        'ORA_HCM_APPLICATION_ADMINISTRATOR_JOB'
    )
    AND :as_of_date BETWEEN pur.start_date AND NVL(pur.end_date, DATE '9999-12-31')
ORDER BY
    pur.role_name,
    fu.user_name;

4. Query 3: Role Assignment Change History

Returns every role added or removed in a date range. SOX change management controls require that all access grants be tied to an authorized request. Cross-reference this output against your access request ticketing system.

Query 3 — Role Assignment Change Log

-- SOX Change Management: role grants and revocations in a period
-- :start_date and :end_date bound the review window (typically a quarter)
SELECT
    fu.user_name,
    fu.email_address,
    pur.role_name,
    CASE
        WHEN pur.start_date BETWEEN :start_date AND :end_date THEN 'GRANTED'
        WHEN pur.end_date   BETWEEN :start_date AND :end_date THEN 'REVOKED'
    END                                             AS change_type,
    COALESCE(pur.start_date, pur.end_date)          AS change_date,
    papf.display_name                               AS employee_name
FROM
    per_user_roles              pur
    JOIN fnd_user               fu    ON  fu.user_id      = pur.user_id
    LEFT JOIN per_all_people_f  papf  ON  papf.person_id  = fu.person_party_id
                                          AND COALESCE(pur.start_date, pur.end_date)
                                              BETWEEN papf.effective_start_date
                                                  AND papf.effective_end_date
WHERE
    (
        pur.start_date BETWEEN :start_date AND :end_date
        OR
        pur.end_date   BETWEEN :start_date AND :end_date
    )
ORDER BY
    COALESCE(pur.start_date, pur.end_date) DESC,
    fu.user_name;

5. Query 4: Payroll Period Variance (SOX Control)

Compares total payroll run cost between two consecutive periods. SOX payroll controls require that payroll changes above a materiality threshold are reviewed and signed off. The threshold (:variance_pct_threshold) is typically 5% for most organizations, though some use dollar-amount floors instead.

Query 4 — SOX Payroll Variance: Period-over-Period

-- SOX Payroll Controls: variance between two payroll run periods
-- :prior_payroll_id = prior period's payroll action ID
-- :current_payroll_id = current period's payroll action ID
WITH prior_run AS (
    SELECT
        ppa.payroll_id,
        SUM(prr.result_value)   AS total_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
    WHERE
        ppa.payroll_action_id = :prior_payroll_id
        AND ppa.action_type   = 'R'                 -- R = payroll run
        AND prr.element_type_id IN (                 -- filter to earnings elements only
            SELECT element_type_id
            FROM   pay_element_types_f
            WHERE  classification_name IN ('Earnings', 'Supplemental Earnings')
            AND    :as_of_date BETWEEN effective_start_date AND effective_end_date
        )
    GROUP BY ppa.payroll_id
),
current_run AS (
    SELECT
        ppa.payroll_id,
        SUM(prr.result_value)   AS total_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
    WHERE
        ppa.payroll_action_id = :current_payroll_id
        AND ppa.action_type   = 'R'
        AND prr.element_type_id IN (
            SELECT element_type_id FROM pay_element_types_f
            WHERE  classification_name IN ('Earnings', 'Supplemental Earnings')
            AND    :as_of_date BETWEEN effective_start_date AND effective_end_date
        )
    GROUP BY ppa.payroll_id
)
SELECT
    p.payroll_id,
    p.total_pay                                     AS prior_period_pay,
    c.total_pay                                     AS current_period_pay,
    c.total_pay - p.total_pay                       AS variance_amount,
    ROUND((c.total_pay - p.total_pay) / NULLIF(p.total_pay, 0) * 100, 2) AS variance_pct,
    CASE
        WHEN ABS(ROUND((c.total_pay - p.total_pay) / NULLIF(p.total_pay,0) * 100, 2))
             > :variance_pct_threshold               THEN 'REVIEW REQUIRED'
        ELSE 'Within Threshold'
    END                                             AS sox_status
FROM
    prior_run p
    JOIN current_run c ON c.payroll_id = p.payroll_id;

6. Query 5: Terminated Employees with Active System Access

SOX ITGC requires that terminated employees have their system access revoked promptly — typically within 24 hours of the termination effective date. This query returns any terminated person whose FND_USER account is still active beyond the grace period.

Query 5 — Terminated Employees with Active User Accounts

-- SOX ITGC: Terminated employees who still have active system access
-- :days_grace = allowed days between term date and account suspension (usually 1–3)
SELECT
    papf.person_number,
    papf.display_name,
    ppos.actual_termination_date,
    fu.user_name,
    fu.end_date                                     AS account_suspended_date,
    NVL(fu.end_date, DATE '9999-12-31')           AS effective_end,
    ROUND(NVL(fu.end_date, SYSDATE) - ppos.actual_termination_date)
                                                    AS days_access_after_term
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 fnd_user               fu    ON  fu.person_party_id = papf.person_id
WHERE
    ppos.actual_termination_date IS NOT NULL
    AND ppos.actual_termination_date BETWEEN :start_date AND :end_date
    AND (
        fu.end_date IS NULL
        OR fu.end_date > ppos.actual_termination_date + :days_grace
    )
ORDER BY
    days_access_after_term DESC;

7. Query 6: Period-End Headcount Certification

Produces the headcount roll that control owners sign off on at each period end. Returns active headcount by legal employer and business unit as of the certification date, with hire and termination counts for the period — allowing reconciliation to prior-period numbers.

Query 6 — Period-End Headcount Certification

-- SOX headcount certification: active count + period hires/terms
-- :period_start and :period_end define the review period
-- :as_of_date = last day of period for active count (= :period_end)
SELECT
    le.name                                         AS legal_employer,
    bu.name                                         AS business_unit,
    COUNT(DISTINCT CASE
        WHEN paam.assignment_status_type = 'ACTIVE_ASSIGN'
         AND :as_of_date BETWEEN paam.effective_start_date AND paam.effective_end_date
        THEN paam.person_id
    END)                                            AS active_headcount,
    COUNT(DISTINCT CASE
        WHEN ppos.date_start BETWEEN :period_start AND :period_end
        THEN ppos.person_id
    END)                                            AS hires_in_period,
    COUNT(DISTINCT CASE
        WHEN ppos.actual_termination_date BETWEEN :period_start AND :period_end
        THEN ppos.person_id
    END)                                            AS terms_in_period
FROM
    per_all_assignments_m           paam
    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 bu
                                      ON  bu.organization_id = paam.business_unit_id
                                          AND :as_of_date     BETWEEN bu.effective_start_date
                                                                        AND bu.effective_end_date
    LEFT JOIN per_periods_of_service  ppos
                                      ON  ppos.person_id    = paam.person_id
WHERE
    paam.assignment_type         = 'E'
    AND paam.primary_flag        = 'Y'
    AND paam.effective_latest_change = 'Y'
GROUP BY
    le.name,
    bu.name
ORDER BY
    le.name,
    bu.name;

8. Query 7: Salary Change Authorization Audit

Returns all salary changes in a review period. For SOX, compensation changes above a materiality threshold must be approved by the employee's manager and HR. Use this output to verify that every change has an approved workflow history record in HRC_TXNS_F.

Query 7 — Salary Change Authorization Audit

-- SOX Change Management: salary changes in a review period
SELECT
    papf.person_number,
    papf.display_name,
    csa_new.annual_salary                           AS new_salary,
    csa_old.annual_salary                           AS prior_salary,
    csa_new.annual_salary - NVL(csa_old.annual_salary, 0)
                                                    AS change_amount,
    ROUND((csa_new.annual_salary - NVL(csa_old.annual_salary, 0))
          / NULLIF(csa_old.annual_salary, 0) * 100, 2)
                                                    AS change_pct,
    csa_new.effective_start_date                    AS change_effective_date,
    dept.name                                       AS department,
    mgr.display_name                                AS manager_name
FROM
    cmp_salary              csa_new
    JOIN per_all_people_f   papf    ON  papf.person_id      = csa_new.person_id
                                        AND csa_new.effective_start_date
                                            BETWEEN papf.effective_start_date
                                                AND papf.effective_end_date
    LEFT JOIN cmp_salary    csa_old ON  csa_old.person_id   = csa_new.person_id
                                        AND csa_old.effective_end_date
                                                = csa_new.effective_start_date - 1
    LEFT JOIN per_all_assignments_m paam
                                    ON  paam.person_id      = csa_new.person_id
                                        AND csa_new.effective_start_date
                                            BETWEEN paam.effective_start_date
                                                AND paam.effective_end_date
                                        AND paam.primary_flag            = 'Y'
                                        AND paam.assignment_type         = 'E'
                                        AND paam.effective_latest_change = 'Y'
    LEFT JOIN hr_all_organization_units_f dept
                                    ON  dept.organization_id = paam.organization_id
                                        AND csa_new.effective_start_date
                                            BETWEEN dept.effective_start_date
                                                AND dept.effective_end_date
    LEFT JOIN per_all_people_f mgr  ON  mgr.person_id       = paam.manager_id
                                        AND csa_new.effective_start_date
                                            BETWEEN mgr.effective_start_date
                                                AND mgr.effective_end_date
WHERE
    csa_new.effective_start_date BETWEEN :start_date AND :end_date
ORDER BY
    change_pct DESC;

9. Query 8: Bank Account Changes Audit

Bank account changes are among the highest-risk SOX-relevant HR actions — they are a common vector for payroll fraud. This query returns all bank account adds, changes, and deactivations in the audit period, with the user who made the change.

Query 8 — Bank Account Changes Audit

-- SOX Fraud Controls: bank account changes in a review period
SELECT
    papf.person_number,
    papf.display_name,
    pba.bank_account_name,
    pba.bank_account_number,
    pba.currency_code,
    pba.primary_flag,
    pba.effective_start_date                        AS change_date,
    pba.effective_end_date,
    CASE
        WHEN pba.effective_start_date BETWEEN :start_date AND :end_date
          AND pba.effective_start_date = (
                  SELECT MIN(b2.effective_start_date)
                  FROM   pay_personal_payment_methods_f b2
                  WHERE  b2.personal_payment_method_id = pba.personal_payment_method_id
              )                                     THEN 'NEW'
        WHEN pba.effective_end_date   BETWEEN :start_date AND :end_date
          AND pba.effective_end_date < DATE '9999-12-31'  THEN 'CLOSED'
        ELSE 'CHANGED'
    END                                             AS change_type
FROM
    pay_personal_payment_methods_f  pba
    JOIN per_all_people_f           papf  ON  papf.person_id = pba.payee_person_id
                                              AND pba.effective_start_date
                                                  BETWEEN papf.effective_start_date
                                                      AND papf.effective_end_date
WHERE
    (
        pba.effective_start_date BETWEEN :start_date AND :end_date
        OR
        pba.effective_end_date   BETWEEN :start_date AND :end_date
    )
ORDER BY
    pba.effective_start_date DESC,
    papf.display_name;

10. SOX Audit Best Practices

Bind every date variable. Never run SOX queries with SYSDATE — auditors need reproducibility. Log the exact :as_of_date value used for each evidence export.
Use EFFECTIVE_LATEST_CHANGE = 'Y' on _M tables. Without it you pull every historical row in a date range, inflating counts. The _M suffix tables are the recommended post-24B standard for assignments and work relationships.
Store query outputs in an evidence vault. SOX evidence must be retained for 7 years. Export results to an auditor-controlled share, not email attachments.
Run role queries at quarter-end dates. Run Query 1 (SoD) and Query 2 (Privileged Access) for each quarter-end — not just at year-end. Auditors test quarterly controls.
Cross-reference the role name to Oracle's role hierarchy. PER_USER_ROLES stores the directly assigned role. Inherited roles from parent roles won't appear here — use Oracle's Role Explorer in Security Console to see effective permissions.

Compliance SQL Pack — EEO-1, ACA, FLSA & SOX Queries

12 production-ready Oracle Fusion HCM SQL queries for EEO-1 headcount, ACA FTE calculation, FLSA overtime eligibility, and SOX audit trails — fully documented with inline comments and BI Publisher setup notes. One-time $499.

Get the Compliance SQL Pack ($499) → Search HCM Tables →

Need Help With Your SOX Audit Evidence?

Our Oracle HCM consultant network includes specialists in compliance reporting, SOX audit preparation, and Oracle Security Console configuration. Get expert help before your next audit cycle.

Find an Oracle HCM Consultant →

Table of Contents