Search This Blog

Monday, August 26, 2013

IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:Fatal: Exception when attempting to perform exact match for given payee context

Payable Open Interface Import Issue:

The issue is related to Payable Open Interface Import.
While loading invoices, we found that Payable Open Interface has fetched the invoices but those invoices are not found in the Rejection Table AP_INTERFACE_REJECTIONS.

While searching the log file, we found that those invoices has failed in to "Derive Party ID Validation".
On the Debug Switch and we will find that invoices failed in Exception:

Check Invoice Validation 17:
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:Fatal: Exception when attempting to perform exact match for given payee context.6
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:SQL code: -14226
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:SQL err msg: ORA-01422: exact fetch returns more than requested number of rows6
IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext:EXIT 

We raised the SR with Oracle and found there is data fix required for the issue.
No RCA has provided by Oracle on the Issue.

Solution:
Data Fix Patch
13857555 - RCA: ISSUES WITH DUPLICATE PAYEE DELETION GDF: 10140168
Post patch - Run the script brought in by the Data fix patch.
1.      Run the script $IBY_TOP/patch/115/sql/iby_dup_payee_sel.sql 
2.      log file 10140168-diag-.html which shows affected transactions.
3.      Check the report 10140168-diag-.html to identify/review the affected transactions. 
4.      Run $IBY_TOP/patch/115/sql/iby_dup_payee_fix.sql to fix the duplicate records.
5.      This will generate log file 10140168-fix-.html.This log will also list the backup table names

6.      Verify.
a) Check the results. Run the following query and verify that no records are returned.

SELECT
A.EXT_PAYEE_ID , 
A.PAYEE_PARTY_ID , 
A.PAYMENT_FUNCTION , 
A.EXCLUSIVE_PAYMENT_FLAG ,
A.PARTY_SITE_ID ,
A.SUPPLIER_SITE_ID ,
A.ORG_ID ,
A.ORG_TYPE ,
A.DEFAULT_PAYMENT_METHOD_CODE ,
A.ECE_TP_LOCATION_CODE ,
A.BANK_CHARGE_BEARER ,
A.BANK_INSTRUCTION1_CODE ,
A.BANK_INSTRUCTION2_CODE ,
A.BANK_INSTRUCTION_DETAILS ,
A.PAYMENT_REASON_CODE ,
A.PAYMENT_REASON_COMMENTS ,
A.INACTIVE_DATE ,
A.PAYMENT_TEXT_MESSAGE1 ,
A.PAYMENT_TEXT_MESSAGE2 ,
A.PAYMENT_TEXT_MESSAGE3 ,
A.DELIVERY_CHANNEL_CODE ,
A.PAYMENT_FORMAT_CODE ,
A.SETTLEMENT_PRIORITY ,
A.REMIT_ADVICE_DELIVERY_METHOD ,
A.REMIT_ADVICE_EMAIL ,
A.REMIT_ADVICE_FAX 
FROM iby_external_payees_all a
WHERE EXISTS (SELECT 'duplicates' 
                                FROM iby_external_payees_all b 
                                WHERE a.payee_party_id = b.payee_party_id 
                                AND a.payment_function = b.payment_function 
                                AND NVL(a.party_site_id, '0') = NVL(b.party_site_id, '0') 
                                AND NVL(a.supplier_site_id, '0') = NVL(b.supplier_site_id, '0') 
                                AND NVL(a.org_id, '0') = NVL(b.org_id, '0') 
                                AND NVL(a.org_type, '0') = NVL(b.org_type, '0') 
                                AND a.ext_payee_id <> b.ext_payee_id
                                )
ORDER BY a.PAYEE_PARTY_ID, a.last_update_date DESC;


b) Also check the log file 10140168-fix-.html

No comments:

Post a Comment