Symptoms

• Posted GL journal entries reference incorrect code_combination_id (CCID) values on GL_JE_LINES: lines were posted to wrong account segments (e.g., segment1 62250 instead of the correct 62200, and/or missing segment5 194000) causing balances to accumulate on incorrect accounts.
• GL_BALANCES show activity on incorrect CCIDs (5621, 5625) while the correct CCIDs (5783, 5788) have no corresponding balance rows, or show incorrect beginning balances in subsequent periods.
• Intercompany journal lines (CCIDs 5380, 5752) associated with the same journal entries are erroneous and must be removed.
• The problem spans multiple journal headers (je_header_ids 14448, 15178, 15197, 15198) and affects periods MAR-19 through JUN-19.

Root Cause

GL journal entries were posted referencing CCIDs that correspond to account strings with incorrect segment values. The correct account code combinations (accounts 62200.000.000000.50225.194000.00000000.00000000 and 62200.000.100100.62305.194000.00000000.00000000) did not exist in GL_CODE_COMBINATIONS at the time of posting, or the journal import process selected the wrong CCIDs. The fix creates the correct CCIDs, re-codes the GL journal lines, removes erroneous intercompany lines, and restates GL_BALANCES across all affected periods. The resolution was developed iteratively across four phases as the full scope of the problem (multiple journal headers, intercompany entries, multi-period balance impact) was established.

Resolution — Script 01: Create CCID for Account A

Run 01_setup_tables_a.sql. Calls FND_FLEX_EXT.GET_COMBINATION_ID with:

• Application: SQLGL
• ID Flex Code: GL#
• Structure: looked up from FND_ID_FLEX_STRUCTURES where ID_FLEX_STRUCTURE_CODE = 'ACCOUNTING_FLEXFIELD'
• Segment string: 62200.000.000000.50225.194000.00000000.00000000

If the code combination does not exist, the function inserts a new row in GL_CODE_COMBINATIONS and returns the new CCID (stored as XXRSI_CCID_A / CCID 5783 in the subsequent fix scripts).

Resolution — Script 02: Create CCID for Account B

Run 02_setup_tables_b.sql. Same pattern as Script 01 using segment string 62200.000.100100.62305.194000.00000000.00000000 (CCID 5788 in subsequent fix scripts).

Resolution — Script 03: Phase 1 — Diagnostic Investigation and Initial Backup

Script 03_data_fix_phase_1.sql contains diagnostic SELECT statements to understand the scope of the problem, followed by backup table creation:

• Queries GL_JE_BATCHES for the affected batch (name matching 'HAR%Acct%2872555%').
• Queries GL_JE_HEADERS for je_batch_id = 75711.
• Queries GL_JE_LINES for je_header_id = 14448, using FND_FLEX_EXT.GET_SEGS to display account segment strings alongside entered_dr/cr amounts.
• Queries GL_CODE_COMBINATIONS to confirm existence/absence of the source CCIDs (5625, 5621) and target CCIDs (3726, 3967, then 5783, 5788) for both account patterns.
• Queries GL_BALANCES for CCIDs 5625, 5621, 3726, 3967 in periods MAR-19 through JUN-19.

Creates initial backup tables:

• xxrsi_gl_je_line_172542 — snapshot of GL_JE_LINES for je_header_id=14448 where CCID in (5625, 5621, 3726, 3967).
• xxrsi_gl_bal_172542 — snapshot of GL_BALANCES for those CCIDs in MAR-19.

Resolution — Script 04: Phase 2 — First Fix Attempt (Intermediate CCIDs)

Script 04_data_fix_phase_2.sql was the first fix attempt, targeting intermediate CCIDs. Drops and recreates the backup tables from Phase 1, then:

1. Updates GL_JE_LINES for je_header_id = 14448: CCID 5625 → 3726; CCID 5621 → 3967.
2. Updates GL_BALANCES for MAR-19:
   • CCID 5625: period_net_cr - 58.98
   • CCID 3726: period_net_cr + 58.98
   • CCID 5621: period_net_cr - 1065.67
   • CCID 3967: period_net_cr + 1065.67
