Symptoms

• The AP Trial Balance or Liability Balance report is missing a row for a specific accounting event header, causing an imbalance or gap in the AP subledger liability reporting.
• The AP_LIABILITY_BALANCE table does not contain an entry corresponding to a LIABILITY-type AP_AE_LINES_ALL row for a specific ae_header_id.
• One or more AP_AE_LINES_ALL rows, AP_LIABILITY_BALANCE rows, and AP_TRIAL_BAL rows reference an incorrect code_combination_id (CCID), causing postings to the wrong GL account.

Root Cause

In Oracle 11i, AP_LIABILITY_BALANCE is populated from AP_AE_LINES_ALL LIABILITY-type rows during the GL Transfer process. For this case, a LIABILITY line in AP_AE_LINES_ALL (for ae_header_id = 15843039) has no corresponding row in AP_LIABILITY_BALANCE, and the CCID recorded across AP_AE_LINES_ALL, AP_LIABILITY_BALANCE, and AP_TRIAL_BAL is incorrect. The fix is executed in two phases: Phase A inserts the missing liability balance row and refreshes the trial balance flag; Phase B corrects the CCID across all three tables after backing up the original values.

Resolution

Run 01_data_fix_phase_a.sql. This script:

1. Inserts into AP_LIABILITY_BALANCE from AP_AE_LINES_ALL for ae_header_id = 15843039, selecting rows where ae_line_type_code = 'LIABILITY' and no matching row already exists in AP_LIABILITY_BALANCE (NOT EXISTS check on ae_header_id and ae_line_num).
2. Calls AP_TRIAL_BALANCE_PKG.Update_Trial_Balance_Flag(l_gl_transfer_run_id) to refresh the trial balance flag for the affected transfer run, ensuring the newly inserted row is reflected in trial balance reporting.
3. Commits after the insert and package call.

Run 02_data_fix_phase_b.sql. This script:

1. Creates backup tables:
   • RSI_AP_AE_LINES_31801 — backup of AP_AE_LINES_ALL rows for the affected ae_line_id and ae_header_id
   • RSI_AP_LIABILITY_BAL_31801 — backup of AP_LIABILITY_BALANCE rows for the affected ae_header_id
   • RSI_AP_TRIAL_BAL_31801 — backup of AP_TRIAL_BAL rows for the affected invoice ID
2. Updates AP_AE_LINES_ALL: sets code_combination_id = 68855 (correct CCID) where it was incorrectly set to 68985, for the specific ae_line_id and ae_header_id.
3. Updates AP_LIABILITY_BALANCE: sets code_combination_id = 68855 where it was 68985, for the same ae_header_id.
4. Updates AP_TRIAL_BAL: sets code_combination_id = 68855 where it was 68985, for the affected invoice ID.
5. Commits after all updates.

Script Inventory

Key Tables Affected