Results for “igw_prop_user_roles”

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

Overview

The IGW_PROP_USER_ROLES table resides in the IGW schema, which belongs to the Oracle Grants Proposal module — a component that has since been classified as obsolete within the Oracle E-Business Suite. In Oracle EBS 12.1.1 and 12.2.2 environments where the module was historically deployed, this table stores the association between users and the roles they were granted for the purpose of accessing specific proposals. It therefore embodies the access-control layer of the grants proposal workflow, determining which individuals could act upon which proposals and in what capacity.

From a dimensional modeling perspective, the metadata documents a heuristic Data Vault classification of link. This is consistent with the table's structure: it is a pure association (junction) entity whose primary key is composed entirely of foreign key columns referencing parent entities. As a modeling suggestion, the table serves as a many-to-many resolution between proposals, users, and roles, and would sit at the center of any Data Vault representation of proposal-level access control rather than acting as a descriptive hub or satellite.

It is important to note that the ETRM documentation records this object as "Not implemented in this database," indicating that in many 12.1.1 / 12.2.2 instances the table may exist only as a legacy or dormant artifact. Analysts should verify physical presence before relying on it in queries.

Key Information Stored

The table is documented with nine physical columns in the ETRM 12.1.1 schema. The most significant columns and their roles are as follows:

• PROPOSAL_ID — Identifies the proposal to which the access grant applies; part of the composite primary key and a foreign key to IGW_PROP_USERS.
• USER_ID — Identifies the user receiving the role assignment; part of the composite primary key and a foreign key to IGW_PROP_USERS.
• ROLE_ID — Identifies the role being granted; part of the composite primary key and a foreign key to IGW_ROLES.
• LAST_UPDATE_DATE, CREATION_DATE — Standard EBS audit timestamps recording when the row was created and last modified.
• LAST_UPDATED_BY, CREATED_BY — WHO columns identifying the application user responsible for creation and modification.
• LAST_UPDATE_LOGIN — The login session associated with the most recent change.
• RECORD_VERSION_NUMBER — Optimistic locking counter used to detect concurrent updates.

The surrogate primary key is IGW_PROP_USER_ROLES_PK, defined over (PROPOSAL_ID, USER_ID, ROLE_ID). Because the compact unique index IGW_PROP_USER_ROLES_U1 covers the same three columns, that triplet functions as the business-key candidate that guarantees a given role is assigned to a user for a proposal only once.

Common Use Cases and Queries

The principal use case is reporting and auditing who holds which role on a given proposal. A typical query enumerates role assignments for a proposal:

• SELECT USER_ID, ROLE_ID FROM IGW_PROP_USER_ROLES WHERE PROPOSAL_ID = :p_prop;
• Reverse lookup of all proposals accessible to a given user: SELECT PROPOSAL_ID FROM IGW_PROP_USER_ROLES WHERE USER_ID = :p_user;
• Determining which users hold a specific role across proposals: SELECT PROPOSAL_ID, USER_ID FROM IGW_PROP_USER_ROLES WHERE ROLE_ID = :p_role;

Additional scenarios include reconciliation of access grants against IGW_ROLES definitions, security reviews verifying that role holders remain active, and historical trend analysis using the CREATION_DATE audit column.

Related Objects

The table participates in two documented foreign key relationships and depends on several surrounding objects:

• IGW_PROP_USERS — Referenced via PROPOSAL_ID and USER_ID; supplies the proposal-user context.
• IGW_ROLES — Referenced via ROLE_ID; defines the role being granted.
• IGW_PROP_USER_ROLES_PK — The primary key constraint enforcing uniqueness of the proposal/user/role triplet.
• IGW_PROP_USER_ROLES_U1 — The unique index mirroring the primary key as the business-key candidate.

Because the module is obsolete, downstream views and APIs in 12.2.2 may be deprecated; validation against the target instance is recommended before integration.