Script to Activate and Deactivate Oracle EBS Workflow Listeners

CREATE OR REPLACE PACKAGE apps.workflow_listener_pkg

AS

   PROCEDURE MAIN(p_errbuf    OUT VARCHAR2,

                  p_retcode   OUT VARCHAR2);

END workflow_listener_pkg;

CREATE OR REPLACE PACKAGE BODY apps.workflow_listener_pkg

AS

   PROCEDURE MAIN(p_errbuf    OUT VARCHAR2,

                  p_retcode   OUT VARCHAR2)

   IS

      ln_spid   NUMBER;

  ln_user_id             NUMBER := fnd_profile.VALUE('USER_ID');

      ln_responsibility_id   apps.fnd_responsibility.responsibility_id%TYPE := fnd_profile.VALUE('RESP_ID');

      ln_resp_appl_id        apps.fnd_responsibility.application_id%TYPE := fnd_profile.VALUE('RESP_APPL_ID');

  

      CURSOR cur_wfsrv

      IS

         SELECT   DISTINCT fcq.concurrent_queue_id,

                           fcq.concurrent_queue_name,

                           fcq.user_concurrent_queue_name

         FROM     apps.fnd_concurrent_queues_vl fcq,

                  fnd_svc_components fsc

         WHERE    fsc.concurrent_queue_id = fcq.concurrent_queue_id

         AND      fcq.concurrent_queue_name IN ('WFALSNRSVC',

                                                'WFMLRSVC')

         AND      fsc.component_status = 'STOPPED'

         AND      fsc.component_id NOT IN (10040,

                                           10042)

         ORDER BY fcq.concurrent_queue_id;

   BEGIN

      -- WFMLRSVC : Workflow Mailer Service

  -- WFALSNRSVC : Workflow Agent Listener Service

      -- WFWSSVC : Workflow Document Web Services Service

      fnd_file.put_line(fnd_file.LOG, 'Workflow Listeners Program start');

  

  fnd_file.put_line(fnd_file.LOG, 'ln_user_id:' || ln_user_id);

      fnd_file.put_line(fnd_file.LOG, 'ln_responsibility_id:' || ln_responsibility_id);

      fnd_file.put_line(fnd_file.LOG, 'ln_resp_appl_id:' || ln_resp_appl_id);

      fnd_global.apps_initialize(ln_user_id, ln_responsibility_id, ln_resp_appl_id);

  

      FOR rec_wfsrv IN cur_wfsrv

      LOOP

         fnd_file.put_line(fnd_file.LOG, 'loop start');

         fnd_file.put_line(fnd_file.LOG, 'user_concurrent_queue_name:' || rec_wfsrv.user_concurrent_queue_name);

         fnd_file.put_line(fnd_file.LOG, 'concurrent_queue_name:' || rec_wfsrv.concurrent_queue_name);

         ln_spid   := fnd_request.submit_svc_ctl_request(command => 'ACTIVATE', service => rec_wfsrv.concurrent_queue_name, service_app => 'FND');

         fnd_file.put_line(fnd_file.LOG, 'ln_spid:' || ln_spid);

         IF ln_spid = 0 THEN

            fnd_file.put_line(fnd_file.LOG, 'IF ln_spid = 0:' || fnd_message.get);

         END IF;

         fnd_file.put_line(fnd_file.LOG, 'loop end');

      END LOOP;

      fnd_file.put_line(fnd_file.LOG, 'Workflow Listeners Program end');

   EXCEPTION

      WHEN OTHERS THEN

         fnd_file.put_line(fnd_file.LOG, 'In Main Exception :' || SQLCODE || ' - ' || SQLERRM);

         p_retcode   := 2;

         p_errbuf    := SQLCODE || ' - ' || SQLERRM;

   END MAIN;

END workflow_listener_pkg;

/

Note:

For Activate

spid   := fnd_request.submit_svc_ctl_request(command => 'ACTIVATE', service => l_cqname, service_app => 'FND');

For Deactivate

spid   := fnd_request.submit_svc_ctl_request(command => 'DEACTIVATE', service => l_cqname, service_app => 'FND');

For Abort

spid   := fnd_request.submit_svc_ctl_request(command => 'ABORT', service => l_cqname, service_app => 'FND');