Symptoms

• A set of aged AR transactions (invoices, receipts, credit memos, adjustments) with gl_date_closed prior to 01-JAN-1998 cannot be processed by the AR archiving program because their posting_control_id values are not set to the sentinel value -9999 that the archive program requires.
• The AR archiving utility identifies eligible transactions by checking for posting_control_id = -9999 across RA_CUSTOMER_TRX_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL, AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPT_HISTORY_ALL, and AR_ADJUSTMENTS_ALL. Transactions whose posting_control_id was not correctly set by earlier processes remain stuck outside the archive scope.
• The fix iterates through qualifying transactions and updates posting_control_id = -9999 on all affected tables, making the transactions visible to the archive program.

Root Cause

In Oracle 11i AR, the archiving mechanism uses posting_control_id = -9999 as an eligibility flag across multiple AR tables. Transactions that were closed prior to 01-JAN-1998 but were never stamped with this value — due to data migration gaps, prior incomplete archive runs, or manual posting corrections — remain ineligible for archiving. The fix directly sets posting_control_id = -9999 on all relevant AR tables after backing up the current values, enabling the standard archive program to process these transactions.

The solution is delivered in four phases with increasing scope:

• Phase B (Script 03) — Hardcoded single-transaction test fix for customer_trx_id = 110653. Updates 4 tables (no adjustments). Used to validate the mechanism before broader execution.
• Phase C (Script 04) — Date-filtered batch fix: processes up to 1,000 transactions per run where trx_date < 01-JAN-1998. Updates the same 4 tables as Phase B.
• Phase D (Script 05) — Full view-driven batch: processes all transactions and related transactions returned by RSI_DriverView_68994_V. Expands scope to 5 tables (adds AR_ADJUSTMENTS_ALL) and includes full audit stamping (LAST_UPDATE_DATE, LAST_UPDATED_BY = -9999).
• Phases E and F (Scripts 06 and 07) — Identical logic to Phase D; used for subsequent re-runs against the same driver view after earlier batches.

Resolution — Script 01: Create Backup and Error Tables

Run 01_setup_objects.sql first. Creates all backup and error tables idempotently (ORA-955 handler skips creation if already present):

• RSI_CUST_TRX_68994 — Stores (posting_control_id, customer_trx_id) from RA_CUSTOMER_TRX_ALL before update. Unique index on customer_trx_id.
• RSI_CUST_TRX_DIST_68994 — Stores (posting_control_id, customer_trx_id) from RA_CUST_TRX_LINE_GL_DIST_ALL before update. Unique index on customer_trx_id.
• RSI_REC_APP_68994 — Stores (posting_control_id, receivable_application_id) from AR_RECEIVABLE_APPLICATIONS_ALL before update. Unique index on receivable_application_id.
• RSI_CASH_REC_HIST_68994 — Stores (posting_control_id, cash_receipt_history_id) from AR_CASH_RECEIPT_HISTORY_ALL before update. Unique index on cash_receipt_history_id.
• RSI_AR_ADJUSTMENTS_68994 — Stores (posting_control_id, customer_trx_id) from AR_ADJUSTMENTS_ALL before update. Unique index on customer_trx_id.
• RSI_DATAFIX_68994_ERRORS — Error tracking table (customer_trx_id, table_name, error_message, error_block) for per-transaction processing failures.

Resolution — Script 02: Create Driver View

Run 02_setup_driver_table.sql to create the view RSI_DriverView_68994_V. This view identifies AR transactions eligible for archive (closed prior to 01-JAN-1998, up to 500 rows per component). It is a 7-part UNION covering:

1. Invoice transactions — from RA_CUSTOMER_TRX_ALL joined to RA_CUSTOMER_TRX_LINES_ALL, lookups, tax, rules, and AR_DEFERRED_LINES_ALL where gl_date_closed < 01/01/1998.
2. Transaction GL distributions — from RA_CUST_TRX_LINE_GL_DIST_ALL where account_set_flag <> 'Y' and latest_rec_flag = 'Y'.
3. Adjustments — from AR_ADJUSTMENTS_ALL.
4. Contingencies — from AR_LINE_CONTS_ALL.
5. Cash receipts — from AR_CASH_RECEIPT_HISTORY_ALL joined to AR_CASH_RECEIPTS_ALL where nvl(current_record_flag,'N') = 'Y'; excludes receipts already present in AR_ARCHIVE_DETAIL.
6. Receipt applications to invoices — from AR_RECEIVABLE_APPLICATIONS_ALL joined to AR_CASH_RECEIPTS_ALL.
7. Credit memo applications — from AR_RECEIVABLE_APPLICATIONS_ALL joining both CM and invoice RA_CUSTOMER_TRX_ALL rows.