3. COMMIT.

Resolution — Script 05: Phase 3 — Revised Fix (Correct CCIDs, Single Header, Multi-Period)

Script 05_data_fix_phase_3.sql corrects the Phase 2 fix by targeting the proper CCIDs (5783, 5788) and extending the balance restatement to include April and May carry-forward effects:

1. Backup: Creates xxrsi_gl_je_line_172542A (GL_JE_LINES for je_header_id=14448, CCIDs 5625/5621) and xxrsi_gl_bal_172542A (GL_BALANCES for CCIDs 5625, 5621, 5788, 5783 in MAR-19).
2. Update Journal Lines: For je_header_id=14448: CCID 5625 → 5783; CCID 5621 → 5788.
3. Update MAR-19 Balances: Subtract activity from old CCIDs 5621 (-1065.67) and 5625 (-58.98) on GL_BALANCES.period_net_cr for MAR-19.
4. Insert MAR-19 Balance Rows for New CCIDs: Inserts new GL_BALANCES rows for CCID 5788 (period_net_cr=1065.67) and CCID 5783 (period_net_cr=58.98) in MAR-19, set_of_books_id=1001, period_year=2019, period_num=3.
5. Update APR-19 and MAY-19 Beginning Balances: Adjusts BEGIN_BALANCE_CR on GL_BALANCES:
   • CCIDs 5621, 5625: subtract amounts for APR-19 and MAY-19.
   • CCIDs 5788, 5783: add amounts for APR-19 and MAY-19.
6. Insert Missing APR-19 / MAY-19 Balance Rows: Inserts GL_BALANCES rows for CCID 5788 in APR-19 (begin_balance_cr=1065.67, period_net_cr=0) and CCID 5783 in MAY-19 (begin_balance_cr=1065.67, period_net_cr=0).
7. COMMIT.

Resolution — Script 06: Phase 4 — Final Comprehensive Fix (All Headers, Intercompany Cleanup)

Script 06_data_fix_phase_4.sql is the final and most comprehensive version, extending the fix to all four affected journal headers and removing erroneous intercompany entries:

1. Backup: Creates xxrsi_gl_bal_172542B (GL_BALANCES for CCIDs 5625, 5621, 5788, 5783, 5752, 5380 in MAR-19 through JUN-19) and xxrsi_gl_je_lines_172542B (GL_JE_LINES for je_header_ids 14448, 15178, 15197, 15198).
2. Update Journal Lines — All Headers: For je_header_ids 14448, 15178, 15197, 15198:
   • CCID 5621 → 5788
   • CCID 5625 → 5783
3. Delete Intercompany Lines: Removes GL_JE_LINES rows for je_header_ids 14448, 15178, 15197, 15198 where CCID in (5380, 5752).
4. Update GL_BALANCES for New CCIDs (APR-19 to JUN-19): Adds all balance columns (period_net_dr/cr, quarter_to_date_dr/cr, project_to_date_dr/cr, begin_balance_dr/cr) from the old CCID 5625 rows (sourced from the xxrsi_gl_bal_172542B backup) to the existing CCID 5783 rows, period-by-period.
5. Zero Out Old and Deleted Intercompany CCIDs: Sets all balance columns to 0 on GL_BALANCES for CCIDs 5621, 5625, 5752 in MAR-19 through JUN-19.
6. Adjust Intercompany CCID 5380 Balances: Reduces period_net_cr and period_net_dr on CCID 5380 for APR-19; adjusts quarter-to-date, project-to-date, and begin-balance amounts for MAY-19 and JUN-19 to reflect removal of the deleted intercompany lines.
7. Insert Missing Balance Rows for New CCIDs: Inserts GL_BALANCES rows cloned from the backup for:
   • CCID 5783 in MAR-19 (from CCID 5625 MAR-19 row)
   • CCID 5788 in MAR-19, APR-19, MAY-19, JUN-19 (from CCID 5621 rows for those periods)
8. COMMIT.

Script Inventory

Key Tables Affected