HCM-01: Employee Record Diagnostic

Oracle EBS HR employee record integrity failures cascade across every Oracle module that consumes HR data — Payroll, Fixed Assets, Purchasing, Accounts Payable, and Projects all reference PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F for employee identity, cost center, supervisor, and location. When an employee record has a gap in its date-effective history — a period where no valid assignment row exists — the downstream modules see the employee as inactive for that period, producing payroll processing failures, AP expense report holds, and PO approval routing errors.

Date-effective record management is the defining complexity of Oracle EBS HR. Every change to an employee record — a promotion, a transfer, a salary change, a supervisor change — creates a new date-effective row rather than updating the existing one. The resulting history is a series of rows with START_DATE and END_DATE pairs that must be contiguous and non-overlapping. When a correction is made incorrectly — for example, when an assignment is corrected by end-dating the wrong row — gaps appear in the history that cause the employee to appear terminated or inactive for specific periods.

The most common gap scenario is the supervisor assignment termination cascade. When a manager leaves the organization and their Oracle account is end-dated, Oracle HR sometimes propagates the end-date to the subordinate assignment records as part of the termination process — specifically to the SUPERVISOR_ID field. If the subordinate's assignment is end-dated incorrectly as part of this process, the subordinate appears inactive to all downstream modules including PO approval routing and AP expense report validation.

HCM-01 runs a complete employee record diagnostic — date-effective history gap detection in PER_ALL_ASSIGNMENTS_F, assignment status validation across the full history, supervisor chain completeness up to the top of hierarchy, cost center and GL account assignment validity for Payroll and Projects cost allocation, FND_USER account linkage, and cross-module impact assessment showing exactly which downstream transactions are affected by each gap or error.

Date-Effective History

Full PER_ALL_ASSIGNMENTS_F history scan — gap detection, overlap detection, status validation across every row. Identifies the exact date range of any gap and the rows on either side of it.

Supervisor Chain

Supervisor chain completeness from the employee up to the top of the organization hierarchy. Active status check for every supervisor in the chain. Flags chains that will fail PO approval routing or expense report validation.

Cross-Module Impact

Downstream transaction impact assessment — AP expense reports on hold, PO approvals stalled, Payroll assignment errors, Projects cost allocation gaps. Identifies every transaction affected by the assignment history issue.

Account & Identity

Cost center CCID validity. FND_USER linkage and account active status. Location assignment. Business group and operating unit assignment. All the fields consumed by downstream modules.

════════════════════════════════════════════════════════════
  ORACLE EBS R12 — EMPLOYEE RECORD DIAGNOSTIC
════════════════════════════════════════════════════════════
  Employee Number    : EMP-10482
  Name               : J. Martinez
  Assignment ID      : 12841
  Case Number        : HCM-481022
  Report Date        : 20-FEB-2026 09:15:44
════════════════════════════════════════════════════════════

[ SECTION 1 — DATE-EFFECTIVE HISTORY ]      STATUS: ✗ GAP DETECTED
────────────────────────────────────────────────────────────
  Row 1              : 01-JAN-2024 to 14-FEB-2026 — ACTIVE ✓
  Gap                : 15-FEB-2026 to 18-FEB-2026 — NO ASSIGNMENT ROW ✗
  Row 2              : 19-FEB-2026 to 31-DEC-4712 — ACTIVE ✓
  ✗ FAIL: 4-day gap — employee appears inactive 15-18 FEB

[ SECTION 2 — SUPERVISOR CHAIN ]            STATUS: ✓ PASS
────────────────────────────────────────────────────────────
  J.Martinez → P.Chen → C.Williams → (top) ✓
  All supervisors active ✓

[ SECTION 3 — COST CENTER ]                 STATUS: ✓ PASS
────────────────────────────────────────────────────────────
  Cost center 500 — IT Operations — active CCID ✓

[ SECTION 4 — FND_USER LINKAGE ]            STATUS: ✓ PASS
────────────────────────────────────────────────────────────
  FND_USER JMARTINEZ linked — account active ✓

[ SECTION 5 — DOWNSTREAM IMPACT ]           STATUS: ✗ IMPACTED
────────────────────────────────────────────────────────────
  ✗ AP Expense Report ER-2026-00481 on hold — employee inactive 16-FEB
  ✗ PO-2026-004520 approval stalled — requester shown inactive
  Payroll             : FEB payroll run not yet processed — no impact ✓

════════════════════════════════════════════════════════════
  DIAGNOSTIC SUMMARY
════════════════════════════════════════════════════════════
  4-day assignment gap — 2 downstream transactions impacted
  FIX: Close gap in PER_ALL_ASSIGNMENTS_F — update END_DATE on Row 1
