Symptoms

• The AR to GL Transfer program completed with errors or was interrupted, leaving AR transaction records in a partially-posted state.
• Affected records across multiple AR tables show a non-null posting_control_id and gl_posted_date pointing to the failed transfer run, but the corresponding GL journal entries were not created.
• The GL Transfer program cannot be resubmitted for the affected records because they appear already posted.
• Affected transaction types include: cash receipts, receivable applications, adjustments, miscellaneous cash, and transaction history.

Root Cause

An AR to GL Transfer concurrent program run updated the posting_control_id and gl_posted_date columns on AR transaction records to mark them as transferred, but the corresponding GL journal entries were not successfully created (due to a program error, timeout, or abort). The AR records are now stuck in a posted state for the failed posting control ID. Resetting these columns to their unposted values (NULL / -3) allows the GL Transfer to be resubmitted.

Resolution

The script first produces a summary report of all AR records affected by the failed posting control ID, grouped by transaction type (Cash Receipts, Receivable Applications, Adjustments, Misc Cash, Transaction History), showing total debit and credit amounts per type.

Run data_fix.sql. Backup tables are created for all affected rows in:

• AR_CASH_RECEIPT_HISTORY_ALL
• AR_RECEIVABLE_APPLICATIONS_ALL
• AR_ADJUSTMENTS_ALL
• AR_MISC_CASH_DISTRIBUTIONS_ALL
• AR_TRANSACTION_HISTORY_ALL

For each affected transaction type, the script iterates through records matching the failed posting_control_id and resets:

• gl_posted_date = NULL
• posting_control_date = -3 (the Oracle AR standard value for unposted)
• last_updated_by and last_update_date stamped for audit

This is performed for all five transaction types in sequence.

The script issues COMMIT after processing all record types and produces a summary of records updated by type. Resubmit the AR to GL Transfer program for the affected period.

Script Inventory

Key Tables Affected