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.
The Oracle workflow tables impacted by the WF Components
So here is the list
|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 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
The WF_ITEM_TYPES table defines an item that is transitioning through a workflow process.
select * from WF_ITEM_TYPES where name =’OEOH’
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’
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
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’
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 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’
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’
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’
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
Holds the data for an activity within a specific process.
SQL : select * from WF_PROCESS_ACTIVITIES where process_item_type =’OEOH’
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;
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
Query this table for check for event errors
select a.user_data.event_name, a.user_data.event_key, a.user_data.error_message,
from wf_error a where a.user_data.event_name = ‘oracle.apps.ar.hz.PartySite.create’;
WF_EVENTS table stores the business event details.
select * From WF_EVENTS where name=’oracle.apps.ont.oi.xml_int.status’
Hope the above list of oracle workflow tables and its usage helps .
If you work on EBS integrations do check the Integration SOA gateway Post.
References: Oracle workflow developer guide