AR-04: Customer Account Health Diagnostic

The Oracle EBS customer master is a three-layer structure: the TCA party model in HZ_PARTIES stores the legal entity, HZ_CUST_ACCOUNTS stores the Oracle customer account, and HZ_CUST_SITE_USES_ALL stores the individual site uses — bill-to, ship-to, and statements. Each layer can have independent active/inactive flags, and a customer transaction can fail at any layer. An invoice entry failure that presents as 'customer not found' may have its root cause in an inactive HZ_PARTIES record, an inactive HZ_CUST_ACCOUNTS record, a missing bill-to site use, or a bill-to site use that exists but is inactive. Each of these requires a different fix in a different form.

Credit limit management is operationally critical in AR. When a customer's AR_CUSTOMER_PROFILES_ALL credit limit is set, Oracle AR tracks the total outstanding balance in AR_PAYMENT_SCHEDULES_ALL and blocks new order entry when the exposure reaches the limit — but only if credit checking is enabled on the order management side. When credit checking is disabled or the limit is set incorrectly — too high because the profile was never updated after a customer's payment behavior changed, or too low because the limit was set conservatively at account opening — the result is either uncollectable receivables building up against a customer who should have been on credit hold, or blocked orders for a customer who is a good payer.

Duplicate customer records in Oracle EBS carry the same consequences as duplicate vendor records in AP, with an additional complication: the TCA architecture means that a customer can be duplicated at the party level (two HZ_PARTIES records for the same legal entity), at the customer account level (two HZ_CUST_ACCOUNTS records under the same party), or at both levels. A duplicate at the party level is the most serious because TCA party data is shared across AR, AP (supplier), and PO — merging a party-level duplicate requires the Merge Parties concurrent program and affects records across all three modules.

AR-04 runs a complete customer account health diagnostic — party and account active status, all site uses across operating units with active status, credit limit versus current exposure analysis, dunning letter and payment terms configuration, duplicate detection at both party and account levels by name similarity and tax ID, and transaction history summary showing the customer's payment behavior pattern across the last 12 months.

Customer Active Status

HZ_PARTIES and HZ_CUST_ACCOUNTS active status. Customer account relationship type. Account classification. Tax registration number. Salesperson assignment.

Site Uses Configuration

Bill-to and ship-to site uses active status. Payment terms per site. Primary bill-to site assignment. Invoice currency per site. AutoInvoice grouping rule.

Credit Limit and Exposure

Credit limit amount. Current credit exposure vs. limit. Days beyond credit limit. Tolerance for over-limit invoicing. Credit hold status.

Dunning Configuration

Dunning letter set assignment. Days between dunning letters. Minimum dunning amount. Dispute flag impact on dunning. Last dunning letter date.

Duplicate Customer Detection

TCA duplicate detection — same name, tax ID, or address across HZ_PARTIES. Merge candidate identification. Account hierarchy for parent/child duplicates.

════════════════════════════════════════════════════════════
  ORACLE EBS R12 — CUSTOMER ACCOUNT HEALTH DIAGNOSTIC
════════════════════════════════════════════════════════════
  Customer Account   : 22184 — Hawthorne Industrial Group
  Party ID           : HZ-88412
  Operating Unit     : US Operations OU
  Case Number        : AR-324100
  Report Date        : 23-FEB-2026 11:30:40
════════════════════════════════════════════════════════════

[ SECTION 1 — PARTY AND ACCOUNT STATUS ]     STATUS: ✓ PASS
────────────────────────────────────────────────────────────
  HZ_PARTIES Status  : ACTIVE ✓
  CUST_ACCOUNT Status: ACTIVE ✓

[ SECTION 2 — SITE USES ]                    STATUS: ✗ FAIL
────────────────────────────────────────────────────────────
  Bill-to Primary    : ACTIVE ✓
  Bill-to Secondary  : INACTIVE — 14 open invoices reference this site ✗
  Ship-to            : ACTIVE ✓
  ✗ FAIL: INACTIVE bill-to site referenced by 14 open invoices
  Fix                : Customers > Standard > Business Purposes: reactivate or transfer

