Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wf250dat.sql

REM $Header: wf250dat.sql 26.1 2001/11/05 21:59:35 ctilley ship $
REM +======================================================================+
REM | Copyright (c) 1998 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM   NAME
REM       wf250dat.sql - WorkFlow 2.5.0 Data Upgrade Script
REM   DESCRIPTION
REM       Upgrade Workflow 2.0.3 -> Workflow 2.5.0 data.
REM   USAGE
REM       sqlplus apps/apps @wf250dat
REM       sqlplus wfinst/wfinst @wf250dat
REM   NOTES
REM       This script populate columns
REM
REM
--
REM   dbdrv: none
--

WHENEVER SQLERROR EXIT FAILURE ROLLBACK
 
DEFINE hdr = "$Header: wf250dat.sql 26.1 2001/11/05 21:59:35 ctilley ship $"
 
REM WF_LOCAL_USER_ROLES
update WF_LOCAL_USER_ROLES lur
   set (USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID) = 
	(select nvl(u.ORIG_SYSTEM,'WF_LOCAL_USERS'), nvl(u.ORIG_SYSTEM_ID, 0)
	   from WF_USERS u where lur.USER_NAME = u.NAME)
 where USER_ORIG_SYSTEM is null
    or USER_ORIG_SYSTEM_ID is null;

commit;
      
update WF_LOCAL_USER_ROLES lur
   set (ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID) = 
	(select nvl(r.ORIG_SYSTEM,'WF_LOCAL_ROLES'), nvl(r.ORIG_SYSTEM_ID, 0)
	   from WF_ROLES r where lur.ROLE_NAME = r.NAME)
 where ROLE_ORIG_SYSTEM is null
    or ROLE_ORIG_SYSTEM_ID is null;

commit;

REM WF_ACTIVITIES
update WF_ACTIVITIES
   set error_item_type = 'WFERROR'
 where error_item_type is null;

commit;

update WF_ACTIVITIES
   set runnable_flag = 'Y'
 where runnable_flag is null;

commit;

REM copy all existing defered data to the deferred queue
declare
  cursor curs_deferred is
    select 
         CWIAS.ITEM_TYPE, 
         CWIAS.ITEM_KEY,
         CWIAS.PROCESS_ACTIVITY,
         greatest((CWIAS.BEGIN_DATE  - sysdate)*86400,0) delay
    from WF_ITEM_ACTIVITY_STATUSES CWIAS, WF_PROCESS_ACTIVITIES CWPA,
         WF_ACTIVITIES CWA, WF_ITEMS WI,
         WF_PROCESS_ACTIVITIES PWPA, WF_ITEM_ACTIVITY_STATUSES PWIAS
    where CWIAS.ACTIVITY_STATUS = 'DEFERRED'
    and CWIAS.PROCESS_ACTIVITY = CWPA.INSTANCE_ID
    and CWPA.ACTIVITY_ITEM_TYPE = CWA.ITEM_TYPE
    and CWPA.ACTIVITY_NAME = CWA.NAME
    and CWIAS.ITEM_TYPE = WI.ITEM_TYPE
    and CWIAS.ITEM_KEY = WI.ITEM_KEY
    and WI.BEGIN_DATE >= CWA.BEGIN_DATE
    and WI.BEGIN_DATE < nvl(CWA.END_DATE, WI.BEGIN_DATE+1)
    and CWPA.PROCESS_NAME = PWPA.ACTIVITY_NAME
    and CWPA.PROCESS_ITEM_TYPE = PWPA.ACTIVITY_ITEM_TYPE
    and PWPA.INSTANCE_ID = PWIAS.PROCESS_ACTIVITY
    and PWIAS.ITEM_TYPE = CWIAS.ITEM_TYPE
    and PWIAS.ITEM_KEY = CWIAS.ITEM_KEY
    and PWIAS.ACTIVITY_STATUS <> 'SUSPEND';
