Search Results fnd_usr




Overview
Explore in AI Chat

Overview

FND_USR_UR is a view owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2, delivered as part of the FND — Application Object Library product. Its documented status is VALID. The view presents a unified listing of Oracle EBS application users together with their corresponding role or principal identifiers, resolving each user to either a human-resources-backed person record or a standalone FND user identity. This dual-nature resolution makes the view a convenient reporting and integration surface for queries that must associate an application user name with the originating system identifier used by Oracle Workflow and the role-based access control model.

The view is defined in ETRM as being constructed over two synonyms, FND_USER and WF_LOCAL_ROLES, and it exposes a compact column list oriented around user name, origin system, origin system identifier, and effective date range. Its principal value lies in hiding the union logic and partitioning detail required to reconcile HR-sourced users with non-HR users, allowing downstream reports, interfaces, and troubleshooting scripts to consume a single consistent result set.

Underlying Base Objects

Two documented base objects are referenced: FND_USER (synonym) and WF_LOCAL_ROLES (synonym). FND_USER is the core application user repository, supplying USER_NAME, USER_ID, EMPLOYEE_ID, START_DATE, and END_DATE. WF_LOCAL_ROLES is the Oracle Workflow local roles table, referenced in the view text through the PER_ROLE partition. The join condition matches USR.EMPLOYEE_ID to PER.ORIG_SYSTEM_ID, and date filtering restricts the PER_ROLE side to currently effective rows using TRUNC(SYSDATE) compared against NVL(PER.START_DATE, TRUNC(SYSDATE)) and NVL(PER.EXPIRATION_DATE, TRUNC(SYSDATE+1)).

The definition is a UNION ALL. The first branch joins FND_USER to WF_LOCAL_ROLES PARTITION (PER_ROLE) and emits a literal origin system of 'PER' with PER.ORIG_SYSTEM_ID. The second branch selects from FND_USER alone for rows where EMPLOYEE_ID IS NULL, emitting a literal origin system of 'FND_USR' with USR.USER_ID. Together these branches ensure every application user appears once, classified by whether an HR person record backs the account.

Key Columns

  • USER_NAME — The application user login name from FND_USER, appearing as the first and fourth projected column in the view text.
  • Origin system literal — A constant string, either 'PER' for HR person-backed users or 'FND_USR' for users without an employee association.
  • Origin system identifier — For the PER branch, PER.ORIG_SYSTEM_ID; for the non-HR branch, USR.USER_ID. This is the value that ties the user to their role or person identity.
  • START_DATE and END_DATE — Taken from FND_USER and reflecting the user account's effective period.
  • NULL placeholder — A trailing NULL column present in both UNION ALL branches, retained for positional consistency in the view definition.

Common Use Cases and Queries

Typical uses include identifying the origin system identifier for a given login, separating HR-backed accounts from manually created ones, and feeding user-to-role reconciliation reports. A representative query filters by user name:

  • SELECT user_name, origin_system, origin_system_id, start_date, end_date FROM apps.fnd_usr_ur WHERE user_name = :user_name;
  • SELECT origin_system, COUNT(*) FROM apps.fnd_usr_ur GROUP BY origin_system;
  • SELECT * FROM apps.fnd_usr_ur WHERE origin_system = 'FND_USR';

Because the view already applies current-date filtering on the PER_ROLE partition, consumers need not repeat effectiveness predicates when querying active HR-backed users. Analysts should confirm exact column names in their release before publishing dependent reports, since the view text projects several columns positionally without explicit aliases.