By Narsimhulu Sanika-Oracle on Feb 15, 2016
While logging a bug in Workflow area, it is always advisable to provide output of some of the diagnostic scripts such as wfmlrdbg.sql and wfbesdbg.sql. This blog provides a detailed description about the usage of and the information this script provide. Output of wfmlrdbg.sql provides details about a particular notification that are used to debug any issues with a Workflow Notification. wfbesdbg.sql is used to debug the event and subscription related issues in Business Event System.
This script can be used to debug the notification related issues for Mailer. It can be run using the below command.
SQL>sqlplus apps/apps @$FND_TOP/sql/wfmlrdbg.sql <notification_id>
It generates a html file with name format wfmlrdbg<notification ID>.html. You can get the following information from this
1.Notification information like message type, message name, context, recipient ..etc
2.Recipient details and its members
4.Recipient routing rules
5.Notification message details in WF_DEFERRED AQ
6.Notification message details in WF_NOTIFICATION_OUT AQ
7.Message payload from generate function
8.Message payload in WF_NOTIFICATION_OUT AQ
9.Recipient message payload in WF_NOTIFICATION_IN AQ
10.Notification Message definition in wft file
11.Notification Mailer component details
The following section explains each item from the output file:
Notification Item Information
It provides the notification item information such as of notification id,message type,message name,recipient role,mail status.
Notification Recipient Role Members
Figure - 1.
As seen the figure -1 it provides the Notification Recipient Role members information such as user_name, email address, notification preferences, etc.
Notification Recipient Role Information
Figure - 2 .
As seen the figure -2 it provides the Notification Recipient Role information such as role name, email address, notification preferences, etc.
Notification Recipient Routing Rules
It provides the recipient routing rules information such as Action, Begin Date, End Date, Message Type, Message Name, Action Argument, Name, Type, Value.
Notification Message Attribute Values
It provides Notification message attributes information such as its name, value, format.
Notification Attribute Values
It provides Notification attribute values information such as name, number value, date value, text value.
Notification Message Result Attribute Values
It provides lookup type, lookup code values.
Notification User Comments
It provides from-role, from-user, action, user comment values.
WF_DEFERRED Queue Status
It provides Message Id, Message State, Consumer Name, Queue, Event Name, Event Key, Enqueue Time, Dequeue Time of wf_deferred Queue.
WF_ERROR Queue Status
All the errors in notification goes into this queue and it provides errors information such as Error Message, Error Stack.
It provides the error notification information such as Notification Id, Date, Notification Subject, Error Message, Error, Error Stack.
Profile Option Values
It provides the base url information.
Workflow Notification Tags Value
It provides notification tags information such as name, tag id, action, pattern.
GSC WF_MAILER Component Parameters
It provides the component parameters information such as component status, Inbound agent name, out bound agent name.
It provides notification message payload and status information whether it is processed by the Mailer
Contains the recipient payload message and status information when placed by Notification mailer
This script can be used to debug the event and subscription related issues in BES. It can be run using the below command
SQL>sqlplus apps/apps @$FND_TOP/sql/wfbesdbg.sql <eventName> <eventKey>
It generates a html file with name format wfevtdbg<evntKey>.html. You can get the following information from this
3.Agent listeners components status
4.Event details in WF_DEFERRED ans WF_JAVA_DEFERRED queue
5.Event details in WF_ERROR ans WF_JAVA_ERROR queue