Search Results role_orig_system_id




Overview
Explore in AI Chat

Overview

APPS.WF_ALL_USER_ROLES is a reporting and integration view in Oracle E-Business Suite 12.1.1 and 12.2.2 that exposes user-to-role assignments as maintained by the Oracle Workflow directory services layer. It presents one row per assignment of a role to a user, including the origin system metadata that identifies where the user and the role were sourced. The view functions as the "all" counterpart to the local user role repository, providing a consolidated read interface suitable for reports, extracts, concurrent programs, and interface logic that must determine which responsibilities, roles, or grants are held by a given user.

Because it surfaces the ORIG_SYSTEM columns, the view is especially relevant to environments running multiple directory providers or integrated external repositories, where role and user identities are not exclusively local to the EBS instance. Users searching on role_orig_system are typically attempting to resolve which source system owns a role assignment, a requirement common in identity reconciliation, federation, and cross-application provisioning.

Underlying Base Objects

The view is defined over a single base object: WF_LOCAL_USER_ROLES, accessed through a SYNONYM in the APPS schema. The documentation confirms WF_LOCAL_USER_ROLES is a synonym, meaning the physical segment resides in the Oracle Workflow schema (typically WF) and is exposed to APPS. The view applies no join, filter, or aggregation — it is a direct projection of the underlying synonym's columns, preserving row cardinality.

WF_LOCAL_USER_ROLES stores the assignments that the Workflow directory services recognize for the local instance. Administrators should note that this view is a read layer; DML against user-role assignments is normally performed through Workflow directory APIs or the underlying tables rather than directly against the view.

Key Columns

Common Use Cases and Queries

The view is frequently queried to enumerate the roles held by a specific user, to identify assignments originating from a particular directory provider, and to feed reconciliation extracts. The following examples reflect typical usage.

  • List all roles for a user, showing the origin system of each role:

    SELECT user_name, role_name, role_orig_system, role_orig_system_id
    FROM apps.wf_all_user_roles
    WHERE user_name = :p_user;

  • Find all users holding roles sourced from a non-local directory:

    SELECT user_name, role_name, role_orig_system
    FROM apps.wf_all_user_roles
    WHERE role_orig_system <> 'WF_LOCAL';

  • Determine currently effective assignments, excluding expired ones:

    SELECT user_name, role_name, start_date, expiration_date
    FROM apps.wf_all_user_roles
    WHERE SYSDATE BETWEEN NVL(start_date, SYSDATE) AND NVL(expiration_date, SYSDATE + 1);

  • Count roles per user for entitlement reporting:

    SELECT user_name, COUNT(*) role_count
    FROM apps.wf_all_user_roles
    WHERE role_orig_system = 'WF_LOCAL'
    GROUP BY user_name;

Because the view performs no filtering, callers should always constrain queries by user, role, or origin system to avoid full scans of the directory repository, and should apply appropriate date logic where only active assignments are required.