════════════════════════════════════════════════════════════
  Backup Created  : CONS_BACKUP.PER_ALL_ASSIGNMENTS_F_481022 ✓
  Registry ID     : 7041
════════════════════════════════════════════════════════════

Diagnostic Engine

Runs 30+ checks across date-effective history gap detection in PER_ALL_ASSIGNMENTS_F, assignment status validation, supervisor chain completeness, cost center and GL account validity, FND_USER account linkage, and cross-module downstream impact across AP, PO, Payroll, and Projects.

Backup Created

Before any HR record is modified, CONS_BACKUP.PER_ALL_PEOPLE_F_<case#> and PER_ALL_ASSIGNMENTS_F_<case#> are created and row counts verified. Date-effective corrections are always made to the END_DATE of the preceding row — never by inserting new rows without full history review.

Guided Data Fix

Date-effective gap closures are the primary direct-fix target — correcting END_DATE on the row preceding the gap. Supervisor assignment corrections, cost center updates, and FND_USER linkage fixes are applied with full backup. All corrections validated against the full assignment history before execution.

KB Article Generated

Complete KB article generated — employee number, gap dates, downstream transactions impacted, fix applied, downstream holds released. Upload directly to your knowledge base.

KB-HCM-481022-001 · Script: HCM-01

Employee EMP-10482 Assignment Gap 15-18 FEB — AP Hold and PO Approval Stall

Module: HR / Payroll
Platform: EBS R12.2.10
Status: RESOLVED

Symptom

J.Martinez (EMP-10482) assignment history has a 4-day gap (15-18 FEB 2026) — no valid PER_ALL_ASSIGNMENTS_F row exists. AP Expense Report ER-2026-00481 on hold (employee shown inactive). PO-2026-004520 approval stalled (requester inactive).

Root Cause

Manager R.Davidson's termination on 15-FEB-2026 triggered an incorrect end-date propagation to J.Martinez's assignment row. The HR system end-dated J.Martinez's assignment as 14-FEB-2026 as part of the supervisor termination process. A new assignment row was created 19-FEB-2026 when the error was reported, leaving a 4-day gap.

Tables Affected

PER_ALL_ASSIGNMENTS_F — EFFECTIVE_END_DATE = 14-FEB-2026 (Row 1, Assignment 12841)
PER_ALL_ASSIGNMENTS_F — EFFECTIVE_START_DATE = 19-FEB-2026 (Row 2, Assignment 12841)

Fix Applied

PER_ALL_ASSIGNMENTS_F Row 1 END_DATE corrected from 14-FEB-2026 to 18-FEB-2026 with full backup — closing the 4-day gap. AP Expense Report ER-2026-00481 hold released on revalidation. PO-2026-004520 approval resubmitted and routed correctly to P.Chen.

Prevention

Manager termination process updated: HR must confirm no subordinate assignment gaps before FND_USER end-date is applied. HCM-01 gap detection should run as part of the monthly HR data quality check.

Date-Effective Assignment Gap

No valid assignment row for a date range — employee appears inactive or terminated to all downstream modules for those dates. Affects AP expense reports, PO approvals, payroll, and project cost allocation simultaneously.

Cascade

Supervisor Termination Cascade

Manager termination incorrectly propagates an end-date to subordinate assignment rows. Most common cause of unexpected assignment gaps. HCM-01 detects the pattern and identifies all affected subordinates.

Status

Assignment Terminated in Error

Assignment status set to TERMINATED incorrectly — may have triggered final payroll, benefits termination, or Oracle account deactivation that must also be reversed. HCM-01 assesses all downstream termination actions before correcting.

Account

Invalid Cost Center Assignment

Employee's cost center CCID disabled or end-dated — payroll cost allocation and project charging will fail for the employee. HCM-01 identifies the inactive CCID and the active replacement.

Table	Module	Purpose
PER_ALL_PEOPLE_F	HR	Employee person record — date-effective identity
PER_ALL_ASSIGNMENTS_F	HR	Assignment history — status, supervisor, cost center, OU
PER_ASSIGNMENT_STATUS_TYPES	HR	Assignment status codes — ACTIVE, TERMINATED, SUSPENDED
HR_LOCATIONS_ALL	HR	Location codes — active status
FND_USER	FND	Oracle user account — linkage to employee record
GL_CODE_COMBINATIONS	GL	Cost center CCID — active status
AP_EXPENSE_REPORT_HEADERS_ALL	AP	Expense reports — hold status for impacted employee
PO_HEADERS_ALL	PO	POs stalled due to requester assignment gap

