rem
rem   $Header: wfverchk.sql 26.1 2001/11/05 20:24:04 ctilley ship $
rem NAME
rem   wfverchk.sql - WorkFlow Version Check
rem USAGE
rem   sqlplus <usr>/<passwd>@db @wfverchk.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                      wfverfix.sql corrects version errors
--
rem   dbdrv: none
--
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

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;

commit;
exit;