WF-DB-Retry:Oracle Scripts Workflow Activity/Operation from backend

摘自：http://viralji.blogspot.com/p/oracle-scripts.html

Oracle Scripts

Workflow Activity/Operation from backend.
Many times it becomes necessary to kick start 100's of errored/stuck workflow from backend. Many times workflow will be active but awaiting some trigger to proceed, but you want to skip that step. A bug in the process has stop and one want to retry/re execute a particular activity for multiple workflow instance. For any such requirements, find the most useful and easy solution.

Contact me/comment for any more help.

-- Below query list all the process/activites for a given workflow
-- Identify the activity you want to retry/force complete/ execute again/ etc.
-- Check specific activity id for given workflow
SELECT PROCESS_ITEM_TYPE
      ,PROCESS_NAME
      ,ACTIVITY_NAME
      ,INSTANCE_ID ACTIVITY_ID
      ,PERFORM_ROLE_TYPE
FROM   WF_PROCESS_ACTIVITIES
WHERE  PROCESS_ITEM_TYPE = UPPER('&Workflow_internal_name');
-- From above query, take activity id on which you want to perform action.
-- User below query to see the status of that activity for given workflow instance.
SELECT *
FROM   WF_ITEM_ACTIVITY_STATUSES
WHERE  ITEM_TYPE = UPPER('&Workflow_internal_name')
AND    ITEM_KEY = '&Item_key'
AND    PROCESS_ACTIVITY = NVL('&Activity_ID'
                             ,PROCESS_ACTIVITY);                                       
-- Now comes scripting part. Use below script. Use it and enjoy.
-- Test if for one particular item_key and then u can run it for all.
-- Warning : Comment the process you are not going to call.

DECLARE
    CURSOR WF_PER_FORCE_OPER IS    
-- Select all the ef instance on which you want to perform operation. Modify criterio as per you need.        
SELECT WI.ITEM_TYPE
              ,WI.ITEM_KEY
              --,WI.USER_KEY
              ,WI.BEGIN_DATE
              ,WPA.INSTANCE_ID ACTIVITY_ID
              ,WPA.ACTIVITY_NAME ACTIVITY_NAME
        FROM   APPS.WF_ITEMS                  WI
              ,APPS.WF_ITEM_ACTIVITY_STATUSES WIAS
              ,APPS.WF_PROCESS_ACTIVITIES     WPA
        WHERE  1 = 1
        AND    WI.ITEM_TYPE = WIAS.ITEM_TYPE
        AND    WIAS.ITEM_TYPE = WPA.PROCESS_ITEM_TYPE
        AND    WI.ITEM_KEY = WIAS.ITEM_KEY
        AND    WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
        AND    WPA.ACTIVITY_NAME = UPPER('&Activity_Name')
        AND    WI.ITEM_TYPE = UPPER('&Workflow_internal_name')
        --AND    WI.ITEM_KEY IN ('10322','10130')
        AND    WI.END_DATE IS NULL
        ORDER BY begin_date;
    ln_count NUMBER DEFAULT 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Item Type           Item Key            Result');
     DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------- START');
    FOR I IN WF_PER_FORCE_OPER
    LOOP
        BEGIN
            -- Use below to force the activity with desired result. Give desired result as in parameter.
            WF_ENGINE_UTIL.COMPLETE_ACTIVITY(ITEMTYPE => i.item_type
                                            ,ITEMKEY  => i.item_key
                                            ,ACTID    => i.activity_id
                                            ,RESULT   => 'S' -- Give you output
                                            ,RUNPNTF  => TRUE);
        
            -- Use below to execute the activity again. So that the it executes again and perform required operation.
            -- Difference between execute and retry is that begin date of activity doesn't change in execute.            

            WF_ENGINE_UTIL.EXECUTE_ACTIVITY(ITEMTYPE => i.item_type
                                           ,ITEMKEY  => i.item_key
                                           ,ACTID    => i.activity_id
                                           ,FUNMODE  => 'RUN');        
            -- Use below to retry of skip a particular activity. Command can be 'RETRY' or 'SKIP'                                              

            WF_ENGINE.HANDLEERROR(ITEMTYPE => i.item_type
                                 ,ITEMKEY  => i.item_key
                                 ,ACTIVITY => i.Activity_name
                                 ,COMMAND  => 'RETRY');  -- or 'SKIP'                                             

            DBMS_OUTPUT.PUT_LINE(RPAD(I.ITEM_TYPE,20,' ')  ||RPAD(I.item_key,20,' ')|| 'Success');
            COMMIT;
            ln_count := ln_count +1;
        EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(RPAD(I.ITEM_TYPE,20,' ')  ||RPAD(I.item_key,20,' ')|| 'Error  ' || SQLERRM);
            ROLLBACK;
        END;        
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('=================================================================');
        DBMS_OUTPUT.PUT_LINE('Total Workflow Update    ' || ln_count);
EXCEPTION WHEN OTHERS THEN 
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Main Error ' || SQLERRM);
END;