OracleFree referencePDF, Excel, Word exportOracle E-Business SuiteAll Oracle E-Business Suite tools

Oracle EBS Workflow Tables Reference (WF_ITEMS, WF_NOTIFICATIONS and Queues)

A searchable reference for the Oracle Workflow tables inside EBS 12.1/12.2: definitions, runtime status, notifications, the directory and the AQ queue tables. Use it to write queries for errored activities, stuck notifications and purge planning.

Download:

Table/View	Type	Holds	Key columns	Joins	Typical use
WF_ITEM_TYPES	Definition	Item types (workflow definitions), for example OEOH, OEOL, POAPPRV, REQAPPRV, WFERROR	NAME	NAME = WF_ITEMS.ITEM_TYPE	Check PERSISTENCE_TYPE (TEMP or PERM) and PERSISTENCE_DAYS before purging
WF_ITEM_TYPES_TL	Definition	Display name and description of each item type	NAME, LANGUAGE	NAME = WF_ITEM_TYPES.NAME	Show 'OM Order Header' instead of OEOH in reports
WF_ITEMS	Runtime	One row per workflow instance	ITEM_TYPE, ITEM_KEY	ITEM_TYPE + ITEM_KEY to every runtime table; PARENT_ITEM_TYPE + PARENT_ITEM_KEY to the parent flow	END_DATE null = still open; ROOT_ACTIVITY is the top process; OWNER_ROLE; USER_KEY often holds the document number
WF_ITEM_ATTRIBUTES	Definition	Attribute definitions per item type	ITEM_TYPE, NAME	ITEM_TYPE + NAME = WF_ITEM_ATTRIBUTE_VALUES	TYPE (VARCHAR2, NUMBER, DATE, DOCUMENT and others) and defaults
WF_ITEM_ATTRIBUTE_VALUES	Runtime	Attribute values for each running item	ITEM_TYPE, ITEM_KEY, NAME	ITEM_TYPE + ITEM_KEY = WF_ITEMS	Read TEXT_VALUE, NUMBER_VALUE or DATE_VALUE, for example DOCUMENT_ID on a PO approval; usually the largest runtime table
WF_ACTIVITIES	Definition	Processes, functions, notifications and events, versioned	ITEM_TYPE, NAME, VERSION	ITEM_TYPE + NAME = WF_PROCESS_ACTIVITIES.ACTIVITY_ITEM_TYPE + ACTIVITY_NAME	TYPE (PROCESS, FUNCTION, NOTICE, EVENT), FUNCTION (the PL/SQL called), RESULT_TYPE, BEGIN_DATE/END_DATE select the version in force
WF_ACTIVITIES_TL	Definition	Display names of activities	ITEM_TYPE, NAME, VERSION, LANGUAGE	Same key as WF_ACTIVITIES	Show the label users see in Status Monitor
WF_PROCESS_ACTIVITIES	Definition	Each use of an activity inside a process diagram	INSTANCE_ID	INSTANCE_ID = WF_ITEM_ACTIVITY_STATUSES.PROCESS_ACTIVITY; PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION; ACTIVITY_ITEM_TYPE, ACTIVITY_NAME	Turn a PROCESS_ACTIVITY number into a process and activity name; INSTANCE_LABEL is what WF_ENGINE calls accept
WF_ACTIVITY_TRANSITIONS	Definition	Arrows between activities in a process	FROM_PROCESS_ACTIVITY, RESULT_CODE, TO_PROCESS_ACTIVITY	Both ends = WF_PROCESS_ACTIVITIES.INSTANCE_ID	See which result code leads where when a flow takes an unexpected branch
WF_ITEM_ACTIVITY_STATUSES	Runtime	Current status of each activity reached by each item	ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY	PROCESS_ACTIVITY = WF_PROCESS_ACTIVITIES.INSTANCE_ID; NOTIFICATION_ID = WF_NOTIFICATIONS.NOTIFICATION_ID	ACTIVITY_STATUS ACTIVE, COMPLETE, DEFERRED, ERROR, NOTIFIED, SUSPEND, WAITING; ERROR_NAME, ERROR_MESSAGE, ERROR_STACK on failures; ACTIVITY_RESULT_CODE
WF_ITEM_ACTIVITY_STATUSES_H	Runtime	History of earlier passes through an activity (loops, retries, resets)	ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY, BEGIN_DATE	Same as WF_ITEM_ACTIVITY_STATUSES	See earlier errors after an activity was retried
WF_NOTIFICATIONS	Runtime	Every notification sent to a role	NOTIFICATION_ID	RECIPIENT_ROLE = WF_ROLES.NAME; MESSAGE_TYPE + MESSAGE_NAME = WF_MESSAGES.TYPE + NAME; CONTEXT holds item_type:item_key:activity_id	STATUS OPEN, CLOSED, CANCELED; MAIL_STATUS MAIL (to send), SENT, FAILED, ERROR or null (no email needed); BEGIN_DATE, DUE_DATE, RESPONDER, SUBJECT
WF_NOTIFICATION_ATTRIBUTES	Runtime	Message attribute values for each notification, including responses	NOTIFICATION_ID, NAME	NOTIFICATION_ID = WF_NOTIFICATIONS.NOTIFICATION_ID	Read RESULT and response values; TEXT_VALUE, NUMBER_VALUE, DATE_VALUE
WF_COMMENTS	Runtime	Action history on a notification: reassign, request information, respond	NOTIFICATION_ID, COMMENT_DATE	NOTIFICATION_ID = WF_NOTIFICATIONS.NOTIFICATION_ID	Who forwarded or answered and the comment text; FROM_ROLE, TO_ROLE, ACTION, USER_COMMENT
WF_MESSAGES	Definition	Message definitions used by notifications	TYPE, NAME	TYPE + NAME = WF_NOTIFICATIONS.MESSAGE_TYPE + MESSAGE_NAME; text in WF_MESSAGES_TL	Subject and body templates live in WF_MESSAGES_TL (SUBJECT, BODY, HTML_BODY)
WF_ROLES	Directory	View of all roles: users, responsibilities, ad hoc and other roles	NAME (+ ORIG_SYSTEM, ORIG_SYSTEM_ID)	NAME = WF_NOTIFICATIONS.RECIPIENT_ROLE	EMAIL_ADDRESS, NOTIFICATION_PREFERENCE, STATUS; first stop when a user says they get no email
WF_USER_ROLES	Directory	View of user to role membership	USER_NAME, ROLE_NAME	ROLE_NAME = WF_ROLES.NAME	Who receives a notification sent to a responsibility or group role
WF_LOCAL_ROLES	Directory	Base table behind WF_ROLES, partitioned by ORIG_SYSTEM	NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID	Feeds WF_ROLES	Synchronised from FND_USER, PER_ALL_PEOPLE_F and responsibilities; out-of-sync email here is a common mailer issue
WF_LOCAL_USER_ROLES	Directory	Base table behind WF_USER_ROLES	USER_NAME, ROLE_NAME, USER_ORIG_SYSTEM, ROLE_ORIG_SYSTEM	Feeds WF_USER_ROLES	START_DATE, EXPIRATION_DATE of memberships
WF_USER_ROLE_ASSIGNMENTS	Directory	How each user got a role, directly or through role inheritance	USER_NAME, ROLE_NAME, ASSIGNING_ROLE	Rolls up into WF_LOCAL_USER_ROLES	Explain why a user has a role in RBAC setups
WF_EVENTS	BES	Business Event System event and event group definitions	GUID (NAME is unique)	GUID = WF_EVENT_SUBSCRIPTIONS.EVENT_FILTER_GUID	STATUS ENABLED or DISABLED; for example oracle.apps.fnd.wf.ds.user.updated
WF_EVENT_SUBSCRIPTIONS	BES	Subscriptions that react to events	GUID	EVENT_FILTER_GUID = WF_EVENTS.GUID	PHASE (under 100 runs synchronously), STATUS, RULE_FUNCTION, WF_PROCESS_TYPE and WF_PROCESS_NAME, OWNER_NAME
WF_DEFERRED	Queue	AQ queue table in APPLSYS for deferred Business Event System events	Queue message ID; CORR_ID	Read through the APPLSYS.AQ$WF_DEFERRED view	Processed by the Workflow Deferred Agent Listener; a backlog here means that listener is down or slow
WF_DEFERRED_TABLE_M	Queue	AQ queue table for activities deferred to the Background Engine	Queue message ID; CORR_ID carries the item type	Read through the APPLSYS.AQ$WF_DEFERRED_TABLE_M view	Drained by Workflow Background Process (FNDWFBG) for the item type
WF_NOTIFICATION_OUT	Queue	AQ queue table in APPLSYS for outbound notification messages	Queue message ID; CORR_ID; MSG_STATE	Read through the APPLSYS.AQ$WF_NOTIFICATION_OUT view	Notification Mailer dequeues from here; count by MSG_STATE (READY, PROCESSED) to see a mail backlog
WF_ERROR	Queue	AQ queue table in APPLSYS for errored BES events	Queue message ID; CORR_ID	Read through the APPLSYS.AQ$WF_ERROR view	Processed by the Workflow Error Agent Listener, which launches error handling
FND_SVC_COMPONENTS	BES	Workflow service components: notification mailer, agent listeners	COMPONENT_ID	Container and parameters in related FND_SVC_ tables	COMPONENT_NAME, COMPONENT_TYPE (for example WF_MAILER, WF_AGENT_LISTENER), COMPONENT_STATUS (RUNNING, STOPPED, DEACTIVATED_SYSTEM and others)

