Debugging the Error Workflow

This post describes various methods of debugging oracle workflow. Oracle workflow is generally used to integrate the ERP business process into Oracle applications

Initial Level Checks:

1. Oracle workflow requires the Workflow Background process to be scheduled for every 10 minutes in the system with the following parameters:

• Y,N,N
• N,Y,N
• N,N,Y

1. First get the concurrent program ID:

   SELECT concurrent_program_id  FROM fnd_concurrent_programs_tl

  WHERE user_concurrent_program_name  = 'Workflow Background Process';

2. Check for the programs frequency of execution:

   SELECT request_date,actual_date,actual_completion_date,phase_code,status_code

   FROM fnd_concurrent_requests

   Where concurrent_program_id = <concurrent_program_id>

   AND argument_text = ', , , N, Y, N'

   AND resubmit_interval = 10

   AND resubmit_interval_unit_code = ‘MINUTES’

   Order By 1 Desc

3. Check whether the program is scheduled and running for every 10 mins

4. Similarly repeat step 2 for other arguments.

Check whether all the agent listeners are up and running as shown below:

     Navigation Path:

a.      Go to ‘Workflow Administrator Web Applications’ responsibility and click on ‘Workflow Manager’ as shown below.

After undergoing the initial level checks we need to categorize the workflow issue to any one of the below category:

1. Workflow not running
2. Notifications not being fired.

Workflow not running:

We assume that the workflow has been initiated and it’s not running further. We need to get the workflow name and itemkey for the workflow not running. Itemkey is a key to identify the workflow instances below are some of the examples for Itemkey

OM Header workflow   

SELECT header_id FROM oe_order_headers_all

WHERE order_number = <order_number>

AND   org_id = <Organization of the order>;

OM Line level workflow

SELECT line_id FROM oe_order_lines_all

WHERE header_id = <order header_id>

AND   org_id = <Organization of the order>;

PO Approval Workflow

SELECT wf_item_key FROM po_headers_all
WHERE segment1 = <PO Number>

AND   org_id = <Organization of the order>;


PO Approval Workflow
SELECT wf_item_key FROM po_headers_all
WHERE segment1 = <PO Number>

AND   org_id = <Organization of the order>;

Requisition Workflow  

SELECT wf_item_key FROM po_requisition_headers_all
WHERE segment1 = <REQ Number>

AND   org_id = <Organization of the order>;

After getting the workflow name and itemkey for the workflow which is not running follow the below steps:

·         Go to workflow status monitor.

 Enter the Workflow type and Itemkey of the workflow

·         Workflow may be in Active/Error/Complete/Defferred status

Ø       Complete - Workflow has successfully completed.

Ø       Error       - Workflow has error out. 

Ø       Active      - Workflow is still active.

Ø       Defferred - Workflow is waiting to be picked up by workflow

                       background engine.

        

      Active:

·         Select the workflow which we need to troubleshoot and click on the activity history.

·         Ensure that the recent activity is not in deferred status if so run the workflow background process.

·         Click on the activity to get the details

     If the function type is PL/SQL then debug the package mentioned in the Function column (e.g OE_STANDARD_WF.STANDARD_BLOCK).

Retry

·         Select the recent activity and click on the retry button if the activity shows as error to restart the workflow.

·         If the error still exists then click on the error to debug the package as done for the active status.

Deferred

·         If the recent status is deferred then check the workflow background engine status. Also check the deferred queue in the wf_deferred_table_m for the specific workflow

Select * from wf_deferred_table_m where corrid = ‘APPS’ + <item_type>;

Notifications not getting fired:

  All the workflow notifications are stored in the WF_NOTIFICATIONS table.

Select * from WF_Notifications where subject = <Subject of the notification>;

          

Column Descriptions:

• Mail_status:

ü       Sent: - Mails are sent to the recipients.

ü       Error: - Mails are not delivered to the recipient may be due to invalid email address.

• Status:

ü       Open: - Mails have been sent to the recipient and not yet viewed by the user.

ü       Closed: - Mail has been viewed by the recipient.

ü       Error: - Mail server is unable to deliver the message.

ü       Cancelled :- Workflow got cancelled

ü       Timeout :- Notification got timed out

• Context: It has the itemtype and itemkey separated by Colons.
• Recipient Role : It has the recipient roles
• 
  

Select * from WF_roles where name = <recipient_role>;

Select * from wf_user_roles where role_name = <recipient_role>

Common Reasons:

• Recipient has left the company so the role has expired.
• Invalid email address.
• Email address may be different from the HR tables in such cases run the concurrent program “WF Synchronize Local Tables”
• If emails got struck in the mail server contact the DBA team.
• If emails are not sent to the correct recipients then check the setups.
• Notification preference of the recipients may be Query/Summary which will not send email notification but can be viewed from the recipient notification worklist.

Other workflow Tips:

• Always clear the cache if you are not able to open the notifications listed in the notification worklist.
• If you are not able to view the workflow status diagram of the workflows owned by other users then set the workflow Administrator privilege to “*”

Workflow Configuration Page 

• “Purge Obsolete Workflow runtime data” concurrent program should be running every day to improve the performance of the workflow.
• Use the Set Override address in Test instances to check the email notifications.

Process of Debugging the Error Workflow :- 
=====================================

First Step get the item_key which got error out for the workflow

From Front end Applications you can get it from the
Workflow Administrator :-> Status Monitor =>  Give Internal name , Workflow Status, Workflow Started and
Click on Go button. It will list out the Workflow items based on the Criteria Selected.

From Back end :-

