Symptoms:

Patch 31856789 completes but says fails at the end

$ adop phase=apply apply_mode=downtime patches=31856789 restart=yes abandon=no patchtop=/mnt/install_images/oracle/EBS122Patches/EBS12211/12211 workers=16 wait_on_failed_job=yes

Enter the APPS password:

Enter the EBS_SYSTEM password:

Enter the WLSADMIN password:

Validating credentials.

Initializing.

    Run Edition context  : /dodiapps/prod/prodapps/fs1/inst/apps/PROD_prodapp/appl/admin/PROD_prodapp.xml

    Patch edition context: /dodiapps/prod/prodapps/fs2/inst/apps/PROD_prodapp/appl/admin/PROD_prodapp.xml

    Patch file system free space: 503.85 GB

Validating system setup.

    Node registry is valid.

Checking for existing adop sessions.

            Session Id            :   4

            Prepare phase status  :   NOT APPLICABLE

            Apply phase status    :   ATLEAST ONE PATCH IS ALREADY APPLIED

            Cutover  phase status :   NOT APPLICABLE

            Abort phase status    :   NOT APPLICABLE

            Session status        :   RUNNING

    Continuing with existing session [Session ID: 4].

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

ADOP (C.Delta.14)

Session ID: 4

Node: prodapp

Phase: apply

Log: /dodiapps/prod/prodapps/fs_ne/EBSapps/log/adop/4/20230411_124938/adop.log

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

Applying patch 31856789.

    Log: /dodiapps/prod/prodapps/fs_ne/EBSapps/log/adop/4/20230411_124938/apply/prodapp/31856789/log/u31856789.log

***

Continue as if it were successful :

***

AutoPatch could not find a response to the above prompt

in the defaults file.

The patch has FAILED,

Please check the adpatch logs for more details.

You should check the file

/dodiapps/prod/prodapps/fs_ne/EBSapps/log/adop/4/20230411_124938/apply/prodapp/31856789/log/u31856789.log

for errors.

    [UNEXPECTED]Error occurred executing “adpatch  abandon=no restart=yes workers=16 wait_on_failed_job=yes   options=hotpatch     console=no interactive=no  defaultsfile=/dodiapps/prod/prodapps/fs1/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/mnt/install_images/oracle/EBS122Patches/EBS12211/12211/31856789 driver=u31856789.drv logfile=u31856789.log”

    [UNEXPECTED]Refer to the log files for more information.

    [UNEXPECTED]Apply phase has failed.

[STATEMENT] Please run adopscanlog utility, using the command

“adopscanlog -latest=yes”

to get the list of the log files along with snippet of the error message corresponding to each log file.

adop exiting with status = 1 (Fail)

prodapp(oracle):/mnt/install_images/oracle/EBS122WD_GOLIVE/12211/pay_us_tax_types_tab_xdf_fail/332726936591

$

Solution:

1. Take a backup of existing PAYGBTX.fmb

cd $AU_TOP/forms/US
mv PAYGBTAX.fmb PAYGBTAX.fmb.69

2. Copy PAYGBTX.fmb from patch top:33738081 to $AU_TOP/forms/US

cd /mnt/install_images/oracle/EBS122Patches/EBS12211/Post12211/33738081/pay/forms/US/
cp PAYGBTAX.fmb $AU_TOP/forms/US

3. Make sure the copied PAYGBTAX.fmb is version 120.67.12020000.81

4. Generate fmx

cd $AU_TOP/forms/US
$ORACLE_HOME/bin/frmcmp_batch module=$AU_TOP/forms/US/PAYGBTAX.fmb userid=APPS/APPS output_file=$PAY_TOP/forms/US/PAYGBTAX.fmx module_type=form compile_all=special

Oracle Reference:

SR 3-32752554737 : Applying 12.2.11 Patch 31856789 fail with: ERROR generating form “forms/US/PAYGBTAX.fmx”

Symptoms

Worker log shows below

Current system time is Wed Apr 12 23:30:22 2023

CURRENT SESSION_ID is : 670185973

CURRENT EDITION_NAME is : ORA$BASE

Uploading from the data file /dodiapps/prod/prodapps/fs1/EBSapps/appl/hxc/12.0.0/patch/115/import/US/hxczzffrmula0014.ldt

Altering database NLS_LANGUAGE environment to AMERICAN

Uploading FF_FORMULAS_F 01/01/2001 31/12/4712 HXC_PTO_TIMECARD_VALIDATION

Error occured for FF_FORMULAS_F key name FORMULA_TYPE_NAME with value OTL Time Entry Rules

A database error occurred:

  ORA-20160: Encountered an error while getting the ORACLE user account for your concurrent request.

Contact your system administrator.

ORA-06512: at “APPS.ALR_FF_FORMULAS_F_IAR”, line 1

ORA-04088: error during execution of trigger ‘APPS.ALR_FF_FORMULAS_F_IAR’

ORA-06512: at line 177

The error occurred while executing the following statement:

Solution

1. Disable below trigger

         SQL> alter trigger ALR_FF_FORMULAS_F_IAR disable;

        Trigger altered.

SQL>

2. Restart the failed worker using adctrl Option 2

Oracle Reference:

SR 3-32749852381 : Patch 31856789 fails at hxczzffrmula0014.ldt

Symptoms

US_pncat01_ldt.log in failed worker log shows below errors

Uploading from the data file /dodiapps/prod/prodapps/fs1/EBSapps/appl/pn/12.0.0/patch/115/import/US/pncat01.ldt

Altering database NLS_LANGUAGE environment to AMERICAN

Dumping from LCT/LDT files (/dodiapps/prod/prodapps/fs1/EBSapps/appl/gl/12.0.0/patch/115/import/glmlscat.lct(120.4.12020000.3), /dodiapps/prod/prodapps/fs1/EBSapps/appl/pn/12.0.0/patch/115/import/US/pncat01.ldt) to staging tables

Dumping LCT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/gl/12.0.0/patch/115/import/glmlscat.lct(120.4.12020000.3) into FND_SEED_STAGE_CONFIG

Dumping LDT file /dodiapps/prod/prodapps/fs1/EBSapps/appl/pn/12.0.0/patch/115/import/US/pncat01.ldt into FND_SEED_STAGE_ENTITY

Dumped the batch (GL_JE_CATEGORIES PN Lease Booking , GL_JE_CATEGORIES PN Lease Termination ) into FND_SEED_STAGE_ENTITY

Uploading from staging tables

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  Translatable column USER_JE_CATEGORY_NAME value changed to PN LEASE BOOKING(1)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  Translatable column DESCRIPTION value changed to Property Manager Expense Lease Booking(1)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  Translatable column USER_JE_CATEGORY_NAME value changed to PN LEASE BOOKING(2)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  Translatable column DESCRIPTION value changed to Property Manager Expense Lease Booking(2)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  Translatable column USER_JE_CATEGORY_NAME value changed to PN LEASE BOOKING(3)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  Translatable column DESCRIPTION value changed to Property Manager Expense Lease Booking(3)

  Error loading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Booking,  ORA-00001: unique constraint (GL.GL_JE_CATEGORIES_TL_U3) violated

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  Translatable column USER_JE_CATEGORY_NAME value changed to PN LEASE REVISION(1)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  Translatable column DESCRIPTION value changed to Property Manager Lease Revision(1)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  Translatable column USER_JE_CATEGORY_NAME value changed to PN LEASE REVISION(2)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  Translatable column DESCRIPTION value changed to Property Manager Lease Revision(2)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  Translatable column USER_JE_CATEGORY_NAME value changed to PN LEASE REVISION(3)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  Translatable column DESCRIPTION value changed to Property Manager Lease Revision(3)

  Error loading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Revision,  ORA-00001: unique constraint (GL.GL_JE_CATEGORIES_TL_U3) violated

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  Translatable column USER_JE_CATEGORY_NAME value changed to PN TERMINATION(1)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  Translatable column DESCRIPTION value changed to Property Manager Lease Termination(1)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  Translatable column USER_JE_CATEGORY_NAME value changed to PN TERMINATION(2)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  Translatable column DESCRIPTION value changed to Property Manager Lease Termination(2)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  Translatable column USER_JE_CATEGORY_NAME value changed to PN TERMINATION(3)

  Uploading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  Translatable column DESCRIPTION value changed to Property Manager Lease Termination(3)

  Error loading seed data for GL_JE_CATEGORIES:  JE_CATEGORY_NAME = PN Lease Termination,  ORA-00001: unique constraint (GL.GL_JE_CATEGORIES_TL_U3) violated

Concurrent request completed

Current system time is Wed May 17 18:47:50 2023

Solution:

1. Perform below data fix

sqlplus apps

SQL> create table GL_JE_CATEGORIES_TL_b4pncat01fix as select * from GL_JE_CATEGORIES_TL;

Table created.

SQL> select count(*) from GL_JE_CATEGORIES_TL_b4pncat01fix;

  COUNT(*)

———-

       503

SQL> select count(*) from GL_JE_CATEGORIES_TL;

  COUNT(*)

———-

       503

SQL> update gl_je_categories_tl set JE_CATEGORY_NAME=’PN Lease Booking’ where USER_JE_CATEGORY_NAME=’PN LEASE BOOKING’ and je_category_name=60;

2 rows updated.

SQL> update gl_je_categories_tl set JE_CATEGORY_NAME=’PN Lease Revision’ where USER_JE_CATEGORY_NAME=’PN LEASE REVISION’ and je_category_name=61;

2 rows updated.

SQL> update gl_je_categories_tl set JE_CATEGORY_NAME=’PN Lease Termination’ where USER_JE_CATEGORY_NAME=’PN TERMINATION’ and je_category_name= 62;

2 rows updated.

SQL> commit;

Commit complete.

2. Restart failed worker using adctrl Option 2

Oracle Reference:

Above data fix was provided in SR 3-32740907261 : Patch 31856789 fails at pncat01.ldt,

Symptoms:

Hook Package: HR_PERSON_ADDRESS_BK3

———————————–

217/1  PLS-00201: identifier ‘INVALID_SEE_COMMENT_IN_SOURCE’ must be declared

