Search Results igs_ps_unit_ref_hist_all




Overview
Explore in AI Chat

Overview

The IGS_PS_UNIT_REF_HIST_ALL table is a core data object within the Oracle E-Business Suite Student System (IGS). It functions as a historical audit trail, capturing all changes made to the reference codes associated with a specific unit version. In the context of academic administration, a unit version represents a particular offering of a course subject, and reference codes are standardized classifications (such as government reporting codes, discipline codes, or funding categories) linked to it. This table ensures a complete, time-stamped record of these classifications is maintained for compliance, reporting, and historical analysis purposes across both EBS releases 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to uniquely identify each historical entry and its context. The primary key is a composite of five columns: UNIT_CD (the unit code), VERSION_NUMBER (the specific version of the unit), REFERENCE_CD_TYPE (the category of the reference code), REFERENCE_CD (the actual code value), and HIST_START_DT (the effective date the historical record became active). This combination ensures that every change to a reference code for a unit version is recorded with its precise effective start date. Additional columns, implied by the nature of a history table, would typically include an end date (HIST_END_DT) to mark when the record was superseded, and a CREATION_DATE or LAST_UPDATE_DATE to track system auditing.

Common Use Cases and Queries

This table is critical for generating audit reports and understanding the evolution of a unit's classification over time. A common use case is auditing all reference code changes for a unit version for regulatory compliance. Another is determining the valid reference code for a unit at a specific point in the past, which is essential for accurate historical reporting. A typical query pattern involves joining to the unit version and reference code type tables for descriptive information.

  • Sample Query (Historical Audit): SELECT unit_cd, version_number, reference_cd_type, reference_cd, hist_start_dt FROM igs_ps_unit_ref_hist_all WHERE unit_cd = 'MATH101' ORDER BY hist_start_dt;
  • Sample Query (Point-in-Time Validation): SELECT reference_cd FROM igs_ps_unit_ref_hist_all WHERE unit_cd = :p_unit_cd AND version_number = :p_ver_num AND reference_cd_type = 'GOV_CODE' AND :p_query_date BETWEEN hist_start_dt AND NVL(hist_end_dt, SYSDATE);

Related Objects

The IGS_PS_UNIT_REF_HIST_ALL table maintains strict referential integrity with key master tables in the Student System, as defined by its foreign key constraints.

  • IGS_PS_UNIT_VER_ALL: This is the parent table for unit versions. The history table references it via the UNIT_CD and VERSION_NUMBER columns to ensure every historical record is tied to a valid, existing unit version.
  • IGS_GE_REF_CD_TYPE_ALL: This is the parent table for reference code types. The history table references it via the REFERENCE_CD_TYPE column, validating that the category of the historical reference code is defined in the system.

These relationships are fundamental, ensuring that historical data remains consistent and traceable back to the core administrative entities.