Issue

Error while applying Patch 17919161 to upgrade from R12.2.3 to R12.2.4. This issue is a showstopper and ADOP Apply Phase is resulting in error. Unable to proceed further with the upgrade.

Error Description

The patch's u17919161.log file gives the following error:

FAILED: file wfreprole.ldt on worker  1 for product fnd username APPS.
 Time is: Sat Sep 13 2014 18:05:01
 ATTENTION: All workers either have failed or are waiting:
 FAILED: file wfreprole.ldt on worker  1.
 ATTENTION: Please fix the above failed worker(s) so the manager can continue.

The adwork001.log file gives the following error:

Loading data using  FNDLOAD function.
 **** Reseting package states in the current session ****
 FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/afrole.lct @FND:patch/115/import/US/wfreprole.ldt - 
 Calling FNDLOAD function.
 Returned from FNDLOAD function.
 Log file: /u01/oracle/VIS/fs_ne/EBSapps/log/adop/15/apply_20140913_180321/VIS_ebs122/17919161/log/US_wfreprole_ldt.log
 Error calling FNDLOAD function.

The US_wfreprole_ldt.log file gives the following error:

Uploading from the data file /u01/oracle/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/US/wfreprole.ldt
 Altering database NLS_LANGUAGE environment to AMERICAN
 Dumping from LCT/LDT files (/u01/oracle/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/afrole.lct(120.7.12020000.3), /u01/oracle/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/US/wfreprole.ldt) to staging tables
 Dumping LCT file /u01/oracle/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/afrole.lct(120.7.12020000.3) into FND_SEED_STAGE_CONFIG
 Dumping LDT file /u01/oracle/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/import/US/wfreprole.ldt into FND_SEED_STAGE_ENTITY
 Dumped the batch (FND_SECURITY_GROUPS STANDARD , FND_LOOKUP_ASSIGNMENTS UMX_CATEGORY_LOOKUP INFORMATION_TECHNOLOGY ) into FND_SEED_STAGE_ENTITY
 Uploading from staging tables
 Error loading seed data for WF_ROLE_HIERARCHY:  ROLE_NAME = UMX|FND_SYSTEM_INTEGRATION_ANALYST, SUPER_NAME = FND_RESP|FND|FND_REP_APP|STANDARD, SUB_NAME = UMX|FND_SYSTEM_INTEGRATION_ANALYST,  ORA-20002: 3825: Error '-4063 - ORA-04063: package body "APPS.FND_REQUEST" has errors
 ORA-06508: PL/SQL: could not find program unit being called: "APPS.FND_REQUEST"' encountered during execution of Rule function 'WF_ROLE_HIERARCHY.Propagate_RF' for event 'oracle.apps.fnd.wf.ds.roleHierarchy.relationshipCreated' with key '1891'.
   Error loading seed data for WF_ROLE_HIERARCHY:  ROLE_NAME = UMX|FND_SYSTEM_INTEGRATION_DEVELOPER, SUPER_NAME = UMX|FND_SYSTEM_INTEGRATION_ANALYST, SUB_NAME = UMX|FND_SYSTEM_INTEGRATION_DEVELOPER,  ORA-20002: 3825: Error '-4063 - ORA-04063: package body "APPS.FND_REQUEST" has errors
 ORA-06508: PL/SQL: could not find program unit being called: "APPS.FND_REQUEST"' encountered during execution of Rule function 'WF_ROLE_HIERARCHY.Propagate_RF' for event 'oracle.apps.fnd.wf.ds.roleHierarchy.relationshipCreated' with key '1892'.

Investigation Done

1. The error clearly states that APPS.FND_REQUEST package body has errors.

2. Upon further investigation found that APPS.FND_REQUEST package body was under 'INVALID' status under the patch file edition.

3. Tried to recompile the package body manually but it throws the following errors:

$ alter package FND_REQUEST compile body;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY FND_REQUEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1698/9   PL/SQL: SQL Statement ignored
1732/39  PL/SQL: ORA-00904: "RECALC_PARAMETERS": invalid identifier

4. On further investigation found that both the FND_REQUEST package specification and package body have errors in the pl/sql code.

Resolution

Step 1. Login to Linux terminal > change user to applmgr, and find your patch file edition:

. /u01/oracle/VIS/EBSapps.env run
  RUN File System : /u01/oracle/VIS/fs2/EBSapps/appl
  PATCH File System : /u01/oracle/VIS/fs1/EBSapps/appl
  Non-Editioned File System : /u01/oracle/VIS/fs_ne
  DB Host: ebs122.aclnz.com  Service/SID: VIS

Step 2. Set the patch file edition environment:

cd /u01/oracle/VIS/fs1/EBSapps/appl
. VIS_ebs122.env run

Step 3. Login to SQL in patch file environment in the same terminal:

sqlplus apps/apps

Step 4. Download the existing definition FND_REQUEST package and package body from patch file edition using the following commands:

spool fnd_request.sql
SELECT source from all_source where name = 'FND_REQUEST' and type = 'PACKAGE';
spool off
spool fnd_request_body.sql
SELECT source from all_source where name = 'FND_REQUEST' and type = 'PACKAGE BODY';
spool off

Step 5. In the downloaded file fix the following and simply recreate the FND_REQUEST package body and package specification on SQL prompt:

Step 5.1. FND_REQUEST package specification

Uncommented text found "e creating actual request"

Fix - comment out the above text

Step 5.2. FND_REQUEST package body

Issue-1 - Under the package look for this text "sys_con('userenv', 'current_edition_name')"

Fix - change to above text to "sys_context('userenv', 'current_edition_name')"

Issue-2 - Look for the insert statement "Insert Into Fnd_Concurrent_Requests". Within this make following fixes:

Fix - Remote 'Recalc_Parameters' and 'P_RECALC_PARAMETERS'. The reason for removing is that this column is not defined under Fnd_Concurrent_Requests table.

Step 5.3. Recreate the above updated FND_REQUEST package specification and package body on SQL Prompt, and they will be created successfully.

Step 5.4. Now we need to restart the failed worker. So type the following command on the terminal:

adctrl

Choose option-1 to find out the exact number of the failed worker

The Choose 2 to restart that worker.

Step 5.5. Now go back to another linux terminal where you were applying patch 17919161 using ADOP Utility and issue the following command:

ADOP phase=apply patches=17919161 workers=10 restart=yes

And this time ADOP will not fail and resume the rest of the patch application to upgrade to R12.2.4.

Note - it was a bit of a pain to find out the root cause of this issue, and took me couple of hours to fix it. Sharing for the benefit of the community.

Environment

• Oracle E-Business Suite Release 12.2.3 to 12.2.4 upgrade process while applying Patch 17919161
• Linux 5.7 (x86-64)

Disclaimer - The above fix is for development environment only and NOT for production environment. aclnz.com takes no liablity about it. Please contact Oracle support and raise a SR to get a patch to fix it in your Production environment.