Search Results igs_au_gen_002




Overview
Explore in AI Chat

Overview

IGS_PS_VER_HIST_V is a public Oracle EBS Applications view owned by the APPS schema and defined in the Student Systems (IGS) product family, specifically within the PeopleSoft-derived Higher Education course and program versioning modules. The view exposes the historical version records of courses, presenting a consolidated, version-aware picture of course attributes across the effective date range of each version. Its principal role is to support reporting and integration consumers that require point-in-time or full-history visibility of course definitions rather than only the current, active record.

A distinguishing characteristic of this view is that it does not simply project stored columns from a single history table. Instead it reconstructs column values by combining the raw history row (aliased cvh1) with a fallback chain: first the current-version row (aliased cv1), then a call to the audited-change accessor function IGS_AU_GEN_002.audp_get_cvh_col. This design allows the view to return a meaningful value for every attribute even when a specific version record was stored sparsely.

Underlying Base Objects

The documented ETRM metadata for this view lists no referenced base objects, and the excerpt provided does not include the FROM clause of the view text. From the visible SELECT list, however, two logical sources are unambiguously referenced by alias:

  • cvh1 — the course version history record, providing columns such as course_cd, version_number, hist_start_dt, hist_end_dt, and hist_who, together with the versioned attribute columns (start_dt, review_dt, expiry_dt, end_dt, course_status, title, short_title, abbreviation, and the various indicator flags).
  • cv1 — the current or primary course version record, used as the second-level default when the history column is null and the audit accessor returns no value.

Because the ETRM entry records no base objects, the exact physical table names (typically the IGS_PS_VER history and current tables) cannot be confirmed from the supplied metadata and should be verified against the live data dictionary in the target environment before reliance.

Key Columns

  • course_cd, version_number — the identifying key of each course version; the pair also forms the arguments passed to audp_get_cvh_col.
  • hist_start_dt, hist_end_dt, hist_who — the effective date span and the user who created the history record, supporting audit and as-of reporting.
  • start_dt, review_dt, expiry_dt, end_dt — lifecycle dates for the version, each resolved through the NVL fallback chain and passed through IGS_GE_DATE.IGSDATE for date normalisation.
  • course_status — truncated to ten characters; the status of the course version at the history point.
  • title, short_title, abbreviation — descriptive attributes truncated to 90, 40 and 20 characters respectively to match target column widths.
  • supp_exam_permitted_ind, generic_course_ind, graduate_students_ind — single-character indicator flags, each similarly resolved and truncated to length one.

Common Use Cases and Queries

Typical uses include audit reconstruction of a course definition as it stood on a given date, comparison of successive versions, and integration extracts feeding downstream reporting marts. Because the audit accessor invokes IGS_AU_GEN_002 per column, performance can be materially affected on large result sets, so queries should filter aggressively on course_cd or version_number and constrain hist_start_dt/hist_end_dt where possible.

A representative query retrieving the history of a single course is:

  • SELECT course_cd, version_number, hist_start_dt, hist_end_dt, title, course_status FROM apps.igs_ps_ver_hist_v WHERE course_cd = :course_cd ORDER BY version_number, hist_start_dt;

An as-of query restricts the effective window:

  • SELECT course_cd, version_number, title FROM apps.igs_ps_ver_hist_v WHERE hist_start_dt <= :as_of AND NVL(hist_end_dt, :as_of) >= :as_of;

Where the physical base objects are required for tuning, the view definition in ALL_VIEWS should be queried directly, since the supplied ETRM metadata does not document them.