Workflow Service Containers Activate/Deactivate Script

REM HEADER
REM   $Header: atg_supp_wf_srv_ctl.sql v2.0 GGGRANT $
REM  
REM MODIFICATION LOG:
REM
REM    14-JUN-2010    GGGRANT Added Abort and an option and numerical prompt for operation.
REM           
REM   atg_supp_wf_srv_ctl.sql
REM    
REM       This is an admin script to start and stop the Workflow Service Containers.
REM      
REM
REM   How to run it?
REM  
REM   sqlplus apps/<password>
REM
REM   @atg_supp_wf_srv_ctl.sql
REM
REM  Type Number to Perform Operation:
REM
REM   1. Activate
REM
REM   2. Deactivate
REM
REM   3. Abort
REM  
REM 
REM
REM
REM   Created: Apr 21th, 2010
REM   Last Updated: Jun 14th, 2010



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;

Ref: 1124356.1