Search Results igs_ps_ofr_unit_set




Overview
Explore in AI Chat

Overview

The IGS_PS_OFR_UNIT_SET table is a core data entity within the Oracle E-Business Suite Student System (IGS), specifically designed for versions 12.1.1 and 12.2.2. It serves as a fundamental junction table that establishes and manages the permissible academic relationships between programs (course offerings) and unit sets. Its primary role is to define which unit sets, such as majors, minors, or specializations, are available for a student to undertake within a specific program version and academic calendar. This linkage is essential for enforcing academic structure, enabling accurate program planning, and ensuring compliance with degree requirements. It is important to note that the ETRM metadata explicitly marks the IGS Student System as "Obsolete," indicating this table belongs to a legacy product line, though it remains a critical component in implementations still utilizing this module.

Key Information Stored

The table's structure is defined by its composite primary key, which uniquely identifies each valid program-unit set combination. The key columns are COURSE_CD and CRV_VERSION_NUMBER, which reference a specific program and its version; CAL_TYPE, which specifies the academic calendar type for the offering; and UNIT_SET_CD and US_VERSION_NUMBER, which identify a specific unit set and its version. This five-column key ensures that relationships are tracked with precise versioning for both the program and the unit set, maintaining historical and concurrent academic integrity. The table itself appears to be a simple intersection entity, implying that its core purpose is to store the existence of the relationship, with more detailed rules (like optionality or hierarchy) likely managed in related entities such as IGS_PS_OF_OPT_UNT_ST.

Common Use Cases and Queries

This table is central to queries that list all academic specializations available within a degree program. A common reporting use case involves generating a catalog of available majors and minors for a given program. For instance, an academic advisor might need to retrieve all unit sets linked to a specific Business Administration program offering. A typical SQL pattern would involve joining IGS_PS_OFR_UNIT_SET to IGS_PS_OFR_ALL for program details and to IGS_EN_UNIT_SET_ALL for unit set descriptions.

SELECT ofr.course_cd,
       ofr.crv_version_number,
       us.unit_set_cd,
       us.us_version_number,
       us.unit_set_name
FROM igs_ps_ofr_unit_set link
JOIN igs_ps_ofr_all ofr
  ON link.course_cd = ofr.course_cd
 AND link.crv_version_number = ofr.crv_version_number
 AND link.cal_type = ofr.cal_type
JOIN igs_en_unit_set_all us
  ON link.unit_set_cd = us.unit_set_cd
 AND link.us_version_number = us.version_number
WHERE ofr.course_cd = 'BSC_CS'
  AND ofr.cal_type = 'SEMESTER';

Related Objects

The table sits at the center of a defined relational schema, with documented foreign key relationships to several other entities in the Student System. These relationships are critical for understanding its integration points.

  • Parent Tables:
    • IGS_PS_OFR_ALL: The table references this parent via columns (COURSE_CD, CRV_VERSION_NUMBER, CAL_TYPE) to validate the program offering.
    • IGS_EN_UNIT_SET_ALL: The table references this parent via columns (UNIT_SET_CD, US_VERSION_NUMBER) to validate the unit set definition.
  • Child Tables / Referencing Objects:
    • IGS_PS_OF_OPT_UNT_ST: References IGS_PS_OFR_UNIT_SET using its full primary key. This table likely stores additional information about the unit set within the program, such as whether it is mandatory or optional.
    • IGS_PS_OF_UNT_SET_RL: References IGS_PS_OFR_UNIT_SET twice, using the full primary key columns for both a subordinate (SUB) and a superior (SUP) unit set. This indicates IGS_PS_OFR_UNIT_SET is used to define hierarchical relationships between unit sets within a program, such as a major containing several concentration tracks.