Search Results igs_ps_ver_hist_all_u1

Overview

IGS.IGS_PS_VER_HIST_ALL is a transactional table in the Oracle E-Business Suite (EBS) student systems data model that stores the effective-dated history of changes applied to a program version. A program version, identified by a program code (COURSE_CD) and a VERSION_NUMBER, represents a specific, time-bounded offering of an academic program. Because attributes such as title, status, expiry dates, credit requirements and financial aid indicators change over the life of a program, this table preserves each historical state of the record rather than overwriting it. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its indexes are held in APPS_TS_TX_IDX, consistent with standard EBS transactional object placement.

In Data Vault modeling terms, the mined structure is classified heuristically as standalone. This classification is a modeling suggestion only: the table behaves as a satellite-like record of an academic program version, but no foreign key dependencies were mined, so it is presented as a standalone entity rather than being formally asserted as a hub, link or satellite.

Key Information Stored

The table is defined with a three-column primary key, IGS_PS_VER_HIST_PK, comprising COURSE_CD, VERSION_NUMBER and HIST_START_DT. The unique index IGS_PS_VER_HIST_ALL_U1 covers the identical column set (COURSE_CD, VERSION_NUMBER, HIST_START_DT) and therefore represents the business-key candidate for this object. The surrogate primary key columns are the same as those of the business key here, which is typical of effective-dated history structures.

• COURSE_CD – The program code identifying the program to which the version belongs.
• VERSION_NUMBER – The version number of the program. Only one version may hold a system status of active with no expiry date set.
• HIST_START_DT / HIST_END_DT – The effective start and end date and time of the history record, defining the validity window of each captured state.
• HIST_WHO – The person who performed the update that generated the history record.
• START_DT, REVIEW_DT, EXPIRY_DT, END_DT – The date the version became current, the date it is due for review (no automatic closure or rollover implied), the date it expires, and the date it is completely ended.
• COURSE_STATUS – The institution-defined program status indicating the level of the program.
• TITLE, SHORT_TITLE, ABBREVIATION – The full, short and abbreviated names of the program version.
• RESPONSIBLE_ORG_UNIT_CD – The organisation unit responsible for the program version.
• SUPP_EXAM_PERMITTED_IND – Indicates whether supplementary examinations or other assessment may be undertaken by enrolled students.
• Last-updated audit columns – CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE and LAST_UPDATE_LOGIN; ORG_ID provides the multi-org context.

Common Use Cases and Queries

Typical reporting uses include point-in-time reconstruction of a program version as of a given date, auditing of attribute changes over time, and compliance reporting against expiry, review and end dates. A representative pattern retrieves the effective history row for a program version at a chosen timestamp:

• SELECT * FROM igs.igs_ps_ver_hist_all WHERE course_cd = :course_cd AND version_number = :version_number AND hist_start_dt <= :as_of AND NVL(hist_end_dt, SYSDATE) > :as_of;
• Tracking changes in status or credit requirements: select HIST_START_DT, HIST_WHO, COURSE_STATUS, CREDIT_POINTS_REQUIRED ordered by HIST_START_DT for a COURSE_CD and VERSION_NUMBER.
• Identifying versions approaching expiry or review: filter on EXPIRY_DT or REVIEW_DT within a range and join to the current program version record.

Related Objects

The ETRM metadata records no mined foreign key relationships for this table, and it is classified as standalone, so dependencies are inferred from the program-version key structure rather than from documented constraints. The principal related objects are:

• Program version base table (keyed by COURSE_CD, VERSION_NUMBER) – the current-state parent of this history table; joins are made on COURSE_CD and VERSION_NUMBER.
• Program / course base table (keyed by COURSE_CD) – the parent program definition.
• Organisation unit table – joined via RESPONSIBLE_ORG_UNIT_CD, and RESPONSIBLE_OU_START_DT for the effective-dated organisation relationship.
• Student program enrolment tables – reference COURSE_CD and VERSION_NUMBER to determine which program version a student was enrolled in at a point in time.
• FND Design Data registration – IGS.IGS_PS_VER_HIST_ALL is registered in FND Design Data, which exposes the object to the standard EBS diagnostic and dependency reporting mechanisms.