rem   $Header: wfverupd.sql 26.1 2001/11/05 20:25:00 ctilley ship $
rem NAME
rem   wfverupd.sql - WorkFlow Version Check and Update
rem USAGE
rem   sqlplus <usr>/<passwd>@db @wfverupd.sql
rem DESCRIPTION
rem   Check all workflow activities for potentially invalid version
rem   histories (more than one version of an activity active at any 
rem   given time).  Correct any errors found.
rem
rem HISTORY
rem   19-OCT-98  RSEIDEN separated wfverchk.sql into two scripts:
rem                      wfverchk.sql checks for version errors
rem                      wfverupd.sql corrects version errors
--
rem   dbdrv: none
--

WHENEVER SQLERROR EXIT FAILURE

select item_type, name, begin_date, end_date, version
from wf_activities a1
where (end_date is not null and
       end_date > 
        (select min(a2.begin_date)
        from wf_activities a2
        where a2.item_type = a1.item_type
        and a2.name = a1.name
        and a2.begin_date > a1.begin_date))
or (end_date is null and
    exists (select null
        from wf_activities a2
        where a2.item_type = a1.item_type
        and a2.name = a1.name
        and a2.begin_date > a1.begin_date))
order by 1,2,3;

update wf_activities a1 set
  end_date =
    (select min(a2.begin_date)
    from wf_activities a2
    where a2.item_type = a1.item_type
    and a2.name = a1.name
    and a2.begin_date > a1.begin_date)
where (end_date is not null and
       end_date > 
        (select min(a2.begin_date)
        from wf_activities a2
        where a2.item_type = a1.item_type
        and a2.name = a1.name
        and a2.begin_date > a1.begin_date))
or (end_date is null and
    exists (select null
        from wf_activities a2
        where a2.item_type = a1.item_type
        and a2.name = a1.name
        and a2.begin_date > a1.begin_date));
commit;
exit;