Tables and queries used for oracle workflow

Below tables are most commonly used in oracle workflow,


select * from wf_item_types;

select * from wf_item_types_tl;

select * from wf_items;

select * from wf_item_attributes;

select * from wf_item_attribute_values;

select * from wf_activities;

select * from wf_activity_attributes;

SELECT * FROM WF_NOTIFICATIONS;

SELECT * FROM WF_NOTIFICATIONS_out;

select * from WF_NOTIFICATION_attributes;

select * from wf_messages;

select * from wf_process_Activities;

select * from wf_item_activity_statuses;

select * from WF_LOOKUP_TYPES_TL;


SELECT A.*, b.*

FROM APPS.WF_ITEM_ACTIVITY_STATUSES A,

apps.WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

AND B.PROCESS_ITEM_TYPE = A.ITEM_TYPE

AND A.ITEM_TYPE = 'CREATEPO'

AND A.ITEM_KEY = &p_item_key 

order by instance_id desc;

SELECT A.*, b.*

FROM APPS.WF_ITEM_ACTIVITY_STATUSES A,

apps.WF_PROCESS_ACTIVITIES B

WHERE A.PROCESS_ACTIVITY = B.INSTANCE_ID(+)

And B.Process_Item_Type = A.Item_Type

AND A.ITEM_TYPE = 'POAPPRV'

And A.Item_Key = &p_item_key

AND INSTANCE_LABEL=&p_label_name

order by instance_id desc;

WF TABLES

WF_ITEM_TYPES

The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE

WF_ITEM_ATTRIBUTES

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. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES

The WF_ACTIVITY_ATTRIBUTES table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.

ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES

WF_MESSAGES contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS

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.

WF_NOTIFICATION_ATTRIBUTES

WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

WF_ITEM_ACTIVITY_STATUSES

The WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)

WF_ITEM_ACTIVITY_STATUSES_H

The WF_ITEM_ACTIVITY_STATUSES_H table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS

The WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES

The WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

select * from apps.wf_activities where name ='AP_EXPENSE_REPORT_PROCESS';

SELECT * FROM APPS.WF_ACTIVITY_ATTRIBUTES WHERE ACTIVITY_ITEM_TYPE='APEXP';

most commonly used api's in workflow,


To initiate workflow (creating and starting a process)



WF_ENGINE.CREATEPROCESS (ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,PROCESS IN VARCHAR2 DEFAULT );

WF_ENGINE.STARTPROCESS (ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2);

To set values

WF_ENGINE.SETITEMATTRTEXT(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2,AVALUE IN VARCHAR2);

WF_ENGINE.SETITEMATTRNUMBER(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2,AVALUE IN NUMBER);

WF_ENGINE.SETITEMATTRDATE (ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2,AVALUE IN DATE);
 
To get values


WF_ENGINE.GETITEMATTRTEXT(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2) RETURN VARCHAR2;

WF_ENGINE.GETITEMATTRNUMBER(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2) RETURN NUMBER;

WF_ENGINE.GETITEMATTRDATE(ITEMTYPE IN VARCHAR2,ITEMKEY IN VARCHAR2,ANAME IN VARCHAR2) RETURN DATE;

To synchronize workflow tables - below program needs to run

Synchronize Workflow LOCAL tables

Meaning of status -


FUNCMODE : For functions, it can be RUN or CANCEL
For notifications, it can be either of RESPOND, FORWARD, TRANSFER or TIMEOUT

RESULTOUT : COMPLETE  - Means Activity is completed successfully.
WAITING : Pending for some other activity
DEFERRED: : Activity deferred till
NOTIFIED::: Activity notified to with a . Externally, must be completed using WF_ENGINE.CompleteActivity.
ERROR: :ACTIVITY ENCOUNTERS AN ERROR