Decision Framework

How Every Fix Decision Is Made

Before any data is modified in a production Oracle database, AP-01 walks through a four-stage decision process. Every condition identified by the diagnostic maps to exactly one resolution path.

First Option — Always

Can the front end fix this?

Oracle's own forms and concurrent programs are always the first option. If the condition can be corrected through Oracle's standard UI — a form, a concurrent program, a setup screen — that path is taken first. No consultant SQL required, no database risk, and the fix is fully supported by Oracle. The diagnostic identifies these conditions explicitly and states the exact front-end navigation path.

✓ Functional Fix

When Front End Is Not Available

Is a direct data fix safe to apply?

When the front-end path is unavailable or would require an unacceptable volume of manual steps, a direct fix is evaluated against explicit criteria. All of the following must be true before proceeding:

→The fix mirrors exactly what Oracle's own code would have done if the underlying condition were corrected

→All dependent tables have been identified and will be updated in the same transaction

→The fix is fully reversible — a single INSERT from the backup table restores the original state

→The environment patch level has been confirmed against the fix logic version

⚡ Direct Fix

Hard Stops — No Exceptions

Does this require Oracle Support?

Certain table areas are never touched directly, regardless of how well the underlying structure is understood. The diagnostic flags these conditions and generates the Service Request documentation:

✗XLA subledger tables — XLA_EVENTS, XLA_AE_HEADERS — incorrect changes corrupt the subledger audit trail in ways undetectable until period close fails or an auditor requests a reconciliation

✗Workflow engine tables — WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES — ad-hoc DML can corrupt the workflow engine state instance-wide

✗Recurring conditions after a fix — indicates a code defect, not a data error. Documented and submitted as a Service Request with a reproducible test case

⚠ Oracle Support

For All Approved Direct Fixes

Backup → Execute → Verify → Document

A timestamped backup table is created and row-count verified before the first UPDATE fires. Explicit parameter confirmation is required — the script will not self-execute. After execution, a verification query confirms the expected state. A complete change record — rows affected, before and after values, database username, timestamp — is written to the FIX_BACKUP_REGISTRY and becomes the primary artifact in the knowledge base entry for this incident.

📋 Documented

Condition Identified	Resolution Path	Notes
Date-effective gap in assignment history	Direct Fix	HCM-01 corrects EFFECTIVE_END_DATE on the preceding assignment row to close the gap with full backup. Pre-flight confirms the gap is not intentional (e.g., not a leave of absence period) before closing.
Supervisor assignment incorrect or missing	Direct Fix	HCM-01 updates SUPERVISOR_ID in PER_ALL_ASSIGNMENTS_F with full backup. The correct supervisor is confirmed against the organization chart before the update is applied. Date-effective row selection is validated.
Assignment status TERMINATED in error	Direct Fix	HCM-01 corrects ASSIGNMENT_STATUS_TYPE_ID to the correct active status with full backup. Pre-flight confirms no downstream termination actions (final payroll, benefits end-date) have been triggered based on the incorrect status.
Cost center invalid — CCID disabled or end-dated	Direct Fix	HCM-01 updates the GL cost center in PER_ALL_ASSIGNMENTS_F with full backup. The replacement CCID is confirmed active in GL_CODE_COMBINATIONS before the update.
FND_USER not linked to employee	Functional First	Link the Oracle user account to the employee via System Administration > Users. HCM-01 identifies the employee number and the FND_USER record that should be linked.
Overlapping date-effective rows	Direct Fix	HCM-01 identifies the overlapping rows and corrects END_DATE on the earlier row with full backup. The overlap is confirmed against the full assignment history before correction.
Employee appears in multiple OUs — incorrect OU assignment	Direct Fix	HCM-01 identifies the incorrect OU assignment and corrects BUSINESS_GROUP_ID in PER_ALL_ASSIGNMENTS_F with full backup after confirming the correct OU.
Location assignment invalid — location end-dated	Direct Fix	HCM-01 updates LOCATION_ID in PER_ALL_ASSIGNMENTS_F with full backup. The replacement location is confirmed active in HR_LOCATIONS_ALL.

Safeguards

Nothing Executes Without a Safety Net

Before any data fix runs, the script verifies pre-flight conditions and creates a complete verified backup. If any check fails, the script aborts. There is no partial execution path.

Pre-Flight Checks — All Must Pass

✓

POSTED_FLAG = 'N'

Will not modify a posted transaction under any circumstances

✓

ACCOUNTING_EVENT_ID IS NULL

Will not modify rows with an active or pending XLA accounting event

✓

No active database lock

