Oracle WF Mailer : Restart script

You can ask me why I created the script for restart WF Mailer.
You right, I can do it from Application by stop and start it...


The script was created by me for the framework developers that sometimes need to restrt the manager for save the changes. The always asked me to do it.


I decided to create a host concurrent that will do it for me :-)


Here the sources:
For 11.5.8 (run it with apps user ander application OS user)
How to run for start :
WF_MAILER_STOP_START.sql WFMGSMD ACTIVATE
WF_MAILER_STOP_START.sql WFMGSMS ACTIVATE
WF_MAILER_STOP_START.sql WFMGSMD RESUME
WF_MAILER_STOP_START.sql WFMGSMS RESUME


How to run for stop :
WF_MAILER_STOP_START.sql WFMGSMD DEACTIVATE
WF_MAILER_STOP_START.sql WFMGSMS DEACTIVATE
========================================
REM Start of SCRIPT
REM
SET SERVEROUTPUT ON SIZE 200000
Declare
l_qaid number;
l_queue number;
spid number;
req_data varchar2(10);
errbuf varchar2(300);
Begin
fnd_global.apps_initialize(0,20420,1);
begin
select application_id, CONCURRENT_QUEUE_ID into l_qaid, l_queue
from fnd_concurrent_queues
where CONCURRENT_QUEUE_NAME = '&1';
exception
when others then
l_queue := 0;
end;
if l_queue > 0 then
spid:=fnd_request.submit_svc_ctl_request( command => '&2',
service => '&1',
service_app => 'FND');
end if;
DBMS_OUTPUT.put_line('Appl id: '||l_qaid);
DBMS_OUTPUT.put_line('Queue number: '||l_queue);
DBMS_OUTPUT.put_line('Return Value: '||spid);
if spid = 0 then
errbuf := fnd_message.get;
DBMS_OUTPUT.put_line('--top----');
DBMS_OUTPUT.put_line(errbuf);
DBMS_OUTPUT.put_line('--end----');
end if;
end;
/
commit
/
exit
REM
REM End of Script


For 11.5.10 - 12.1.1
set serveroutput on size 1000000
declare
n_component_id number;
v_component_status varchar2(40);
l_errcode number;
l_errstr varchar2(4000);
v_time varchar2(60);
begin
select component_id,component_status into n_component_id,v_component_status from fnd_svc_components
where component_name='Workflow Notification Mailer';
dbms_output.put_line(n_component_id||' '||v_component_status);
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_time from dual;
dbms_output.put_line(v_time);
dbms_output.put_line('Stopping Notification Mailer');
FND_SVC_COMPONENT.stop_Component(n_Component_Id, l_errcode, l_errstr);
commit;
dbms_output.put_line(n_component_id||' '||l_errcode||l_errstr);
dbms_lock.sleep(90);
dbms_output.put_line(n_component_id||' '||v_component_status);
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_time from dual;
dbms_output.put_line(v_time);
dbms_output.put_line('Starting Notification Mailer');
FND_SVC_COMPONENT.start_Component(n_Component_Id, l_errcode, l_errstr);
commit;
dbms_output.put_line(n_component_id||' '||l_errcode||l_errstr);
dbms_lock.sleep(65);
dbms_output.put_line(n_component_id||' '||v_component_status);
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') into v_time from dual;
dbms_output.put_line(v_time);
end;
/


Good luck!!!