1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Workflow Key tables

Discussion in 'Oracle Apps Technical' started by amut_1, Mar 19, 2011.

  1. amut_1

    amut_1 Guest

    Introduction

    This article list the key tables in workflow and the uploaded code give a way to release a stuck workflow.

    Key-Tables

    WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modeled workflow process, which can be included as an activity in other processes to represent a sub process. A notification activity sends a message to a performer. A functions activity performs an automated function that is written as a PL/SQL stored procedure. A folder activity is not part of a process; it provides a means of grouping activities.

    WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system.

    The WF_ITEM_ATTRIBUTES table stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data.

    WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.


    The code

    Code (SQL):

    CREATE OR REPLACE PROCEDURE APPS.xxx_workflow_stuck (
       errbuf                     OUT      VARCHAR2
      ,retcode                    OUT      NUMBER
      ,p_item_type                IN       VARCHAR2
      ,p_process_activity         IN       VARCHAR2
      ,p_retry_yn                 IN       VARCHAR2
    ) IS

     i NUMBER;

     cursor err_items(ITEM_TYPE_SELECTED  VARCHAR,
                      ERRANT_INSTANCE_ID  VARCHAR) IS
      SELECT STA.ITEM_TYPE                ITEM_TYPE
          ,  STA.ACTIVITY_RESULT_CODE     RESULT
          ,  PRA.PROCESS_NAME ||':'||
                 PRA.INSTANCE_LABEL       PROCESS_ACTIVITY_LABEL
          ,  STA.PROCESS_ACTIVITY         INSTANCE_ID
          ,  STA.ITEM_KEY                 ITEM_KEY
     FROM  WF_ITEM_ACTIVITY_STATUSES  STA  
         , WF_PROCESS_ACTIVITIES      PRA
     WHERE STA.ACTIVITY_STATUS     = 'ERROR'
       AND STA.PROCESS_ACTIVITY    = PRA.INSTANCE_ID
       AND STA.ITEM_TYPE           = ITEM_TYPE_SELECTED
       AND STA.PROCESS_ACTIVITY    = ERRANT_INSTANCE_ID;

     RESULT varchar2(100);


    BEGIN
     

      IF UPPER(p_retry_yn) = 'Y' THEN
        i:= 0;
        FOR e1 IN err_items(p_item_type,p_process_activity ) loop
          dbms_output.put_line('Processing Item Type: ' || e1.ITEM_TYPE || ' Item Key: ' || e1.ITEM_KEY);
     
          -- Allow dynamic reseting of the environment context
          wf_engine.preserved_context := FALSE;

          -- Now set the environment if the selector function does that
          RESULT := wf_engine_util.execute_selector_function(e1.item_type, e1.item_key, 'SET_CTX');

          wf_engine.handleError(e1.item_type, e1.item_key, e1.PROCESS_ACTIVITY_LABEL, 'RETRY',NULL);

          i:= i+1;
          -- Commit after processing 20 record
          IF i = 20 THEN
            commit;
            i := 0;
          END IF;

        END loop;
      END IF;
       commit;

       fnd_file.put_line (fnd_file.LOG, 'Workflow Successfully Progressed');
    EXCEPTION
       WHEN OTHERS THEN
          NULL;
          raise_application_error (-20000, 'Workflow progression failed due to the following error: ' || SQLERRM);
    END;
    /