How to debug workflow in oracle apps

How to debug workflow in oracle apps

In this post , We will be discuss , how we can debug the oracle workflow mailer in oracle apps. We often got issues in the Oracle Workflow mailer , that email notifications are not receiving by the approvers or some time approvers said that they have approve the Document from email but in application we can not getting that track so in this case we should have an idea of the debugging of workflow in oracle apps. Here below I will share some important SQL queries which helps to debug the workflow in Oracle apps.

 

 

2 Important actions of Workflow in oracle apps

1.Inbound : To get the Email responses from the approvers Outside the application to the Oracle Environment.

2.Outbound: To send the Email Notifications to approvers from Oracle applications.

 

So First we need to identify which part of the actions we are going to debug in the oracle application.

 

 

5 Steps of How to debug workflow in oracle apps

Step1:- Ensure that all the Workflow Complements are running fine with out any error. We can verify this with the help on this below sql.

 

select component_type, component_name, Component_status,COMPONENT_STATUS_INFO Error from fnd_svc_components where component_type like 'WF%'

Step 2:- For Outbound Notifications check the Pending message queue with this below sql Query

select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state

Step3:- To validate any Pending Alert Outbound Emails Notifications

select decode(wno.state,
0, '0 = Pending in mailer queue',
1, '1 = Pending in mailer queue',
2, '2 = Sent by mailer on '||to_char(DEQ_TIME),
3, '3 = Exception', 4,'4 = Wait', to_char(state)) State,
to_char(DEQ_TIME),
wno.user_data.TEXT_VC
from wf_notification_out wno
where corrid='APPS:ALR'

Step4:- To verify how many notifications got affected with this Issue or Pending for Deliver.

select mail_status, status from wf_notifications where mail_status='MAIL'

--If mail_status is MAIL , it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"

Step5:- to get the Workflow Log file Name to do the Complete Debugging of the Workflow use this below sql.

select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC', 'mailer container',
'WFALSNRSVC','listener container',fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A'
and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC')