Run all scripts as 'APPS' user.
1) Workflow Notification Mailer Status:
select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');
2) Workflow Mailer Services / Service components Status:
select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name, DECODE(fcp.OS_PROCESS_ID,NULL,'NotRunning',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, 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;
3) Workflow Mailes Status:
select *
from wf_notifications
where 1=1
and begin_Date >= sysdate-1
--and mail_status='FAILED'
order by begin_Date desc;
4) Stop Notification Mailer from backend:
This will deactivate notification mailer. Run this script as apps user.
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
5) Start Notification Mailer from backend:
Run this script as apps user.
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/
