11.5.10.2 MP Mandatory co-requisite patch for Service module

If you are planning to upgrade from a pre-11.5.10 version to 11.5.10, and are using Service module, there is a mandatory patch 5204153 which needs to be merged with the 11.5.10 maintenance pack patch 3480000. This came to light, when our team was checking all the post 11.5.10 functional patches. Readme of this patch says: "Customers who are upgrading to 11.5.10 from prior releases should merge this patch with 11.5.10 before applying 11.5.10 maintenance pack to avoid data corruptions". Since we missed this patch in our first iteration, I had a long SR with Oracle to find out why we can't apply this after applying the maintenance pack. Some excerpts from the SR:

Question: If this patch is not applied does it cause MP/CU2 to fail ?
Answer: No, there won't be any build failures.
.
Question: Can customer handle this post 11510.2 upgrade either thr' a documented post step in 11510.2 or thr' RPL.
Answer: No. This needs to be merged with the 11i10/Cu1/Cu2 patches. The script in question populates a value in one of the columns if the column value is null. CU2 version populates a wrong value.

This patch is a mandatory pre-req for all Service customers using Charges functionality. Not required for Service customers if they do not use Charges module. In that sense it is conditional.

Let me know if you have any additional questions.

1. Question: In case someone does a fresh install from CDs, how is that person supposed to know about this patch. There is no mention of this patch in the 11.5.10.2 installation guide or the maintenance pack readme.

Answer:
There is currently a documentation Bug logged to update the README of all 11.5.10 patches to inclulde Patch 5204153 as a pre-req.

Bug 5259247, entitled, 3480000 SHOULD HAVE Patch 5204153 ADDED AS A PREREQUISITE contains the following:
"3480000 11.5.10.CU.2 should have Patch 5204153 added as a prereq.
.
5204153 has been added to the Recommended Patch list.
What appears to happen is that an upgrade script, csxchest.sql which is contained in 3480000 has a major flaw. They have issued a new Patch 5204153 as the repair to this patch.
With instructions to merge this new patch with the 3480000. We feel that this patch should in that case be included as a prerequisite for Patch 3480000. Please see the readme instructions at the bottom on this page that they have included on Patch 5204153.
.
"This patch (5204153)needs to be downloaded prior to applying any one of the following patches:
.
a. Patch 11i.SCM_PF.J(3384350)
b. Oracle Applications Release 11.5.10 (3140000)
c. 11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1 (3240000)
d. 11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2 (3460000)
e. ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK (3480000)
.
After downloading this patch please run the admrgpch utility to merge the current Standalone patch driver(s) and the Maintenance Pack driver(s)."

2. Question: Also if a client wants to implement service module later, how is he supposed to apply this patch.
Answer: As far as I understand this Patch is specific to making sure data from pre-11.5.10 is migrated correctly. Therefore, a fresh install or new data will not be impacted if this patch is not applied.

3. Question: What is the problem if this patch is not applied ? Do we lose some functionality?
Answer:
Patch 5204153 includes the following 8 files:
cs patch/115/sql cs00aaa.sql 115.6.11510.2 +
cs patch/115/sql cs00aau.sql 115.5.11510.2 +
cs patch/115/sql cs00sca.sql 115.6.11510.2 +
cs patch/115/sql cs00sia.sql 115.6.11510.2 +
cs patch/115/sql cs00snc.sql 115.5.11510.2 +
cs patch/115/sql cs00ssr.sql 115.8.11510.2 +
cs patch/115/sql cs22gchu.sql 115.8.11510.2 +
cs patch/115/sql csxchest.sql 115.0.11510.2 +

The first 7 files are specific to upgrading pre-11i data.
csxchest.sql contains the following:

