Multiple Sets of Books is not compatible with MultiOrg - Adadmin Issue

Issue while running the adadmin utility:

adadmin error
Error in addgbc():
Multiple Sets of Books is not compatible with MultiOrg

The issue is caused by the following setup:

Duplicate entries for BSDK Bestseller application in FND_PRODUCT_INSTALLATIONS table.

==========================================================

When the following queries are executed, two entries are found for the same application in FND_PRODUCT_INSTALLATIONS table with same application name and application id but different oracle id.

1 . SELECT APPLICATION_ID,COUNT(*)
FROM FND_PRODUCT_INSTALLATIONS
GROUP BY APPLICATION_ID
HAVING COUNT(*) > 1;
----------------------------------------------------------
Upon execution the result will be as follows:

APPLICATION_ID COUNT(*)
-------------- ----------
703 2

----------------------------------------------------------

2. SELECT FPI.APPLICATION_ID, FPI.ORACLE_ID, FAL.APPLICATION_NAME
FROM FND_PRODUCT_INSTALLATIONS FPI, FND_APPLICATION_TL FAL
WHERE FPI.APPLICATION_ID= "<--this value will be obtained from the above query-->"
AND FPI.APPLICATION_ID = FAL.APPLICATION_ID;
----------------------------------------------------------
Upon execution the result will be as follows:-

APPLICATION_ID  ORACLE_ID APPLICATION_NAME
-------------- ---------- --------------------------------------------------
         703          431 Engineering
         703          703 Engineering


Note: The data over here is customer specific, it may be different for case to case basis.
--------------------------------------------------------


Solution

SELECT LAST_UPDATE_DATE, APPLICATION_ID, STATUS, ORACLE_ID FROM
FND_PRODUCT_INSTALLATIONS WHERE APPLICATION_ID = <--this value is customer specific-->;

NOTE: There will be two duplicate entries in the FND_PRODUCT_INSTALLATIONS table.

SQL> /

LAST_UPDATE_DATE   APPLICATION_ID S  ORACLE_ID
------------------ -------------- - ----------
10/16/2016 10:03:54 PM 703                     I   703
2/4/2017 7:27:41 PM         703                     L   431

3. In the FND_PRODUCT_INSTALLATIONS table, check for the STATUS = 'L' for each application which from the above query when executed.

4. Remove the application which has values for STATUS = 'L' and application_id same as the other one.

(STATUS =>, I means installed, L means custom, N means not installed, and S means installed as shared product in FND_PRODUCT_INSTALLATIONS table).

5. Take backup of FND_PRODUCT_INSTALLATIONS table (either with export or with create table)

6. SQL> delete from FND_PRODUCT_INSTALLATIONS where APPLICATION_ID= 703 and ORACLE_ID=431;

7. SQL> SELECT FPI.APPLICATION_ID, FPI.ORACLE_ID, FAL.APPLICATION_NAME FROM FND_PRODUCT_INSTALLATIONS FPI, FND_APPLICATION_TL FAL WHERE FPI.APPLICATION_ID=703 AND FPI.APPLICATION_ID = FAL.APPLICATION_ID;

    APPLICATION_ID  ORACLE_ID APPLICATION_NAME
    -------------- ---------- --------------------------------------------------
           703                  703                Engineering
   
    SQL> SELECT APPLICATION_ID,COUNT(*)
    FROM FND_PRODUCT_INSTALLATIONS
    GROUP BY APPLICATION_ID
    HAVING COUNT(*) > 1;  2    3    4
   
    no rows selected

8. SQL> commit

Reference Note id: Error in addgbc(): Multiple Sets of Books is not compatible with MultiOrg (Doc ID 1163458.1)