11.5.10.2 - Supplier Notifications Not Sent Due to 'ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: 2018: Unable to generate the notification XML' and ORA-1403 (Doc ID 986151.1)

Last updated on AUGUST 21, 2016

Applies to:

Oracle Purchasing - Version 11.5.10 to 11.5.10.4 [Release 11.5]
Information in this document applies to any platform.
***Checked for relevance on 18-Mar-2015***

Symptoms

When attempting to deliver the approval confirmation to the supplier on a standard Purchase Order approval notification,  the workflow notification fails with the error below:

Additionally this issue can cause an issue when opening FYI supplier notification. The output shows 'no data found'.

ERROR
-------

Error Message

ERROR_MESSAGE=3835: Error '-20002 - ORA-20002:
2018: Unable to generate the notification XML.
Caused by: 2020: Error when getting notification content.
Caused by: ORA-01403: no data found
ORA-01403: no data found
wf_notification.GetAttrDoc2(3172191, PO_EMAIL_HEADER, text/html)
Wf_Notification.GetAttrDoc(3172191, PO_EMAIL_HEADER, text/html)
Wf_Notification.GetText(3172191, text/html)
Wf_Notification.GetBody(3172191, text/html)
WF_NOTIFICATION.GetFullBody(nid => 3172191, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 3172191, r_ntf_pre' encountered during execution
of Generate function 'WF_XML.Generate' for event
' oracle.apps.wf.notification.send'.

Error WFE_DISPATCH_GEN_ERR

Error Stack

WF_MAIL.GetLOBMessage3(3172191, WFMAIL, 2020: Error when getting notification content.
Caused by: ORA-01403: no data found
ORA-01403: no data found
wf_notification.GetAttrDoc2(3172191, PO_EMAIL_HEADER, text/html)
Wf_Notification.GetAttrDoc(3172191, PO_EMAIL_HEADER, text/html)
Wf_Notification.GetText(3172191, text/html)
Wf_Notification.GetBody(3172191, text/html)
WF_NOTIFICATION.GetFullBody(nid => 3172191, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 3172191, r_ntf_pref => MAILHTM2), Step -> Getting text/html body)
WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 3172191)
WF_XML.Generate(oracle.apps.wf.notification.send, 3172191)
WF_XML.Generate(oracle.apps.wf.notification.send, 3172191)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 3172191, WF_XML.Generate)
Wf_Event.dispatch_internal()

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create new Release
2. Set the supplier notification method to 'Email' before submit
3. Submit for approval the new PO and approve


In review of the wfstat output it is noted that the item type and item key are not being passed as follows:

PO_EMAIL_BODY PLSQLCLOB:PO_EMAIL_GENERATE.GENERATE_HTML/472797:PO
PO_EMAIL_HEADER PLSQL:PO_EMAIL_GENERATE.GENERATE_HEADER/472797:PO

What should be passed (and what is resolved in <Bug 7336070>) is the item type and item key seen in POAPPRV:472797-658631 below:

'PLSQLCLOB:PO_EMAIL_GENERATE.GENERATE_HTML/POAPPRV:472797-658631'

Instructions to produce the wfstat.sql can be found in Note <> 'Running WFSTATUS and WFRETRY For Oracle Purchasing Workflows'

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms