Rem
Rem $Header: whdev/2.0/owb/shiphome/owb/patches/bug10236629/fix10236629.sql /main/7 2010/11/07 23:54:27 swu Exp $
Rem
Rem fix10236629.sql - please connect as OWBSYS (owb11g or later) or repository owner(before owb11g) to run this script.
Rem
Rem Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      fix10236629.sql
Rem
Rem    DESCRIPTION
Rem      Bug 10236629 - "duplicate transition in process flow"
Rem      Also duplicate bug of bug 5603953 and bug 7603716.
Rem      This bug is about Process Flows where an activity can have 2 outgoing 
Rem      transitions with the same order number. This causes execution problems
Rem      in Process Flows when the flow path is incorrect.
Rem      This bug is fixed in 11.2.0.2 Release. However, for existing Process Flows
Rem      with this duplicate transitions ordering problem, this script should be run  
Rem      to identify and report the problematic Process Flows that have the 
Rem      duplicate transition ordering problem. User can then manually edit these 
Rem      Process Flow and fix the transitions order. 
Rem
Rem    NOTES
Rem      (1)  To run this script, log in to SQL*Plus as OWBSYS.
Rem      ----------------------------------------------------------------------------
Rem      For example,
Rem      $ sqlplus OWBSYS/<passwdofOWBSYS>
Rem        SQL>  @fix10236629.sql
Rem        Workspace: MP9 Project: TTT_DWH ProcessFlow Module: DAILYRUN Process Flow
Rem        Package: DAILY ProcessFlow: DAILY_OVERALL Activity: DM_ALL has
Rem        duplicate outgoing transitions!
Rem        Workspace: TAU Project: T_DWH ProcessFlow Module: EDW Process Flow Package:
Rem        PRODUCT ProcessFlow: PRODUCT_LNK Activity: PRODUCT_BRAND has duplicate
Rem        outgoing transitions!
Rem
Rem        PL/SQL procedure successfully completed.
Rem           
Rem      ----------------------------------------------------------------------------
Rem
Rem      (2)  After the problematic process flows are identified, follow these steps 
Rem      to manually re-order the transitions:
Rem        To reorder the transforms, select the activity with duplicate outbound
Rem        transitions, right mouse and select Edit Details,
Rem        Select Outgoing Transitions
Rem        Click and hold on any row header till the black outline appears to 
Rem        show it is selected.  
Rem        Drag up or down to force the transitions to be reordered.
Rem        Click OK. Transitions are reordered.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    swu         10/31/10 - Created
Rem

SET SERVEROUTPUT ON

DECLARE
  result sys_refcursor;
  detail varchar2(1000);
  query1 varchar2(2000):='
    SELECT ''Workspace: ''||wks.name||'' Project: ''||proj.name||'' ProcessFlow Module: ''||modu.name||'' Process Flow Package: ''||pkg.name||'' ProcessFlow: ''||pro.name||'' Activity: ''||act.name||'' has duplicate outgoing transitions!''
    FROM cmpbaseprocessactivity_r act,
      cmpprocess_r pro,
      cmpprocesspackage_r pkg,
      cmpprocessinstalledmodule_r modu,
      cmpwbproject_r proj,
      cmpworkspace_r wks,
      (SELECT sourceactivity,owningprocess,
        (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 act.elementid           =trans.sourceactivity
    AND trans.owningprocess       =pro.elementid
    AND pro.owningpackage         =pkg.elementid
    AND pkg.PROCESSINSTALLEDMODULE=modu.elementid
    AND modu.owningproject        =proj.elementid
    AND proj.owningworkspace      =wks.elementid
    AND wks.elementid             >1
    AND trans.isduplicate         >0 ';

  query2 varchar2(2000):='
    SELECT ''Project: ''||proj.name||'' ProcessFlow Module: ''||modu.name||'' Process Flow Package: ''||pkg.name||'' ProcessFlow: ''||pro.name||'' Activity: ''||act.name||'' has duplicate outgoing transitions!''
    FROM cmpbaseprocessactivity_r act,
      cmpprocess_r pro,
      cmpprocesspackage_r pkg,
      cmpprocessinstalledmodule_r modu,
      cmpwbproject_r proj,
      (SELECT sourceactivity,owningprocess,
        (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 act.elementid           =trans.sourceactivity
    AND trans.owningprocess       =pro.elementid
    AND pro.owningpackage         =pkg.elementid
    AND pkg.PROCESSINSTALLEDMODULE=modu.elementid
    AND modu.owningproject        =proj.elementid
    AND trans.isduplicate         >0 ';  

BEGIN
    OPEN result FOR query1;
    LOOP
      FETCH result INTO detail;
      EXIT WHEN result%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(detail);
    END LOOP;
    CLOSE result;
    EXCEPTION
      WHEN OTHERS THEN
        OPEN result FOR query2;
        LOOP
          FETCH result INTO detail;
          EXIT WHEN result%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(detail);
        END LOOP;
        CLOSE result;

END;
/