Results for “wf_user_role_assignments”

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

Overview

WF_USER_ROLE_ASSIGNMENTS is an Oracle EBS Workflow (WF) repository table owned by the APPLSYS schema and catalogued under the FND — Application Object Library product family. It stores the direct assignment of roles to users within the Oracle Workflow directory service, which is the underlying identity model used by the Workflow Notification System, the Worklist, and the routing engine. When an item is routed to a performer via a role, the Workflow engine resolves that role through the rows held in this table (and its companion user/role definition tables), ultimately producing the list of human recipients who receive the notification.

Under a heuristic Data Vault classification mined from the foreign key structure, this object is modelled as a standalone object — it references JTF_FM_PARTITION_X_REQUEST through PARTITION_ID but is not otherwise joined into a hub-and-satellite chain via enforced foreign keys. This suggests it functions primarily as a business-key-driven relationship record rather than a dependent satellite, and designers modelling a vault on top of EBS identity data would typically treat it as a link between user, role, and assigning-role hubs.

The table is present and valid in both Oracle EBS 12.1.1 and 12.2.2, with the documented physical schema exposing 28 columns in the ETRM 12.2.2 edition.

Key Information Stored

The row identity is a composite business key rather than a single generated surrogate. The documented unique index WF_USER_ROLE_ASSIGNMENTS_U1 covers (ASSIGNING_ROLE, USER_NAME, RELATIONSHIP_ID, PARTITION_ID), which means no separate synthetic primary key column is enforced at the database level — the natural key is the combination of the user, the granting role, the relationship, and the partition.

• USER_NAME — the Workflow directory user receiving the assignment.
• ROLE_NAME — the role granted to that user.
• ASSIGNING_ROLE — the role from which the assignment originates; central to the unique index.
• RELATIONSHIP_ID — relationship qualifier for the assignment, also part of the unique key.
• PARTITION_ID — partition reference, foreign-keyed to JTF_FM_PARTITION_X_REQUEST.
• START_DATE / END_DATE — the effective window of the assignment record itself.
• USER_START_DATE / USER_END_DATE — validity window for the user side of the grant.
• ROLE_START_DATE / ROLE_END_DATE — validity window for the role side of the grant.
• ASSIGNING_ROLE_START_DATE / ASSIGNING_ROLE_END_DATE — validity window for the granting role.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — normalised effective dating used when resolving current assignments.
• USER_ORIG_SYSTEM / USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM / ROLE_ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM / PARENT_ORIG_SYSTEM_ID — external-system identifiers supporting synchronisation with an LDAP or third-party directory.
• OWNER_TAG and ASSIGNMENT_REASON — ownership tag and free-text rationale for the assignment.
• Standard WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

The most frequent consumer is troubleshooting: determining why a given user did or did not receive a notification. A typical query lists all active role assignments for a user name:

• SELECT role_name, assigning_role, relationship_id, start_date, end_date FROM wf_user_role_assignments WHERE user_name = :user AND SYSDATE BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE + 1);
• Reverse lookups — identify every user currently holding a role, used for access reviews and segregation-of-duties reporting.
• Effective-dating analysis using EFFECTIVE_START_DATE and EFFECTIVE_END_DATE to reconstruct historical assignments.
• Directory synchronisation reconciliation, joining USER_ORIG_SYSTEM and ROLE_ORIG_SYSTEM against the external directory to detect drift.

Reports should always account for the multiple date pairs so that a role valid on the role side but expired on the user side is not mistakenly counted as active.

Related Objects

• JTF_FM_PARTITION_X_REQUEST — referenced via PARTITION_ID; the only documented foreign key target.
• WF_ROLES — role definitions that supply the ROLE_NAME and ASSIGNING_ROLE values.
• WF_USERS — user definitions corresponding to USER_NAME.
• WF_LOCAL_ROLES and WF_LOCAL_USER_ROLES — the local directory tables populated from this and other assignment sources.
• WF_NOTIFICATIONS — notification rows resolved through the role assignments held here.
• FND_USER — the EBS application user master, joined on USER_NAME for application-level reporting.
• Workflow directory service APIs, including WF_DIRECTORY, which read these assignments when expanding role recipients.