Symptoms

• AR cash receipts have incorrect or duplicate accounting distribution rows in AR_DISTRIBUTIONS_ALL that prevent the receipts from being reversed or deleted through the UI.
• The distribution rows for the affected cash receipts have incorrect source_type entries or wrong amounts, blocking the reversal process.
• The fix requires deleting the incorrect distribution rows and inserting correct REC (receivable) and UNAPP (unapplied) distribution pairs for each affected receipt.

Root Cause

AR cash receipt distribution records in AR_DISTRIBUTIONS_ALL were created with incorrect source types, incorrect amounts, or duplicated rows. When the receipts are reversed, the AR module checks for specific distribution row patterns (source_type = 'REC' for the receivable credit, and two UNAPP rows — one DR and one CR for the unapplied cash clearing). If these rows are missing, duplicated, or have wrong amounts, the reversal fails. The fix removes the incorrect distributions and inserts the correct three-row pattern (REC, UNAPP-DR, UNAPP-CR) for each affected receipt.

Resolution

Run 01_setup_log_tables.sql. Creates the RSI_27242_RCPT_LOG_MESSAGES table with a sequence and index for tracking fix progress and errors.

Run 02_log_package_spec.sql and 03_log_package_body.sql to compile the logging package used by the fix scripts.

Run 04_data_fix_transaction.txt. For the affected transaction's cash receipt:

1. Verify whether incorrect distribution rows exist (query for source_table='RA' distributions linked via receivable application IDs).
2. If incorrect rows exist: back up to RSI_27242_AR_DISTS_ALL, then delete them.
3. Insert three replacement distribution rows using ra_cust_trx_line_gl_dist_s.nextval for line_id:
   • source_type = 'REC': credit to the receivable account (amount_cr)
   • source_type = 'UNAPP' row 1: debit to the unapplied cash account (amount_dr), with secondary source reference
   • source_type = 'UNAPP' row 2: credit to the receivable account (amount_cr)
4. Commit and attempt to reverse/delete the receipt.

Run 05_data_fix_receipt_1.txt through 09_data_fix_receipt_5.txt. Each script applies the same pattern to a different cash receipt:

1. Back up incorrect AR_DISTRIBUTIONS_ALL rows to a per-receipt backup table (joined via AR_CASH_RECEIPT_HISTORY_ALL, excluding source_table = 'CRH' rows).
2. Delete the incorrect rows.
3. Insert the three correct distribution rows (REC credit, UNAPP debit, UNAPP credit) with the correct amounts, CCIDs, and party information.
4. Commit and attempt to reverse the receipt.

Script Inventory

Key Tables Affected