[ SECTION 3 — CREDIT LIMIT ]                 STATUS: ⚠ WARNING
────────────────────────────────────────────────────────────
  Credit Limit       : $500,000
  Current Exposure   : $488,200 — 97.6% utilized
  AR > 90 days       : $84,200 — 17.2% of exposure
  ⚠ WARNING: Near credit limit — new orders may be blocked soon

[ SECTION 4 — DUNNING ]                      STATUS: ✗ FAIL
────────────────────────────────────────────────────────────
  ✗ No dunning letter set assigned to this customer profile
  ✗ $84,200 AR balance over 90 days — no collection letters sent

[ SECTION 5 — DUPLICATE DETECTION ]          STATUS: ✓ PASS
════════════════════════════════════════════════════════════
  DIAGNOSTIC SUMMARY
════════════════════════════════════════════════════════════
  3 conditions: inactive site (14 invoices), credit limit risk, missing dunning
════════════════════════════════════════════════════════════

KB-AR-324100-001 · Script: AR-04

Customer Account Health — Inactive Bill-to Site with Open Invoices, Missing Dunning Configuration

Module: Accounts Receivable
Platform: EBS R12.2.10
Status: RESOLVED

Symptom

Customer 22184 Hawthorne Industrial Group has an inactive bill-to site (US-SECONDARY) referenced by 14 open invoices. Credit limit 97.6% utilized. No dunning letter set assigned — $84,200 AR balance over 90 days without collection letters.

Root Cause

Bill-to site US-SECONDARY inactivated during a data cleanup project — 14 invoices created against this site before inactivation were not migrated to the primary bill-to. Credit limit was set at account opening and never reviewed. Dunning letter set was never assigned to this customer profile during the original setup.

Tables Affected

HZ_CUST_SITE_USES_ALL — STATUS = INACTIVE (Site Use ID: 88210, BILL_TO)
AR_CUSTOMER_PROFILES_ALL — CREDIT_LIMIT, DUNNING_LETTERS (Customer ID: 22184)
AR_PAYMENT_SCHEDULES_ALL — AMOUNT_DUE_REMAINING (14 open invoices)

Fix Applied

Bill-to site US-SECONDARY reactivated via Customers workbench. Credit limit review initiated — Controller reducing to $450,000 based on 12-month payment history. Dunning letter set STANDARD-NET assigned to customer profile.

Prevention

Site use inactivation should require a cross-reference check against AR_PAYMENT_SCHEDULES_ALL for open balances — AR-04 can run this check before any site inactivation. Annual customer credit limit reviews should be scheduled. AR-04 customer health check should run monthly for all customers with AR over 60 days.

Table / View	Schema	Purpose in Diagnostic
HZ_PARTIES	HZ	Party records for customer active status
HZ_CUST_ACCOUNTS	HZ	Customer account configuration
HZ_CUST_SITE_USES_ALL	HZ	Bill-to and ship-to site uses
HZ_LOCATIONS	HZ	Customer address for duplicate detection
AR_CUSTOMER_PROFILES_ALL	AR	Credit limits and dunning setup
HZ_PARTY_RELATIONSHIPS	HZ	Customer account hierarchy
RA_CUST_TRX_TYPES_ALL	AR	Default transaction types per customer
AR_PAYMENT_SCHEDULES_ALL	AR	Open balance for credit exposure

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
Inactive bill-to site with open invoices or orders	Functional First	Reactivate via Customers > Standard > Business Purposes, or transfer open invoices to the active site via Transactions workbench. AR-04 identifies all transactions referencing the inactive site for pre-reactivation review.
Customer on credit hold — exposure at or over limit	Functional First	Credit limit review and update via Customers > Standard > Credit Profile. AR-04 provides the exact exposure amount, AR aging breakdown, and 12-month payment history for the credit review decision.
Missing dunning letter set — past-due accounts not dunned	Functional First	Assign a dunning letter set via Customers > Standard > Payment Profile. AR-04 identifies the customer's overdue balance and the number of days since last dunning contact.
Duplicate customer at account level	Functional First	Customer account merge via TCA > Customers > Merge. AR-04 identifies all transactions, receipts, and open balances referencing both account IDs for pre-merge validation.
Duplicate customer at party level	Oracle Support	Party-level merge requires the Merge Parties concurrent program and affects AP, PO, and AR simultaneously. AR-04 identifies all cross-module references for the SR documentation.
Payment terms not assigned or inactive payment terms	Functional First	Assign active payment terms via Customers > Standard > Payment Profile or the site use payment terms override. AR-04 identifies the current terms value and the active terms options.
HZ_PARTIES inactive — transaction entry blocked	Direct Fix	AR-04 reactivates the HZ_PARTIES record (STATUS from INACTIVE to ACTIVE) with full backup where the party should clearly be active based on open transaction history. Requires business confirmation before execution.
No bill-to site in the required operating unit	Functional First	Create the bill-to site use for the OU via Customers > Standard > Business Purposes. AR-04 identifies which OUs are missing the bill-to assignment based on open invoice references.

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.

