Search Results igs_ps_of_unt_set_rl




Overview
Explore in AI Chat

Overview

The table IGS_PS_OF_UNT_SET_RL is a core data structure within the Oracle E-Business Suite Student System (IGS) for versions 12.1.1 and 12.2.2. It is designed to enforce and manage hierarchical relationships between unit sets (also known as majors, minors, or specializations) offered within a specific program (course) version. Its primary role is to define and restrict permissible parent-child relationships between unit sets that a student can select, ensuring academic structure and compliance with program rules. This table is essential for maintaining the integrity of a student's academic plan by governing how unit sets can be combined.

Key Information Stored

The table stores a composite record that uniquely defines a supervisory relationship between two unit sets in a program offering. Its primary key consists of seven columns that collectively identify the precise context of the relationship. The key columns are: COURSE_CD (the program code), CRV_VERSION_NUMBER (the program version), CAL_TYPE (the academic calendar type), SUP_UNIT_SET_CD (the code of the supervising or parent unit set), SUP_US_VERSION_NUMBER (the version of the parent unit set), SUB_UNIT_SET_CD (the code of the subordinate or child unit set), and SUB_US_VERSION_NUMBER (the version of the child unit set). Each row establishes that the subordinate unit set is a valid child selection under the specified supervisory unit set within that particular program offering.

Common Use Cases and Queries

A primary use case is validating a student's unit set enrollment during academic planning or administrative processes. The system queries this table to determine if a student attempting to enroll in a secondary unit set is permitted based on their primary unit set enrollment. For reporting, it is used to analyze program structures and unit set dependencies. A common query pattern involves joining to the unit set offering table (IGS_PS_OFR_UNIT_SET) twice to retrieve descriptive details for both the parent and child unit sets.

SELECT sup.unit_set_cd AS Parent_Set,
       sub.unit_set_cd AS Child_Set,
       rl.course_cd,
       rl.crv_version_number
FROM igs_ps_of_unt_set_rl rl,
     igs_ps_ofr_unit_set sup,
     igs_ps_ofr_unit_set sub
WHERE rl.course_cd = sup.course_cd
  AND rl.crv_version_number = sup.crv_version_number
  AND rl.cal_type = sup.cal_type
  AND rl.sup_unit_set_cd = sup.unit_set_cd
  AND rl.sup_us_version_number = sup.us_version_number
  AND rl.course_cd = sub.course_cd
  AND rl.crv_version_number = sub.crv_version_number
  AND rl.cal_type = sub.cal_type
  AND rl.sub_unit_set_cd = sub.unit_set_cd
  AND rl.sub_us_version_number = sub.us_version_number
  AND rl.course_cd = :p_course_code;

Related Objects

The IGS_PS_OF_UNT_SET_RL table has a direct and exclusive foreign key relationship with the IGS_PS_OFR_UNIT_SET table, which stores the definition of a unit set offered within a program. This relationship is implemented twice from IGS_PS_OF_UNT_SET_RL to IGS_PS_OFR_UNIT_SET, once for the parent unit set and once for the child unit set. The specific join columns are:

  • For the Parent (Supervisory) Unit Set: IGS_PS_OF_UNT_SET_RL joins to IGS_PS_OFR_UNIT_SET on (COURSE_CD, CRV_VERSION_NUMBER, CAL_TYPE, SUP_UNIT_SET_CD, SUP_US_VERSION_NUMBER).
  • For the Child (Subordinate) Unit Set: IGS_PS_OF_UNT_SET_RL joins to IGS_PS_OFR_UNIT_SET on (COURSE_CD, CRV_VERSION_NUMBER, CAL_TYPE, SUB_UNIT_SET_CD, SUB_US_VERSION_NUMBER).

This design ensures referential integrity, meaning a relationship cannot be created unless both the parent and child unit sets are validly offered in the specified program version and calendar.