REM dbdrv: none
REM HEADER
REM   $Header: wfevqcln.sql 26.2 2005/02/28 11:27:31 dmani noship $
REM *********************************************************************
REM NAME
REM   wfevqcln.sql - WorkFlow Queue Clean
REM
REM DESCRIPTION
REM   Purges the messages in the READY state of a Queue of WF_EVENT_T
REM   payload type. Supports correlation id based purge and 
REM   time-based selective purge with correlation id.
REM   Manual Script
REM
REM USAGE
REM   sqlplus apps/<apps pwd> @wfevqcln.sql <Agent> <Event> <Age in days>
REM 
REM   For Example: 
REM   sqlplus apps/<apps pwd> @wfevqcln.sql WF_ERROR oracle.apps.event% 60
REM
REM   The messages older than the specified age will be purged.
REM   Suppose messages older than 1 hour (compared to the current time) are
REM   to be purged, set the l_age parameter to the value 0.041667 (1/24)
REM   If the messages are to be purged irrespective of the age then 
REM   enter 0 for the AGE parameter
REM
REM   **************************** WARNING *******************************
REM  
REM   THIS SCRIPT SIMPLY DEQUEUES MESSAGES FROM THE SPECIFIED QUEUE WITHOUT
REM   PROCESSING THEM. PLEASE NOTE THAT ONCE THE MESSAGES ARE REMOVED USING 
REM   THIS SCRIPT THEY ARE LOST AND THEY CANNOT BE RETRIEVED.
REM
REM   PLEASE *DO NOT* RUN THIS SCRIPT IN PRODUCTION WITHOUT THE ADVISE FROM
REM   ORACLE WORKFLOW DEVELOPMENT/SUPPORT.
REM
REM   **************************** WARNING ********************************
REM
REM NOTE
REM
REM   1. If you want all messages of a given event to be purged, specify 
REM      an age of 0 days.
REM   2. If you want to clean the entire queue you can always re-create the 
REM      queue. This will save the tablespace.This script is more ideal when 
REM      you want to remove specific messages from the queue while there are 
REM      still valid messages in the queue.
REM   3. In BES, event names are case sensitive so when you specify the event
REM      name please be aware of it .
REM   4. Once you run the script the message state will become 'PROCESSED' 
REM      from 'READY', but will reamin in the queue until the retention time 
REM      of the queue.
REM
REM MODIFICATION LOG:
REM   Roshin Thomas       Created     28/03/2003
REM   Roshin Thomas       Specialised script for any specific 
REM                       event to be dequeued from the queue.
REM   dmani (24/02/05)    Refer the Bugs 4185444 and 4084220 
REM                       for the changes made to this script.   
REM   
REM ************************************************************************
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;

set pagesize 999
set linesize 80
set arraysize 1
set serveroutput on

declare
   l_commit_frequency number;
   l_agent_name       varchar2(30);
   l_correlation_id   varchar2(80);
   l_age              number;
   l_msg_count        number;
   
begin
   -- Default commit frequency is 500
   l_commit_frequency := 500;
   l_agent_name       := upper('&1');
   l_correlation_id   := '&2';
   l_age              := nvl(to_number('&3'), 0);

   -- The overloaded wf_queue.clean_evt procedure is called depending on the AGE
   if(l_age=0) then
      -- Since the AGE is 0 the messages are purged based ONLY on the corr id
      wf_queue.clean_evt(l_agent_name, l_correlation_id, l_commit_frequency, l_msg_count);
   else   
      -- Since the AGE is specified, all the messages older than the specified
      -- age AND matching the given correlation id are retrieved and dequeued.
      -- Here the dequeue is based on the message id
      wf_queue.clean_evt(l_agent_name, l_correlation_id, l_commit_frequency, l_msg_count, l_age);
   end if;

   -- The OUT parameter l_msg_count contains the count of the
   -- number of purged messages
   dbms_output.put_line('Number of purged messages = ' || l_msg_count);
     
exception 
   when others then
      raise_application_error(-20000, 'Oracle Error = '||to_char(sqlcode)||' - '||sqlerrm);
end;
/
set verify on
commit;
exit;