Search Results wf_users




Overview
Explore in AI Chat

Overview

WF_USERS is a VALID APPS-owned view in the FND – Application Object Library product, documented in ETRM for Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the set of active workflow users and roles derived from the Workflow local roles repository, filtered by partition and expiration rules. Rather than being a standalone base table, the view is a reporting and runtime convenience layer over WF_LOCAL_ROLES and its translation table WF_LOCAL_ROLES_TL, presenting a unified, language-aware directory of notification recipients. Because it consolidates FND users, PER (HR) persons, ad-hoc users, and HZ_PARTY person records into a single presentation structure, it is the canonical source used by Oracle Workflow's notification mailer and directory services, and by custom reporting or integration code requiring a list of valid workflow identities.

Underlying Base Objects

The view is defined over two documented base objects: WF_LOCAL_ROLES, which stores the role definitions, and WF_LOCAL_ROLES_TL, its translation (language) table. Both carry a PARTITION_ID, NAME, ORIG_SYSTEM, and ORIG_SYSTEM_ID that form the join key. The view text performs an outer join against the translation table on LANGUAGE = USERENV('LANG'), so an untranslated row still returns its base DISPLAY_NAME and DESCRIPTION via NVL. The WHERE clause restricts rows to two disjoint sets: (1) PARTITION_ID = 1 (FND users and PER persons), or (2) PARTITION_ID IN (0, 9) with USER_FLAG = 'Y' (ad-hoc and HZ_PARTY persons). In all cases NVL(EXPIRATION_DATE, SYSDATE+1) > SYSDATE, so expired roles are excluded at query time. The view therefore behaves dynamically; rows appear and disappear as roles expire or partitions change without any DDL action.

Key Columns

  • NAME – the role/user identifier used throughout workflow queues and attributes.
  • DISPLAY_NAME – the translated, human-readable label (falls back to the base role display name).
  • DESCRIPTION – translated description of the role.
  • NOTIFICATION_PREFERENCE – the delivery channel preference (mail, fax, etc.) for that user.
  • LANGUAGE / TERRITORY – locale attributes used to select the correct translation row.
  • EMAIL_ADDRESS / FAX – the notification endpoints used by the Workflow mailer for delivery.
  • ORIG_SYSTEM / ORIG_SYSTEM_ID – the source system (e.g., FND, PER) and its primary key for the user.
  • PARENT_ORIG_SYSTEM / PARENT_ORIG_SYSTEM_ID – the owning parent role or organization.
  • START_DATE / EXPIRATION_DATE – validity window for the user record.
  • STATUS – current status indicator applied to the role.
  • OWNER_TAG – partitioning/ownership attribute used by Workflow for grouping.
  • SECURITY_GROUP_ID, USER_FLAG, PARTITION_ID – governance and classification flags that determine visibility and behavior.

Common Use Cases and Queries

WF_USERS is typically queried to resolve a workflow user name to a display name or e-mail address, to enumerate valid notification recipients, and to troubleshoot missing or expired users. A common lookup by name is:

SELECT name, display_name, email_address, status
FROM   apps.wf_users
WHERE  name = 'OPERATIONS';

To retrieve all active users with e-mail addresses for a mailer audit:

SELECT name, display_name, email_address, notification_preference
FROM   apps.wf_users
WHERE  email_address IS NOT NULL
ORDER  BY name;

To locate users sourced from a particular origin system, such as HR persons:

SELECT name, display_name, orig_system, orig_system_id
FROM   apps.wf_users
WHERE  orig_system = 'PER'
AND    status = 'ACTIVE';

Because the view already excludes expired rows and filters by partition, no additional date predicate is normally required. For performance-sensitive or high-volume reporting, note that the view applies a functional predicate on EXPIRATION_DATE and joins to the translation table via USERENV('LANG'), which is not index-friendly; joining directly to WF_LOCAL_ROLES with explicit partition predicates may be preferable when the translated display name is not needed.