Search Results igs_ps_ref_cd_hist_all




Overview
Explore in AI Chat

Overview

The table IGS_PS_REF_CD_HIST_ALL is a core data entity within the Oracle E-Business Suite Student System (IGS). Its primary function is to maintain a historical audit trail of changes made to reference codes associated with specific program versions. In the context of the Student System, a program version represents a distinct offering of an academic course or program. Reference codes are standardized classifications, such as government reporting codes or internal program types, linked to these versions. This table ensures that any modification to these critical classifications is permanently recorded, providing a complete lineage for compliance, reporting, and historical analysis.

Key Information Stored

The table's structure is designed to uniquely identify each historical record and its relationship to core master data. The primary key is a composite of five columns, ensuring a unique entry for each change: COURSE_CD and VERSION_NUMBER identify the specific program version from the IGS_PS_VER_ALL table. The REFERENCE_CD_TYPE and REFERENCE_CD specify the category and the actual code value that was changed. The HIST_START_DT column captures the effective date when that specific reference code assignment became active for the program version. While the provided metadata does not list all columns, typical historical tables in this module would also include an end date (HIST_END_DT) and columns to track who made the change.

Common Use Cases and Queries

This table is essential for auditing and generating point-in-time reports. A common scenario involves reconciling program data for a past academic period, such as a government submission for a specific fiscal year. Analysts can query this table to determine exactly which reference codes were active on any given historical date. Sample SQL to find the state of reference codes for a program as of a certain date would involve filtering on the historical date range.

SELECT course_cd, version_number, reference_cd_type, reference_cd
FROM igs_ps_ref_cd_hist_all
WHERE course_cd = 'BSC_COMP'
  AND version_number = 2020
  AND hist_start_dt <= TO_DATE('15-MAR-2021')
  AND (hist_end_dt IS NULL OR hist_end_dt > TO_DATE('15-MAR-2021'));

Another critical use case is analyzing the frequency and pattern of reference code changes across program versions for process improvement.

Related Objects

The table maintains strict referential integrity through defined foreign key relationships, anchoring it to master data tables. The key documented relationships are:

  • IGS_PS_VER_ALL: The table is linked to the Program Version master via the columns COURSE_CD and VERSION_NUMBER. This ensures every historical record corresponds to a valid program version.
  • IGS_GE_REF_CD_TYPE_ALL: The table references the Reference Code Type definition table via the REFERENCE_CD_TYPE column. This validates that the type of reference code being tracked is a configured and active code type within the system.

The primary key constraint IGS_PS_REF_CD_HIST_PK ensures the uniqueness of each historical entry. This table is likely referenced by various Student System reports and audit processes that require historical program attribute data.