rem HEADER
rem   $Header: wfchacta.sql 26.1 2001/11/05 20:04:23 ctilley ship $
rem NAME
rem   wfchacta.sql - WorkFlow CHange ACTivity Attribute
rem USAGE
rem   @wfchacta ACT_TYPE ACT_NAME OLD_ACTATT NEW_ACTATT
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 attribute name
prompt    @wfchacta ACT_TYPE ACT_NAME OLD_ACTATT NEW_ACTATT
prompt    Specify an activity attribute belong to ACT_NAME of ACT_TYPE.

define act_type = '&&1'
define act_name = '&&2'
define old_actatt = '&&3'
define new_actatt = '&&4'

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,
 ACTIVITY_NAME,
 ACTIVITY_VERSION,
 '&new_actatt',
 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 = '&act_name'
  and NAME = '&old_actatt';

prompt -- update wf_activity_attributes_tl
update WF_ACTIVITY_ATTRIBUTES_TL
   set NAME = '&new_actatt'
 where ACTIVITY_ITEM_TYPE = '&act_type'
   and ACTIVITY_NAME = '&act_name'
   and NAME = '&old_actatt';

prompt -- update wf_activity_attr_values
update WF_ACTIVITY_ATTR_VALUES WAV
   set NAME = '&new_actatt'
 where NAME = '&old_actatt'
   and exists
 (select null from WF_PROCESS_ACTIVITIES
   where PROCESS_ITEM_TYPE = '&act_type'
     and PROCESS_NAME = '&act_name'
     and INSTANCE_ID = WAV.PROCESS_ACTIVITY_ID);

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

commit;
exit;