15 Important Oracle Workflow tables

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

oracle-workflow-tables
Image Courtesy. docs.oracle.com

So here is the list

Table NameDescription
WF_ITEMSRuntime table for workflow processes.
WF_ITEM_TYPESWorkflow item that is running.
WF_ITEM_ATTRIBUTESDefinitions of attributes associated with a process.
WF_NOTIFICATIONSStores the runtime information about a specific instance of a sent message.
WF_MESSAGESDefinitions of messages for notifications.
WF_NOTIFICATION_ATTRIBUTESAttributes of a notification.
WF_MESSAGE_ATTRIBUTESFor message attribute definitions.
WF_ACTIVITIESStores the definition of an activity.
WF_ACTIVITY_ATTRIBUTESAttributes of activity DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
WF_ITEM_ACTIVITY_STATUSESRuntime table for a work item.
WF_ITEM_ACTIVITY_STATUSES_HStores the history of the item activities
WF_ACTIVITY_TRANSITIONSDefines the transitions from one activity to another in a process.
WF_ACTIVITY_ATTR_VALUESStores the data for the activity attributes.
WF_PROCESS_ACTIVITIESHolds 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

image 10 Oracle Workflow tables 1

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

image 11 Oracle Workflow tables 2

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

image 12 Oracle Workflow tables 3

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

image 13 Oracle Workflow tables 4

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

image 14 Oracle Workflow tables 5

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

image 15 Oracle Workflow tables 6

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

image 16 Oracle Workflow tables 7

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

image 17 Oracle Workflow tables 8

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

image 18 Oracle Workflow tables 9

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

image 19 Oracle Workflow tables 10

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

image 20 Oracle Workflow tables 11

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’;

WF_EVENTS

image 21 Oracle Workflow tables 12

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

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart