ORA-06502

Purchase Order Workflow Errors With "ORA-06502: PL/SQL: numeric or value error" at APPS.WF_NOTIFICATION When Sending Notification [ID 579729.1]

 

Applies to:

Oracle Purchasing - Version 11.5.10.2 to 12.0.6 [Release 11.5 to 12]
Information in this document applies to any platform.
***Checked for relevance on 23-Oct-2012***

Symptoms

When opening a More Info Requested Notification for a PO with more than 20 lines, there is the following error in the notification :

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.WF_NOTIFICATION", line 5382
ORA-06512: at line 5

The wfstatus output for the PO shows the following error :

Error Name: WF_ERROR
Error Message: [WF_ERROR] 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-06502: PL/SQL: numeric or value error: character string buffer too small
Wf_Notification.runFuncOnBody(150360, text/html) Wf_Notification.GetBody(150360, text/html)
WF_NOTIFICATION.GetFullBody(nid => 150360, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 150360, r_ntf_pref => MAILHTML)' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'.
Error Stack:
ERROR_STACK= WF_MAIL.GetLOBMessage3(150360, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Wf_Notification.runFuncOnBody(150360, text/html) Wf_Notification.GetBody(150360, text/html)
WF_NOTIFICATION.GetFullBody(nid => 150360, disptype => text/html) WF_MAIL.GetLOBMessage3(nid => 150360, r_ntf_pref => MAILHTML), Step -> Getting text/html body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 150360)
WF_XML.Generate(oracle.apps.wf.notification.send, 150360) WF_XML.Generate(oracle.apps.wf.notification.send, 150360)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 150360, WF_XML.Generate) Wf_Event.dispatch_internal()

The issue can be reproced at will with the following steps :
1. Create a PO with more than 20 lines
2. Submit the PO for approval
3. Log in as approver
4. Request for more information from the "Standard Purchase Order 1234 for USD 999,999.99 requires your approval" notification
5. Log in as the buyer
6. Open the "More Information Requested: Standard Purchase Order 1234 for USD 999,999.99 requires your approval" notification.

Cause

When the line details exceed the available limit of the notification it gives the ORA-06512. This is happening because of the 32K size limit.

This is explained in the following bug:
Bug 6932794 MORE INFO REQUESTED NOTIFICATIONS ERROR WITH ORA-06502: PL/SQL: NUMERIC OR VALUE

Solution

To implement the solution, execute the following steps:

For Release 11.5.10:

1. Download and review the readme and pre-requisites for Patch 7370646.

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
POXWPA1B.pls 115.206.11510.62
POXWPA7S.pls 115.6.11510.2
POXWPA7B.pls 115.38.11510.23
Use the following sql:
select text
from dba_source
where line = 2
and name in ('PO_REQAPPROVAL_INIT1','PO_WF_PO_NOTIFICATION');

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

For Release 12:

 1. Download and review the readme and pre-requisites for Patch 7218243 - Procurement R12.0 Update July 2008 or higher
or
One-off Patch 7612490 which indirectly includes the fix for this issue.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
POXWPA1B.pls-120.46.12000000.13 or higher
POXWPA7S.pls-120.0.12000000.2 or higher
POXWPA7B.pls-120.8.12000000.3 or higher

You can use the commands like the following:
strings -a $PO_TOP/patch/115/sql/POXWPA1B.pls | grep -i '$Header:'
strings -a $PO_TOP/patch/115/sql/POXWPA7S.pls | grep -i '$Header:'
strings -a $PO_TOP/patch/115/sql/POXWPA7B.pls | grep -i '$Header:'
5. Retest the issue.

6. Migrate the solution as appropriate to other environments.