Workflow Notification Mailer Status Check

Check workflow mailer service current status

select running_processes from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';
Please Note : Number of running processes should be greater than 0

Find current mailer status

To check the status of notification mailer, we need to do the following.

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');
Possible values are :
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

Stop notification mailer

To stop notification mailer , we need to execute the following.

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
------------------------------
select component_id into m_mailerid from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
-- Stop Mailer
------------------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
-------------------------+
Start notification mailer|
-------------------------+
To start notification mailer, we need to execute the following.
sqlplus apps/
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
-----------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
-------------------------------+
All WF Mailer Service Stausses`|
-------------------------------+
select component_name,COMPONENT_STATUS,COMPONENT_TYPE,STARTUP_MODE,INBOUND_AGENT_NAME,OUTBOUND_AGENT_NAME from apps.fnd_svc_components;

SET PAGESIZE 400
SET LINESIZE 120
SET PAGESIZE 50
COLUMN COMPONENT_NAME FORMAT A45
COLUMN STARTUP_MODE FORMAT A15
COLUMN COMPONENT_STATUS FORMAT A15
SELECT FSC.COMPONENT_NAME,FSC.STARTUP_MODE,FSC.COMPONENT_STATUS
FROM APPS.FND_CONCURRENT_QUEUES_VL FCQ, apps.FND_SVC_COMPONENTS FSC
WHERE FSC.CONCURRENT_QUEUE_ID = FCQ.CONCURRENT_QUEUE_ID(+)
ORDER BY COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
=====================

1) Identify the concurrent tiers node where mailer runs 
by running script below:
select target_node from fnd_concurrent_queues where concurrent_queue_name like 'WFMLRSVC%'; 

It will return for example:
TARGET_NODE
------------------------------
ebiz1                     

In this example ebiz1 is the node where java mailer runs.

2) Gather other parameters values necessary for the SMTP telnet test:
To perform the SMTP telnet test, in addition to mailer node, you will also need to know on which node is the SMTP server (this is mailer “outbound server” parameter), and what is the reply to address that is set up for the java mailer (this is mailer “reply to” parameter).

To get these values run the following:
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;