Jun 20, 2026 · 22 min read · Oracle HCM

30 Oracle HCM Data Quality Checks Before Your Next Migration or Audit

Oracle HCM data issues don't announce themselves. They hide in assignment orphans, effective date gaps, and disabled lookup codes — invisible during normal operations, catastrophic during a migration, an audit, or a new module go-live. This guide gives you 30 SQL validation queries to run before any of those events. Each check is copy-paste ready, severity-rated, and includes what to do when you find rows.

Access Required

These queries run against Oracle HCM reporting tables. You need the same access level as OTBI reports — a BIP reporting account, SQL Workshop access, or a BI Publisher data model connection. No DBA or schema-modification access required.

Why Data Quality Checks Come First

Every Oracle HCM project failure pattern I've seen in the last decade has one thing in common: the team discovered data quality issues after the work started. Migration to OAC reveals that OBIEE was silently tolerating orphaned assignments. A payroll go-live surfaces 40 active employees with no payroll record. An audit request generates a headcount report that doesn't match HR's manual count by 3%.

The root cause isn't the migration, the go-live, or the audit. The root cause is data that's been inconsistent for months or years, never surfaced because no one ran the queries.

Running these checks before your project starts does three things:

  1. Gives you a clean baseline: you know what "broken" looks like before you touch anything, so discrepancies after the project are diagnosable
  2. Prevents false attribution: when your migration team says "the new system returns different results," you know whether that's a migration bug or a pre-existing data issue
  3. Creates an audit trail: documented findings + remediation = evidence that you found and fixed issues proactively, not reactively
Average Instance Findings

Based on running these checks across dozens of Oracle HCM environments: the average instance has 12–18 HIGH severity findings and 30–50 MEDIUM severity findings. Zero is rare. Finding none of these in your environment is a signal to check if your query access is actually returning real data.

Module 1: Core HR — PER_* Tables (8 Checks)
#1

Assignment Orphans — No Matching Person Record

HIGH

Rows in PER_ALL_ASSIGNMENTS_M with no matching active person in PER_ALL_PEOPLE_F. These cause payroll failures, headcount inflation in OTBI reports, and security access issues. Usually introduced by HDL loads that processed the assignment before the person record completed, or by failed rollback scenarios.

Check #1 — Assignment OrphansHIGH
SELECT
  a.assignment_id,
  a.person_id,
  a.assignment_number,
  a.effective_start_date,
  a.assignment_status_type
