REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=pls \
REM dbdrv: checkfile:~PROD:~PATH:~FILE 
--=========================================================================--
--  Copyright (c)2002 Oracle Corporation, Redwood Shores, California, USA  --
--                          All rights reserved.                           --
--=========================================================================--
-- FILENAME
--   WFBESCUS.pls
--
-- DESCRIPTION
--   Code to clean up dead JMS subscribers to the WF_CONTROL queue.
--   
-- HISTORY
--   DD-MON-RR  userid    comment
--   16-AUG-02  gashford  Initial version.
--=========================================================================--

set verify off
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;

create or replace package wf_bes_cleanup as
/* $Header: WFBESCUS.pls 26.5 2004/10/20 23:20:46 yohuang noship $ */
/*# 
 * The Workflow Business Event System cleanup API can be used to clean up the
 * standard WF_CONTROL queue in the Business Event System by removing inactive 
 * subscribers from the queue.
 * @rep:scope public
 * @rep:product OWF
 * @rep:displayname Workflow Control Queue Cleanup
 * @rep:lifecycle active
 * @rep:compatibility S
 * @rep:category BUSINESS_ENTITY WF_EVENT
 * @rep:ihelp FND/@besclnapi See the related online help
 */

--------------------------------------------------------------------------------
-- Removes dead subscribers from the WF_CONTROL queue.  This procedure will be
-- run periodically to remove subscribers which have died.  This procedure:
-- 1. Checks to make sure that sufficient time has elapsed since the last time
--    it was run.  If not, it just returns.
-- 2. Removes dead queue subscribers.  A subscriber is assumed to be dead
--    if it has not responded to at least one ping.
-- 3. Sends out a new ping to the current subscribers.
--
-- errbuf - the error buffer
-- retcode - the return code
--------------------------------------------------------------------------------
/*#
 * Performs cleanup for the standard WF_CONTROL queue. When a middle tier 
 * process for Oracle e-Business Suite or for standalone Oracle Workflow starts
 * up, it creates a JMS subscriber to the WF_CONTROL queue. Then, when an event 
 * message is placed on the queue, a copy of the event message is created for 
 * each subscriber to the queue. If a middle tier process dies, however, the 
 * corresponding subscriber remains in the database. For more efficient 
 * processing, you should ensure that WF_CONTROL is periodically cleaned up by
 * running Cleanup_Subscribers() to remove the subscribers for any middle tier 
 * processes that are no longer active.
 * @param errbuf Return Error Message
 * @param retcode Return Error Code
 * @rep:scope public
 * @rep:lifecycle active
 * @rep:displayname Cleanup Dead Subscribers
 * @rep:compatibility S
 * @rep:ihelp FND/@besclnapi#a_clnsub See the related online help
 */
procedure cleanup_subscribers(errbuf  out nocopy varchar2,
                              retcode out nocopy varchar2);  

-------------------------------------------------------------------------------
-- Used by a subscriber to acknowledge receiving a ping.
--
-- ping_number - the ping number
-- queue_name - the queue name
-- subscriber_name - the subscriber name
--------------------------------------------------------------------------------
procedure acknowledge_ping(p_ping_number     in number,
                           p_queue_name      in varchar2,
                           p_subscriber_name in varchar2);
   
-------------------------------------------------------------------------------
-- Used to dequeue messages from exception queue
-- p_owner  Owner of the queue whose exception message will be removed
-- p_queue_table queue table of the queue
--------------------------------------------------------------------------------
-- Purge JMS Queue 

-- p_queue_name     Queue name in format of owner.queue
-- p_consumer_name  mandatary for multiple consumer queue. Default is null
-- p_correlation    Optional to purge message with certain correlation. Default is null
-- p_commit_frequency how frequently the transaction should commit. Default is 100                 
procedure purge_jms_queue(p_queue_name in VARCHAR2,
                          p_consumer_name in VARCHAR2 default null, 
                          p_correlation in VARCHAR2 default null,
                          p_commit_frequency in NUMBER default 100);

--------------------------------------------------------------------------------
-- Purge EVT Queue 

-- p_queue_name     Queue name in format of owner.queue
-- p_consumer_name  mandatary for multiple consumer queue. Default is null
-- p_correlation    Optional to purge message with certain correlation. Default is null
-- p_commit_frequency how frequently the transaction should commit. Default is 100                                
procedure purge_evt_queue(p_queue_name in VARCHAR2,
                          p_consumer_name in VARCHAR2 default null, 
                          p_correlation in VARCHAR2 default null,
                          p_commit_frequency in NUMBER default 100);
                                                                                                     
end wf_bes_cleanup;
/
commit;
exit;