Checks V$LOCK — will not proceed if another session holds a lock on target rows

✓

CONS_BACKUP schema writable

Backup schema must be accessible before any DML is permitted

✓

Explicit confirmation required

Script outputs a confirmation prompt — DML does not execute until consultant enters the case number as the execution parameter

Backup Methodology

Before the first UPDATE fires, the script creates a complete copy of every row to be modified. Tables are named CONS_BACKUP.<TABLE>_<CASE#> and persist permanently after execution.

PER_ALL_PEOPLE_F

PER_ALL_ASSIGNMENTS_F

PER_ASSIGNMENT_STATUS_TYPES

Row count verified after backup creation. If backup fails for any reason the script aborts without executing any DML. Rollback is a single INSERT from the backup table. No reconstruction required.

HCM-01 — Pre-Flight & Backup Verification

════════════════════════════════════════════════════════════
  PRE-FLIGHT SAFETY CHECK
════════════════════════════════════════════════════════════
  Employee Number    : EMP-10482
  Assignment ID      : 12841
  Gap                : 15-FEB-2026 to 18-FEB-2026 (4 days)
  Payroll Processed  : FEB payroll not yet run — safe to correct ✓
  Termination Actions: None triggered — status was not TERMINATED ✓
  CONS_BACKUP Schema : Accessible ✓
────────────────────────────────────────────────────────────
  ALL PRE-FLIGHT CHECKS PASSED
════════════════════════════════════════════════════════════
  Creating : CONS_BACKUP.PER_ALL_ASSIGNMENTS_F_481022
  Rows     : 2 rows backed up ✓
  Registry : FIX_BACKUP_REGISTRY — ID 7041 created ✓
────────────────────────────────────────────────────────────
  BACKUP COMPLETE
════════════════════════════════════════════════════════════
  Enter case number to confirm : 481022
  Confirmed. Closing assignment gap...
════════════════════════════════════════════════════════════

Knowledge Base

Every Execution Produces a Record

The knowledge base article is generated automatically from the script's execution output. No manual documentation required. It becomes the institutional record — for the team, for auditors, and for every future engagement in the same environment.

Zero Manual Effort

Every field — environment, tables, before/after values, backup reference, root cause, prevention — is generated from actual execution output. Nothing is written by hand.

Patterns Surface Over Time

The first engagement produces findings. The third produces patterns. Recurring conditions that are invisible as individual incidents become obvious as knowledge base trends.

Survives Staff Turnover

The knowledge base is an institutional record of the Oracle environment. A new manager, a new DBA, or an external auditor can read exactly what happened, what was done, and what prevents recurrence.

KB-HCM-481022-001

Employee EMP-10482 Assignment Gap 15-18 FEB — AP Hold and PO Approval Stall

EBS R12.2.10 · HR / Payroll

● RESOLVED

Symptom

Root Cause

Tables

PER_ALL_ASSIGNMENTS_F — EFFECTIVE_END_DATE = 14-FEB-2026 (Row 1, Assignment 12841)
PER_ALL_ASSIGNMENTS_F — EFFECTIVE_START_DATE = 19-FEB-2026 (Row 2, Assignment 12841)

Fix Applied

Prevention

Manager termination process updated: HR must confirm no subordinate assignment gaps before FND_USER end-date is applied. HCM-01 gap detection should run as part of the monthly HR data quality check.

R12 Guide (PDF)	Title & Chapter	Detail
120funmo.pdf	Oracle Applications Multiple Organizations Implementation Guide — Business Groups	pp. 2-1 to 2-10: Business group and HR organization structure for employee records
120apug.pdf	Oracle Payables User's Guide — Ch. 5: Expense Reports	Ch. 5: Employee expense report integration with AP and HR employee data
120funmo.pdf	Oracle Applications Multiple Organizations Implementation Guide	HR: Business Group profile and responsibility assignment for employee data access

Employee Record Diagnostic

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in HCM-01

Backup & Rollback for HCM-01

Tables Backed Up — HCM-01

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

What This Script Finds

Date-Effective Assignment Gap

Supervisor Termination Cascade

Assignment Terminated in Error

Invalid Cost Center Assignment

Tables Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Ready to Resolve This in Your Environment?

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in HCM-01

Backup & Rollback for HCM-01

Tables Backed Up — HCM-01

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

What This Script Finds

Date-Effective Assignment Gap

Supervisor Termination Cascade

Assignment Terminated in Error

Invalid Cost Center Assignment

Tables Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Related Diagnostic Scripts

Payroll Run Diagnostic

AP Invoice Validation Diagnostic

Ready to Resolve This in Your Environment?