rem HEADER
rem   $Header: wfchlut.sql 26.1 2001/11/05 20:05:50 ctilley ship $
rem NAME
rem   wfchlut.sql - WorkFlow CHange LookUp Type
rem USAGE
rem   @wfchlut OLD_LUT NEW_LUT
rem DESCRIPTION
rem   It will change the internal name and all references to it.
rem   It attempts to use '@', '~' and '~@' as prefix in that order.
rem   Reports error if all attempts failed.
rem NOTE
rem   If the display name is longer than 78 bytes, it maybe truncated.
rem   In an extremely rare situation, the truncated display name may
rem   be in conflict with an existing display name.

rem dbdrv: none

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

prompt -- Change lookup type
prompt    @wfchlut OLD_LUT NEW_LUT
prompt    Truncation of display name may occur if display name is longer
prompt    than 78 bytes.

define old_lut = '&&1'
define new_lut = '&&2'

declare
  dummy      varchar2(80);
  dname_pfx  varchar2(2) := '@';
  NO_PREFIX_EXCEPTION exception;
begin
  dbms_output.put_line('-- searching for a prefix to use');

  begin
    select null into dummy
      from WF_LOOKUP_TYPES_TL
     where LOOKUP_TYPE = '&old_lut'
       and DISPLAY_NAME like dname_pfx||'%';
    
    dname_pfx := '~';
    select null into dummy
      from WF_LOOKUP_TYPES_TL
     where LOOKUP_TYPE = '&old_lut'
       and DISPLAY_NAME like dname_pfx||'%';

    dname_pfx := '~@';
    select null into dummy
      from WF_LOOKUP_TYPES_TL
     where LOOKUP_TYPE = '&old_lut'
       and DISPLAY_NAME like dname_pfx||'%';

    raise NO_PREFIX_EXCEPTION;
  exception
    when NO_DATA_FOUND then
      null;

    when NO_PREFIX_EXCEPTION then
      dbms_output.put_line('--   no valid prefix found');
      raise;
  end;
  
  dbms_output.put_line('-- insert new lookup type with prefix "'||dname_pfx
                       ||'"');
  begin
    insert into wf_lookup_types_tl
    (
     LOOKUP_TYPE,
     DISPLAY_NAME,
     LANGUAGE,
     ITEM_TYPE,
     SOURCE_LANG,
     PROTECT_LEVEL,
     CUSTOM_LEVEL,
     DESCRIPTION
    ) select
     '&new_lut',
     substr(dname_pfx||DISPLAY_NAME, 1, 80),
     LANGUAGE,
     ITEM_TYPE,
     SOURCE_LANG,
     PROTECT_LEVEL,
     CUSTOM_LEVEL,
     DESCRIPTION
    from wf_lookup_types_tl
    where lookup_type = '&old_lut';

  exception
    when OTHERS then
      dbms_output.put_line('--  failed to insert prefixed display name');
      raise;
  end;

  dbms_output.put_line('-- updating wf_lookups_tl');
  update wf_lookups_tl
     set lookup_type = '&new_lut'
   where lookup_type = '&old_lut';

  dbms_output.put_line('-- updating wf_activities');
  update wf_activities
     set result_type = '&new_lut'
   where result_type = '&old_lut';

  dbms_output.put_line('-- deleting wf_lookup_types_tl');
  delete wf_lookup_types_tl
  where lookup_type = '&old_lut';

  dbms_output.put_line('-- update lookup type display name');
  update wf_lookup_types_tl
     set DISPLAY_NAME = substr(DISPLAY_NAME, length(dname_pfx)+1)
   where LOOKUP_TYPE = '&new_lut';

exception
  when OTHERS then
    dbms_output.put_line('-- some excpetion caught, rollback and exit.');
    rollback;
end;
/

commit;
exit;