Search Results igs_ps_unt_crclm_all_pk

Overview

The table IGS_PS_UNT_CRCLM_ALL is a master data table within the Oracle E-Business Suite (EBS) 12.1.1 / 12.2.2 Student System (IGS) module, which is documented as obsolete. Its primary function is to serve as a reference or lookup table for curriculum identifiers. It establishes a controlled list of valid CURRICULUM_ID values, enforcing data integrity for the core unit version data stored in the IGS_PS_UNIT_VER_ALL table. By defining this primary key relationship, the table acts as a parent entity, ensuring that any curriculum ID associated with a unit version is a valid, pre-defined identifier within the system.

Key Information Stored

Based on the provided ETRM metadata, the table's structure is centered on a single primary key column. The core data element stored is the CURRICULUM_ID, which is the unique identifier for a curriculum definition. While the explicit documentation does not list other columns, such master tables in EBS typically include standard descriptive columns (e.g., NAME, DESCRIPTION, START_DATE, END_DATE) and WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) for auditing. The critical fact from the metadata is that CURRICULUM_ID is the sole component of the primary key constraint IGS_PS_UNT_CRCLM_ALL_PK.

Common Use Cases and Queries

The primary use case for this table is referential integrity validation and reporting on curriculum associations. A common operational query would involve joining this master table to the unit version detail to retrieve a comprehensive list of units with their assigned curriculum names. For instance, a report to list all active units and their associated curriculum would utilize an outer join, as not all units may have a curriculum assigned. A sample SQL pattern is:

• SELECT uv.UNIT_CD, uv.VERSION_NUMBER, uc.CURRICULUM_ID FROM IGS_PS_UNIT_VER_ALL uv LEFT JOIN IGS_PS_UNT_CRCLM_ALL uc ON uv.CURRICULUM_ID = uc.CURRICULUM_ID WHERE uv.CLOSED_IND = 'N';

Another critical use case is data validation during migrations or integrations, ensuring that any CURRICULUM_ID populated in transactional tables exists in this master table.

Related Objects

The table has a direct, documented foreign key relationship with a core transactional table in the Student System. The relationship is defined as follows:

• Child Table: IGS_PS_UNIT_VER_ALL
• Relationship: The column IGS_PS_UNIT_VER_ALL.CURRICULUM_ID references IGS_PS_UNT_CRCLM_ALL.CURRICULUM_ID.
• Join Condition: IGS_PS_UNIT_VER_ALL.CURRICULUM_ID = IGS_PS_UNT_CRCLM_ALL.CURRICULUM_ID

This relationship signifies that the curriculum identifier for any given unit version is validated against the master list defined in IGS_PS_UNT_CRCLM_ALL. No other foreign key relationships are documented in the provided metadata.