Results for “wf_user_roles”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

WF_USER_ROLES is an Oracle Application Object Library (FND) view owned by the APPS schema. It presents a currently-effective snapshot of the role assignments held by users within the Oracle Workflow directory service. The view is a filtered projection of WF_LOCAL_USER_ROLES, returning only those assignment rows that fall within their validity window as of the query execution date. In Oracle EBS 12.1.1 and 12.2.2, it is the primary reporting and integration interface through which applications, concurrent programs, and custom code determine which roles a given user occupies, and which users occupy a given role, without having to reproduce the effective-dating logic themselves.

Because the view applies a SYSDATE-based filter, it is not a historical record. It answers the question "who holds which role right now" rather than "who held which role on a past date." This distinction is important for point-in-time reconciliation and audit reporting, where the underlying WF_LOCAL_USER_ROLES table must be queried directly with explicit date predicates instead.

Underlying Base Objects

The view is defined over a single referenced object, WF_LOCAL_USER_ROLES, which is exposed in the APPS schema as a synonym. WF_LOCAL_USER_ROLES is the local materialization of user-to-role assignments within the Workflow directory, holding both native Workflow assignments and roles synchronized from external repositories such as Oracle Internet Directory or third-party LDAP directories.

The view text applies two compound date predicates. The first governs the start boundary: an assignment is considered effective when either its EFFECTIVE_START_DATE is null and each of START_DATE, USER_START_DATE, and ROLE_START_DATE is null or has already been reached, or when EFFECTIVE_START_DATE has been reached. The second governs the end boundary in symmetric fashion: an assignment remains effective when EFFECTIVE_END_DATE is null and each of EXPIRATION_DATE, USER_END_DATE, and ROLE_END_DATE is null or still in the future, or when EFFECTIVE_END_DATE has not yet passed. All comparisons are performed at day granularity using TRUNC. This three-tier date model allows the directory to suppress an assignment because of the user's own validity, the role's own validity, or the assignment itself, and the view collapses that logic into a single accessible result set.

Key Columns

• USER_NAME — the directory name of the user holding the assignment.
• ROLE_NAME — the directory name of the assigned role, including roles such as "Requisitioning Buyer" or application-specific Workflow roles.
• USER_ORIG_SYSTEM / USER_ORIG_SYSTEM_ID — the originating system and identifier for the user, allowing distinction between FND-native users and users provisioned from an external directory.
• ROLE_ORIG_SYSTEM / ROLE_ORIG_SYSTEM_ID — the originating system and identifier for the role.
• START_DATE / EXPIRATION_DATE — the direct validity window of the assignment record.
• ASSIGNMENT_TYPE — classifies the nature of the assignment, for example whether it is a direct grant or derived through group membership.
• PARENT_ORIG_SYSTEM / PARENT_ORIG_SYSTEM_ID — identifies the parent object, typically a group, through which an indirect assignment is inherited.
• PARTITION_ID — the directory partition to which the assignment belongs, relevant in multi-partition directory configurations.
• ASSIGNMENT_REASON — free-text or coded explanation recorded at the time the assignment was created.

Common Use Cases and Queries

The view is most frequently used to resolve role membership for approval routing, to drive responsibility and function-security analysis, and to populate custom dashboards listing user entitlements. A typical query retrieves all current roles for a named user:

SELECT role_name, role_orig_system, assignment_type, start_date, expiration_date FROM apps.wf_user_roles WHERE user_name = :p_user_name ORDER BY role_name;

The inverse query identifies all current holders of a role, which supports segregation-of-duties reviews and notification distribution lists:

SELECT user_name, user_orig_system, parent_orig_system_id FROM apps.wf_user_roles WHERE role_name = :p_role_name ORDER BY user_name;

For entitlement extraction feeding an external identity governance platform, a full export is common:

SELECT user_name, role_name, user_orig_system, role_orig_system, assignment_type, partition_id FROM apps.wf_user_roles;

When resolving indirect assignments, filtering on PARENT_ORIG_SYSTEM_ID distinguishes roles granted directly from those inherited through a group. Because the view contains no SYSDATE-independent history and performs no joins to PER_ALL_PEOPLE_F or FND_USER, consumers requiring person attributes or usernames rather than directory names should join to those objects explicitly. Queries executed from a non-APPS schema should reference APPS.WF_USER_ROLES or rely on the public synonym, and performance is generally acceptable given the underlying table is indexed on user and role names.