rem HEADER
rem   $Header: wfchluc.sql 26.1 2001/11/05 20:05:25 ctilley ship $
rem NAME
rem   wfchluc.sql - WorkFlow CHange LookUp Code
rem USAGE
rem   @wfchluc LOOKUP_TYPE OLD_LUC NEW_LUC
rem DESCRIPTION
rem   It will change the internal name and all references to it.
rem

rem dbdrv: none

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

prompt -- Change lookup code
prompt    @wfchluc LOOKUP_TYPE OLD_LUC NEW_LUC

define lu_type = '&&1'
define old_luc = '&&2'
define new_luc = '&&3'

REM ### Should we forbid changing of hidden attribute?

prompt -- updating wf_lookups_tl
update WF_LOOKUPS_TL
   set LOOKUP_CODE = '&new_luc'
 where LOOKUP_TYPE = '&lu_type'
   and LOOKUP_CODE = '&old_luc';

prompt -- updating wf_activity_transistions
update WF_ACTIVITY_TRANSITIONS WAT
   set RESULT_CODE = '&new_luc'
 where RESULT_CODE = '&old_luc'
   and exists
 (select null from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WA
   where WA.RESULT_TYPE = '&lu_type'
     and WPA.PROCESS_ITEM_TYPE = WA.ITEM_TYPE
     and WPA.PROCESS_NAME = WA.NAME
     and WPA.INSTANCE_ID = WAT.FROM_PROCESS_ACTIVITY);

commit;
exit;