Case 00029668 – AR Cash Receipt Status and Application Amounts Incorrect

AR Cash Receipt Status and Application Amounts Incorrect — Zero Amounts and Set Status for Reversal

Case Number	00029668
Oracle Module	Accounts Receivable (AR)
EBS Version	R12
Functional Area	Cash Receipts / Receivable Applications / Receipt Reversal
Related Patches	None

Symptoms

Multiple AR cash receipts have incorrect application amounts in AR_RECEIVABLE_APPLICATIONS_ALL and cannot be reversed or reprocessed through the UI.
The AR_PAYMENT_SCHEDULES_ALL records for the affected receipts show non-zero amount_due_remaining and acctd_amount_due_remaining values that should be zero, and an incorrect status.
The AR_CASH_RECEIPTS_ALL status is not set to 'REV' (Reversed) for receipts that should be in a reversed state.
The corresponding AR_DISTRIBUTIONS_ALL rows for the affected receivable applications have non-zero amounts that need to be cleared.

Root Cause

Several AR cash receipts are stuck in an incorrect state — the receipt status, payment schedule status and remaining amounts, receivable application amounts, and distribution amounts are not consistent with the intended reversed/closed state for the receipts. This prevents the receipts from being properly reversed, written off, or closed through normal application processing. The fix directly updates the affected tables to zero out the application and distribution amounts, set payment schedules to closed, and set the cash receipt status to reversed ('REV').

Resolution

Step 1 – Create backup tables

Run 01_setup_backup_tables.sql. Backup tables are created for:

AR_CASH_RECEIPTS_ALL — for all affected cash receipt IDs
AR_DISTRIBUTIONS_ALL — joined via AR_CASH_RECEIPT_HISTORY_ALL for the affected receipts
AR_RECEIVABLE_APPLICATIONS_ALL — for the affected receipt IDs
AR_CASH_RECEIPT_HISTORY_ALL — for specific receipt history rows
AR_PAYMENT_SCHEDULES_ALL — for the affected payment schedule IDs

Step 2 – Apply per-receipt data fix (scripts 02 through 07)

Run each per-receipt fix script (02_data_fix_trx_*.sql) for the corresponding cash receipt. Each script applies the same pattern:

AR_CASH_RECEIPTS_ALL: Update status = 'REV' for the affected cash receipt.
AR_PAYMENT_SCHEDULES_ALL: Update amount_due_remaining = 0, acctd_amount_due_remaining = 0, status = 'CL' for the associated payment schedule.
AR_RECEIVABLE_APPLICATIONS_ALL: Zero out amount_applied, acctd_amount_applied_from, acctd_amount_applied_to, line_applied, line_ediscounted, and acctd_earned_discount_taken for APP-status application rows not matching the correct applied amount. Also zero amount_applied and acctd_amount_applied_from for UNAPP-status rows not matching the correct amount.
AR_DISTRIBUTIONS_ALL: Zero out amount_cr/acctd_amount_cr or amount_dr/acctd_amount_dr for distribution rows linked to the affected receivable application IDs.

The six per-receipt scripts (02–07) each cover a different cash receipt ID and its associated payment schedule and application records.

Note: Each per-receipt script contains the specific cash receipt ID, payment schedule ID, and receivable application IDs for that receipt — verify these values against the database before applying. The scripts do not commit individually; a final commit should be issued after all per-receipt scripts have been run and the results verified. After the data fix, the receipts should be reversible or removable through the AR receipt management window.

Script Inventory

File	Description
01_setup_backup_tables.sql	Creates backup tables for AR_CASH_RECEIPTS_ALL, AR_DISTRIBUTIONS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPT_HISTORY_ALL, and AR_PAYMENT_SCHEDULES_ALL for all affected receipts.
02_data_fix_trx_1640.sql through 07_data_fix_trx_eft2013085.sql	Per-receipt fix scripts (one per affected cash receipt). Each updates AR_CASH_RECEIPTS_ALL status to REV, zeros AR_PAYMENT_SCHEDULES_ALL remaining amounts and sets to CL, zeros AR_RECEIVABLE_APPLICATIONS_ALL applied amounts, and zeros AR_DISTRIBUTIONS_ALL amounts for the affected application IDs.

Key Tables Affected

Table	Action
AR_CASH_RECEIPTS_ALL	Update status to 'REV' for affected receipts
AR_PAYMENT_SCHEDULES_ALL	Update amount_due_remaining and acctd_amount_due_remaining to 0; set status to 'CL'
AR_RECEIVABLE_APPLICATIONS_ALL	Zero amount_applied, acctd_amount_applied_from/to, line_applied, and discount amounts
AR_DISTRIBUTIONS_ALL	Zero amount_dr/cr and acctd_amount_dr/cr for affected application distribution rows