select * from wf_items;
WF_Items Table List out all the item_keys and item_types of the workflow.

Second Step :-

Once You get the Item_key, From Front End Applications, when we click on the Item key error , the error message would be something like this

Error message : 3205: 'xxxx' Is Not A Valid Role Or User Name

we should not consider this error as a main error , which is causing the problem for the workflow.

How to debug more on this to get the exact error message is as follows :-

select * from wf_items -> will get the item_keys

select * from WF_ITEM_ACTIVITY_STATUSES where item_key=<enter errored item key>;

select * from WF_ITEM_ATTRIBUTE_VALUES where item_key=<enter errored item key>;

WF_ITEM_ATTRIBUTE_VALUES table will give you the exact error message occurred value in the column 'TEXT_VALUE'

Oracle Workflow Basics

Oracle Workflow is unique in providing a workflow solution for both internal processes and business process coordination between applications. It automates and streamlines business processes both within and beyond our  enterprise, supporting traditional applications based workflow as well as e-business integration workflow. This technology enables modeling, automation, and continuous improvement of business processes, routing information of any type according to user-defined business rules. Oracle Workflow can route supporting information to each decision maker in a business process, including people both inside and outside our enterprise.

Oracle Workflow Builder is a graphical tool that help us  create, view, or modify a business process with simple drag and drop operations. Using the Workflow Builder,we can create and modify all workflow objects, including activities, item types, and messages etc.

Workflow Components
Depending on the Business process logic we may need to create/modify some or all of the of the following workflow process components.

Item Type
Item Type is a name/identifier of a workflow business process. Any component that we create for a workflow business process must be associated with a particular item type.
when we save our workflow process definition is database it takes the item type as its name. Opening an item type from databse automatically retrieves all the attributes, messages, lookups, notifications, functions and processes associated with that item type.

Example:- Here “Test Leave Workflow” is name of the Leave Approval business process .

Item Attribute
Item Attribute is nothing but global variable that can be accessed/ referenced by any activity within the itemtype. An item attribute often provides/stores information about an item that is necessary for the workflow process to complete. It also help us to share information with the stakeholders of a business process.
There are different types of item attribute:-

a)    Text:- The attribute value is a string of text
Note:- Oracle Workflow does not permit HTML content to be passed in attributes of type text. If Oracle Workflow encounters HTML tags in a text attribute, escape characters will be applied to display the content as plain text rather than executing the HTML.
b)    Number:- The attribute value is a number
c)    Date     :- The attribute value is a date
d)    Lookup : -The attribute value is one of the lookup code values in a specified lookup type.
e)    Form    :- The attribute value is an Oracle Applications internal form function.
f)     URL    :-  The attribute value is a Universal Resource Locator (URL) to a network location. If we  reference a URL attribute in message attribute, the
                     notification, when viewed from the Notification Details Web page or as an HTML-formatted e-mail, displays a link to the URL specified by the
                     URL attribute.
g)    Document:- Document attribute is mainly used to attach document in notification and help stakeholder to see the information. This attribute mainly
                         used for external document integration purpose.The following document types can be used  :-       
                                                            >>  PL/SQL document
                                                            >>  PL/SQL CLOB Document
                                                            >>  PL/SQL BLOB Document

Message & Notification Activity
In our daily life, when we plan to send a letter to anybody, first we write a letter and then we put that inside an envelope, which contain the address of the recipient.

In oracle workflow, Message can be compared with a letter and notification can be compared with envelope. In oracle workflow a message is attached with a notification.

Recipient of the message is known as performer. Here performer needs to be added at notification level, so that workflow engine can understand to whom the notification needs to be delivered.In performer field we need to add  a value(may be dynamic by adding item attribute),so that workflow uniquely identify the  recipient from database.(Username of FND_USER table).

Each message is associated with a particular item type. This allows the message to reference the item type's attributes for token replacement at runtime when the message is delivered. We can create a message with context-sensitive content (dynamic message body) by including message attribute tokens in the message subject and body that reference item type attributes.

Note:- Unlike the real life envelope-letter analogy, here we can attach only a single message(letter) to a notification(envelope).

Function Activity
Function Activity is defined by Pl/SQL stored procedure or other external procedure.Our discussion in oracle workflow will be restricted to Pl/SQL stored procedure only.Function activity helps us to embed our custom business logic applicable to workflow business process.

Example:- We need to examine whether the person has applied for a leave for more than 10 days or less than equal to 10 days.

Conclusion:-
The Key features of Oracle Workflow are
i)The Workflow Engine embedded in the Oracle Database implements process definitions at runtime
ii) oracle uses the Business Event System which is an application service that uses the Oracle Advanced Queuing (AQ) infrastructure to communicate business
    events between systems.
iii)The Workflow Definitions Loader is a utility program tightly integrated with the workflow builder and oracle application. This helps us to download workflow
   definition from database to flat file and upload it to database
iv)Oracle Workflow lets us include our own PL/SQL procedures or external functions as activities in our workflows. Without modifying our application code, you can
    have our own program run whenever the Workflow Engine detects that our program's prerequisites are satisfied.
v)The Notification System sends notifications to and processes responses from users in a workflow. Electronic notifications are routed to a role, which can be an
   individual user or a group of users. Any user associated with that role can act on the notification.
vi)Electronic mail (e-mail) users can receive notifications of outstanding work items and can respond to those notifications using their e-mail application of choice.
   An e-mail notification can include an attachment that provides another means of responding to the notification.
vii)Web users can access a Notification Web page to see their outstanding work items, then navigate to additional pages to see more details or provide a response.

.