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