Search Results hide_show




Overview
Explore in AI Chat

Overview

APPS.UMX_ROLE_ASSIGNMENTS_V is a reporting and integration view within the Oracle E-Business Suite User Management (UMX) subsystem. It presents a unified, user-scoped picture of role assignments by combining two distinct data sets: pending or in-flight role provisioning requests held in the UMX registration/request tables, and existing (already established) role assignments drawn from the Workflow directory services. This dual-source design allows administrators, approval workflows, and self-service pages to present a single list showing both the current state of a user's roles and any role requests that are still being processed.

The view is central to the UMX role-provisioning and approval experience. It joins request records to role definitions and to lookup values, translating internal status codes into human-readable status text, while also computing presentation-oriented flags used by the UMX user interface. Because it resolves the effective user through FND_GLOBAL.USER_ID, the view is inherently context-sensitive: it returns rows relevant to the session user unless a request is explicitly in the UNASSIGNED state, in which case the requesting user's identity governs the filter.

Underlying Base Objects

The view is defined over a UNION ALL of two queries and is documented (ETRM 12.2.2) as referencing the following objects:

  • UMX_REG_REQUESTS (synonym) and UMX_REG_REQUESTS_PVT (package) — the first union branch reads pending, errored, and unassigned role requests, using the package function is_pend_request_error to detect error conditions.
  • WF_ALL_ROLES_VL (view) — supplies role name, display name, and description for each role.
  • WF_ALL_USER_ROLE_ASSIGNMENTS (view) and WF_LOCAL_ROLES (synonym) — the second union branch resolves existing assignments, start and end dates, and assignment reasons.
  • FND_LOOKUP_VALUES_VL (view) — joined on lookup type UMX_ACCESS_ROLE_STATUS to translate status codes into meanings.
  • FND_USER (synonym) — user identity reference.
  • FND_GLOBAL (package) — provides the current session user for filtering and for the requested_by fallback.

The critical join condition linking both branches to the lookup is the lookup type UMX_ACCESS_ROLE_STATUS, which is also the exact term the user searched ("umx_access_role_status"). This lookup is what converts the internal status_code values (PENDING, ERROR, UNASSIGNED, APPROVED, INACTIVE) into their displayed meanings.

Key Columns

  • role_name / display_name / description — the internal role identifier and its user-facing labels.
  • reg_request_id — the registration request identifier; set to -1 for the existing-assignment branch.
  • status_code — the effective status: PENDING, ERROR, UNASSIGNED, APPROVED, or INACTIVE.
  • status_text — the lookup meaning for the status, resolved via UMX_ACCESS_ROLE_STATUS.
  • detail_region_switch / status_text_switch / RemoveImg — UI control columns that tell the UMX pages which rendering region or case applies (ErrorCase, GenericCase, UnassignedCase, AssignedCase, DisabledCase, EnabledCase).
  • active_from / active_to — requested (for requests) or actual (for assignments) validity dates.
  • justification — the business reason supplied for the request or assignment.
  • user_id / USER_NAME — the affected user (USER_NAME is null in the first branch by design).
  • REG_SERVICE_CODE — the registration service that originated the request.

Common Use Cases and Queries

Typical uses include auditing a user's role portfolio, monitoring pending provisioning requests, and diagnosing errored requests. The lookup type UMX_ACCESS_ROLE_STATUS is frequently queried independently to enumerate valid statuses.

  • List a user's roles and pending requests: 
    SELECT role_name, status_code, status_text,
       active_from, active_to
FROM   apps.umx_role_assignments_v
WHERE  user_id = :p_user_id;
  • Find errored or pending provisioning requests: 
    SELECT reg_request_id, role_name, status_text
FROM   apps.umx_role_assignments_v
WHERE  status_code IN ('PENDING','ERROR','UNASSIGNED');
  • Enumerate the status lookup used by the view: 
    SELECT lookup_code, meaning
FROM   apps.fnd_lookup_values_vl
WHERE  lookup_type = 'UMX_ACCESS_ROLE_STATUS';

Because the view filters on FND_GLOBAL.USER_ID, queries run under a specific session return only rows that user is entitled to see unless the request is UNASSIGNED and attributed to the requesting user. Reporting jobs requiring cross-user visibility should therefore join to the underlying base objects directly rather than relying on the view's built-in session filter.