Rem
Rem $Header: whdev/2.0/owb/shiphome/owb/patches/bug10236629/autofix.sql /main/3 2010/11/09 18:35:05 wwan Exp $
Rem
Rem autofix.sql  - use this to fix the duplicate transitions at your own risk!! 
Rem     The prereq is that it is not important with the transition order
Rem     Stop other owb operations before run this script
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      autofix.sql - This script is used to fix the duplicate transition ordering 
Rem      issue in Process Flow.
Rem
Rem    DESCRIPTION
Rem    After running the fix10236629.sql to identify the Process Flows having the 
Rem    duplicate transition ordering issue. This script can be used to re-order the 
Rem    problematic transitions such that they have the correct ordering. 
Rem    Prerequisite: After running this script, the transition ordering of those 
Rem    transitions having duplicate transition orders will be changed, it will be 
Rem    modified in a random sequence starting with 0. If you want to specify the 
Rem    ordering manually, this script should not be used, but rather fix the ordering 
Rem    manually.
Rem
Rem    NOTES
Rem      Run fix10236629.sql first.
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    swu         11/04/10 - Created
Rem

SET SERVEROUTPUT ON

DECLARE
  source_actid NUMBER;
  process_id   NUMBER;
  trans_count  NUMBER;
  update_count NUMBER;
  cur1 sys_refcursor;
  activity_name VARCHAR2(20);
  process_name  VARCHAR2(20);
  pfpkg_name    VARCHAR2(20);
  pfmodule_name VARCHAR2(20);
  project_name  VARCHAR2(20);
  query1        VARCHAR2(1000):='select sourceactivity,owningprocess,totalcnt from (SELECT sourceactivity,owningprocess, count(transitionorder) AS totalcnt,
(COUNT(transitionorder)-COUNT(DISTINCT transitionorder)) AS isduplicate
FROM cmpprocesstransition_r t,
cmpprocess_r p
WHERE t.owningprocess=p.elementid
GROUP BY sourceactivity,owningprocess) trans
where isduplicate>0';
BEGIN
  OPEN cur1 FOR query1;
  LOOP
    FETCH cur1 INTO source_actid,process_id,trans_count;
    EXIT WHEN cur1%NOTFOUND;
    --dbms_output.put_line(source_actid||' '||process_id||' '||trans_count);
/*
    SELECT act.name,
      pro.name,
      pkg.name,
      mod.name,
      proj.name
    INTO activity_name,
      process_name,
      pfpkg_name,
      pfmodule_name,
      project_name
    FROM cmpbaseprocessactivity_r act,
      cmpprocess_r pro,
      cmpprocesspackage_r pkg,
      cmpprocessinstalledmodule_r mod,
      cmpwbproject_r proj
    WHERE act.elementid           =source_actid
    AND pro.elementid             =process_id
    AND pkg.elementid             =pro.owningpackage
    AND pkg.PROCESSINSTALLEDMODULE=mod.elementid
    AND proj.elementid            =mod.owningproject
    AND proj.owningworkspace      >1;

    dbms_output.put_line(project_name||' '||pfmodule_name||' '||pfpkg_name||' '||process_name||' '||activity_name||' has duplicate outgoing transitions! Total transition count: '||trans_count);
*/
    UPDATE
      (SELECT * FROM cmpscoclasses WHERE r_16=source_actid
      ) t1
    SET t1.i_7       =rownum-1;

    update_count    :=SQL%rowcount;
    dbms_output.put_line(update_count||' rows have been updated for sourceactivity: '||source_actid);
    IF update_count <> trans_count THEN
      dbms_output.put_line('update count does not match total transition count, now roll back!');
      ROLLBACK;
    END IF;
  END LOOP;
  CLOSE cur1;
EXCEPTION
WHEN OTHERS THEN
  ROLLBACK;
END;
/