GL: Duplicates in GL_CODE_COMBINATIONS Table (Doc ID 1068370.6)

Last updated on NOVEMBER 08, 2023

Applies to:

Symptoms

This document is applicable for release 11i as well as release 12.

1. Duplicate rows exist in the GL_CODE_COMBINATIONS table.
There are rows in GL_CODE_COMBINATIONS with the same values for all segments.  The code_combination_ids are different.

Errors such as ora-01427 in GLPPOS occur:
PERF0005: Oracle error detected in glpiucc - ORA-01427: single-row subquery returns more than one row
             
When the flexfield is frozen by navigating to financial > flexfields > key > segments screen, the job may execute completes successfully.

2. Execute the following SQL which will bring back results for all charts of accounts (COA).
You may restrict by chart_of_accounts_id or choose the results by COA ID.

The method of fixing this problem is to give the code_combination_id a new value on one segment. We will then use sql to update the segment in one of the code combinations making it unique. We can then journal any balance on the one combination to the remaining valid combination and disable the bad one.

This allows all reference to that code_combination_id to retain its data integrity throughout the system while keeping the correct balance in one place.
You will need to journal the balance in all currencies in a multicurrency environment.

Care should be taken if you have to journal in an MRC environment or where multiple books are involved that a journal is created for all sets of books.

Cause