SQL Script to Start and Stop Oracle EBS Workflow Service Containers

Download(wf_container_restart.sql)

REM   +=============================================================================+ 
REM  
REM   Script Name : wf_container_restart.sql
REM     
REM    This is an admin script to start and stop the Workflow Service Containers.
REM    - WFMLRSVC : Workflow Mailer Service
REM - WFALSNRSVC : Workflow Agent Listener Service
REM     - WFWSSVC : Workflow Document Web Services Service
REM
REM   How to run it?
REM   
REM    sqlplus apps/<password>
REM
REM    @wf_container_restart.sql
REM
REM
REM  Type Number to Perform Operation:
REM
REM    1. Activate
REM
REM    2. Deactivate
REM
REM    3. Abort
REM   
REM  +=============================================================================+ 


WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE;
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 200000


select to_char(rownum)|| '. ' || decode(meaning,
              'Activated', 'Activate',
              'Deactivated', 'Deactivate',
              'Terminated', 'Abort'
              ) meaning
from fnd_lookups 
where lookup_type='CP_CONTROL_CODE' and meaning in ('Activated', 'Terminated', 'Deactivated') order by meaning;


-- Option value from user
accept l_option default '1'  prompt 'Enter Workflow Service Container Operation[1]: '



Declare
 l_qaid               number;
 l_queue              number;
 spid                 number;
 errbuf               varchar2(300);
 l_cqname             varchar2(300);
 l_operation varchar2(30) :=  &l_option;

cursor wfsrv_ctl is
   select application_id, CONCURRENT_QUEUE_ID, CONCURRENT_QUEUE_NAME 
   from fnd_concurrent_queues    where CONCURRENT_QUEUE_NAME in ('WFMLRSVC', 'WFALSNRSVC', 'WFWSSVC');
   
Begin

 fnd_global.apps_initialize(0,20420,1);


 for l_rec in wfsrv_ctl loop
 
 
 l_queue := l_rec.CONCURRENT_QUEUE_ID;
 
 l_cqname := l_rec.CONCURRENT_QUEUE_NAME;
 
 if l_queue > 0 and l_operation = '1' then
   spid:=fnd_request.submit_svc_ctl_request( command => 'ACTIVATE',
                             service => l_cqname,
                             service_app => 'FND');
elsif l_queue > 0 and l_operation = '2' then
   spid:=fnd_request.submit_svc_ctl_request( command => 'DEACTIVATE',
                             service => l_cqname,
                             service_app => 'FND');
else 
   spid:=fnd_request.submit_svc_ctl_request( command => 'ABORT',
                             service => l_cqname,
                             service_app => 'FND');                             
 end if;

if spid = 0 then
    errbuf := fnd_message.get;       DBMS_OUTPUT.put_line(errbuf);
 end if;
 
end loop;

end;
/

commit;
exit;