Search Results igs_ps_unit_lvl_hist_all




Overview
Explore in AI Chat

Overview

The IGS_PS_UNIT_LVL_HIST_ALL table is a core data entity within the Oracle E-Business Suite Student System (IGS) module, specifically designed for releases 12.1.1 and 12.2.2. It functions as a historical audit trail, capturing the chronological record of changes made to the academic level at which a specific unit version is offered within a particular program (course). This table is essential for maintaining data integrity and providing an auditable history of how the relationship between a unit and a course's academic level evolves over time, which is critical for accurate academic planning, reporting, and compliance.

Key Information Stored

The table's structure is defined by a composite primary key and several foreign key relationships. The primary key uniquely identifies each historical record and consists of: UNIT_CD (the unit code), VERSION_NUMBER (the unit version), COURSE_CD (the program/course code), COURSE_VERSION_NUMBER (the program version), and HIST_START_DT (the effective date when the specific unit-level assignment began). Crucially, the HIST_START_DT column enables the temporal tracking of changes. Other significant columns include UNIT_LEVEL, which stores the academic level code (e.g., Undergraduate, Postgraduate) for the unit within the context of the specified course, and COURSE_TYPE, which categorizes the type of program. The table's "ALL" suffix indicates it is a multi-org table containing data for all operating units.

Common Use Cases and Queries

This table is primarily used for historical analysis and regulatory reporting. A common scenario involves auditing the progression of a unit's academic level assignment within a specific program version over time. For instance, an institution may need to report on all units that were classified at a postgraduate level for a Master's program during a specific academic year, even if that classification has since changed. A typical query would join this history table to the unit and course version tables to retrieve a coherent timeline.

  • Sample Query (Retrieve Historical Unit Levels for a Course):
    SELECT ulh.unit_cd, ulh.version_number, ulh.hist_start_dt, ul.unit_level
    FROM igs_ps_unit_lvl_hist_all ulh
    JOIN igs_ps_unit_level_all ul ON ulh.unit_level = ul.unit_level
    WHERE ulh.course_cd = 'BSC_COMPSCI'
    AND ulh.course_version_number = 2020
    ORDER BY ulh.unit_cd, ulh.hist_start_dt;

Related Objects

The IGS_PS_UNIT_LVL_HIST_ALL table is centrally connected to several master data tables via documented foreign key constraints, forming a critical part of the Student System's data model.

  • IGS_PS_UNIT_VER_ALL: References the specific unit version via columns UNIT_CD and VERSION_NUMBER.
  • IGS_PS_VER_ALL: References the specific program (course) version via columns COURSE_CD and COURSE_VERSION_NUMBER.
  • IGS_PS_UNIT_LEVEL_ALL: References the valid academic level code via the UNIT_LEVEL column.
  • IGS_PS_TYPE_ALL: References the valid program type via the COURSE_TYPE column.