rem HEADER
rem   $Header: wfchmsg.sql 26.1 2001/11/05 20:06:46 ctilley ship $
rem NAME
rem   wfchmsg.sql - WorkFlow CHange MeSsaGe
rem USAGE
rem   @wfchmsg MSG_TYPE OLD_MSG NEW_MSG
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 message name
prompt    @wfchmsg MSG_TYPE OLD_MSG NEW_MSG
prompt    MSG_TYPE is the item_type where this message belong.

define msg_type = '&&1'
define old_message = '&&2'
define new_message = '&&3'

prompt -- insert new message
insert into wf_messages
(
 TYPE,
 NAME,
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 DEFAULT_PRIORITY,
 READ_ROLE,
 WRITE_ROLE
) select
 TYPE,
 '&new_message',
 PROTECT_LEVEL,
 CUSTOM_LEVEL,
 DEFAULT_PRIORITY,
 READ_ROLE,
 WRITE_ROLE
from WF_MESSAGES
where TYPE = '&msg_type'
  and NAME = '&old_message';

prompt -- update wf_messages_tl
update WF_MESSAGES_TL
   set NAME = '&new_message'
 where TYPE = '&msg_type'
   and NAME = '&old_message';

prompt -- update wf_activities
update WF_ACTIVITIES
   set MESSAGE = '&new_message'
 where ITEM_TYPE = '&msg_type'
   and MESSAGE = '&old_message';

prompt -- update wf_notifications
update WF_NOTIFICATIONS
   set MESSAGE_NAME = '&new_message'
 where MESSAGE_TYPE = '&msg_type'
   and MESSAGE_NAME = '&old_message';

prompt -- delete old message
delete WF_MESSAGES
 where TYPE = '&msg_type'
   and NAME = '&old_message';

commit;
exit;