Search Results igs_ps_ver_hist_all




Overview
Explore in AI Chat

Overview

IGS_PS_VER_HIST_ALL is a Student System (IGS) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that maintains the change history of program version records. Where the base program version rows in IGS_PS_VER_ALL carry the current attributes of a program version, IGS_PS_VER_HIST_ALL preserves each prior state of those attributes, timestamped by the effective range of the historical record. It is owned by the IGS schema, is documented as VALID, and contains 55 columns in the ETRM 12.1.1 physical schema. The entity is described as the history of changes to a program version, and it is functionally a temporal (audit) shadow of the program version entity.

Mined from its foreign-key structure, the heuristic Data Vault classification for this table is standalone. No FK relationships to parent hubs or links are documented, which suggests that the table is best modeled as a standalone satellite-like history structure keyed by the business identifier of the program version rather than participating in an explicit hub/link network. This classification should be treated as a modeling suggestion, not a normative constraint.

Key Information Stored

The primary key is IGS_PS_VER_HIST_PK, composed of COURSE_CD, VERSION_NUMBER, and HIST_START_DT. A unique index, IGS_PS_VER_HIST_ALL_U1, is defined on the same three columns (COURSE_CD, VERSION_NUMBER, HIST_START_DT), confirming these as the business-key candidates. The primary key is therefore a composite business key rather than a single surrogate sequence column.

Common Use Cases and Queries

Typical usage includes auditing changes to a program version, reconstructing the state of a program version as of a past date, and regulatory or accreditation reporting that requires a full change trail. A common query pattern reconstructs history for one program version:

  • SELECT * FROM igs.igs_ps_ver_hist_all WHERE course_cd = :course_cd AND version_number = :version_number ORDER BY hist_start_dt;
  • Point-in-time reconstruction: WHERE hist_start_dt <= :as_of AND (hist_end_dt IS NULL OR hist_end_dt > :as_of).
  • Change attribution: group by HIST_WHO to identify who modified a program version and when.
  • Change-diff reporting between consecutive HIST_START_DT rows to detect attribute drift in COURSE_STATUS or CREDIT_POINTS_REQUIRED.

Because the table is multi-org aware (ORG_ID), reporting queries should join to the operating unit context or filter by ORG_ID where applicable.

Related Objects

The documented relationship data classifies this object as standalone with no explicit FK references. In practice, the most significant related objects are the current-state counterparts and the shared lookup/validation entities:

  • IGS_PS_VER_ALL — the current program version entity; joined on COURSE_CD and VERSION_NUMBER.
  • IGS_PS_VERSION — the version definition referenced by VERSION_NUMBER.
  • IGS_PS_VER_HIST — the non "_ALL" view/object form of the same history where present.
  • IGS_PS_OFFERING_ALL — offerings derived from a program version, joined via COURSE_CD/VERSION_NUMBER.
  • IGS_EN_PS_VER_ALL — student program version enrollment records referencing the same program version key.
  • Org/OU reference tables — joined via ORG_ID and RESPONSIBLE_ORG_UNIT_CD.

All facts above are drawn from the ETRM metadata; joins beyond COURSE_CD/VERSION_NUMBER should be validated against the live schema.