Search Results assignment_reason

Overview

WF_ALL_USER_ROLE_ASSIGNMENTS is a reporting view owned by the APPS schema in Oracle E-Business Suite, registered under the FND – Application Object Library product family. It exposes a normalized, user-readable projection of the workflow role assignment data stored in the Workflow (WF) schema, which forms the backbone of the Oracle Workflow and Approvals Management (AME) access model. In EBS 12.1.1 and 12.2.2 the view is documented as VALID and is published through ETRM for reference by implementers, DBAs, and integration developers who need to audit or query which users hold which workflow roles.

The view's principal purpose is to translate the internal numeric identifiers held in the underlying assignment table into human-recognizable names and to flatten the semantic distinction between directly granted roles and roles inherited through a role hierarchy. It therefore serves as the canonical reporting surface for role assignment analysis, security reviews, and integration extracts that would otherwise require joining multiple workflow tables.

Underlying Base Objects

Per the ETRM metadata, WF_ALL_USER_ROLE_ASSIGNMENTS is defined over a single referenced base object, described as the synonym WF_USER_ROLE_ASSIGNMENTS. The view text is a direct SELECT from that object, projecting the columns USER_NAME, ROLE_NAME, ASSIGNING_ROLE, START_DATE, END_DATE, RELATIONSHIP_ID (transformed), CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and ASSIGNMENT_REASON.

The relationship is therefore one-to-one at the row level: the view does not aggregate, union, or join additional tables. Its only transformation is a DECODE on RELATIONSHIP_ID, which converts the stored value of -1 into the literal 'DIRECT' and any other value into 'INHERITED'. This means the source object already stores the resolved user and role names, and the view's value-add is the semantic labeling of the assignment relationship type.

Key Columns

• USER_NAME — The workflow user (FND user) to whom the role is assigned; the primary subject of any assignment query.
• ROLE_NAME — The workflow role granted to the user, typically an internal role name rather than a display name.
• ASSIGNING_ROLE — The role through which the assignment was propagated, relevant for inherited assignments.
• START_DATE / END_DATE — The effective period of the assignment; a NULL END_DATE normally indicates an open-ended grant.
• RELATIONSHIP_ID (DECODE) — Rendered as 'DIRECT' when the stored value is -1, otherwise 'INHERITED', indicating whether the role was granted explicitly or derived from a parent role.
• ASSIGNMENT_REASON — Free-text context describing why the assignment exists.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide WHO-column auditability consistent with FND standards.

Common Use Cases and Queries

Typical scenarios include periodic user access reviews, diagnostics of why a user receives a given notification or approval, and data extracts feeding external identity governance. The following examples illustrate standard access patterns.

• List all direct roles for a user:

  SELECT user_name, role_name, start_date, end_date
  FROM   apps.wf_all_user_role_assignments
  WHERE  user_name = 'JDOE'
  AND    DECODE(relationship_id,-1,'DIRECT','INHERITED') IS NOT NULL
  ORDER BY role_name;

• Count assignments by relationship type:

  SELECT DECODE(relationship_id,-1,'DIRECT','INHERITED') rel_type,
         COUNT(*) total
  FROM   apps.wf_all_user_role_assignments
  GROUP BY DECODE(relationship_id,-1,'DIRECT','INHERITED');

• Identify users holding a specific role:

  SELECT user_name, assigning_role, start_date
  FROM   apps.wf_all_user_role_assignments
  WHERE  role_name = 'FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR';

Because the view is a thin projection over the workflow assignment table, queries should always be constrained by USER_NAME or ROLE_NAME to avoid full scans on large production instances.