REM | FILENAME |
REM | csxchorg.sql |
REM | |
REM | DESCRIPTION |
REM | Populate new org_id column |
REM | |
REM | This script populates the new org_id column on the Charges |
REM | table CS_ESTIMATE_DETAILS. The value is retrieved from the |
REM | parent Service Request. |
REM | The following new columns were added for 11.5.9 upgrade. |
REM | Transaction_Inventory_Org: |
REM | The ware house specified in OM order headers table. |
REM | Charge_Line_Type: |
REM | Lookup code which indicates type of charge line. |
REM | A value of 'ACTUAL' populated for every charge line. |
REM | Ship_To_Contact_Id: |
REM | Ship to contact for specified ship to party site |
REM | from OE Lines. |
REM | Bill_To_Contact_Id: |
REM | Invoice to contact for specified bill to party site |
REM | from OE Lines. |
REM | Generated_By_Bca_Engine_Flag: |
REM | Default will be 'N' for every Charge Line. |
REM | Bill_To_Party_Id: |
REM | The party id for the specified bill to org id in Charges form. |
REM | Ship_To_Party_Id: |
REM | The party id for the specified ship to org id in Charges form. |
REM | List_Price: |
REM | This is the selling price of the charge line item. |
REM | Line_Submitted: |
REM | Values are 'N' and Null. A value of 'N' is populated for all ACTUAL|
REM | Charge Lines which are NOT submitted to OM,all others will be |
REM | populated with NULL. |
REM | This column is only to improve performance in the context of ACTUAL|
REM | lines, and do not have any functional significance. |
REM | A NULL does not necessarily indicate Line was submitted to OM. |
REM | |
REM | |
REM | sql abc patch/115/sql csxchorg.sql none none |
REM | none sqlplus phase=upg batchsize |



My questions for you:
A. Are you doing a fresh install of 11.5.10 or are you upgrading?
B. If you are upgrading, what version are you upgrading from?

When I hear back from you I will also post a question to development requesting an update
on the ER and also to confirm if the patch is required if doing a Fresh Install
vs. an Upgrade. As far as I can see the Patch is specific to making sure data
is correct when doing an UPGRADE from pre 11.5.10 and not a fresh install.

Thanks,
Adriana

We are doing an upgrade from 11.5.9 -> 11.5.10.2

I posted to development the issue you are encountering in addition to the following questions:
"1. For an upgrade from 11.5.9 -> 11.5.10, should this patch have been applied before the upgrade?
2. If so, what can the customer do to fix the incorrect data?
3. Does this patch need to be applied for a Fresh Install (looking at the 8 files included in the patch, it doesn't look like it, but I would like to have confirmation)?"

Development responded with the following:
1. Yes.
2.Its very dificult to guess as they have missed a vital step. At this point we suggest them to take a backup of all tables touched by all these script and run these in a test instance.
3. Even for a fresh instance if these files are run should not cause any issue

As per mising this mandatory steps I would suggest to take a backup and try out all these script one at a time. Also try to see what each script is doing and in case of doubt consult development."

Therefore, can you try the suggestion made by development.
Please update the Tar if you encounter any problems so I can forward this to them.
Let me know if you have any questions.

Hi Adriana,

Isn't there any conceivable way to mitigate this, if the 5204153 patch is missed during upgrade ? Can't we have a data fix for the data which is not migrated properly from 11.5.9 to 11.5.10 ?

- Vikram

Hi Vikram,

I will ask development to see if they are able to provide a data fix, if so, I will then proceed to log a bug. I'll let you know what I hear back.

Regards,
Adriana

Hi Vikram,

Development made the following update with respect to this request:
"This patch is very critical and that is the reason we have put that on recommended patch list. If they missed this patch it will be very difficult to help them out here as the 115102 process will update the data here as mentioned in the bug 5259247 . Are they already in 115102 production ? Redoing 1159 to 11510 upgrade in the correct way is only I can suggest here. Figuring out what makes and what breaks will be extremely difficult here."

Therefore, it looks like there is no alternative if the patch was missed other than redoing the up
grade. Let me know if you have any other questions.

Thanks,
Adriana

QUESTION
========
The Read me for Patch 5204153 says: "Customers who are upgrading to 11.5.10 from prior releases should merge this patch with 11.5.10 before applying 11.5.10 maintenance pack
to avoid data corruptions".
Can this patch be applied after applying the maintenance pack or is there any alternative available?

ANSWER
======
The Patch cannot be applied after the upgrade nor are their any alternative fixes available if
the Patch was not applied before the upgrade.

The reason is that this Patch is responsible for specific data updates and if not done, the fixes for this data and any other data that it can be affect cannot be determined.

If the patch was not applied there is no alternative other than re-doing the upgrade and applying the patch when required.