msg_id raw(16);
begin 
    for def_rec in curs_deferred loop
    	   wf_queue.enqueue_event
              (queuename=>wf_queue.DeferredQueue,
               itemtype=> def_rec.item_type,
               itemkey=>def_rec.item_key,
               actid=>def_rec.process_activity,
               delay=>def_rec.delay,
               message_handle=>msg_id);
           -- dont need a message handle 
           msg_id := null;
    end loop;
end;
/

commit;

REM Set Persistence Days
update WF_ITEM_TYPES
set persistence_days = 0
where persistence_type = 'TEMP'
  and persistence_days is null;

commit;

REM
REM *** #TIMEOUT conversions ***
REM

REM Convert wf_activities.timeout to #TIMEOUT attributes.
REM NOTE: Because of the same version of a process might span multiple
REM versions of a child activity, a 100% accurate conversion is not 
REM possible.  Use the version of the child activity active on the
REM begin_date of the process version.  Should be close enough.

insert into WF_ACTIVITY_ATTR_VALUES (
    PROCESS_ACTIVITY_ID,
    NAME,
    VALUE_TYPE,
    PROTECT_LEVEL,
    CUSTOM_LEVEL,
    TEXT_VALUE,
    NUMBER_VALUE,
    DATE_VALUE
) select
    WPA.INSTANCE_ID,
    '#TIMEOUT',
    'CONSTANT',
    WPA.PROTECT_LEVEL,
    WPA.CUSTOM_LEVEL,
    null,
    WAC.TIMEOUT,
    to_date(null)
from WF_PROCESS_ACTIVITIES WPA, WF_ACTIVITIES WAC, WF_ACTIVITIES WAP
where WPA.ACTIVITY_ITEM_TYPE = WAC.ITEM_TYPE
and WPA.ACTIVITY_NAME = WAC.NAME
and WPA.PROCESS_ITEM_TYPE = WAP.ITEM_TYPE
and WPA.PROCESS_NAME = WAP.NAME
and WPA.PROCESS_VERSION = WAP.VERSION
and WAP.BEGIN_DATE >= WAC.BEGIN_DATE
and WAP.BEGIN_DATE < nvl(WAC.END_DATE, WAP.BEGIN_DATE + 1)
and NVL(WAC.TIMEOUT, 0) <> 0
and not exists (
  select null
  from WF_ACTIVITY_ATTR_VALUES SUB
  where SUB.PROCESS_ACTIVITY_ID = WPA.INSTANCE_ID
  and SUB.NAME = '#TIMEOUT');

REM Null out old wf_activities.timeout column to avoid temptation

update WF_ACTIVITIES set
  TIMEOUT = ''
where TIMEOUT is not null;

commit;

REM Remove any vestigial attribute definitions for
REM #TIMEOUT and #PRIORITY attributes.

delete from wf_activity_attributes_tl
where name in ('#TIMEOUT', '#PRIORITY');

commit;

delete from wf_activity_attributes
where name in ('#TIMEOUT', '#PRIORITY');

commit;

REM Set due_date in WIAS for activities to timeout
update WF_ITEM_ACTIVITY_STATUSES S set
  DUE_DATE = (select decode(WAAV.NUMBER_VALUE,
                            0, '', 
                            S.BEGIN_DATE + (WAAV.NUMBER_VALUE / 1440))
             from WF_PROCESS_ACTIVITIES P, WF_ACTIVITY_ATTR_VALUES WAAV
             where S.PROCESS_ACTIVITY = P.INSTANCE_ID
             and P.INSTANCE_ID = WAAV.PROCESS_ACTIVITY_ID
             and WAAV.NAME = '#TIMEOUT')
where S.ACTIVITY_STATUS not in ('COMPLETE', 'ERROR')
and S.DUE_DATE is null;

commit;
REM
REM *** End #TIMEOUT conversions ***
REM

commit;
exit;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de