Search Results igs_au_gen_004




Overview
Explore in AI Chat

Overview

IGS_PS_UNIT_VER_HIST_V is a view owned by the APPS schema within the Oracle E-Business Suite Student System (IGS) product family. It is documented in ETRM as VALID in releases 12.1.1 and 12.2.2. The view presents a consolidated, time-sliced picture of program unit versions by merging records from the unit version history table with the corresponding current unit version record. Each row represents the complete set of column values for a given unit code and version_number over a defined historical time period, allowing the attributes of a unit version to be reconstructed as they existed on any past date up to and including the present day.

Because academic structures change over time — titles, statuses, review dates, and expiry dates are revised without necessarily creating a new version — the history table records only the attributes that changed, while the current unit version table holds the latest values. This view reconciles the two sources so that consumers receive a single, uniform record per history interval. It therefore plays a reporting and integration role: any inquiry, extract, or interface that must show unit version data "as of" a historical date can read from this view rather than reimplementing the reconciliation logic.

Underlying Base Objects

The documented view text references the unit version history source (aliased UVH1), the current unit version source (aliased UV1), and several IGS utility objects: the IGS_GE_DATE package, the IGS_AU_GEN_004 package, and its function AUDP_GET_UVH_COL. The history source supplies the unit code, version_number, history start and end dates, and the historic attribute values. The current version source supplies fallback values. The AUDP_GET_UVH_COL function retrieves the historical column value appropriate to the interval end date, and IGS_GE_DATE.IGSDATE converts the returned value into a date.

ETRM records no referenced base objects for this view in the documented metadata, so the precise physical table names are not formally enumerated. In practice the view resolves to the unit version history table (IGS_PS_UNIT_VER_HIST) and the current unit version table (IGS_PS_UNIT_VER), joined by unit code and version_number. The nested NVL structure establishes the resolution precedence: the explicit history column value is used first, otherwise the value retrieved through AUDP_GET_UVH_COL for the history interval, and finally the current unit version value.

Key Columns

  • UNIT_CD — the code identifying the program unit.
  • VERSION_NUMBER — the version of the unit; this is the column most frequently queried and is central to the view's purpose.
  • HIST_START_DT / HIST_END_DT — the effective boundaries of the history interval each row represents.
  • HIST_WHO — the user or process associated with the history record.
  • START_DT, REVIEW_DT, EXPIRY_DT, END_DT — lifecycle dates resolved through the NVL precedence chain.
  • UNIT_STATUS — the status of the unit version during the interval.
  • TITLE, SHORT_TITLE, ABBREVIATION — descriptive attributes, with SUBSTR applied to enforce the expected column widths (90, 40, and 20 characters respectively).
  • TITLE_OVERRIDE_IND — flag indicating whether the title is overridden.
  • UNIT_LEVEL — the academic level of the unit.

Common Use Cases and Queries

Typical uses include point-in-time reporting of unit attributes, auditing changes to a version, and feeding downstream extracts. A query retrieving the full history for a single version:

SELECT unit_cd, version_number, hist_start_dt, hist_end_dt,
       unit_status, title, unit_level
FROM   apps.igs_ps_unit_ver_hist_v
WHERE  unit_cd = :unit_cd
AND    version_number = :version_number
ORDER BY hist_start_dt;

A query identifying records effective on a specific date uses the interval columns:

SELECT unit_cd, version_number, title, unit_status
FROM   apps.igs_ps_unit_ver_hist_v
WHERE  :as_of_date BETWEEN hist_start_dt AND hist_end_dt;

Because the view already resolves historical and current values, callers need not join the underlying history and current tables themselves, nor replicate the AUDP_GET_UVH_COL logic. Reporting against version_number through this view is the supported way to expose unit version attributes across time.