Search Results wf_fnd_usr_roles




Overview
Explore in AI Chat

Overview

APPS.WF_FND_USR_ROLES is a read-only database view in the Oracle E-Business Suite 12.1.1 and 12.2.2 Application Object Library (FND). It functions as the workflow reconciliation interface between Oracle Workflow role definitions and the EBS application user registry, presenting a unified list of users who are addressable as notification recipients by the Workflow Notification Mailer and related workflow engine components. The view conforms FND users to the column and data-type conventions expected of WF_LOCAL_ROLES, the core Workflow directory table, so that notification routing, role expansion, and directory lookups resolve correctly against application users without duplicating records in the underlying role store.

The view is documented as VALID under the APPS schema and is primarily consumed by Workflow internals and by administrators auditing notification preferences. Because it merges person-based roles and pure application users into a single result set, it is a convenient reporting target when the underlying role table or user table must be filtered or joined in a non-standard way.

Underlying Base Objects

ETRM documentation identifies three referenced base objects, all resolved through synonyms in the APPS schema:

  • FND_USER — the EBS application user registry, supplying user name, start/end dates, email, fax, employee linkage, and description.
  • FND_USER_PREFERENCES — user-level workflow preferences for MAILTYPE, LANGUAGE, and TERRITORY within MODULE_NAME='WF', with -WF_DEFAULT- supplying site defaults.
  • WF_LOCAL_ROLES — the workflow local roles directory; the view reads only the PER_ROLE partition when joining person-based roles.

The definition is a UNION ALL of two branches. The first joins FND_USER to WF_LOCAL_ROLES PARTITION (PER_ROLE) through PER.ORIG_SYSTEM_ID = USR.EMPLOYEE_ID, applies the role validity window (SYSDATE between START_DATE and EXPIRATION_DATE), and outer-joins the three preference rows. The second branch, restricted to USR.EMPLOYEE_ID IS NULL, represents users with no person record, hard-coding default mail type, language, and territory values and using USER_ID as the original system identifier with an FND_USR source flag.

Key Columns

  • NAME — the workflow role name; the person role name in branch one, and USER_NAME in branch two.
  • DISP — display name, taken from person display name or, for non-person users, the user name itself.
  • DESCRIPTION — person description or FND user description.
  • MAILTYPE / LANGUAGE / TERRITORY — resolved notification preferences, defaulting to MAILHTML, AMERICAN, and AMERICA when no user preference exists.
  • EMAIL / FAX — notification destination attributes.
  • ORIG_SYSTEM / ORIG_SYSTEM_ID — the role source (PER or FND_USR) and its key.
  • START_DATE / END_DATE / STATUS — active window and a derived ACTIVE/INACTIVE status flag.

Common Use Cases and Queries

Typical uses include validating notification deliverability, auditing workflow preference coverage, and reconciling person roles against application users. For example, identify active recipients with email addresses:

  • SELECT name, disp, email, mailtype, language FROM apps.wf_fnd_usr_roles WHERE status='ACTIVE' AND email IS NOT NULL;
  • SELECT orig_system, COUNT(*) FROM apps.wf_fnd_usr_roles GROUP BY orig_system;
  • SELECT name, territory, language FROM apps.wf_fnd_usr_roles WHERE territory IS NULL OR mailtype IS NULL;

The view is read-only and should not be modified; performance is governed by the underlying WF_LOCAL_ROLES partition pruning and the outer-joined preference lookups.