Search Results wf_notification_status

Overview

WF_WORKLIST_V is a public view owned by the APPS schema in Oracle E-Business Suite, registered under the FND — Application Object Library product. It exists in both Oracle EBS 12.1.1 and 12.2.2 as a VALID database object, and its DD/ETRM metadata records a view text header carrying the tag VIEW_FND_R12.LDT 120.4, dated 2008/03/14 with the marker APPLDEV NOSHIP. As its description states, WF_WORKLIST_V is the "Public view for worklist."

Functionally, the view presents a flattened, language-resolved projection of the Oracle Workflow notification queue. It joins the transactional notification table (WF_NOTIFICATIONS) to two translated reference tables (WF_ITEM_TYPES_TL and WF_LOOKUPS_TL), producing a single row per notification with human-readable display names and status meanings rather than raw codes. It is the standard read interface for "worklist" reporting — the notifications that appear in a user's Worklist web page, in notification mailers, and in workflow-driven approval routing. Because it is a view rather than a base table, all exposure is read-only and inherits the invoker's privileges on the underlying objects.

Underlying Base Objects

The documented referenced base objects are the synonyms WF_NOTIFICATIONS, WF_ITEM_TYPES_TL, and WF_LOOKUPS_TL. The view definition joins them as follows:

• WF_NOTIFICATIONS WN — the primary driving table, supplying ROWID, NOTIFICATION_ID, PRIORITY, RECIPIENT_ROLE, SUBJECT, BEGIN_DATE, DUE_DATE, END_DATE, STATUS, ORIGINAL_RECIPIENT, MESSAGE_TYPE, MESSAGE_NAME, FROM_USER, TO_USER, LANGUAGE, and MORE_INFO_ROLE.
• WF_ITEM_TYPES_TL WIT — joined on WN.MESSAGE_TYPE = WIT.NAME and filtered by WIT.LANGUAGE = USERENV('LANG'), providing the translated DISPLAY_NAME for the item type.
• WF_LOOKUPS_TL WL — joined on LOOKUP_TYPE = 'WF_NOTIFICATION_STATUS' and WN.STATUS = WL.LOOKUP_CODE, also filtered by WL.LANGUAGE = USERENV('LANG'), supplying the MEANING of the notification status.

The two translated tables are restricted to the session language, so DISPLAY_STATUS and the item type display name are always returned in the language of the connecting session rather than in all installed languages.

Key Columns

The view exposes the following columns (as documented):

• ROW_ID — the ROWID of the underlying WF_NOTIFICATIONS row, useful for diagnostics and duplicate detection.
• NID — the notification identifier, corresponding to WF_NOTIFICATIONS.NOTIFICATION_ID and the unique key of a notification.
• PRIORITY — notification priority (for example 1 through 99, where lower numbers denote higher priority).
• MESSAGE_TYPE / ITEM_TYPE / MESSAGE_NAME — the workflow item type and internal message name that generated the notification. MESSAGE_TYPE and ITEM_TYPE refer to the same underlying value; the view aliases it for convenience.
• RECIPIENT_ROLE — the role to which the notification is currently routed.
• SUBJECT — the notification subject line, as displayed in the Worklist.
• BEGIN_DATE, DUE_DATE, END_DATE — lifecycle timestamps for the notification.
• STATUS / DISPLAY_STATUS — the raw status code and its translated meaning from WF_LOOKUPS_TL (for example OPEN, CLOSED, CANCELED).
• ORIGINAL_RECIPIENT — the role or user who was the initial recipient of the notification before any reassignment or delegation. Because the search term is original_recipient, this column is the focal point: it is the stable, immutable target of the notification, whereas RECIPIENT_ROLE reflects the current holder after forwarding, delegating, or reassigning.
• FROM_USER, TO_USER — the sending and receiving user identities for the notification.
• LANGUAGE — the language code in which the notification was generated.
• MORE_INFO_ROLE — the role used to locate the source document via a "more information" callback.
• DISPLAY_NAME — the translated display name of the message/item type.

Common Use Cases and Queries

The most frequent use of WF_WORKLIST_V is to report the open, pending, or overdue items assigned to a user or role, and to distinguish the current holder from the original recipient when delegations and reassignments are in play. A typical query for currently open worklist items is:

SELECT nid, item_type, message_name, subject, priority, recipient_role, original_recipient, begin_date, due_date, display_status FROM wf_worklist_v WHERE display_status = 'OPEN' AND recipient_role = :role ORDER BY priority, begin_date;

A second common pattern targets the recurring "original recipient" question — finding everything originally addressed to a role regardless of subsequent delegation:

SELECT nid, subject, recipient_role, original_recipient, status FROM wf_worklist_v WHERE original_recipient = :role AND status = 'OPEN';

A third scenario computes aging and SLA metrics by comparing DUE_DATE and END_DATE against SYSDATE. Because the view filters translated tables by USERENV('LANG'), reports intended for multilingual environments should be conscious of the session language under which the query is executed; status and display names will vary accordingly. For high-volume reporting, the view can be joined to WF_ITEM_ACTIVITY_STATUSES or custom notification history tables on NID, but performance depends on the indexes defined on WF_NOTIFICATIONS.NOTIFICATION_ID and STATUS.