HZ_CUST_ACCOUNTS

HZ_CUST_SITE_USES_ALL

AR_CUSTOMER_PROFILES_ALL

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.

AR-04 — Pre-Flight & Backup Verification

════════════════════════════════════════════════════════════
  PRE-FLIGHT SAFETY CHECK
════════════════════════════════════════════════════════════
  Customer Account   : 22184
  Open Transactions  : 14 — site reactivation safe ✓
  CONS_BACKUP Schema : Accessible ✓
  Session Lock Check : No locks detected ✓
────────────────────────────────────────────────────────────
  ALL PRE-FLIGHT CHECKS PASSED
════════════════════════════════════════════════════════════
  Creating : CONS_BACKUP.HZ_CUST_SITE_USES_ALL_324100
  Rows     : 3 rows backed up ✓
  Creating : CONS_BACKUP.AR_CUSTOMER_PROFILES_ALL_324100
  Rows     : 1 row backed up ✓
  Registry : FIX_BACKUP_REGISTRY — ID 3091 created ✓
────────────────────────────────────────────────────────────
  BACKUP COMPLETE
════════════════════════════════════════════════════════════
  Enter case number to confirm : 324100
  Confirmed.
════════════════════════════════════════════════════════════

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-AR-324100-001

Customer Account Health — Inactive Bill-to Site with Open Invoices, Missing Dunning Configuration

EBS R12.2.10 · Accounts Receivable

● RESOLVED

Symptom

Root Cause

Tables

HZ_CUST_SITE_USES_ALL — STATUS = INACTIVE (Site Use ID: 88210, BILL_TO)
AR_CUSTOMER_PROFILES_ALL — CREDIT_LIMIT, DUNNING_LETTERS (Customer ID: 22184)
AR_PAYMENT_SCHEDULES_ALL — AMOUNT_DUE_REMAINING (14 open invoices)

Fix Applied

Prevention

R12 Guide (PDF)	Title & Chapter	Detail
120arig.pdf	Oracle Receivables Implementation Guide — Ch. 3: Customer Profile Classes	pp. 3-6 to 3-16: Customer profile classes, credit limits, and payment term defaults
120arig.pdf	Oracle Receivables Implementation Guide — Ch. 2: Organizations	pp. 2-22 to 2-24: Multiple organization support for customer account partitioning
120funmo.pdf	Oracle Applications Multiple Organizations Implementation Guide	Operating unit and business group configuration for TCA party and customer access

Customer Account Health Diagnostic

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in AR-04

Backup & Rollback for AR-04

Tables Backed Up — AR-04

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

Most Common Issues Detected by AR-04

Customer Over Credit Limit

No Active Bill-to Site

Duplicate Customer Records

No Dunning Configuration

Tables & Views 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 AR-04

Backup & Rollback for AR-04

Tables Backed Up — AR-04

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

Most Common Issues Detected by AR-04

Customer Over Credit Limit

No Active Bill-to Site

Duplicate Customer Records

No Dunning Configuration

Tables & Views Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Related Diagnostic Scripts

AR Transaction Validation

Cash Application Diagnostic

AR Period Close Readiness

Revenue Recognition Diagnostic

Ready to Resolve This in Your Environment?