Results for “fnd_user_resp_groups_all”

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

Overview

FND_USER_RESP_GROUPS_ALL is a VALID view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the FND – Application Object Library product and exposes the association between application users and the responsibilities (roles) assigned to them, together with the security group and effective date range governing each assignment. In EBS releases prior to the introduction of the role-based access control (RBAC) model, responsibility assignments were stored directly in FND_USER_RESP_GROUPS. With Release 12, responsibility grants were migrated to the Oracle Workflow role assignment tables, and FND_USER_RESP_GROUPS_ALL was introduced as a compatibility and reporting layer that reconstructs the traditional user/responsibility structure from those workflow objects.

The view is therefore the principal supported interface for querying which responsibilities a user holds, and is widely referenced by reports, data extracts, integrations, and diagnostic queries that previously targeted the base table of the same name. Its "_ALL" suffix signals that it presents assignments across security groups rather than being filtered to a single group.

Underlying Base Objects

The documented definition selects from the following referenced objects:

• FND_USER (SYNONYM) – supplies USER_ID via the join on USER_NAME.
• WF_ALL_USER_ROLES (VIEW) – provides the user/role pair and the ROLE_ORIG_SYSTEM flag; the view restricts ROLE_ORIG_SYSTEM to 'FND_RESP'.
• WF_ALL_USER_ROLE_ASSIGNMENTS (VIEW) – supplies assignment dates, audit columns, and the encoded ROLE_NAME.
• FND_APPLICATION (SYNONYM) – used in a scalar subquery to resolve APPLICATION_ID from the application short name embedded in the role name.
• FND_SECURITY_GROUPS (SYNONYM) – used in a scalar subquery to resolve SECURITY_GROUP_ID from the security group key embedded in the role name.

The join condition requires matching USER_NAME and ROLE_NAME across WF_ALL_USER_ROLE_ASSIGNMENTS and WF_ALL_USER_ROLES, and matching USER_NAME between FND_USER and the workflow tables. The role name is parsed positionally using INSTR and SUBSTR against the pipe-delimited 'FND_RESP' naming convention, and the predicate NOT WAUR.ROLE_NAME LIKE 'FND_RESP|%|ANY' excludes the synthetic "ANY" role used for wildcard responsibility access. Because the source objects are workflow views over the underlying WF_LOCAL_ROLES and WF_USER_ROLE_ASSIGNMENTS data, the view is effectively a read-only projection and should not be used for DML.

Key Columns

• USER_ID – the FND_USER identifier of the grantee.
• RESPONSIBILITY_ID – the ROLE_ORIG_SYSTEM_ID from WF_ALL_USER_ROLES, corresponding to the responsibility's application object identifier.
• RESPONSIBILITY_APPLICATION_ID – resolved from FND_APPLICATION using the application short name parsed from the role name.
• SECURITY_GROUP_ID – resolved from FND_SECURITY_GROUPS using the security group key parsed from the role name.
• START_DATE / END_DATE – the effective period of the assignment; a NULL END_DATE indicates an open-ended grant.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – standard audit columns inherited from the workflow assignment record.

Common Use Cases and Queries

Typical uses include responsibility access audits, segregation-of-duties reporting, user provisioning extracts, and support diagnostics when a user reports a missing or unexpected menu.

List all active responsibilities for a user:

SELECT fu.user_name,
       fa.application_short_name,
       frt.responsibility_name,
       furga.start_date,
       furga.end_date
FROM   fnd_user_resp_groups_all furga,
       fnd_user fu,
       fnd_application fa,
       fnd_responsibility_tl frt
WHERE  furga.user_id = fu.user_id
AND    furga.responsibility_application_id = fa.application_id
AND    furga.responsibility_id = frt.responsibility_id
AND    frt.language = USERENV('LANG')
AND    fu.user_name = :p_user_name
AND    NVL(furga.end_date, SYSDATE + 1) > SYSDATE;

Identify all users assigned to a given responsibility within a security group:

SELECT fu.user_name, furga.start_date, furga.end_date
FROM   fnd_user_resp_groups_all furga, fnd_user fu
WHERE  furga.user_id = fu.user_id
AND    furga.responsibility_id = :p_resp_id
AND    furga.responsibility_application_id = :p_app_id
AND    furga.security_group_id = :p_group_id;

Detect grants with no expiry date for periodic recertification, and count assignments per user to highlight over-provisioned accounts. Because the view's scalar subqueries parse role names at runtime, queries benefit from filtering on USER_ID or RESPONSIBILITY_ID rather than scanning the full result set.