Symptoms

• GL trial balance or financial reports show incorrect totals for certain account and period combinations.
• Queries against GL_BALANCES return multiple rows for the same combination of set_of_books_id, code_combination_id, currency_code, period_name, actual_flag, budget_version_id, encumbrance_type_id, template_id, and translated_flag — indicating duplicate balance rows exist.
• Posting or translation programs may fail or produce errors due to the duplicate balance records.

Root Cause

Duplicate rows in GL_BALANCES occurred for specific ledger, account, and period combinations, likely caused by a failed or aborted posting run that partially committed balance updates. The duplicate rows cause double-counting in balance calculations and reports. The fix identifies the duplicates using a driver query, backs them up, and removes the extra rows.

Identification SQL

The following query identifies affected account/period combinations with duplicate GL balance rows:

SELECT set_of_books_id, code_combination_id, currency_code, period_name,
       actual_flag, budget_version_id, encumbrance_type_id, template_id,
       DECODE(translated_flag, 'Y', 'Y', 'N', 'Y', 'R', 'R', 'X'),
       COUNT(*)
FROM gl_balances
GROUP BY set_of_books_id, code_combination_id, currency_code, period_name,
         actual_flag, budget_version_id, encumbrance_type_id, template_id,
         DECODE(translated_flag, 'Y', 'Y', 'N', 'Y', 'R', 'R', 'X')
HAVING COUNT(*) > 1;

Resolution

Run data_fix.sql. The script first creates a driver table (rsi_driver_76964A) containing the unique key combination for all GL_BALANCES rows that appear more than once, along with their counts.

A backup table (rsi_gl_bal_76964A) is created containing all GL_BALANCES rows for the affected ledger and period that have matching code_combination_id values in the driver table.

Delete the duplicate GL_BALANCES rows, retaining only one row per unique key combination. The duplicate rows are identified by ROWID, keeping the row with the lowest ROWID for each duplicate group.

Issue COMMIT. Re-run the affected GL balance reports and confirm the totals are correct with no duplicate rows remaining.

Script Inventory

Key Tables Affected