The view exposes trx_id and related_trx_id columns used by Phases D, E, and F to drive processing.

Resolution — Script 03: Single-Transaction Fix (Phase B)

Cursor selects customer_trx_id from RA_CUSTOMER_TRX_ALL where customer_trx_id = 110653 (commented alternative: trx_date < 01/01/1998). Intended as a proof-of-concept run before broader execution.

For each record:

1. Backs up posting_control_id and customer_trx_id from RA_CUSTOMER_TRX_ALL into RSI_CUST_TRX_68994 (DUP_VAL_ON_INDEX protected).
2. Backs up posting_control_id and customer_trx_id from RA_CUST_TRX_LINE_GL_DIST_ALL into RSI_CUST_TRX_DIST_68994.
3. Backs up posting_control_id and receivable_application_id from AR_RECEIVABLE_APPLICATIONS_ALL (via applied_customer_trx_id → cash_receipt_id subquery) into RSI_REC_APP_68994.
4. Backs up posting_control_id and cash_receipt_history_id from AR_CASH_RECEIPT_HISTORY_ALL (same cash_receipt_id subquery) into RSI_CASH_REC_HIST_68994.
5. Updates RA_CUSTOMER_TRX_ALL.posting_control_id = -9999.
6. Updates RA_CUST_TRX_LINE_GL_DIST_ALL.posting_control_id = -9999.
7. Updates AR_RECEIVABLE_APPLICATIONS_ALL.posting_control_id = -9999 (via cash_receipt_id subquery).
8. Updates AR_CASH_RECEIPT_HISTORY_ALL.posting_control_id = -9999 (via cash_receipt_id subquery).
9. Commits per record. Errors logged to RSI_DATAFIX_68994_ERRORS with rollback per transaction.

Note: Phase B does not update AR_ADJUSTMENTS_ALL and does not set LAST_UPDATE_DATE or LAST_UPDATED_BY audit columns.

Resolution — Script 04: Date-Filtered Batch Fix (Phase C)

Cursor selects customer_trx_id from RA_CUSTOMER_TRX_ALL where trx_date < to_date('01/01/98','MM/DD/YY') and rownum < 1001. Processes up to 1,000 records per execution.

Logic per record is identical to Phase B: backs up and updates posting_control_id = -9999 on the same 4 tables (RA_CUSTOMER_TRX_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL, AR_RECEIVABLE_APPLICATIONS_ALL, AR_CASH_RECEIPT_HISTORY_ALL). The initial cleanup block deletes from 4 backup tables (not RSI_AR_ADJUSTMENTS_68994, confirming adjustments are out of scope for Phase C). Commits per record; errors logged to RSI_DATAFIX_68994_ERRORS.

Resolution — Scripts 05–07: View-Driven Full Batch Fix (Phases D, E, F)

Cursor selects UNIQUE trx_id from the UNION of trx_id and related_trx_id columns from RSI_DriverView_68994_V, capturing both primary and related transactions identified by the driver view. Scripts 05, 06, and 07 contain identical logic and are intended for repeated execution as the driver view is refreshed between runs.

Per-record processing expands on Phase C:

1. Backs up all 5 tables: RSI_CUST_TRX_68994, RSI_CUST_TRX_DIST_68994, RSI_AR_ADJUSTMENTS_68994, RSI_REC_APP_68994, RSI_CASH_REC_HIST_68994 (all DUP_VAL_ON_INDEX protected).
2. Updates RA_CUSTOMER_TRX_ALL: sets POSTING_CONTROL_ID = -9999, LAST_UPDATE_DATE = TRUNC(SYSDATE), LAST_UPDATED_BY = -9999.
3. Updates RA_CUST_TRX_LINE_GL_DIST_ALL: same 3-column update.
4. Updates AR_ADJUSTMENTS_ALL: same 3-column update (added in Phase D — not present in Phases B and C).
5. Updates AR_RECEIVABLE_APPLICATIONS_ALL: same 3-column update via cash_receipt_id subquery on applied_customer_trx_id.
6. Updates AR_CASH_RECEIPT_HISTORY_ALL: same 3-column update via cash_receipt_id subquery.
7. Commits per record. Errors logged to RSI_DATAFIX_68994_ERRORS with rollback per transaction.

Script Inventory

Key Tables Affected