Troubleshooting Oracle E Business Suite Workflow Mailer and Notification issues
1. Check status of workflow mailer and its components
SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not
Running',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, apps.fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;
2. Check the log file of workflow mailer
Please run the following query to locate all current Workflow Mailer Service log:
set linesize 155;
set pagesize 200;
set verify off;
column MANAGER format a15;
column MEANING format a15;
SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active';
After setting app env file run the following command to collect the logs
grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log
3. Check If the notification for the user is going to discard folder
- First check, If the notifications approved through email are going to Discard folder for that user.
- Try to approve the notification from workflow worklist from Oracle EBS ERP and see if its going to Discard folder or processing successfully.
Please run below command to see notifications moved to Discard folder
grep ":DISCARD:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrdiscard.log
grep "Approver:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrapprover.log
4. Check below queries if the messages are in ready state
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,
decode(wfe.state,
0,' 0 = Ready',
1,'1 = Delayed',
2,'2 = Retained',
3,'3 = Exception',
to_char(substr(wfe.state,1,12))) State,
count(*) COUNT
from applsys.wf_notification_out wfe
group by wfe.corrid, wfe.state;
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;
select count(*) from APPLSYS.AQ$WF_NOTIFICATION_OUT
where msg_state in ('READY','WAIT');
1. Check status of workflow mailer and its components
SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'Not
Running',fcp.OS_PROCESS_ID) PROCID,
fcq.MAX_PROCESSES TARGET,
fcq.RUNNING_PROCESSES ACTUAL,
fcq.ENABLED_FLAG ENABLED,
fsc.COMPONENT_NAME,
fsc.STARTUP_MODE,
fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES
fcp, apps.fnd_svc_components fsc
where fcq.MANAGER_TYPE = fcs.SERVICE_ID
and fcs.SERVICE_HANDLE = 'FNDCPGSC'
and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+)
and fcp.process_status_code(+) = 'A'
order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE;
2. Check the log file of workflow mailer
Please run the following query to locate all current Workflow Mailer Service log:
set linesize 155;
set pagesize 200;
set verify off;
column MANAGER format a15;
column MEANING format a15;
SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active';
After setting app env file run the following command to collect the logs
grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log
3. Check If the notification for the user is going to discard folder
- First check, If the notifications approved through email are going to Discard folder for that user.
- Try to approve the notification from workflow worklist from Oracle EBS ERP and see if its going to Discard folder or processing successfully.
Please run below command to see notifications moved to Discard folder
grep ":DISCARD:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrdiscard.log
grep "Approver:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrapprover.log
4. Check below queries if the messages are in ready state
select NVL(substr(wfe.corrid,1,50),'NULL - No Value') corrid,
decode(wfe.state,
0,' 0 = Ready',
1,'1 = Delayed',
2,'2 = Retained',
3,'3 = Exception',
to_char(substr(wfe.state,1,12))) State,
count(*) COUNT
from applsys.wf_notification_out wfe
group by wfe.corrid, wfe.state;
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;
select count(*) from APPLSYS.AQ$WF_NOTIFICATION_OUT
where msg_state in ('READY','WAIT');