217/1  PL/SQL: Statement ignored

Hook: Before Process

——————–

(PER_ZA_USER_HOOK_PKG.VALIDATE_PERSON_ADDRESS) There is a parameter to the call

procedure which is not available at this hook. Check your call procedure paramet

ers. Code to carry out this hook call has not been created.

BEGIN hr_api_user_hooks_utility.clear_hook_report; END;

*

ERROR at line 1:

ORA-06501: PL/SQL: program error

ORA-06512: at “APPS.HR_API_USER_HOOKS_UTILITY”, line 891

ORA-06512: at line 1

Time when worker failed: Tue Jun 27 2023 18:53:12 AdjavaWorker has reached the maximum wait timeout specified( 36000000 ). So Exiting.

Solution:

1. Check the status of package HR_PERSON_ADDRESS_BK3

SQL> select owner,object_name, object_type,status
from dba_objects
where object_name = ‘HR_PERSON_ADDRESS_BK3’; 2 3

OWNER

OBJECT_NAME

OBJECT_TYPE STATUS

APPS
HR_PERSON_ADDRESS_BK3
PACKAGE VALID

APPS
HR_PERSON_ADDRESS_BK3
PACKAGE BODY INVALID

OWNER

OBJECT_NAME

OBJECT_TYPE STATUS

SQL>

2. Skip the worker using adctrl option 8
3. Once the patch is applied, please check if the package HR_PERSON_ADDRESS_BK3 is valid.

select owner,object_name, object_type,status
from dba_objects
where object_name = ‘HR_PERSON_ADDRESS_BK3’;

4. Run peaddasd.sql manually

cd $PER_TOP/patch/115/sql/
sqlplus apps/
SQL> @peaddasd.sql

SQL> @peaddasd.sql

PL/SQL procedure successfully completed.

Commit complete.

API User Hook Pre-Processor One Module Report

Created on 2023/09/22 04:52:12 (YYYY/MM/DD HH:MM:SS)

CREATE_PERSON_ADDRESS(Business Process API) successful.

UPDATE_PERSON_ADDRESS(Business Process API) successful.

UPDATE_PERS_ADDR_WITH_STYLE(Business Process API) successful.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Metalink Reference:

Peaddasd.sql error when applying 21900901 (Doc ID 2301823.1)

Symptoms:

12211 Patch 31856789 fails at pay_us_tax_types_tab.xdf

Time is: Tue Apr 11 2023 13:31:44

   FAILED: file pay_us_tax_types_tab.xdf on worker  1 for product pay username APPS.

Time is: Tue Apr 11 2023 13:31:44

ATTENTION: All workers either have failed or are waiting:

           FAILED: file pay_us_tax_types_tab.

xdf on worker  1.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

Current time is: Tue Apr 11 2023 13:31:44

Executing statement

  ALTER TABLE HR.PAY_US_TAX_TYPES ADD CONSTRAINT “PAY_UTT_TAX_TYPE_CODE_CHK” CHECK (TAX_TYPE_CODE IN (‘CITY’, ‘COUNTY’, ‘EIC’, ‘NW_FIT’, ‘FIT’, ‘FUTA’,

‘MEDICARE’, ‘SCHOOL’, ‘SDI’, ‘NW_SIT’, ‘SIT’, ‘SS’, ‘SUI’,  ‘HT’, ‘WC’,

‘WC2’,  ‘STEIC’, ‘SUI1′,’SDI1’, ‘MISC1’, ‘MISC2′,’CITY_MISC’ ) )

Start time for statement above is Tue May 16 18:38:10 CDT 2023

End time for statement above is Tue May 16 18:38:10 CDT 2023

Error in executing statement ALTER TABLE HR.PAY_US_TAX_TYPES ADD CONSTRAINT “PAY_UTT_TAX_TYPE_CODE_CHK” CHECK (TAX_TYPE_CODE IN (‘CITY’, ‘COUNTY’, ‘EIC’, ‘NW_FIT’, ‘FIT’, ‘FUTA’,

‘MEDICARE’, ‘SCHOOL’, ‘SDI’, ‘NW_SIT’, ‘SIT’, ‘SS’, ‘SUI’,  ‘HT’, ‘WC’,

‘WC2’,  ‘STEIC’, ‘SUI1′,’SDI1’, ‘MISC1’, ‘MISC2′,’CITY_MISC’ ) )

Error message is ORA-02293: cannot validate (HR.PAY_UTT_TAX_TYPE_CODE_CHK) – check constraint violated

Error code is    2293

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

Done calling the utility function. Return Code = [1] TimeStamp = [Tue May 16 18:38:10 CDT 2023]

Updating task with status 1

AD Worker error:

The utility FndXdfCmp returned error for the above task.

Solution

1. Skipped the above worker
2. Upgraded to HRMS RUP17 after 12211 patch

Metalink Reference:

Upgrade to 12.2.X Errors ORA-02293: Cannot Validate (HR.PAY_UTT_TAX_TYPE_CODE_CHK) When Loading PAY_US_TAX_TYPES.xdf (Doc ID 2759734.1))