rem HEADER
rem   $Header: wfrmitms.sql 26.1 2001/11/05 20:19:05 ctilley ship $
rem NAME
rem   wfrmitms.sql - WorkFlow ReMove ITMeS
rem USAGE
rem   @wfrmitms <type> <key>
rem DESCRIPTION
rem   removes item status information for items which match 
rem   the supplied type and key patterns.
rem NOTE
rem   
rem
rem  dbdrv: none

set verify off 

undefine type
undefine key

define type='&1'
define key='&2'

prompt Delete ALL runtime data associated with this item type and key,
prompt or only data for completed activities and items?  
prompt Removing ALL data will invalidate any incomplete processes.
accept allflag prompt "Enter ALL for all data, leave blank to delete only complete data: "

begin
  if ('&allflag' = 'ALL') then
    update wf_notifications set 
      end_date = sysdate 
    where group_id in 
      (select notification_id
      from wf_item_activity_statuses
      where item_type = '&type'
      and item_key = '&key'
      union
      select notification_id
      from wf_item_activity_statuses_h
      where item_type = '&type'
      and item_key = '&key');

    update wf_items set 
      end_date = sysdate 
    where item_type = '&type'
    and item_key = '&key';

    update wf_item_activity_statuses set 
      end_date = sysdate 
    where item_type = '&type'
    and item_key = '&key';

    update wf_item_activity_statuses_h set 
      end_date = sysdate 
    where item_type = '&type'
    and item_key = '&key';
  end if;

  wf_purge.total('&type', '&key', sysdate);
end;
/
set verify on

commit;
exit;