-- ===============================================================================
-- = 
-- = 
-- = To Execute and View results:
-- =
-- = SQL> set serveroutput on
-- = SQL> set serveroutput on size 100000
-- = SQL> exec Bug5416660_FixProcessFlows.FixProcessFlows(0); -- Report Only
-- = SQL> exec Bug5416660_FixProcessFlows.FixProcessFlows(1); -- Report and Fix
-- =
-- ===============================================================================

CREATE OR REPLACE PACKAGE BODY Bug5416660_FixProcessFlows AS

TYPE TTYPE is REF CURSOR;

-- ================================================================
-- = Output Debug Messages
-- ================================================================
PROCEDURE MyDebug(debugMessage VARCHAR2)
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(debugMessage);
END MyDebug;

-- ================================================================
-- = Entry point.
-- ================================================================
PROCEDURE FixProcessFlows(executeMode  NUMBER)
IS
BEGIN

    MyDebug('==================================================');
    MyDebug('Bug5416660_FixProcessFlows:FixProcessFlows - Start');
    MyDebug('==================================================');
    
    MyDebug('.');
    if (executeMode = 0) then
        MyDebug('Run Mode : [0 = Report Only]');
    elsif (executeMode = 1) then
        MyDebug('Run Mode : [1 = Report and Fix]');
    else
        MyDebug('Run Mode : Must use modes [0] (Report Only) or [1] (Report and Fix)');
    end if;
    MyDebug('.');
    if (executeMode = 0 or executeMode = 1) then
        findDuplicateTransitions(executeMode);
    end if;
    
    MyDebug('==================================================');
    MyDebug('Bug5416660_FixProcessFlows:FixProcessFlows - End');
    MyDebug('==================================================');
    
END FixProcessFlows;

-- ================================================================
-- = Procedure to identify duplicate transitions.
-- ================================================================
PROCEDURE findDuplicateTransitions(executeMode  NUMBER)
IS
    tcursor TTYPE;
    t_name                  cmpprocesstransition_v.name%TYPE;
    t_elementID             cmpprocesstransition_v.elementid%TYPE;
    t_sourceActivity        cmpprocesstransition_v.sourceactivity%TYPE;
    t_transitionOrder       cmpprocesstransition_v.transitionorder%TYPE;
    t_owningProcess         cmpprocesstransition_v.owningprocess%TYPE;

    prev_elementID          cmpprocesstransition_v.elementid%TYPE;
    prev_sourceActivity     cmpprocesstransition_v.sourceactivity%TYPE;
    prev_transitionOrder    cmpprocesstransition_v.transitionorder%TYPE;
        
BEGIN
    open tcursor for 
        select 
        name, elementid, sourceactivity, transitionorder, owningprocess 
        from 
        cmpprocesstransition_v 
        order by 
        sourceactivity, transitionorder;
    
    loop
        begin
            fetch tcursor 
                into t_name, t_elementID, t_sourceActivity, t_transitionOrder, t_owningProcess;
                exit when tcursor%NOTFOUND;
                            
            -- If we are in the same sourceActivity - and we have a duplicate transition order,
            -- then we have a problem.
            IF (prev_sourceActivity = t_sourceActivity AND
                prev_transitionOrder = t_transitionOrder) THEN
                dumpProcessInformation(executeMode, t_owningProcess, t_sourceActivity, t_elementID, prev_elementID);
            END IF;
            
            -- Save off the current values into the previous values
            prev_elementID       := t_elementID;
            prev_sourceActivity  := t_sourceActivity;
            prev_transitionOrder := t_transitionOrder;

        end;
    end loop;
    close tcursor;
    
EXCEPTION
    when others then	 
        ROLLBACK;
        raise_application_error(-20002, 'Bug5416660_FixProcessFlows.findDuplicateTransitions encountered the SQL error:'||SUBSTR(SQLERRM,1,200));

END findDuplicateTransitions;

-- ================================================================
-- = Procedure to identify duplicate transitions.
-- ================================================================
PROCEDURE dumpProcessInformation(executeMode      NUMBER,
                                 l_owningProcess  cmpprocesstransition_v.owningprocess%TYPE,
                                 l_sourceActivity cmpprocesstransition_v.sourceactivity%TYPE,
                                 l_elementID      cmpprocesstransition_v.elementid%TYPE,
                                 lprev_elementID  cmpprocesstransition_v.elementid%TYPE)
