Results for “igw_prop_user_roles_u1”

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

Overview

IGW.IGW_PROP_USER_ROLES is a transactional table in the Oracle E-Business Suite IGW schema (Grants Proposal subsystem). It stores the roles that have been assigned to individual users for accessing a specific proposal. Each row links one proposal, one user, and one role, and acts as the authorization matrix that determines the level of proposal access granted to a user. The table is classified as VALID and is registered in FND Design Data as IGW.IGW_PROP_USER_ROLES. Rows are stored in the APPS_TS_TX_DATA tablespace with PCTFREE 10.

From a heuristic Data Vault modeling perspective, the object resolves to a link structure: it captures a many-to-many association among proposals, users, and roles, joining them via the business keys PROPOSAL_ID, USER_ID, and ROLE_ID. Its standard WHO columns and RECORD_VERSION_NUMBER make it a candidate for satellite-style descriptive history if change tracking is required.

Key Information Stored

• PROPOSAL_ID (NUMBER 15) — identifies the proposal to which the user-role assignment applies; part of the primary key and of the unique index.
• USER_ID (NUMBER 15) — identifies the user receiving the role assignment; part of the primary key and unique index.
• ROLE_ID (NUMBER 10) — identifies the role granted; part of the primary key and unique index.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns tracking creation and modification.
• RECORD_VERSION_NUMBER (NUMBER) — locking/optimistic-concurrency sequence used to detect concurrent updates.

The composite primary key is IGW_PROP_USER_ROLES_PK (PROPOSAL_ID, USER_ID, ROLE_ID). The unique index IGW_PROP_USER_ROLES_U1 (the object searched) covers the same three columns in tablespace APPS_TS_TX_IDX, enforcing that a user cannot be assigned the same role twice on a given proposal. Because the columns coincide with the primary key, they serve as the business-key candidate.

Common Use Cases and Queries

Typical uses include security auditing, role provisioning reports, and access verification for proposals.

• Listing all users and roles for a proposal:

SELECT r.PROPOSAL_ID, r.USER_ID, r.ROLE_ID
FROM   IGW.IGW_PROP_USER_ROLES r
WHERE  r.PROPOSAL_ID = :proposal_id;

• Determining which proposals a user can access:

SELECT DISTINCT PROPOSAL_ID
FROM   IGW.IGW_PROP_USER_ROLES
WHERE  USER_ID = :user_id;

• Joining to role and user masters to produce readable access matrices, and enforcing uniqueness via IGW_PROP_USER_ROLES_U1 to prevent duplicate grants.

Related Objects

• IGW.IGW_PROP_USERS — referenced via PROPOSAL_ID; the proposal-user parent.
• IGW.IGW_ROLES — referenced via ROLE_ID; defines the role granted.
• APPS.IGW_PROP_USER_ROLES — the APPS-synonym view referencing this table.
• Associated IGW_PROP_... proposal and workflow tables that consume role assignments for access control.

As a dependent object, IGW_PROP_USER_ROLES references the two parent tables above and is referenced by no further database objects documented in the ETRM metadata.