rem HEADER
rem   $Header: wfrmtype.sql 26.1 2001/11/05 20:20:03 ctilley ship $
rem NAME
rem   wfrmtype.sql - WorkFlow ReMove TYPE
rem USAGE
rem   @wfrmtype
rem DESCRIPTION
rem   DANGER *** DANGER *** DANGER *** DANGER *** DANGER *** DANGER
rem
rem   You will be prompted for the type, from a list of valid types.
rem   Purges ALL runtime data associated with a given item type.
rem
rem   DANGER *** DANGER *** DANGER *** DANGER *** DANGER *** DANGER
rem
rem   dbdrv: none

set verify off

select item_type, count(item_key) from wf_items group by item_type;
accept type prompt "Enter Item Type to purge: "

prompt
prompt Purge ALL items associated with this item type, or only completed items?
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'
      union
      select notification_id
      from wf_item_activity_statuses_h
      where item_type = '&type');

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

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

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

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

commit;
exit;