Workflow error in AP payments (ap_payment_event_wf_pkg.rule_function' for event 'oracle.apps.ap.payment

Issue:

To SYSADMIN
Sent 18-Jun-2015 12:44:57
ID 1191209

An Error occurred in the following Event Subscription: Event Subscription

Event Error Name: WFE_DISPATCH_RULE_ERR
Event Error Message: 3825: Error '100 - ORA-01403: no data found
ORA-01403: no data found' encountered during execution of Rule function 'ap_payment_event_wf_pkg.rule_function' for event 'oracle.apps.ap.payment' with key '58478'.
Event Error Stack:
Wf_Event.dispatch_internal()

Cause:
AP_PAYMENT_EVENT_WF_PKG.RULE_FUNCTION has errors

When attempting to do remittance note test in vendor site set to email address then it errored out.

Technical Description:

When attempting to do remittance note test in vendorsite set to email address the following error occurs. Event Error Name: WFE_DISPATCH_RULE_ERR Event Error Message: 3825: Error 100 - ORA -01403:no data found ORA -01403:no data found encountered during execution of
Rule function 'ap_payment_event_wf_pkg.rule_function' for event 'oracle.apps.ap.payment'
with key '10040'.

Event Error Stack: Wf_Event.dispatch_internal()

Resolution:

The error occurs because there was some coding error in the package AP_PAYMENT_EVENT_WF_PKG, where the org context was not set properly for R12. So changed the code for proper setting of org context by the following code:
mo_global.set_policy_context('S',l_org_id);

And after the org context is set , still the work flow was not getting set and the reason was that procedure get_remit_email_address didn't fetch any data with respect to the email address as the query written to fetch the query was wrong for R12 as in R12 the data stored for payment details tab in the supplier window id stored in IBY_EXTERNAL_PAYEES_ALL instead of po_vendor_sites.

So changed the query from

SELECT remittance_email
INTO p_email_address
FROM po_vendor_sites
WHERE vendor_id = l_vendor_id
AND vendor_site_id = l_vendor_site_id;
to
SELECT remit_advice_email
INTO p_email_address
FROM IBY_EXTERNAL_PAYEES_ALL
WHERE supplier_site_id = l_vendor_site_id;


Solutions.:-


1) Please download and review the readme for patch 6119080:R12.AP.A.
6119080:R12.AP.A - brings appewfpb.pls 120.2.12000000.2 which just fixes the bug 6119080 (AP_PAYMENT_EVENT_WF_PKG.RULE_FUNCTION).
2) In a test environment, please apply Patch.6119080:R12.AP.A
3) Please confirm the following file versions:
$AP_TOP/patch/115/sql/appewfpb.pls 120.2.12000000.2
You can use the commands like the following:
strings -a ffffffff |grep '$Header'
4) Bounce all application services (adstpall.sh apps/<apps> & adstrtal.sh apps/<apps>).
5) Please retest the issue.
6) If the issue is resolved, please migrate the solution as appropriate to other environments.