IS
    tcursor TTYPE;
    projectName     cmpwbproject_v.name%TYPE;
    moduleName      cmpprocessinstalledmodule_v.name%TYPE;
    packageName     cmpprocesspackage_v.name%TYPE;
    processName     cmpprocess_v.name%TYPE;
    transitionName  cmpprocesstransition_v.name%TYPE;
    sourceActivity  cmpprocesstransition_v.sourceactivity%TYPE;
    targetActivity  cmpprocesstransition_v.targetactivity%TYPE;
    
    processSource   cmpprocessactivity_v.name%TYPE;
    processTarget   cmpprocessactivity_v.name%TYPE;
    
    loopCount       NUMBER;
    
BEGIN
    open tcursor for 
        select A.name, B.name, C.name, D.name, E.name, E.sourceactivity, E.targetactivity 
        from   cmpwbproject_v A, cmpprocessinstalledmodule_v B, cmpprocesspackage_v C, cmpprocess_v D, cmpprocesstransition_v E
        where  A.elementID = B.owningproject and B.elementID = C.owningfolder and C.elementID = D.owningpackage and D.elementID = E.owningprocess and E.elementID in (l_elementID,lprev_elementID) order by E.elementID;
    
    loopCount := 0; 
    
    loop
        begin
            fetch tcursor 
                into projectName, moduleName, packageName, processName, transitionName, sourceActivity, targetActivity;
                exit when tcursor%NOTFOUND;
              
            -- Pull out the source and target process activity names
            select name into processSource from cmpprocessactivity_v where elementid = sourceActivity;
            select name into processTarget from cmpprocessactivity_v where elementid = targetActivity;
                
            if (loopCount = 0) then
                MyDebug('Project [' || projectName || '] Module [' || moduleName || ']');
                MyDebug('...Package [' || packageName || '] Process [' || processName || ']');
                loopCount := loopCount + 1;
            end if;
            
            MyDebug('......Invalid Transition [' || processSource || '--->' || processTarget || ']');
        end;
    end loop;
    close tcursor;

    -- If we are operation with executeMode = 1, then let's fix this error.
    if (executeMode = 1) then
        fixProcessTransitionOrder(l_owningProcess, l_sourceActivity);
    end if;
    
    MyDebug('.'); 
    
EXCEPTION
    when others then	 
        ROLLBACK;
        raise_application_error(-20002, 'Bug5416660_FixProcessFlows.findDuplicateTransitions encountered the SQL error:'||SUBSTR(SQLERRM,1,200));

END dumpProcessInformation;

-- ================================================================
-- = Procedure to fix duplicate transitions.
-- ================================================================
PROCEDURE fixProcessTransitionOrder(l_owningProcess  cmpprocesstransition_v.owningprocess%TYPE,
                                    l_sourceActivity cmpprocesstransition_v.sourceactivity%TYPE)
IS
    tcursor TTYPE;
    t_name                  cmpprocesstransition_v.name%TYPE;
    t_elementID             cmpprocesstransition_v.elementid%TYPE;
    t_sourceActivity        cmpprocesstransition_v.sourceactivity%TYPE;
    t_transitionOrder       cmpprocesstransition_v.transitionorder%TYPE;
    t_owningProcess         cmpprocesstransition_v.owningprocess%TYPE;
    
    transitionCounter       cmpprocesstransition_v.transitionorder%TYPE;
    updateSQLStatement      VARCHAR2(4000);

BEGIN
    -- Initialize variables
    transitionCounter := 0;
    updateSQLStatement := '';
    
    open tcursor for 
        select 
            name, elementid, sourceactivity, transitionorder, owningprocess 
        from 
            cmpprocesstransition_v 
        where
            sourceactivity = l_sourceActivity
        order by 
            transitionorder;
    
    loop
        begin
            fetch tcursor 
                into t_name, t_elementID, t_sourceActivity, t_transitionOrder, t_owningProcess;
                exit when tcursor%NOTFOUND;
                            
                -- We have found a row, time to update.
--                updateSQLStatement := 'update cmpprocesstransition_v set transitionorder = ' || transitionCounter || ' where elementid = ' || t_elementID || ';' ;
--                MyDebug(updateSQLStatement);
                update 
                    cmpprocesstransition_v 
                set 
                    transitionorder = transitionCounter 
                where 
                    elementid = t_elementID;
                
                -- Increment the transitionCounter value.
                transitionCounter := transitionCounter + 1;

        end;
    end loop;
    close tcursor;

    -- Save the changes.
    commit;
    MyDebug('.........FIXED');

EXCEPTION
    when others then	 
        ROLLBACK;
        raise_application_error(-20002, 'Bug5416660_FixProcessFlows.fixProcessTransitionOrder encountered the SQL error:'||SUBSTR(SQLERRM,1,200));

END fixProcessTransitionOrder;

-- ================================================================
-- = End of package
-- ================================================================
END Bug5416660_FixProcessFlows;

/