Symptoms

• A set of AR cash receipts have incorrect gl_date values on AR_CASH_RECEIPT_HISTORY_ALL, AR_DISTRIBUTIONS_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL — the receipts have posting_control_id = -3 (unposted) and their GL date falls in the prior period (July 2015) rather than the correct period (August 2015).
• A separate group of reversed AR cash receipts (status 'REV') are missing required accounting rows: the AR_CASH_RECEIPT_HISTORY_ALL table is missing a REMITTED history line; AR_RECEIVABLE_APPLICATIONS_ALL is missing 2 to 4 UNAPP application rows; and AR_DISTRIBUTIONS_ALL is missing the corresponding CRH and RA distribution lines needed for GL posting.
• Without the missing rows, the reversed receipts cannot be posted to the GL and remain in an unposted state with posting_control_id = -3.

Root Cause

Two distinct issues require separate script phases:

• Phase C (Script 02) — AR cash receipts were processed with a GL date in July 2015 instead of August 2015. Because the receipts have posting_control_id = -3 (not yet posted to the GL), the GL date can be corrected directly on the three AR tables without reversing posted entries.
• Phase J (Script 03) — Reversed cash receipts are missing the intermediate REMITTED step in AR_CASH_RECEIPT_HISTORY_ALL and the corresponding UNAPP receivable application rows and AR distribution lines. In Oracle 11i AR, a reversal of a confirmed receipt requires: CONFIRMED → REMITTED → REVERSED history sequence; six UNAPP receivable application rows (two confirming, two remitting); and the matching CRH and RA distribution lines in AR_DISTRIBUTIONS_ALL. Missing rows from this sequence prevent GL posting of the reversal.

Resolution — Script 01: Create Backup Tables

Run 01_setup_backup_tables.sql before executing any fix scripts. Creates:

• RSI_AR_DISTS_58329 — mirrors AR_DISTRIBUTIONS_ALL structure, with an additional CASH_RECEIPT_ID column and index, for backing up distribution rows per receipt.
• RSI_AR_CASH_REC_HIST_58329 — mirrors AR_CASH_RECEIPT_HISTORY_ALL for receipt history backups.
• RSI_AR_CASH_REC_APP_58329 — mirrors AR_RECEIVABLE_APPLICATIONS_ALL for receivable application backups.
• RSI_AR_GL_ERRORS_58329 — error tracking table (receipt_id, error_message, error_block) used by Phase J to log per-receipt processing errors without halting the full run.

Resolution — Script 02: AR GL Date Correction (Phase C)

Selects AR cash receipts where AR_RECEIVABLE_APPLICATIONS_ALL.posting_control_id = -3, gl_date is in July 2015, postable = 'Y', and org_id = 1. Processes up to 3 receipts (bounded by ROWNUM <= 3 in the initial scan).

For each qualifying receipt:

1. Updates AR_CASH_RECEIPT_HISTORY_ALL: sets gl_date = 08/01/2015 where posting_control_id = -3.
2. Updates AR_DISTRIBUTIONS_ALL: sets gl_date = 08/01/2015 where posting_control_id = -3 and source_id matches a cash receipt history ID for the receipt.
3. Updates AR_RECEIVABLE_APPLICATIONS_ALL: sets gl_date = 08/01/2015 where posting_control_id = -3 and cash_receipt_id matches.
4. Commits after processing all receipts.

Resolution — Script 03: Reversed Receipt Data Repair (Phase J)

Selects reversed AR cash receipts where AR_RECEIVABLE_APPLICATIONS_ALL.posting_control_id = -3, gl_date = 01-OCT-2015, postable = 'Y', org_id = 1, and AR_CASH_RECEIPTS_ALL.status = 'REV' (reversed).

For each reversed receipt, the script executes a self-contained per-receipt block (rolls back and logs errors individually on failure) with the following steps:

1. Collect key IDs: reads CASH_RECEIPT_HISTORY_ID and ACCOUNT_CODE_COMBINATION_ID for the CONFIRMED and REVERSED history rows; reads THIRD_PARTY_ID and THIRD_PARTY_SUB_ID from existing distributions; reads AMOUNT from AR_CASH_RECEIPTS_ALL; reads PAYMENT_SCHEDULE_ID; reads existing receivable application IDs for application rules 97.2 and 96.
2. Back up current rows: backs up AR_CASH_RECEIPT_HISTORY_ALL and AR_RECEIVABLE_APPLICATIONS_ALL rows for the receipt into the backup tables from Script 01.
3. Insert REMITTED history row (when the remittance row is missing — i.e., only 2 rows exist in AR_CASH_RECEIPT_HISTORY_ALL for the receipt): inserts a new REMITTED row into AR_CASH_RECEIPT_HISTORY_ALL using AR_CASH_RECEIPT_HISTORY_S.nextval; updates the REVERSED row to link to the new REMITTED row (PRV_STAT_CASH_RECEIPT_HIST_ID); updates the CONFIRMED row to link its reversal to the REMITTED row (REVERSAL_CASH_RECEIPT_HIST_ID).
4. Determine missing receivable application rows: counts existing rows in AR_RECEIVABLE_APPLICATIONS_ALL for the receipt; a complete reversal requires 6 rows — inserts either 2 or 4 UNAPP rows (with application rules 97.0, 97.1, 96) as needed using AR_RECEIVABLE_APPLICATIONS_S.nextval.
5. Back up distribution rows from AR_DISTRIBUTIONS_ALL (CRH, RA, and MCD source tables) into RSI_AR_DISTS_58329.
6. Insert missing AR_DISTRIBUTIONS_ALL rows:
   • Updates the existing REVERSED distribution line's source_id to point to the new REMITTED history ID.
   • Inserts a REMITTANCE DR distribution line for the REVERSED history row.
   • Inserts a REMITTANCE DR distribution line for the new REMITTED history row.
   • Inserts a CONFIRMATION CR distribution line for the REVERSED row (if needed).
   • Inserts 6 RA (receivable application) distribution lines — REC debit/credit for both application rules 97.x and 96, plus UNAPP debit/credit lines.
7. Commits per receipt. Errors are logged to RSI_AR_GL_ERRORS_58329 and the script proceeds to the next receipt.

Script Inventory

Key Tables Affected