15 Important Oracle Workflow tables

2850 Views

SaveSavedRemoved 0

EBS is an old war horse in managing enterprise business and with focus shifting to cloud applications, co-existence with Oracle Ebusiness suite is Key. Troubleshooting business processes requires knowledge of oracle workflow tables.

Either its integration with cloud application or customizing EBS, you will need to query oracle workflow tables to check for values after the workflow is triggered.

Table of Contents

The Oracle workflow tables impacted by the WF Components

So here is the list

Table Name	Description
WF_ITEMS	Runtime table for workflow processes.
WF_ITEM_TYPES	Workflow item that is running.
WF_ITEM_ATTRIBUTES	Definitions of attributes associated with a process.
WF_NOTIFICATIONS	Stores the runtime information about a specific instance of a sent message.
WF_MESSAGES	Definitions of messages for notifications.
WF_NOTIFICATION_ATTRIBUTES	Attributes of a notification.
WF_MESSAGE_ATTRIBUTES	For message attribute definitions.
WF_ACTIVITIES	Stores the definition of an activity.
WF_ACTIVITY_ATTRIBUTES	Attributes of activity DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
WF_ITEM_ACTIVITY_STATUSES	Runtime table for a work item.
WF_ITEM_ACTIVITY_STATUSES_H	Stores the history of the item activities
WF_ACTIVITY_TRANSITIONS	Defines the transitions from one activity to another in a process.
WF_ACTIVITY_ATTR_VALUES	Stores the data for the activity attributes.
WF_PROCESS_ACTIVITIES	Holds the data for an activity within a specific process.

Basic SQLs to fetch data from Oracle Workflow Tables


select * From WF_EVENTS where name='oracle.apps.ont.oi.xml_int.status'

select * from wf_deferred

select * from wf_error

select * from wf_bpel_qtab order by enq_time desc 

select * from WF_PROCESS_ACTIVITIES where process_item_type ='OEOH'

select * from WF_NOTIFICATION_ATTRIBUTES where notification_id =38122

select * from WF_NOTIFICATIONS  where message_type = 'OEOH'

select * from WF_MESSAGE_ATTRIBUTES  where message_type = 'OEOH'

select * from WF_messages where type = 'OEOH'

select * from WF_ITEM_ACTIVITY_STATUSES  where item_type = 'OEOH' 

select * from WF_ACTIVITY_ATTRIBUTES where activity_item_type = 'OEOH' 

select * from WF_activities where item_type = 'OEOH' order by begin_date desc

select * from WF_ITEM_ATTRIBUTES where item_type = 'OEOH'

select * from WF_ITEM_TYPES where name ='OEOH'

select * from wf_items where item_type ='OEOH' order by begin_date desc

FAQs On Usage of Oracle Workflow Tables

WF_ITEMS

WF_items stores the workflow item instances. For eg if we submit a sales order for approval, Order workflow with the internal name OEOH gets triggered.

If you query wf_items the workflow internal name is in ITEM_TYPE Column and it shows how many instances have triggered.

Query : select * from wf_items where item_type =’OEOH’ order by begin_date desc

WF_ITEM_TYPES

The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process.

select * from WF_ITEM_TYPES where name =’OEOH’

WF_ITEM_ATTRIBUTES

This Table helps to store the different Workflow Attributes which is currently used in the Workflow Specific Process.
Let’s see for our Order workflow OEOH process

SQl : select * from WF_ITEM_ATTRIBUTES where item_type = ‘OEOH’

WF_ACTIVITIES

This table stores the definition of an activity. Activities can be processes, notifications,functions or folders. Let’s see activities for our Order workflow item type OEOH process

SQL : select * from WF_activities where item_type = ‘OEOH’ order by begin_date desc

WF_ACTIVITY_ATTRIBUTES

The WF_ACTIVITY_ATTRIBUTES table defines attributes that act as parameters for an activity.
the column ACTIVITY_ITEM_TYPE is the workflow item type. In this screenshot we see the activity attributes for workflow item type OEOH.

SQL select * from WF_ACTIVITY_ATTRIBUTES where activity_item_type = ‘OEOH’

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. you can filter by item key to get the required status details

Query : select * from WF_ITEM_ACTIVITY_STATUSES where item_type = ‘OEOH’

WF_MESSAGES

WF_MESSAGES helps to store the Registry or definition of the workflow Notifications messages. Type column is the workflow item type

SQL select * from WF_messages where type = ‘OEOH’

WF_MESSAGE_ATTRIBUTES

This table helps to store the Workflow message attributes in run time and display in the Workflow notification messages.
SQL query : select * from WF_MESSAGE_ATTRIBUTES where message_type = ‘OEOH’

WF_NOTIFICATIONS

This table helps to track the notification notifications sent by workflow each process. We can track the workflow notifications for any workflow processes which has been triggered. Filter the notification by message type.

SQL : select * from WF_NOTIFICATIONS where message_type = ‘OEOH’

WF_NOTIFICATION_ATTRIBUTES

This table stored the attribute value of a notification sent to a user.
Filter the details by notification_Id

SQL : select * from WF_NOTIFICATION_ATTRIBUTES where notification_id =38122

WF_PROCESS_ACTIVITIES

Holds the data for an activity within a specific process.
SQL : select * from WF_PROCESS_ACTIVITIES where process_item_type =’OEOH’

WF_DEFERRED

To Check status of Workflow Event. This will have the event details.

Eg query : select corrid,decode(state,0,’0 = Ready’,1, ‘1 = Wait’,2, ‘2 = Processed’,3,
‘3 = Exception’,to_char(state)) State,count(*) COUNT from
wf_deferred group by corrid, state;

WF_BPEL_Q/ wf_bpel_qtab

SELECT * FROM ALL_QUEUE_SUBSCRIBERS WHERE QUEUE_NAME = 'WF_BPEL_Q'; SELECT * FROM AQ$_WF_BPEL_QTAB_S WHERE NAME IS NOT NULL;
select * from wf_bpel_qtab order by enq_time desc

WF_ERROR

Query this table for check for event errors

e.g
select a.user_data.event_name, a.user_data.event_key, a.user_data.error_message,
a.user_data.error_stack
from wf_error a where a.user_data.event_name = ‘oracle.apps.ar.hz.PartySite.create’;