EBS 12.2 -- ADOP Phase=Cleanup Error -- Unable to get APPS_DDL package version from db

Today's issue is from Oracle EBS domain. I will be fast and clean and give you the error, the cause and  the solution without dancing around the subject. Okay... Let's go!

->

PROBLEM:

While performing ADOP cleanup phase, we failed with the AutoPatch error seen below..

AutoPatch error:

Unable to get APPS_DDL package version from db <XX_CUSTOM_SCHEMA> <APPS_DDL> <PACKAGE>

AutoPatch error:

adpmrp: Error while installing apps_ddl packages.

It was due to an EBS-side config of a custom schema and the the cause of the issue could be identified using the query below;

select ORACLE_USERNAME, READ_ONLY_FLAG, ENABLED_FLAG, ORACLE_ID, INSTALL_GROUP_NUM, last_update_date from FND_ORACLE_USERID where ORACLE_USERNAME in ('XX_CUSTOM_SCHEMA');

--ENABLED_FLAG and READ_ONLY_FLAG were suspicious, and in our case the problem was related with the value of the READ_ONLY_FLAG.

SOLUTION:

*Connect to the database using sqlplus and APPS user

*Take a back up of the table fnd_oracle_userid

create table fnd_oracle_userid_bkp2 as select * from fnd_oracle_userid;

*Check the values before changing for reference.

select ORACLE_USERNAME, READ_ONLY_FLAG, ENABLED_FLAG, ORACLE_ID, last_update_date from FND_ORACLE_USERID where ORACLE_USERNAME in ('XX_CUSTOM_SCHEMA');

*Use the following update, and fix the issue.

update fnd_oracle_userid set read_only_flag='A' where ORACLE_username in ('XX_CUSTOM_SCHEMA');

commit;

--In our case READ_ONLY_FLAG was set to U, and that was causing the issue.

Note the description of the values of READ_ONLY_FLAG column;

C - Category 5 - APPLSYSPUB
U - Category 5 - APPS
E - Category 5 - APPLSYS and APPS_NE
U - Category 5 - APPS variations for the obsolete Multiple Reporting Currencies feature
A - Category 6 - Oracle E-Business Suite Base Product schemas
X - Category 4 - Non-Oracle E-Business Suite schemas

*Check if the values are corrected as expected.

select ORACLE_USERNAME, READ_ONLY_FLAG, ENABLED_FLAG, ORACLE_ID, last_update_date from FND_ORACLE_USERID where ORACLE_USERNAME in ('XENP');

Re-run adop cleanup phase, and you will see the issue will be disappeared.

adop phase=cleanup