rem HEADER
rem   $Header: wfchact.sql 26.1 2001/11/05 20:04:06 ctilley ship $
rem NAME
rem   wfchact.sql - WorkFlow CHange ACTivity
rem USAGE
rem   @wfchact ACT_TYPE OLD_ACT NEW_ACT
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 activity name
prompt    @wfchact ACT_TYPE OLD_ACT NEW_ACT
prompt    ACT_TYPE is the item_type where this activity belong.

define act_type = '&&1'
define old_act = '&&2'
define new_act = '&&3'

prompt -- insert new activity
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
 ITEM_TYPE,
 '&new_act',
 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 = '&act_type'
  and NAME = '&old_act';

prompt -- update wf_activities_tl
update WF_ACTIVITIES_TL
   set NAME = '&new_act'
 where ITEM_TYPE = '&act_type'
   and NAME = '&old_act';

prompt -- update wf_process_activities
update WF_PROCESS_ACTIVITIES
   set ACTIVITY_NAME = '&new_act'
 where PROCESS_ITEM_TYPE = '&act_type'
   and ACTIVITY_NAME = '&old_act';

prompt -- update wf_process_activities 
update WF_PROCESS_ACTIVITIES
   set PROCESS_NAME = '&new_act'
 where PROCESS_ITEM_TYPE = '&act_type'
   and PROCESS_NAME = '&old_act';

prompt -- insert new 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
 ACTIVITY_ITEM_TYPE,
 '&new_act',
 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 = '&act_type'
  and ACTIVITY_NAME = '&old_act';

prompt -- update wf_activity_attributes_tl
update WF_ACTIVITY_ATTRIBUTES_TL
   set ACTIVITY_NAME = '&new_act'
 where ACTIVITY_ITEM_TYPE = '&act_type'
   and ACTIVITY_NAME = '&old_act';

prompt -- delete old wf_activity_attributes
delete from WF_ACTIVITY_ATTRIBUTES
 where ACTIVITY_ITEM_TYPE = '&act_type'
   and ACTIVITY_NAME = '&old_act';

prompt -- delete old wf_activities
delete from WF_ACTIVITIES
 where ITEM_TYPE = '&act_type'
  and NAME = '&old_act';

commit;
exit;