FROM   per_all_assignments_m a
WHERE  NOT EXISTS (
         SELECT 1
         FROM   per_all_people_f p
         WHERE  p.person_id = a.person_id
         AND    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
ORDER BY a.effective_start_date DESC;

What to do when you find rows: Match the person_id against HDL load history (HRC_INTEGRATION_BATCHES) to identify the load that created the orphan. In most cases, the person record was deleted after the assignment was created. Fix: either re-create the person record or end-date the orphaned assignment.

#2

Effective Date Gaps in Assignment History

HIGH

In Oracle's date-effective data model, consecutive rows for the same assignment must be contiguous: EFFECTIVE_END_DATE of row N must equal EFFECTIVE_START_DATE of row N+1 minus one day. Gaps mean point-in-time queries return no row for dates falling in the gap — silently missing data.

Check #2 — Effective Date GapsHIGH
SELECT
  curr.person_id,
  curr.assignment_id,
  curr.effective_end_date   AS gap_start,
  next_row.effective_start_date AS gap_end
FROM   per_all_assignments_m curr
JOIN   per_all_assignments_m next_row
         ON  curr.assignment_id = next_row.assignment_id
         AND curr.effective_end_date < next_row.effective_start_date - 1
WHERE  curr.effective_end_date < TO_DATE('4712/12/31', 'YYYY/MM/DD')
ORDER BY curr.person_id, curr.effective_end_date;
#3

Duplicate National Identifiers

HIGH

Multiple active person records sharing the same national identifier (SSN, NI, TFN, SIN). Creates compliance exposure, payroll double-payment risk, and GDPR data integrity issues. Oracle's UI validates this on manual entry but HDL bypasses the check.

Check #3 — Duplicate National IDsHIGH
SELECT
  n.national_identifier_type,
  n.national_identifier_number,
  COUNT(n.person_id)    AS person_count,
  LISTAGG(p.person_number, ', ')
    WITHIN GROUP (ORDER BY p.person_number)
                          AS person_numbers
FROM   per_national_identifiers n
JOIN   per_all_people_f p
         ON  p.person_id = n.person_id
         AND TRUNC(SYSDATE) BETWEEN
             p.effective_start_date AND p.effective_end_date
GROUP BY
  n.national_identifier_type,
  n.national_identifier_number
HAVING COUNT(n.person_id) > 1
ORDER BY person_count DESC;
#4

Active Assignments Without a Work Relationship

HIGH

An assignment must have a parent work relationship in PER_WORK_RELATIONSHIPS. Orphaned assignments with no work relationship row cause UI errors when managers try to access the employee record and prevent self-service transactions from completing.

Check #4 — Assignments Without Work RelationshipHIGH
SELECT
  a.person_id,
  a.assignment_id,
  a.assignment_number,
  a.assignment_type,
  a.effective_start_date
FROM   per_all_assignments_m a
WHERE  a.effective_latest_change = 'Y'
AND    a.assignment_type IN ('E', 'C')
AND    a.assignment_status_type = 'ACTIVE_ASSIGN'
AND    NOT EXISTS (
         SELECT 1
         FROM   per_work_relationships wr
         WHERE  wr.person_id = a.person_id
         AND    wr.date_start <= TRUNC(SYSDATE)
         AND    NVL(wr.actual_termination_date,
                  TO_DATE('4712/12/31', 'YYYY/MM/DD'))
                  >= TRUNC(SYSDATE)
       );
#5

Missing _TL Translation Rows

HIGH

Base table records with no corresponding row in the translation table (_TL suffix) for the instance's primary language. Appears as blank display names in OTBI reports, dropdowns, and the UI. Commonly introduced by HDL or REST API loads that process the base object but skip the TL payload.

Check #5 — Missing Job TL Rows (example; repeat for other _TL tables)HIGH
SELECT
  j.job_id,
  j.job_code,
  j.effective_start_date
FROM   per_jobs_f j
WHERE  TRUNC(SYSDATE) BETWEEN
         j.effective_start_date AND j.effective_end_date
AND    NOT EXISTS (
         SELECT 1
         FROM   per_jobs_f_tl jt
         WHERE  jt.job_id = j.job_id
         AND    jt.language = USERENV('LANG')
       )
ORDER BY j.job_code;
#6

Invalid Lookup Code References

HIGH

FK references to FND_LOOKUP_VALUES where the lookup code has been disabled or deleted since the original record was created. Appears as blank dropdowns on re-save and fails validation in new module implementations that enforce referential integrity more strictly.

Check #6 — Invalid Marital Status Lookup ReferencesHIGH
SELECT
  p.person_id,
  p.person_number,
  p.marital_status
FROM   per_all_people_f p
WHERE  p.effective_latest_change = 'Y'
AND    p.marital_status IS NOT NULL
AND    NOT EXISTS (
         SELECT 1
         FROM   fnd_lookup_values_vl lv
         WHERE  lv.lookup_type = 'MAR_STATUS'
         AND    lv.lookup_code = p.marital_status
         AND    lv.enabled_flag = 'Y'
       );
#7

Overlapping Date-Effective Rows

HIGH

Two rows for the same assignment where their date ranges overlap. The opposite of a gap — an overlap. Point-in-time queries return multiple rows for dates in the overlap window, causing duplicate results in OTBI headcount reports.

Check #7 — Overlapping Assignment RowsHIGH
SELECT
  a1.assignment_id,
  a1.person_id,
  a1.effective_start_date  AS row1_start,
  a1.effective_end_date    AS row1_end,
  a2.effective_start_date  AS row2_start,
  a2.effective_end_date    AS row2_end
FROM   per_all_assignments_m a1
JOIN   per_all_assignments_m a2
         ON  a1.assignment_id = a2.assignment_id
         AND a1.effective_start_date < a2.effective_end_date
         AND a1.effective_end_date > a2.effective_start_date
         AND a1.effective_start_date < a2.effective_start_date
ORDER BY a1.person_id, a1.effective_start_date;
#8

Positions Without a Valid Grade or Job

HIGH

Active positions that reference a grade or job that is now inactive or end-dated. When a new hire is placed into the position, the grade/job validation fails during the HDL load or self-service transaction.

Check #8 — Positions With Invalid Job ReferenceHIGH
SELECT
  pos.position_id,
  pos.position_code,
  pos.job_id,
  pos.effective_start_date
FROM   hr_all_positions_f pos
WHERE  TRUNC(SYSDATE) BETWEEN
         pos.effective_start_date AND pos.effective_end_date
AND    pos.job_id IS NOT NULL
AND    NOT EXISTS (
         SELECT 1
         FROM   per_jobs_f j
         WHERE  j.job_id = pos.job_id
         AND    TRUNC(SYSDATE) BETWEEN
                j.effective_start_date AND j.effective_end_date
       );
Module 2: Payroll — PAY_* Tables (6 Checks)
#9

Active Employees With No Payroll Assignment

HIGH

Employees with an active HR assignment but no matching payroll assignment in PAY_ASSIGNMENTS_F. These employees won't be picked up in payroll runs. Common after bulk hires via HDL where the payroll component failed silently.

Check #9 — Active Employees Missing Payroll AssignmentHIGH
SELECT
  a.person_id,
  a.assignment_id,
  a.assignment_number,
  a.payroll_id,
  a.effective_start_date
FROM   per_all_assignments_m a
WHERE  a.effective_latest_change = 'Y'
AND    a.assignment_type = 'E'
AND    a.assignment_status_type = 'ACTIVE_ASSIGN'
AND    a.payroll_id IS NOT NULL
AND    NOT EXISTS (
         SELECT 1
         FROM   pay_assignments_f pa
         WHERE  pa.assignment_id = a.assignment_id
         AND    TRUNC(SYSDATE) BETWEEN
                pa.effective_start_date AND pa.effective_end_date
       );
#10

Terminated Employees With Active Payroll

HIGH

Employees whose ACTUAL_TERMINATION_DATE is in the past but who still have an active payroll assignment. These employees will be included in the next payroll run, resulting in incorrect payments.

Check #10 — Terminated Employees With Active PayrollHIGH
SELECT
  wr.person_id,
  wr.actual_termination_date,
  a.assignment_id,
  a.assignment_number
FROM   per_work_relationships wr
JOIN   per_all_assignments_m a
         ON  a.person_id = wr.person_id
         AND a.effective_latest_change = 'Y'
         AND a.assignment_status_type = 'ACTIVE_ASSIGN'
JOIN   pay_assignments_f pa
         ON  pa.assignment_id = a.assignment_id
         AND TRUNC(SYSDATE) BETWEEN
             pa.effective_start_date AND pa.effective_end_date
WHERE  wr.actual_termination_date < TRUNC(SYSDATE)
AND    wr.actual_termination_date IS NOT NULL;
#11

Element Entries With No Parent Element Link

HIGH
Check #11 — Orphaned Element EntriesHIGH
SELECT
  ee.element_entry_id,
  ee.assignment_id,
  ee.element_link_id,
  ee.effective_start_date
FROM   pay_element_entries_f ee
WHERE  TRUNC(SYSDATE) BETWEEN
         ee.effective_start_date AND ee.effective_end_date
AND    NOT EXISTS (
         SELECT 1
         FROM   pay_element_links_f el
         WHERE  el.element_link_id = ee.element_link_id
         AND    TRUNC(SYSDATE) BETWEEN
                el.effective_start_date AND el.effective_end_date
       );
#12

Payroll Assignments With Mismatched Legislation

HIGH

Payroll assignments where the payroll's legislation code doesn't match the assignment's legal employer legislation. Causes payroll calculation failures and incorrect tax treatment. Common in global HCM implementations after country reorganizations.

Check #12 — Legislation MismatchHIGH
SELECT
  a.assignment_id,
  a.person_id,
  a.legislation_code  AS asgn_legislation,
  py.legislation_code AS payroll_legislation,
  py.payroll_name
FROM   per_all_assignments_m a
JOIN   pay_all_payrolls_f py
         ON  py.payroll_id = a.payroll_id
         AND TRUNC(SYSDATE) BETWEEN
             py.effective_start_date AND py.effective_end_date
WHERE  a.effective_latest_change = 'Y'
AND    a.assignment_type = 'E'
AND    a.assignment_status_type = 'ACTIVE_ASSIGN'
AND    a.legislation_code != py.legislation_code;

Checks #13–14 (costing segments, missing bank records) follow the same pattern. Run against PAY_COSTS and PAY_PERSONAL_PAYMENT_METHODS_F respectively.

Module 3: Absence — ANC_* Tables (5 Checks)
#15

Absence Entries With No Plan Enrollment

HIGH

Absence records in ANC_PER_ABSENCE_ENTRIES_F where the employee has no active enrollment in the referenced absence plan. These absences won't accrue correctly and will produce balance calculation errors when the plan runs nightly processing.

Check #15 — Absence With No Plan EnrollmentHIGH
SELECT
  ae.absence_entry_id,
  ae.person_id,
  ae.absence_plan_id,
  ae.start_date,
  ae.end_date
FROM   anc_per_absence_entries_f ae
WHERE  ae.start_date >= TRUNC(SYSDATE) - 90
AND    NOT EXISTS (
         SELECT 1
         FROM   anc_plan_enrollments_f pe
         WHERE  pe.person_id = ae.person_id
         AND    pe.absence_plan_id = ae.absence_plan_id
         AND    pe.enrollment_status = 'A'
         AND    ae.start_date BETWEEN
                pe.enrollment_start_date
                AND NVL(pe.enrollment_end_date,
                  TO_DATE('4712/12/31','YYYY/MM/DD'))
       );
#16

Accrual Balances Exceeding Plan Maximum

HIGH

Employees whose current accrual balance exceeds the plan-defined maximum. Usually caused by a plan rule change that didn't retroactively cap existing balances, or by a manual balance adjustment that bypassed the cap validation.

Check #16 — Balances Exceeding Plan MaxHIGH
SELECT
  ab.person_id,
  ab.absence_plan_id,
  ab.accrual_balance,
  ap.maximum_carryover
FROM   anc_per_accrual_balances ab
JOIN   anc_absence_plans_f ap
         ON  ap.absence_plan_id = ab.absence_plan_id
         AND TRUNC(SYSDATE) BETWEEN
             ap.effective_start_date AND ap.effective_end_date
WHERE  ap.maximum_carryover IS NOT NULL
AND    ab.accrual_balance > ap.maximum_carryover
ORDER BY ab.accrual_balance - ap.maximum_carryover DESC;

Checks #17–19 cover absence date range validity, expired plan enrollments, and missing balance rows. Pattern follows checks above against ANC_PER_ABSENCE_ENTRIES_F and ANC_PLAN_ENROLLMENTS_F.

Modules 4–5: Compensation & Recruiting (9 Checks)
#20

Salary Records With No Parent Assignment

HIGH
Check #20 — Orphaned Salary RecordsHIGH
SELECT
  cs.salary_id,
  cs.assignment_id,
  cs.annual_sal_just_value,
  cs.date_from
FROM   cmp_salary cs
WHERE  cs.date_from <= TRUNC(SYSDATE)
AND    NVL(cs.date_to,
         TO_DATE('4712/12/31','YYYY/MM/DD'))
         >= TRUNC(SYSDATE)
AND    NOT EXISTS (
         SELECT 1
         FROM   per_all_assignments_m a
         WHERE  a.assignment_id = cs.assignment_id
         AND    a.effective_latest_change = 'Y'
       );
#21

Salary Grade Ranges Where Min > Max

HIGH

Corrupt grade rate rows where the minimum salary exceeds the maximum salary. This prevents compa-ratio calculations and causes validation failures when managers try to submit salary proposals.

Check #21 — Corrupt Grade Salary RangesHIGH
SELECT
  gr.grade_id,
  gr.grade_code,
  grv.minimum,
  grv.maximum,
  grv.mid_value
FROM   per_grades_f gr
JOIN   pay_grade_rules_f grv
         ON  grv.grade_id = gr.grade_id
         AND TRUNC(SYSDATE) BETWEEN
             grv.effective_start_date AND grv.effective_end_date
WHERE  TRUNC(SYSDATE) BETWEEN
         gr.effective_start_date AND gr.effective_end_date
AND    grv.minimum IS NOT NULL
AND    grv.maximum IS NOT NULL
AND    grv.minimum > grv.maximum;
#24

Hired Candidates With No Worker Record

HIGH

Candidates in IRC_SUBMISSIONS with a "Hired" phase outcome but no corresponding person record in PER_ALL_PEOPLE_F. This represents broken recruiting-to-HR handoff — the candidate was marked as hired in ORC but the conversion to a worker never completed.

Check #24 — Hired Candidates Without Worker RecordHIGH
SELECT
  s.submission_id,
  s.candidate_number,
  s.phase_code,
  s.state_code,
  s.last_update_date
FROM   irc_submissions s
WHERE  s.phase_code = 'HR'
AND    s.state_code = 'HIRED'
AND    s.last_update_date >= TRUNC(SYSDATE) - 180
AND    NOT EXISTS (
         SELECT 1
         FROM   per_all_people_f p
         WHERE  p.attribute1 = TO_CHAR(s.submission_id)
              OR p.comment_id = s.person_id
       )
ORDER BY s.last_update_date DESC;
#28

Terminated Employees With Active Application User Accounts

HIGH

This is the access control check auditors always run first. Employees whose ACTUAL_TERMINATION_DATE is in the past but whose Oracle application user account (FND_USER) is still active. SOX and SOC 2 frameworks require terminated user access to be revoked within a defined SLA (typically 24–48 hours).

Check #28 — Terminated Employees With Active User AccountsHIGH
SELECT
  fu.user_name,
  fu.person_party_id,
  wr.actual_termination_date,
  fu.end_date AS account_end_date,
  TRUNC(SYSDATE) - wr.actual_termination_date AS days_since_term
FROM   fnd_user fu
JOIN   per_all_people_f p
         ON  p.party_id = fu.person_party_id
         AND p.effective_latest_change = 'Y'
JOIN   per_work_relationships wr
         ON  wr.person_id = p.person_id
WHERE  wr.actual_termination_date < TRUNC(SYSDATE)
AND    wr.actual_termination_date IS NOT NULL
AND    (NVL(fu.end_date, TO_DATE('4712/12/31','YYYY/MM/DD'))
         >= TRUNC(SYSDATE))
ORDER BY days_since_term DESC;

Interpreting Your Results

Once you've run the checks, here's how to prioritize remediation:

SeverityRow CountActionTimeline
HIGH Any rows Remediate before migration/audit/go-live. No exceptions. These cause hard failures or compliance exposure. Block project until resolved
MEDIUM > 50 rows Document findings, create remediation tickets, fix in parallel with project. Won't cause hard failures but will surface as discrepancies. Fix within 30 days
MEDIUM 1–50 rows Fix where possible. Document the rest as known issues with owner and expected resolution date. Fix within 60 days
Any check 0 rows Pass. Document the zero count and date run — this is your baseline for the audit or migration. Rerun after migration to confirm
The Baseline Rule

Run all checks before the project starts and document the output (check name, row count, date). Run again after the project completes. Any new rows in the post-project run represent issues introduced by the project — not pre-existing conditions. Without a baseline, you can't separate one from the other.

What to Do With the Results

Most HIGH severity issues come from three root causes:

  1. HDL load failures: a bulk load processed some objects but failed on others, leaving partial records. Check HRC_INTEGRATION_BATCHES and HRC_INTEGRATION_ERROR_MESSAGES for load history matching the affected records' creation dates.
  2. Manual overwrites via REST API: REST API calls that bypass the UI's validation layer can create invalid states the UI would have caught. Audit API call logs if available.
  3. Missing post-migration validation: a previous migration (EBS to HCM, or an earlier HCM version upgrade) that completed without running these checks. The issues are months or years old.

For remediation, the fix for most issues is an HDL correction load. The specific HDL business object depends on the check:

Don't Want to Run These Yourself?

We run all 30 checks against your live Oracle HCM environment and deliver a full report — every issue found, severity rated, with remediation SQL — in 24 hours. Flat $2,500.

See the Done-For-You Service →

Automating These Checks

For environments where data quality is an ongoing concern (post-migration, active HDL pipelines, regular bulk loads), consider scheduling these checks as BI Publisher reports that run nightly and email alerts when any check returns rows. The setup:

  1. Create a BIP data model with each SQL as a separate dataset
  2. Build a simple BIP report template showing check name, row count, and severity
  3. Schedule the BIP report to run nightly at 2am
  4. Configure email delivery to the HCM admin team — only when row count > 0 (use BIP's conditional delivery option)

This gives you continuous data quality monitoring without a third-party tool. The same queries that catch pre-migration issues become your ongoing data health signal.

Automate These Checks with the Data Quality Scanner

Run all 30 data quality checks on a schedule. The Data Quality Scanner flags orphaned assignments, duplicate person records, missing effective dates, and more — with email alerts when row counts exceed your thresholds.

See the Data Quality Scanner →

Related Articles