Search Results wf_notifications

Overview

The WF_NOTIFICATIONS table is a core runtime data store within the Oracle Workflow engine of Oracle E-Business Suite (EBS) versions 12.1.1 and 12.2.2. Owned by the APPLSYS schema and part of the Application Object Library (FND) module, it functions as the central repository for all notifications generated by workflow processes. A notification is a message sent to a user or role, requesting action or providing information as defined by a workflow's activities. This table tracks the lifecycle of every notification, from its creation and assignment through to its response and completion, making it essential for monitoring workflow progress, user inbox status, and auditing notification history.

Key Information Stored

The table's primary key is NOTIFICATION_ID, a unique identifier for each notification instance. Critical columns include MESSAGE_TYPE and MESSAGE_NAME, which form a foreign key relationship to the WF_MESSAGES table, defining the notification's template and content. Status-related columns, such as STATUS (e.g., 'OPEN', 'CLOSED'), MAIL_STATUS, and the priority, determine the notification's current state in the delivery and response cycle. Recipient information is stored in columns like RECIPIENT_ROLE, while context is provided by GROUP_ID, ORIGINAL_RECIPIENT, and SENT_DATE. The table also holds references to the parent workflow item (ITEM_TYPE, ITEM_KEY) and the specific activity (ACTIVITY_ID) that generated the notification, linking it directly to the executing workflow process.

Common Use Cases and Queries

Primary use cases involve monitoring open notifications, auditing workflow history, and troubleshooting. Common SQL patterns include querying for notifications awaiting a specific user's action, analyzing notification response times, or identifying stalled workflow steps. For example, to find all open notifications for a user:

• SELECT NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, BEGIN_DATE FROM APPLSYS.WF_NOTIFICATIONS WHERE STATUS = 'OPEN' AND RECIPIENT_ROLE = :USER_NAME;

For reporting on notification closure rates, a join with WF_ITEM_ACTIVITY_STATUSES on NOTIFICATION_ID can provide detailed activity history. Administrators often query this table to re-assign or force-close orphaned notifications using the Oracle Workflow APIs, as direct DML is strongly discouraged.

Related Objects

As indicated by the foreign keys, WF_NOTIFICATIONS is integrally linked to several key Workflow tables. WF_MESSAGES supplies the message template details. WF_ITEM_ACTIVITY_STATUSES and its history table, WF_ITEM_ACTIVITY_STATUSES_H, store the runtime status of the activity associated with the notification. WF_NOTIFICATION_ATTRIBUTES holds the runtime values for message attributes specific to each notification instance. The standard API for interacting with this data is the PL/SQL package WF_NOTIFICATION, which provides procedures for sending, responding to, and managing notifications. The primary view for end-user access is typically WF_NOTIFICATIONS_VL, which presents a localized, user-friendly version of the data.