rem HEADER
rem   $Header: wfchmsga.sql 26.1 2001/11/05 20:07:48 ctilley ship $
rem NAME
rem   wfchmsga.sql - WorkFlow CHange MeSsaGe Attribute
rem USAGE
rem   @wfchmsga MSG_TYPE MSG_NAME OLD_ATT NEW_ATT
rem DESCRIPTION
rem   It will change the internal name.
rem   It does not change the reference inside the message body.  It is up
rem   to the new wft file to have the correct reference.
rem

rem dbdrv: none

set serveroutput on
set verify off
whenever SQLERROR exit failure rollback;

prompt -- Change message attribute name
prompt    @wfchmsga MSG_TYPE MSG_NAME OLD_ATT NEW_ATT
prompt    This attribute belongs to MSG_NAME of MSG_TYPE.

define msg_type = '&&1'
define msg_name = '&&2'
define old_att = '&&3'
define new_att = '&&4'

declare
  newseq number;
  oldseq number;
begin
  select max(sequence) + 1 into newseq
    from WF_MESSAGE_ATTRIBUTES
   where MESSAGE_TYPE = '&msg_type'
     and MESSAGE_NAME = '&msg_name';

  select sequence into oldseq
    from WF_MESSAGE_ATTRIBUTES
   where MESSAGE_TYPE = '&msg_type'
     and MESSAGE_NAME = '&msg_name'
     and NAME = '&old_att';

  dbms_output.put_line('-- update sequence for old message attribute');
  update WF_MESSAGE_ATTRIBUTES
     set SEQUENCE = newseq
   where MESSAGE_TYPE = '&msg_type'
     and MESSAGE_NAME = '&msg_name'
     and NAME = '&old_att';

  dbms_output.put_line('-- insert new message attribute');
  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
   MESSAGE_TYPE,
   MESSAGE_NAME,
   '&new_att',
   oldseq,
   TYPE,
   SUBTYPE,
   VALUE_TYPE,
   PROTECT_LEVEL,
   CUSTOM_LEVEL,
   FORMAT,
   TEXT_DEFAULT,
   NUMBER_DEFAULT,
   DATE_DEFAULT
  from WF_MESSAGE_ATTRIBUTES
  where MESSAGE_TYPE = '&msg_type'
    and MESSAGE_NAME = '&msg_name'
    and NAME = '&old_att';
exception
  when others then
    dbms_output.put_line('Failed to insert &new_att with sequence '||to_char(newseq));
    raise;
end;
/

prompt -- update wf_message_attributes_tl
update WF_MESSAGE_ATTRIBUTES_TL
   set NAME = '&new_att'
 where MESSAGE_TYPE = '&msg_type'
   and MESSAGE_NAME = '&msg_name'
   and NAME = '&old_att';

prompt -- delete old message attribute
delete WF_MESSAGE_ATTRIBUTES
 where MESSAGE_TYPE = '&msg_type'
   and MESSAGE_NAME = '&msg_name'
   and NAME = '&old_att';

commit;
exit;