Search Results igs_ps_ver_hist_v




Overview
Explore in AI Chat

Overview

IGS_PS_VER_HIST_V is a view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product family. Its documented status is VALID, and it is shipped as part of the standard IGS data model in both Oracle EBS 12.1.1 and 12.2.2. The view presents a consolidated, time-aware picture of program (course) version data by merging rows from the program version history table with the current program version record. According to the ETRM description, the view exists to "merge program version history with the current program version details enabling all details to be accessed over time, up till the current day," with each entry representing all column values over defined time periods.

The result is a slowly-changing-dimension style output: every historical period for a given course code and version number is preserved as its own row, bounded by HIST_START_DT and HIST_END_DT, while the most recent period is filled in from the currently active version record. This makes the view suitable for point-in-time reporting, audit reconstruction, and institutional research where the state of a program at an arbitrary past date must be reproduced.

Underlying Base Objects

The view is defined primarily over the program version history table, aliased CVH1 in the view text, which supplies the historical snapshot columns (COURSE_CD, VERSION_NUMBER, HIST_START_DT, HIST_END_DT, HIST_WHO). The current program version table, aliased CV1, supplies the fallback values used when no historical value exists for a given column and period. Resolution of historical values is performed through the IGS_AU_GEN_002 package, whose AUDP_GET_CVH_COL function retrieves a named column value for a course/version pair as of a supplied history end date. Date strings returned by that function are converted with IGS_GE_DATE.IGSDATE. The AUD naming convention identifies IGS_AU_GEN_002 as an audit/history generator package, the routine responsible for materialising the change-tracking rows that the view consumes.

The ETRM record documents no referenced base objects explicitly, but the view text makes the dependency on the program version history table, the program version table, IGS_AU_GEN_002, and IGS_GE_DATE unambiguous. The APPS synonym layer exposes the view to reporting users and to other IGS components.

Key Columns

  • COURSE_CD — Program (course) code identifying the academic offering.
  • VERSION_NUMBER — Version of the program; history is tracked per course and version combination.
  • HIST_START_DT / HIST_END_DT — The effective window for each historical row; the end date also drives the AUDP_GET_CVH_COL lookups.
  • HIST_WHO — The user or process that created the historical record, supporting audit requirements.
  • START_DT, REVIEW_DT, EXPIRY_DT, END_DT — Program lifecycle dates resolved historically first, then from the current version record.
  • COURSE_STATUS — Status of the program during the period, normalised to ten characters.
  • TITLE, SHORT_TITLE, ABBREVIATION — Descriptive attributes resolved historically where available.
  • SUPP_EXAM_PERMITTED_IND, GENERIC_COURSE_IND — Indicator flags with the same historical-first precedence.

Common Use Cases and Queries

Typical uses include reconstructing the catalogue state of a program on a given date, auditing who changed a title or status and when, and feeding downstream extracts where a full history rather than just the current row is required. A representative query returns the timeline for one program:

  • SELECT course_cd, version_number, hist_start_dt, hist_end_dt, course_status, title FROM igs_ps_ver_hist_v WHERE course_cd = :course_code AND version_number = :version ORDER BY hist_start_dt;

Point-in-time lookups filter on the effective window, for example WHERE :as_of_date BETWEEN hist_start_dt AND hist_end_dt, returning the exact attribute set in force on that date. Because resolution depends on PL/SQL function calls, queries against large date ranges can be resource-intensive; restricting by course code or history window is advisable in production reporting.