Results for “igw_prop_users”

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

Overview

IGW_PROP_USERS is a transactional table in the IGW (Grants Proposal) product of Oracle E-Business Suite, documented as VALID in the IGW schema across the 12.1.1 and 12.2.2 releases. The table stores information about users who have proposal access, functioning as the authorization cross-reference between a sponsored-research proposal and the application user granted visibility to it. In practice, it answers the question of which FND_USER accounts are permitted to view, edit, or administer a given record in IGW_PROPOSALS_ALL.

From a dimensional-modeling standpoint, the mined foreign-key structure classifies IGW_PROP_USERS as satellite-leaning. Its identity is fully derived from two parent entities — the proposal and the user — and its non-key attributes are descriptive and time-bounded rather than transactional. A Data Vault implementation would therefore model it as a link between the proposal and user hubs, with the date-validity columns carried on a satellite. This classification is a heuristic suggestion, not a documented Oracle design decision.

Key Information Stored

The table carries ten documented columns. Its surrogate primary key is IGW_PROP_USERS_PK, composed of PROPOSAL_ID and USER_ID. A separate unique index, IGW_PROP_USERS_U1, is defined on the same two columns, which the metadata flags as a business-key candidate; the combination of proposal and user is thus the natural business key as well as the physical key. PROPOSAL_ID is the foreign key to IGW_PROPOSALS_ALL, and USER_ID is the foreign key to FND_USER.

Effective-dating is expressed through START_DATE_ACTIVE and END_DATE_ACTIVE, which define the window during which a user's access to the proposal is valid. Standard EBS auditing columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — record who created and last modified each row and through which login session. RECORD_VERSION_NUMBER provides optimistic-locking support for concurrent updates. The presence of two distinct date mechanisms should be noted: START_DATE_ACTIVE and END_DATE_ACTIVE govern business validity, while LAST_UPDATE_DATE reflects the most recent database change.

Common Use Cases and Queries

The most frequent use is resolving which users may access a proposal, typically joined to FND_USER to obtain a human-readable identity:

• Access list for a proposal: SELECT u.user_name, u.description FROM igw_prop_users p, fnd_user u WHERE p.user_id = u.user_id AND p.proposal_id = :proposal_id ORDER BY u.user_name;
• Active-access filter using the validity window: add AND TRUNC(SYSDATE) BETWEEN NVL(p.start_date_active, TRUNC(SYSDATE)) AND NVL(p.end_date_active, TRUNC(SYSDATE)) to exclude future-dated or expired grants.
• Proposals visible to a given user: SELECT pp.proposal_number, pp.title FROM igw_prop_users p, igw_proposals_all pp WHERE p.proposal_id = pp.proposal_id AND p.user_id = :user_id;
• Audit and stale-access reporting: query on LAST_UPDATE_DATE, LAST_UPDATED_BY, and CREATED_BY to reconcile orphaned or long-lived access rows during security reviews.

Reporting use cases include proposal-team rosters, segregation-of-duties analysis, and migration validation when access assignments are loaded into a new environment.

Related Objects

The following objects participate most directly in the referential structure of IGW_PROP_USERS:

• IGW_PROPOSALS_ALL — joined on PROPOSAL_ID; the parent proposal record that the access row qualifies.
• FND_USER — joined on USER_ID; supplies application user identity and credentials.
• IGW_PROP_USER_ROLES — references IGW_PROP_USERS on the composite (PROPOSAL_ID, USER_ID), extending access to role-level granularity.
• IGW_PROP_USERS_PK / IGW_PROP_USERS_U1 — the primary key and unique index enforcing uniqueness of proposal-and-user combinations.

Together these relationships place IGW_PROP_USERS at the center of proposal-level authorization within the Grants Proposal module.