Scope: Oracle Workflow as embedded in EBS 12.1 and 12.2 (APPLSYS schema, accessed through APPS synonyms). Queue tables are read through their AQ$ views; never delete from them directly.

Workflow backlog or purge review

Send us row counts for the WF_ runtime tables and your top item types. We will suggest a purge schedule and the queries to monitor stuck items.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How ITEM_TYPE and ITEM_KEY join everything

Every running workflow is identified by ITEM_TYPE plus ITEM_KEY. The item type is the internal name of the definition (OEOH for an order header flow, OEOL for an order line flow, POAPPRV for PO approval, REQAPPRV for requisition approval, WFERROR for the default error process) and the key is chosen by the product, for example the HEADER_ID for OEOH or the LINE_ID for OEOL. WF_ITEMS, WF_ITEM_ATTRIBUTE_VALUES and WF_ITEM_ACTIVITY_STATUSES all carry both columns, so filter on both. Activity names are not stored in the status table: PROCESS_ACTIVITY is an INSTANCE_ID that you resolve through WF_PROCESS_ACTIVITIES to PROCESS_NAME and ACTIVITY_NAME. Child flows point to their parent through PARENT_ITEM_TYPE and PARENT_ITEM_KEY on WF_ITEMS.

Finding errored activities

This query lists errored activities with the reason: SELECT s.item_type, s.item_key, pa.process_name, pa.activity_name, s.begin_date, s.error_name, s.error_message FROM wf_item_activity_statuses s JOIN wf_process_activities pa ON pa.instance_id = s.process_activity WHERE s.activity_status = 'ERROR' AND s.item_type = 'OEOL' ORDER BY s.begin_date DESC. ERROR_STACK holds the PL/SQL call stack and is the column to send to a developer. For each errored activity the error process (usually WFERROR) starts as a child item and sends a notification to the role in WF_ADMINISTRATOR or the item's error role. Retry or skip from Status Monitor or with WF_ENGINE.HANDLEERROR. Rows with ACTIVITY_STATUS = 'DEFERRED' are waiting for the background engine, not broken.

