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'