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');
