Search Results wf_local_user_roles




Overview
Explore in AI Chat

Overview

WF_LOCAL_USER_ROLES is a core Oracle Workflow and Application Object Library (FND) table residing in the APPLSYS schema. It functions as the local repository of user-to-role assignments within the Oracle E-Business Suite, capturing the principals (users) and the roles (responsibilities, positions, or workflow roles) to which they are granted access. In EBS 12.1.1 and 12.2.2, this table underpins the Workflow directory services used for routing, notifications, and access control, and it is closely tied to the FND_USER and WF_ROLES infrastructure.

From a data modeling perspective, the metadata heuristic classifies WF_LOCAL_USER_ROLES as standalone, meaning it does not participate in an inferred parent-child hub/link/satellite structure through its foreign keys alone. In practice, it behaves as a link (association) table, resolving the many-to-many relationship between users and roles while carrying descriptive and effective-dating attributes. This classification is offered as a modeling suggestion rather than a documented constraint.

Key Information Stored

The table holds 26 columns in the documented 12.2.2 schema. The most significant include:

The unique index WF_LOCAL_USER_ROLES_U1 spans USER_NAME, ROLE_NAME, the origin-system pairs, and PARTITION_ID, making these the business-key candidates versus the surrogate primary key columns.

Common Use Cases and Queries

Typical uses include determining which responsibilities a user holds, auditing role assignments for SoD analysis, and troubleshooting workflow routing when notifications fail to reach the correct recipient. A frequent query pattern lists active assignments for a user:

  • SELECT role_name, start_date, expiration_date FROM wf_local_user_roles WHERE user_name = :user AND (expiration_date IS NULL OR expiration_date > SYSDATE);
  • Reporting joins to WF_LOCAL_ROLES or FND_USER to resolve display names and email addresses.
  • Migration and reconciliation queries using the origin-system columns to compare local grants against an LDAP or OID source.
  • Effective-dating queries filtering on EFFECTIVE_START_DATE and EFFECTIVE_END_DATE for as-of reporting.

Related Objects

  • FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID, defining the security group scope of the assignment.
  • JTF_FM_PARTITION_X_REQUEST — referenced by PARTITION_ID, supporting partitioned multi-tenant scenarios.
  • WF_LOCAL_ROLES — the role master; joined on ROLE_NAME to resolve role attributes.
  • WF_LOCAL_USERS — the user master; joined on USER_NAME.
  • FND_USER — the EBS application user repository, linked by USER_NAME for validation and reporting.
  • WF_USER_ROLES — the corresponding runtime view that consolidates local and external role assignments for the Workflow directory.
  • WF_ROLES — the canonical role definition view used in directory service lookups.

Together these objects form the identity and access backbone that Workflow and FND rely upon across Oracle EBS 12.1.1 and 12.2.2.