rem HEADER
rem   $Header: wfrmitt.sql 26.1 2001/11/05 20:19:24 ctilley ship $
rem NAM
rem   wfrmitt.sql - WorkFlow ReMove ITemType
rem USAGE
rem   @wfrmitt
rem DESCRIPTION
rem   DANGER *** DANGER *** DANGER *** DANGER *** DANGER *** DANGER
rem
rem   You will be prompted for the item type, from a list of valid types.
rem   Deletes all workflow information for the specified item type.
rem   ALL OF IT.  I'm not kidding.
rem
rem   DANGER *** DANGER *** DANGER *** DANGER *** DANGER *** DANGER
rem
rem   dbdrv: none

set verify off
whenever sqlerror exit failure rollback;

select name from wf_item_types order by 1;
accept type prompt "Enter Item Type to delete: "

prompt ---- wf_item_activity_statuses_h
delete from WF_ITEM_ACTIVITY_STATUSES_H
where  PROCESS_ACTIVITY in
           (select INSTANCE_ID from WF_PROCESS_ACTIVITIES
            where  PROCESS_ITEM_TYPE = '&type'
            or     ACTIVITY_ITEM_TYPE = '&type');
commit;

prompt ---- wf_item_activity_statuses
delete from WF_ITEM_ACTIVITY_STATUSES
where  PROCESS_ACTIVITY in
           (select INSTANCE_ID from WF_PROCESS_ACTIVITIES
            where  PROCESS_ITEM_TYPE = '&type'
            or     ACTIVITY_ITEM_TYPE = '&type');
commit;

prompt ---- wf_item_attribute_values
delete from wf_item_attribute_values
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_items
delete from wf_items
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_notification_attributes
delete from wf_notification_attributes NA
where exists (select 'X' from wf_notifications N
              where N.notification_id = NA.notification_id
              and N.message_type = '&type');
commit;

prompt ---- wf_notifications
delete from wf_notifications
where message_type = '&type';
commit;

prompt ---- wf_routing_rule_attributes
delete from wf_routing_rule_attributes RA
where exists (select 'X' from wf_routing_rules R
              where R.rule_id = RA.rule_id
              and R.message_type = '&type');
commit;

prompt ---- wf_routing_rules
delete from wf_routing_rules
where message_type = '&type';
commit;

prompt ---- wf_activity_transitions
delete from wf_activity_transitions PAT
where  exists (select 'X' from wf_process_activities PAC
               where  PAT.FROM_PROCESS_ACTIVITY = PAC.instance_id
               and    PAC.PROCESS_ITEM_TYPE = '&type');
commit;

prompt ---- wf_activity_attr_values
delete from wf_activity_attr_values ATV
where  exists (select 'X' from wf_process_activities PAC
               where  ATV.PROCESS_ACTIVITY_ID = PAC.instance_id
               and    PAC.PROCESS_ITEM_TYPE = '&type');
commit;

prompt ---- wf_process_activities
delete from wf_process_activities
where  PROCESS_ITEM_TYPE = '&type';
commit;

prompt ---- wf_activity_attributes_tl
delete from wf_activity_attributes_tl
where  ACTIVITY_ITEM_TYPE = '&type';
commit;

prompt ---- wf_activity_attributes
delete from wf_activity_attributes
where  ACTIVITY_ITEM_TYPE = '&type';
commit;

prompt ---- wf_activities_tl
delete from wf_activities_tl
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_activities
delete from wf_activities
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_message_attributes_tl
delete from wf_message_attributes_tl
where  message_type = '&type';
commit;

prompt ---- wf_message_attributes
delete from wf_message_attributes
where  message_type = '&type';
commit;

prompt ---- wf_messages_tl
delete from wf_messages_tl
where  type = '&type';
commit;

prompt ---- wf_messages
delete from wf_messages
where  type = '&type';
commit;

prompt ---- wf_item_attributes_tl
delete from wf_item_attributes_tl
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_item_attributes
delete from wf_item_attributes
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_lookups_tl
delete from wf_lookups_tl LUC
where  exists (select 'X' from WF_LOOKUP_TYPES_TL LUT
               where  LUT.ITEM_TYPE = '&type'
               and    LUT.LOOKUP_TYPE = LUC.LOOKUP_TYPE);
commit;

prompt ---- wf_lookup_types_tl
delete from wf_lookup_types_tl
where  ITEM_TYPE = '&type';
commit;

prompt ---- wf_item_types_tl
delete from wf_item_types_tl
where  NAME = '&type';
commit;

prompt ---- wf_item_types
delete from wf_item_types
where  NAME = '&type';
commit;

exit;