Notification tables versus queues

A notification is a row in WF_NOTIFICATIONS the moment it is sent, whether or not anyone gets an email. If the recipient's preference is an email type, MAIL_STATUS is set to MAIL and a message is enqueued on WF_NOTIFICATION_OUT; the Workflow Notification Mailer dequeues it and sets MAIL_STATUS to SENT, or to FAILED or ERROR. So a user with no email has three places to check: WF_ROLES (EMAIL_ADDRESS, NOTIFICATION_PREFERENCE), WF_NOTIFICATIONS.MAIL_STATUS, and the queue backlog in APPLSYS.AQ$WF_NOTIFICATION_OUT grouped by MSG_STATE. Then check the mailer component status in FND_SVC_COMPONENTS and its log, which is a FNDCPGSC file in the concurrent log directory. Responses by email come back through WF_NOTIFICATION_IN.

Purging runtime data

Runtime tables grow quickly; WF_ITEM_ATTRIBUTE_VALUES and WF_ITEM_ACTIVITY_STATUSES are often among the largest in APPLSYS. The concurrent program Purge Obsolete Workflow Runtime Data (FNDWFPR) deletes completed items older than an age you supply, for an item type or all types, respecting each item type's persistence type (TEMP or PERM). Only items with END_DATE set are purged, so open items that should have ended (for example order line flows left open by a data fix) are never removed; close or abort them first. Run it regularly for high-volume types, schedule it outside business hours, and set the Core Workflow Only parameter deliberately: Y purges only obsolete runtime data for work items, N (the default) also purges obsolete design data. Purging also removes the associated closed notifications.

Frequently Asked Questions

What are the main workflow tables in Oracle EBS?

Runtime: WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES, WF_ITEM_ATTRIBUTE_VALUES and WF_NOTIFICATIONS. Definition: WF_ITEM_TYPES, WF_ACTIVITIES, WF_PROCESS_ACTIVITIES, WF_ACTIVITY_TRANSITIONS and WF_MESSAGES. Directory: WF_ROLES and WF_USER_ROLES views over WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES. Queues such as WF_NOTIFICATION_OUT and WF_DEFERRED are AQ tables in APPLSYS.

Which table shows workflow errors?

WF_ITEM_ACTIVITY_STATUSES where ACTIVITY_STATUS = 'ERROR'. ERROR_NAME, ERROR_MESSAGE and ERROR_STACK give the reason and call stack. Join PROCESS_ACTIVITY to WF_PROCESS_ACTIVITIES.INSTANCE_ID for the activity name. Errored BES events are a separate path through the WF_ERROR queue and the Workflow Error Agent Listener.

Which tables hold workflow notifications?

WF_NOTIFICATIONS holds one row per notification with RECIPIENT_ROLE, STATUS and MAIL_STATUS. WF_NOTIFICATION_ATTRIBUTES holds attribute and response values, and WF_COMMENTS the action history. The email itself goes through the WF_NOTIFICATION_OUT queue. The CONTEXT column links a notification back to its item type, item key and activity.

How do I see these tables in eTRM?

In the Oracle Electronic Technical Reference Manual (eTRM) on My Oracle Support, search for the WF_ object by name; the workflow tables are owned by APPLSYS. Columns are documented there per release. On your own instance, DESCRIBE APPS.WF_ITEMS or query ALL_TAB_COLUMNS with OWNER = 'APPLSYS' to confirm the exact columns at your patch level.

If you want your support team to ask Oracle EBS which approvals and order flows are stuck, in plain English on your own servers, we can build that read-only over these workflow tables.

Talk to an Expert Explore More Free Tools