Troubleshooting methods for Oracle Workflow

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

Descriptions

Oracle workflow is a complete solution for integrating End to End business process within ERP. Oracle workflow lets people receive emails about their activities and also sends remainder mails to the document approvers. Oracle workflow builder helps to define the workflow process and integrate all the processes to define the business process. 

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.

Figure 1:  Queries for checking the workflow Background Process

2. 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.

Figure 1: Navigation to Oracle Workflow Manager

b.      You will be guided to the following page and here all of the highlighted must be up.

Figure 2:  Workflow Components Dashboard

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>;

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.

Figure 3:  Navigation 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

Figure 4:  Activity Details of Workflow Activity

·         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>;

After analyzing the columns of the wf_notifications, wf_roles, & wf_user_roles we can get the reason for the notifications not getting fired.

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 “*”.

Figure 5:  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.

Credit goes to the below website(s) :

http://oracleappstechnicalr12.blogspot.com

Useful concurrent programs for WF:

   WF Synchronize Local Tables
   Purge Obsolete Workflow runtime data
   Retry Errored Workflow Activities
   Resend Failed/Error Workflow Notifications
   Workflow Directory Services User/Role Validation

Useful SQL:
 select *
 from WF_ITEM_ACTIVITY_STATUSES_V 
 where user_key='110133340'
 order by execution_time;