rem HEADER
rem   $Header: wfchitt.sql 26.1 2001/11/05 20:05:09 ctilley ship $
rem NAME
rem   wfchitt.sql - WorkFlow CHange ITemType
rem USAGE
rem   @wfchitt OLD_TYPE NEW_TYPE
rem DESCRIPTION
rem   It will change the internal name and all references to it.
rem

rem dbdrv: none

set verify off
whenever SQLERROR exit failure rollback;

prompt -- Change item type name
prompt    @wfchitt OLD_TYPE NEW_TYPE

define old_type = '&&1'
define new_type = '&&2'

prompt ---- wf_item_types
insert into WF_ITEM_TYPES
(
 name,
 protect_level,
 custom_level,
 persistence_type,
 persistence_days,
 wf_selector,
 read_role,
 write_role,
 execute_role
) select '&new_type',
 protect_level,
 custom_level,
 persistence_type,
 persistence_days,
 wf_selector,
 read_role,
 write_role,
 execute_role
 from wf_item_types where name = '&old_type';

prompt ---- wf_item_types_tl
update wf_item_types_tl
   set name = '&new_type'
 where name = '&old_type';

prompt ---- wf_lookup_types_tl
update wf_lookup_types_tl
   set item_type = '&new_type'
 where item_type = '&old_type';

prompt ---- wf_item_attributes
insert into wf_item_attributes
(
 ITEM_TYPE,
 NAME,
 SEQUENCE,
 TYPE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 SUBTYPE,
 FORMAT,
 TEXT_DEFAULT,
 NUMBER_DEFAULT,
 DATE_DEFAULT
) select 
 '&new_type',
 NAME,
 SEQUENCE,
 TYPE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 SUBTYPE,
 FORMAT,
 TEXT_DEFAULT,
 NUMBER_DEFAULT,
 DATE_DEFAULT
 from wf_item_attributes
where item_type = '&old_type'; 

prompt ---- wf_item_attributes_tl
update wf_item_attributes_tl
   set item_type = '&new_type'
 where item_type = '&old_type';

prompt ---- wf_messages
insert into wf_messages
(
 TYPE,
 NAME,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 DEFAULT_PRIORITY,
 READ_ROLE,
 WRITE_ROLE
) select
 '&new_type',
 NAME,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 DEFAULT_PRIORITY,
 READ_ROLE,
 WRITE_ROLE
 from wf_messages
where type = '&old_type';

prompt ---- wf_messages_tl
update wf_messages_tl
   set type = '&new_type'
 where type = '&old_type';

prompt ---- wf_message_attributes
insert into wf_message_attributes
(
 MESSAGE_TYPE,
 MESSAGE_NAME,
 NAME,
 SEQUENCE,
 TYPE,
 SUBTYPE,
 VALUE_TYPE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 FORMAT,
 TEXT_DEFAULT,
 NUMBER_DEFAULT,
 DATE_DEFAULT
) select
 '&new_type',
 MESSAGE_NAME,
 NAME,
 SEQUENCE,
 TYPE,
 SUBTYPE,
 VALUE_TYPE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 FORMAT,
 TEXT_DEFAULT,
 NUMBER_DEFAULT,
 DATE_DEFAULT
 from wf_message_attributes
 where message_type = '&old_type';

prompt ---- wf_message_attributes_tl
update wf_message_attributes_tl
   set message_type = '&new_type'
 where message_type = '&old_type';

prompt ---- wf_notifications
update wf_notifications
   set message_type = '&new_type'
 where message_type = '&old_type';

prompt ---- wf_activities
insert into wf_activities
(
 ITEM_TYPE,
 NAME,
 VERSION,
 TYPE,
 RERUN,
 EXPAND_ROLE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 BEGIN_DATE,
 RUNNABLE_FLAG,
 ERROR_ITEM_TYPE,
 END_DATE,
 FUNCTION_TYPE,
 FUNCTION,
 RESULT_TYPE,
 COST,
 READ_ROLE,
 WRITE_ROLE,
 EXECUTE_ROLE,
 ICON_NAME,
 MESSAGE,
 ERROR_PROCESS
) select
 '&new_type',
 NAME,
 VERSION,
 TYPE,
 RERUN,
 EXPAND_ROLE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 BEGIN_DATE,
 RUNNABLE_FLAG,
 ERROR_ITEM_TYPE,
 END_DATE,
 FUNCTION_TYPE,
 FUNCTION,
 RESULT_TYPE,
 COST,
 READ_ROLE,
 WRITE_ROLE,
 EXECUTE_ROLE,
 ICON_NAME,
 MESSAGE,
 ERROR_PROCESS
 from wf_activities
where item_type = '&old_type';

prompt ---- wf_activities_tl
update wf_activities_tl
   set item_type = '&new_type'
 where item_type = '&old_type';

prompt ---- wf_activity_attributes
insert into wf_activity_attributes
(
 ACTIVITY_ITEM_TYPE,
 ACTIVITY_NAME,
 ACTIVITY_VERSION,
 NAME,
 SEQUENCE,
 TYPE,
 VALUE_TYPE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 SUBTYPE,
 FORMAT,
 TEXT_DEFAULT,
 NUMBER_DEFAULT,
 DATE_DEFAULT
) select
 '&new_type',
 ACTIVITY_NAME,
 ACTIVITY_VERSION,
 NAME,
 SEQUENCE,
 TYPE,
 VALUE_TYPE,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 SUBTYPE,
 FORMAT,
 TEXT_DEFAULT,
 NUMBER_DEFAULT,
 DATE_DEFAULT
from wf_activity_attributes
where activity_item_type = '&old_type';

prompt ---- wf_activity_attributes_tl
update wf_activity_attributes_tl
   set activity_item_type = '&new_type'
 where activity_item_type = '&old_type';

prompt ---- wf_process_activities (process_item_type)
update wf_process_activities
   set process_item_type = '&new_type'
 where process_item_type = '&old_type';

prompt ---- wf_process_activities (activity_item_type)
update wf_process_activities
   set activity_item_type = '&new_type'
 where activity_item_type = '&old_type';

prompt ---- wf_routing_rules
update wf_routing_rules
   set message_type = '&new_type'
 where message_type = '&old_type';

prompt ---- wf_items
update wf_items
   set item_type = '&new_type'
 where item_type = '&old_type';

prompt ---- wf_item_attribute_values
update wf_item_attribute_values
   set item_type = '&new_type'
 where item_type = '&old_type';

prompt deleting ---- wf_activity_attributes
delete wf_activity_attributes
 where activity_item_type = '&old_type';

prompt deleting ---- wf_activities
delete wf_activities
 where item_type = '&old_type';

prompt deleting ---- wf_message_attributes
delete wf_message_attributes
 where message_type = '&old_type';

prompt deleting ---- wf_messages
delete wf_messages
 where type = '&old_type';

prompt deleting ---- wf_item_attributes
delete wf_item_attributes
 where item_type = '&old_type';

prompt deleting ---- wf_item_types
delete WF_ITEM_TYPES
 where name = '&old_type';

commit;
exit;