Search Results igs_ps_unt_dscp_hist_all

Overview

The table IGS_PS_UNT_DSCP_HIST_ALL is a core data entity within the Oracle E-Business Suite Student System (IGS) module, specifically designed for versions 12.1.1 and 12.2.2. It serves a critical audit and historical tracking function by recording the complete history of changes to the discipline classification assigned to a specific unit version. In academic administration, a unit (or course) version can be associated with one or more discipline groups (e.g., Computer Science, Mathematics) for reporting, accreditation, and analytical purposes. This table ensures that any modification to this association is permanently logged, providing a reliable audit trail for compliance and enabling accurate historical analysis of unit offerings and their disciplinary focus over time.

Key Information Stored

The table's structure is centered on identifying the unit version, the discipline, and the historical period. The primary key is a composite of four columns: UNIT_CD (the code identifying the academic unit), VERSION_NUMBER (the specific version of that unit), DISCIPLINE_GROUP_CD (the code for the associated discipline group), and HIST_START_DT (the effective date when this particular discipline association became active for the unit version). This design allows the system to store multiple records for the same unit version and discipline combination across different time periods, effectively building a timeline. While the provided metadata focuses on keys, typical complementary columns would include HIST_END_DT to define the period's conclusion and CREATION_DATE for system auditing.

Common Use Cases and Queries

This table is essential for generating historical reports on curriculum development and compliance. A primary use case is auditing changes to a unit's disciplinary alignment for accreditation reviews, answering questions about when a unit was classified under a specific discipline. Another key scenario is performing historical trend analysis for strategic planning, such as tracking the growth of STEM disciplines over several academic years. Common SQL queries involve joining this history table with the unit and discipline master tables to retrieve descriptive names.

For example, to retrieve the full discipline history for a specific unit version:

• SELECT h.unit_cd, h.version_number, h.discipline_group_cd, d.description, h.hist_start_dt, h.hist_end_dt FROM igs_ps_unt_dscp_hist_all h JOIN igs_ps_dscp_all d ON h.discipline_group_cd = d.discipline_group_cd WHERE h.unit_cd = 'MATH101' AND h.version_number = 2 ORDER BY h.hist_start_dt;

To find all units associated with a given discipline at a specific point in time (e.g., for a past catalog year):

• SELECT h.unit_cd, h.version_number FROM igs_ps_unt_dscp_hist_all h WHERE h.discipline_group_cd = 'COMPSCI' AND TO_DATE('01-JAN-2020', 'DD-MON-YYYY') BETWEEN h.hist_start_dt AND NVL(h.hist_end_dt, SYSDATE);

Related Objects

The IGS_PS_UNT_DSCP_HIST_ALL table maintains strict referential integrity with master data tables through defined foreign key relationships. It is a child table that depends on two primary parent entities:

• IGS_PS_UNIT_VER_ALL: This table stores the master definition of unit versions. The history table references it via the columns UNIT_CD and VERSION_NUMBER. This ensures every historical discipline record is tied to a valid, existing unit version.
• IGS_PS_DSCP_ALL: This table is the master list of all valid discipline groups. The history table references it via the DISCIPLINE_GROUP_CD column, guaranteeing that only defined discipline codes are recorded in the history.

The primary key constraint IGS_PS_UNT_DSCP_HIST_PK enforces uniqueness on the combination of these foreign keys and the history start date, forming the foundational structure for the historical timeline.