Monday Feb 15, 2016

Understanding the Workflow debugging scripts wfmlrdbg.sql and wfbesdbg.sql

Introduction

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.

1.wfmlrdbg.sql

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

    3.Notification attributes

    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.

Error Notification(s)

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.

WF_NOTIFICATION_OUT AQ

 It provides notification message payload and status information whether it is processed by the Mailer

WF_NOTIFICATION_IN AQ

Contains the recipient payload message and status information when placed by Notification mailer

2. wfbesdbg.sql

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

    1.Event Information

    2.Subscription Information

    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



Understanding the Workflow debugging scripts wfstat.sql

Introduction

While logging a bug in Workflow area, it is always advisable to provide output of some of the diagnostic scripts such as wfstat.sql. This blog provides a detailed description about the usage of and the information this script provide. Output of wfstat.sql script provides workflow activity details for a given workflow process. 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.

1.wfstat.sql

This script can be used to debug the workflow activity issues for a given workflow. It can be run using the below command. Use spool command to generate the output of this into a text file.


SQL>spool wfstat.txt
SQL>sqlplus apps/apps @$FND_TOP/sql/wfstat.sql <item type> <item key>
SQL>spool off;


It generates a text file called wfstat.txt that contains output of this script. You can get the following information from this:

  1. Workflow item type and itemkey
  2. Workflow activities' start time and end time
  3. Parent process and child process information
  4. Status and result of each activity
  5. Start date and End date of each activity
  6. Errored Activities and Error Process Activity Statuses
  7. Notification details with notification id and notification recipient
  8. Performer of each activity.

The following section explains each item from the output file:

WorkFlow Item

It provides item type, item key, start date and end date for the workflow item. It also provides the root activity details for the same.

Activity Statuses

It provides start date, end date, status, result for each of the activities. In addition to that it provides the performer user and notification ID for Notification activities. It also provides the sequence of execution of the activities.

Activity Statuses History

It provides status history of the all the actions performed on the different activities

Notifications

It provides information about a notification such as Notification ID, status, message name, begin date, end date, from-user, to-user, subject.

Errored Activities

It provides all the activities information that are resulted into ERROR status.

Error Process Activity Statuses

It provides the WF ERROR process activity statuses in case of errors.

Item Attribute Values

It provides information about attribute such as  attribute name, attribute type, attribute value.



About

This blog is dedicated to bring latest information on Oracle Workflow new features, best practices, troubleshooting techniques and important fixes directly from it's Development Team.

Search

Archives
« May 2016
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
    
       
Today