Oracle Workflow Table details with relationship diagram

A) Some comments on the graphical representation:

The columns belonging to the primary key are indicated in red.

Only the columns that either belong to a primary key or a foreign key are shown on this diagram.
The columns belonging to a foreign key may also belong to a primary key.

This model is split into 2 diagrams. Due to this, sometimes the start or the end point of a foreign key arrow is not on the current diagram.

Due to the graphical representation of foreign key structure, a column name may appear more than once inside a table. Of course this does not mean the column is duplicated within the table.

For a complete description of each of the tables, please refer to:

Oracle eTRM Technical Reference web site: http://etrm.oracle.com or note 150230.1 (ETRM DIRECT ACCESS DOCUMENT).


B) Some comments on workflow tables:

1) Difference between WF_ACTIVITIES and WF_PROCESS_ACTIVITIES

WF_ACTIVITIES contains only the activities that belong to the item type
Does NOT contain activities that belong to other item types (ie pre-defined activities).

WF_PROCESS_ACTIVITIES contains the activities of the item type AND the pre-defined activities that are referenced in the item type.

WF_PROCESS_ACTIVITIES, in addition to its primary key instance_id, has a unique key defined on the following columns:

1.Instance_label
2.Process_item_type
3.Process_name
4.Process_version


2) The following tables have a corresponding translation ( _TL) table:


- WF_ITEM_TYPES
- WF_ITEM_ATTRIBUTES
- WF_ACTIVITIES
- WF_ACTIVITY_ATTRIBUTES
- WF_MESSAGES
- WF_MESSAGE_ATTRIBUTES

The workflow lookup tables only exist as translation (_TL) tables:

WF_LOOKUP_TYPES_TL
WF_LOOKUPS_TL


(Translation tables are not shown in the above diagrams)



3) Difference between WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H

WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H have the same structure.

When the same activity is executed multiple times within a workflow (ie looping activity),
WF_ITEM_ACTIVITY_STATUSES will only contain information on the last execution of the activity, whereas the information on all the previous executions will be stored in WF_ITEM_ACTIVITY_STATUSES_H.

When an activity is executed only once within a workflow, only WF_ITEM_ACTIVITY_STATUSES will be populated.