1530 invalids after upgrading 11.5.9/9.2.0.8 to 11.5.10.2/10.2.0.3

The DBA team did a test upgrade of an 11.5.9/9.2.0.8 instance to 11.5.10.2/10.2.0.3 instance and in OLTP instance they got 1530 new invalid objects, in ASCP only 5 new invalids. This seems very strange and I am investigating it. Of the 1530, 717 invalids are owned by APPS. Out of these 717, we have 424 objects which have names starting with OE% and 126 objects which have names starting with ONT%. So 550 objects alone belong to Order Entry.

I see bug 5020914, open for a similar issue, in which the customer re-applied the maintenance pack to fix the invalids. I have an SR open with Oracle Order Entry for this. They asked me to run $ONT_TOP/patch/115/sql/ontup251.sql which did nothing.

I compiled one of the invalid objects at random and this is the result:

SQL> alter package OE_2PC2_ORDLINS_RETROLN compile;

Warning: Package altered with compilation errors.

SQL> show errors
No errors.
SQL> alter package OE_2PC2_ORDLINS_RETROLN compile body;

Warning: Package Body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY OE_2PC2_ORDLINS_RETROLN:
17/4 PL/SQL: SQL Statement ignored
19/10 PL/SQL: ORA-00904: "A"."RETROBILL_REQUEST_ID": invalid identifier

The column RETROBILL_REQUEST_ID doesn't exist in the view OE_AK_ORDER_LINES_V, which is causing the above error. A query on eTRM (electronic Technical Reference Manual) shows that OE_AK_ORDER_LINES_V should have the column RETROBILL_REQUEST_ID. Looking at each object individually is going to be a very tedious task and is not productive as it will not give me a solution.

Since Oracle was taking long time to solve this one, I thought of applying the latest OM patch to fix the invalids. Patch 5753507 April 2007 Order Management 11.5.10 is the latest OM patch. However it had pre-requisites like 4665900 (October 2005 OM Patch) which were not present. So I applied patch 4665900, and voila, the over 500 invalid OE and ONT objects got fixed. Only 1 invalid object remained: OE_CREDIT_CARD_MIGRATE_UTIL. The metalink analyst quoted metalink note 367765.1, which states :

You can drop this invalid object. It is okay if this package is invalid. It would not cause any problem.

Please ignore it. This package will be valid and available for customers who apply 11i cc (credit card) encryption patch when they take up that functionality.

So not a single order management object is invalid now. I logged SRs for remaining invalid objects. You need to log a separate SR for each product. The Oracle SDM told me that since it was for fixing invalids, I could open SRs for products for which we didn't have license also.

I'll update this post with how all the Apps seeded invalids got fixed.