ORA-1403

Unable To Create A New Supplier Site. ORA-1403 Error Arises In AP_VENDOR_PUB_PKG After Patch:8889211 [ID 1053597.1]

Applies to:

Oracle Payables - Version: 12.0.0 to 12.1.3 - Release: 12.0 to 12.1
Information in this document applies to any platform.
AP Vendors Package: AP_VENDOR_PUB_PKG
Payables Invoices Entry form - APXINWKB

Symptoms

After applying Patch:8889211 users are unable to create a new Supplier Site due to the following error:
   --- --- ---
   Address and Site Creation - Unable to create address and sites because of the following error when
   trying to create the site in operating unit [Test OU]: [ORA-01403: no data found in Package
   AP_VENDOR_PUB_PKG Procedure Validate_Vendor_Site]. Either resolve this issue and try again
   or deselect the operating unit with the error and continue.
   Note: any new address requires at least one site in order to be created.
   --- --- ---

After several attempts the customer was able to save the Site but when entering Invoices and selecting the Site and tabbing out the following error occurs:
   --- --- ---
   FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403
   --- --- ---

Additional error message
   --- --- ---
   Address and Site Creation - Unable to create address and sites due to the following error when trying to
   create the site in operating unit
   ORA-20001: APP-SQLAP-10000: ORA-1403: no data found occurred in
   AP_APXVDMVD_PKG.INITIALIZE<- with parameters (SHIP_TO_LOCATION_ID =
   BILL_TO_LOCATION_ID = FOB_LOOKUP_CODE = FREIGHT_TERMS_LOOKUP_CODE =
   TERMS_ID = PAYMENT_METHOD_LOOKUP_CODE = in Package AP_VENDOR_PUB_PKG
   Procedure Create_Vendor_Site. Either resolve this issue and try again or deselect the operating unit with
   the error and continue.
   Note: any new address requires at least one site in order to be created.
   --- --- ---

Cause

Patch:8889211 was defective as detailed in Bug:9066129 - FRM-40735-WHEN-VALIDATE-ITEM TRIGGER RAISED UNHANDLED EXCEPTION ORA-01403.  The Create_Vendor_Site procedure was passing a null value for the Exclusive_Pay_Flag to IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee which was causing this error.

The replacement patch for Patch:8889211 is now  Patch:9066129 - FRM-40735-WHEN-VALIDATE-ITEM TRIGGER RAISED UNHANDLED EXCEPTION ORA-01403 which delivers for R12.0.x patch/115/sql/appvndrb.pls version 120.43.12000000.131 and for R12.1.x patch/115/sql appvndrb.pls 120.78.12010000.72.

However, it is best to apply the latest version available which is currently via Patch:10218075 -  INACTIVE PAYMENT METHOD AT SUPPLIER CAUSING CORRUPTION IN CREATION OF ITS SITES.  This is available for both R12.0.x and R12.1.x customers (no password required).

Solution

To implement the solution:

   1. Download and review the readme and pre-requisites for the following patch:
          For R12.0.x  -> Patch:10218075:R12.AP.A
          For R12.1.x  -> Patch:10218075:R12.AP.B

   2. After apply this patch, confirm the following file versions:
           For R12.0.x  ->  patch/115/sql appvndrb.pls 120.43.12000000.141
           For R12.1.x  ->  patch/115/sql appvndrb.pls version 120.78.12010000.93
       You can use the commands like the following:
              strings -a $AP_TOP/patch/115/sql/appvndrb.pls |grep '$Header'

   3. Migrate the solution as appropriate to other environments.