FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403

R12 TRYING TO ENTER INVOICE: FRM-40735: ON-COMMIT TRIGGER AND ORA-01403 ERROR - was logged for this issue and it was determined to be an issue due to the existence of duplicate payees.
Patch 10140168 was released for this but was obsoleted and replaced with GDF patch 13857555 which delivers a select and fix scripts for this issue.

Solution


1. Download and review the readme and pre-requisites for the following patch:
For R12.0.x: Patch 13857555:R12.IBY.A: RCA: ISSUES WITH DUPLICATE PAYEE DELETION GDF: 10140168
For R12.1.x: Patch 13857555:R12.IBY.B: RCA: ISSUES WITH DUPLICATE PAYEE DELETION GDF: 10140168
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
This patch does not fix any data it just delivers the data fix scripts.
For R12.0.x:
iby_dup_payee_fix.sql 120.0.12000000.5
iby_dup_payee_sel.sql 120.0.12000000.6

For R12.1.x:
iby_dup_payee_fix.sql 120.0.12010000.3
iby_dup_payee_sel.sql 120.0.12010000.2
You can use the commands like the following:
        strings -a $XX_TOP/filename |grep '$Header'
4. Run $IBY_TOP/patch/115/sql/iby_dup_payee_sel.sql

This is a select script and does not update any data, just shows/confirms the extent of the corruption in the html output file.
5. Run $IBY_TOP/patch/115/sql/iby_dup_payee_fix.sql
This is the actual data fix script that will correct the corruption and allow the invoice to be entered.