Search Results igs_ad_i_prg_prf_ust_u3

Overview

The IGS_AD_I_PRG_PRF_UST table is a core data structure within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically in the IGS (iGrants) module. It functions as a junction or intersection table, central to managing admissions and enrollment preferences. Its primary role is to store and link inquiry unit set preferences to specific inquiry programs. This establishes a many-to-many relationship, allowing a single inquiry program preference to be associated with multiple unit set preferences and vice versa, which is essential for configuring complex academic program structures and student choice tracking during the admissions lifecycle.

Key Information Stored

The table stores relationship identifiers and standard audit columns. The primary data columns are all mandatory and serve as foreign keys to related entities.

• INQ_PRG_PREF_USET_ID: The primary key and unique system-generated identifier for each record in this table.
• INQ_PROG_PREF_ID: Foreign key referencing an inquiry program preference (IGS_AD_I_PRG_PREFS). This identifies the overarching program preference context.
• INQ_PROG_UNIT_SET_ID: Foreign key referencing an inquiry unit set (IGS_AD_I_PRG_U_SET). This links to the specific unit set definition within the inquiry.
• UNIT_SET_PREF_ID: Represents the specific unit set preference chosen or assigned within the context of the linked inquiry program and unit set.
• Standard WHO Columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN): Provide audit trails for data creation and modification.

Common Use Cases and Queries

This table is critical for reporting and data validation in admissions processes. A common use case is retrieving all unit set preferences configured for a specific inquiry program preference to validate setup or generate student choice reports. Troubleshooting often involves querying for missing or orphaned relationships. The following sample SQL patterns are derived from the documented structure:

• Basic Data Retrieval: The foundational query, as provided in the ETRM, selects all columns for analysis or export.
  SELECT INQ_PRG_PREF_USET_ID, INQ_PROG_PREF_ID, INQ_PROG_UNIT_SET_ID, UNIT_SET_PREF_ID, CREATED_BY, CREATION_DATE FROM IGS.IGS_AD_I_PRG_PRF_UST;
• Relationship Validation: A join to parent tables to ensure referential integrity and display meaningful descriptions instead of IDs.
  SELECT ust.*, pref.PROGRAM_CODE, uset.UNIT_SET_CODE FROM IGS.IGS_AD_I_PRG_PRF_UST ust JOIN IGS.IGS_AD_I_PRG_PREFS pref ON ust.INQ_PROG_PREF_ID = pref.INQ_PROG_PREF_ID JOIN IGS.IGS_AD_I_PRG_U_SET uset ON ust.INQ_PROG_UNIT_SET_ID = uset.INQ_PROG_UNIT_SET_ID;

Related Objects

The table's integrity is maintained through defined primary and foreign key relationships, as documented in the provided metadata. It sits between two key preference entities.

• Primary Key: IGS_AD_I_PRG_PRF_UST_PK on column INQ_PRG_PREF_USET_ID.
• Foreign Keys (Referenced By This Table):
  • IGS_AD_I_PRG_PREFS via column INQ_PROG_PREF_ID. This links to the master inquiry program preference.
  • IGS_AD_I_PRG_U_SET via column INQ_PROG_UNIT_SET_ID. This links to the defined inquiry unit set.
• Unique Indexes: The table enforces critical business rules through unique constraints:
  • IGS_AD_I_PRG_PRF_UST_U1: Uniqueness on the primary key (INQ_PRG_PREF_USET_ID).
  • IGS_AD_I_PRG_PRF_UST_U2: Uniqueness on the combination of INQ_PROG_PREF_ID and INQ_PROG_UNIT_SET_ID, preventing duplicate unit set links for a program preference.
  • IGS_AD_I_PRG_PRF_UST_U3: Uniqueness on INQ_PROG_PREF_ID and UNIT_SET_PREF_ID, ensuring a specific unit set preference is not assigned multiple times to the same program preference.