--------------------------------------------------------------------------------------------------------------------------
Check workflow mailer service current status --Number of running processes should be greater than 0
---------------------------------------------------------------------------------
select running_processes
from apps.fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
-------------------------------------------------------------------------------------------------------------------------
Find current mailer status
---------------------------------------------------------------------------------
select component_status
from apps.fnd_svc_components
where component_id =
(select component_id
from apps.fnd_svc_components
where component_name = 'Workflow Notification Mailer');
-------------------------------------------------------------------------------------------------------------------------
Robust script for monitoring the wf_notifications table
---------------------------------------------------------------------------------
select message_type, mail_status, count(*) from wf_notifications
where status = 'OPEN'
GROUP BY MESSAGE_TYPE, MAIL_STATUS
--messages in 'FAILED' status can be resent using the concurrent request 'resend failed workflow notificaitons'
--messages which are OPEN but where mail_status is null have a missing email address for the recipient, but the notification preference is 'send me mail'
-------------------------------------------------------------------------------------------------------------------------
Some messages like alerts don't get a record in wf_notifications table so you have to watch the WF_NOTIFICATION_OUT queue.
---------------------------------------------------------------------------------
select corr_id, retry_count, msg_state, count(*)
from applsys.aq$wf_notification_out
group by corr_id, msg_state, retry_count
order by count(*) desc;
--Messages with a high retry count have been cycling through the queue and are not passed to smtp service.Messages which are 'expired' can be rebuilt using the wfntfqup.sql
-------------------------------------------------------------------------------------------------------------------------
The following SQL to collect all the info except IMAP account password.
---------------------------------------------------------------------------------
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
fnd_svc_components c
where c.component_type = 'WF_MAILER'
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
------------------------------------------------------------------------------------------------------------------------
Check The Workflow notification has been sent or not
---------------------------------------------------------------------------------
SELECT status, mail_status FROM wf_notifications WHERE notification_id = 10609859;
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(‘&recipient_role’);
-------------------------------------------------------------------------------------------------------------------------
Query to get the log file of active workflow mailer and workflow agent listener Container
---------------------------------------------------------------------------------
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')
order by fcp.logfile_name;
-------------------------------------------------------------------------------------------------------------------------
Query to Check Workflow Mailer Backlog --State=Ready implies that emails are not being sent & Waiting mailer to send emails
---------------------------------------------------------------------------------
select tab.msg_state, count(*) from applsys.aq$wf_notification_out tab group by tab.msg_state ;
-------------------------------------------------------------------------------------------------------------------------
Check any particular Alert Message email has be pending by Mailer
---------------------------------------------------------------------------------
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'
and upper(wno.user_data.TEXT_VC) like '%<Subject of Alert Email>%';
-------------------------------------------------------------------------------------------------------------------------
Check Whether workflow background Engine is working for given workflow or not in last 2 days -- Note: Workflow Deferred activities are run by workflow background engine.
---------------------------------------------------------------------------------
select a.argument1,a.phase_code, a.status_code ,a.actual_start_date,a.* from fnd_concurrent_requests a
where CONCURRENT_PROGRAM_ID =
(select concurrent_program_id from fnd_concurrent_programs where
CONCURRENT_PROGRAM_NAME='FNDWFBG')
and last_update_Date>sysdate-2 and argument1='<Workflow Item Type>'
order by last_update_date desc
--------------------------------------------------------------------------------------------------------------------------
Query to get the log file of active workflow mailer and workflow agent listener Container
---------------------------------------------------------------------------------
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')
order by fcp.logfile_name;
-------------------------------------------------------------------------------------------------------------------------
Linux Shell script Command to get outbound error in Mailer
---------------------------------------------------------------------------------
grep -i '^\[[A-Za-z].*\(in\|out\).*boundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
--Note: All Mailer log files starts with name FNDCPGSC prefix
--------------------------------------------------------------------------------------------------------------------------
Linux Shell script Command to get inbound processing error in Mailer
---------------------------------------------------------------------------------
grep -i '^\[[A-Za-z].*.*inboundThreadGroup.*\(UNEXPECTED\|ERROR\).*exception.*' <logfilename> | tail -10 ;
----------------------------------------------------------------------------------------------------
1) Identify the concurrent tiers node where mailer runs
----------------------------------------------------------------------------------------------------
select target_node
from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%';
----------------------------------------------------------------------------------------------------
2) Gather other parameters values necessary for the SMTP telnet test:
----------------------------------------------------------------------------------------------------
SELECT b.component_name,
c.parameter_name,
a.parameter_value
FROM fnd_svc_comp_param_vals a,
fnd_svc_components b,
fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;
----------------------------------------------------------------------------------------------------
3) Perform the SMTP telnet test as follows:
----------------------------------------------------------------------------------------------------
3.1) Log on to the node where mailer runs (to identify it, please refer to step 1)
This is mandatory. SMTP telnet test is only meaningful when it is performed from the concurrent tier where mailer runs.
3.2) From mailer node, issue the following commands one by one:
telnet hostname.domain 25
EHLO OPRDCM1
MAIL FROM: mailserver@email.com
RCPT TO: abc@xyz.com
DATA
Subject: Test message
Test message body
.
quit
--------------------------------------------------------------------------------------------------------------------------
