Search Results fnd_usr




Overview
Explore in AI Chat

Overview

APPS.WF_FND_USR_UR is a Oracle E-Business Suite view that exposes Oracle Workflow user-role mappings derived from FND_USER, the application's central user repository. In the Oracle EBS 12.1.1 and 12.2.2 file systems, this view serves as a bridge between the FND security model and the Workflow directory services (WF_LOCAL_ROLES / WF_LOCAL_USER_ROLES) used for notification routing, role resolution, and directory synchronization. Its name reflects its purpose: it maps an FND user (WF_FND_USR) to a role identity (UR) that the Workflow engine can resolve. Because Workflow requires every notification recipient to exist as a role in its local directory, this view normalizes FND_USER records into the ORIG_SYSTEM / ORIG_SYSTEM_ID addressing scheme understood by WF_LOCAL_ROLES, allowing the notification mailer and directory service to locate the correct recipient without duplicating user maintenance.

Underlying Base Objects

The ETRM metadata documents two referenced base objects, both accessed through APPS synonyms: FND_USER and WF_LOCAL_ROLES. The view is a UNION ALL of two branches. The first branch joins FND_USER (alias USR) to WF_LOCAL_ROLES PARTITION (PER_ROLE) (alias PER) on the condition USR.EMPLOYEE_ID = PER.ORIG_SYSTEM_ID, with a date filter requiring TRUNC(SYSDATE) to fall between NVL(PER.START_DATE, TRUNC(SYSDATE)) and NVL(PER.EXPIRATION_DATE, TRUNC(SYSDATE+1)). This branch resolves FND users who are linked to an HR person record (employees) to the corresponding PER role entry. The second branch selects FND users whose EMPLOYEE_ID IS NULL — that is, externally defined or non-employee users — directly, without joining to WF_LOCAL_ROLES, and labels them with the origin system 'FND_USR'. Both branches project the same ten-column shape. The partition reference PER_ROLE indicates the query is partition-aware against WF_LOCAL_ROLES, which improves performance in environments where that table is partitioned by origin system.

Key Columns

Each branch returns a ten-column result set. The first column, USER_NAME, is the FND user login name and the first element of the ORIG_SYSTEM / ORIG_SYSTEM_ID pair. The second column is a literal origin-system label — 'PER' in the employee branch and 'FND_USR' in the non-employee branch. The third column supplies the identity value used by Workflow: PER.ORIG_SYSTEM_ID (the HR person identifier) for employees, or USR.USER_ID for non-employees. Columns four through six mirror columns one through three, providing the role-name and origin components required by WF_LOCAL_ROLES conventions. Columns seven and eight expose START_DATE and END_DATE from FND_USER, establishing the validity window of the user-role mapping. Column nine is a literal NULL, and column ten is the constant 1. The view therefore does not expose display names, email addresses, or responsibility assignments; those remain in FND_USER and related tables.

Common Use Cases and Queries

The view is primarily consumed by Workflow directory synchronization programs (such as WF Directory Services loaders) to populate WF_LOCAL_USER_ROLES and WF_USER_ROLES. It is also useful for diagnostics when notifications fail because a recipient cannot be resolved. A typical query lists resolvable user-role pairs:

  • SELECT user_name, orig_system, orig_system_id FROM apps.wf_fnd_usr_ur WHERE user_name = 'SMITHJ';
  • SELECT orig_system, COUNT(*) FROM apps.wf_fnd_usr_ur GROUP BY orig_system; — distinguishes employee versus non-employee users.
  • SELECT u.user_name FROM apps.wf_fnd_usr_ur u WHERE u.end_date < SYSDATE; — identifies expired mappings.

Because the view already applies a SYSDATE-based validity filter in the employee branch, queries against it return only currently effective PER roles, while the FND_USR branch returns non-employees without a corresponding date restriction. Administrators should note this asymmetry when auditing notification recipients.