Results for “igs_ps_unit_ver_hist_all”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IGS_PS_UNIT_VER_HIST_ALL is a student system table owned by the IGS schema in Oracle E-Business Suite, delivered as part of the Student System (IGS) product family. The entity captures the historical trail of changes applied to a unit version. In Oracle EBS terminology, a "unit" is the academic equivalent of a course offering; each unit carries one or more versions, and every modification to a version — whether to title, credit points, status, workload, or assessment attributes — is recorded as a dated historical row. The table is therefore an audit and temporal-versioning store rather than an operational master, preserving a snapshot of each unit version as it existed at a defined point in time.

The heuristic Data Vault classification mined from the foreign-key structure is satellite. This classification is consistent with the table's design: its primary key combines the natural business key of the parent unit version (UNIT_CD, VERSION_NUMBER) with an effective-dating component (HIST_START_DT), which is the canonical satellite pattern. No child tables reference this entity, reinforcing its role as a leaf-level descriptive record rather than a hub or link.

Key Information Stored

The primary key, IGS_PS_UNIT_VER_HIST_PK, is the composite (UNIT_CD, VERSION_NUMBER, HIST_START_DT). A unique index, IGS_PS_UNIT_VER_HIST_ALL_U1, covers the same three columns and confirms this triplet as the business-key candidate for the version history. The table is documented with 99 columns in ETRM 12.1.1; the most significant are summarized below.

• UNIT_CD, VERSION_NUMBER — the unit code and version number identifying the parent unit version whose history is being tracked.
• HIST_START_DT, HIST_END_DT — the effective start and end dates delimiting the validity window of each historical snapshot; the driver of the temporal query pattern.
• HIST_WHO — identifies the user or process that produced the historical change.
• TITLE, SHORT_TITLE, ABBREVIATION, SUBTITLE — descriptive text of the unit version at the point in time captured.
• UNIT_STATUS, UNIT_LEVEL, UNIT_TYPE_ID — status, academic level, and unit type classifications in effect for the snapshot.
• ENROLLED_CREDIT_POINTS, ACHIEVABLE_CREDIT_POINTS, POINTS_MIN, POINTS_MAX — credit and points configuration as historically recorded.
• REVIEW_DT, EXPIRY_DT, START_DT, END_DT — lifecycle dates associated with the unit version.
• SUBJECT area identifiers — SUBTITLE_ID, CURRICULUM_ID, RPT_FMLY_ID reference descriptive masters (see Related Objects).
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ORG_ID.

Common Use Cases and Queries

Typical reporting scenarios include reconstructing the state of a unit version as of a given date, comparing successive versions to detect credit-point or title changes, and auditing which user modified a unit's status. The temporal pattern relies on HIST_START_DT and HIST_END_DT.

-- Point-in-time snapshot of a unit version
SELECT unit_cd, version_number, title, unit_status, enrolled_credit_points
FROM   igs.igs_ps_unit_ver_hist_all
WHERE  unit_cd        = :unit_cd
AND    version_number = :version_number
AND    :as_of_dt BETWEEN hist_start_dt AND NVL(hist_end_dt, :as_of_dt);

-- Full change history ordered by effective date
SELECT hist_start_dt, hist_end_dt, hist_who, title, unit_status
FROM   igs.igs_ps_unit_ver_hist_all
WHERE  unit_cd = :unit_cd
ORDER  BY hist_start_dt;

Because the table is standalone (no child FKs), joins flow outward to reference masters rather than inward from dependents.

Related Objects

The outbound foreign keys connect the history satellite to its descriptive masters. The significant related objects and join columns are:

• IGS_PS_UNIT_SUBTITLE — joined on SUBTITLE_ID; provides subtitle text.
• IGS_PS_UNT_CRCLM_ALL — joined on CURRICULUM_ID; resolves the curriculum for the version.
• IGS_PS_RPT_FMLY_ALL — joined on RPT_FMLY_ID; supplies the report family grouping.
• IGS_PS_UNIT_TYPE_LVL — joined on UNIT_TYPE_ID; supplies unit type and level descriptions.
• The parent unit version master (Unit Version entity) — logically joined on UNIT_CD and VERSION_NUMBER, providing the current live version against which history is compared.

Together these relationships position IGS_PS_UNIT_VER_HIST_ALL as the satellite layer over the unit-version hub, delivering full change-audit and point-in-time reporting for the IGS Student System.