Results for “igs_ps_unit_lvl_hist_pk”

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

Overview

IGS.IGS_PS_UNIT_LVL_HIST_ALL is a validated table in the IGS (Student System) product schema of Oracle E-Business Suite, present in both the 12.1.1 and 12.2.2 releases. Per the ETRM documentation, the entity describes the history of changes to a unit version's program unit level. In practical terms, it is an audit-and-versioning record: whenever the program unit level assignment (for example, the level at which a unit version is taught, such as undergraduate or postgraduate) changes for a given unit version within a course context, this table captures the prior or current state together with effective dating.

Its role in EBS is supporting curriculum and program-of-study maintenance within the Student System, preserving the temporal lineage of unit-level attributes so that historical reporting, enrollment eligibility checks, and academic record reconstruction remain accurate after changes are made. The heuristic Data Vault classification mined from the foreign-key structure is link. As a modeling suggestion, this reflects that the table primarily resolves relationships between a unit version, a course version, a course type, and a unit level, with the history columns acting as an effectivity attribute set rather than as a standalone hub or a purely descriptive satellite.

Key Information Stored

The table contains 16 documented columns. The most significant are:

• UNIT_CD, VERSION_NUMBER — Identify the unit and unit version whose level history is being tracked; these form the reference to IGS_PS_UNIT_VER_ALL.
• COURSE_CD, COURSE_VERSION_NUMBER — Identify the course version against which the unit version is being leveled; these reference IGS_PS_VER_ALL.
• COURSE_TYPE — Classifies the course context; references IGS_PS_TYPE_ALL.
• UNIT_LEVEL — The program unit level value assigned; references IGS_PS_UNIT_LEVEL_ALL.
• HIST_START_DT, HIST_END_DT — Effective-dating pair defining the validity window of each historical row.
• HIST_WHO — Records the user or process responsible for the change.
• WAM_WEIGHTING — Weighted assessment mark weighting associated with the level assignment.
• ORG_ID — Multi-org operating unit context.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO-column audit trail.

No surrogate system-generated primary key is documented. The primary key is IGS_PS_UNIT_LVL_HIST_PK, defined on the composite business key UNIT_CD, VERSION_NUMBER, COURSE_CD, COURSE_VERSION_NUMBER, HIST_START_DT. The unique index IGS_PS_UNIT_LVL_HIST_ALL_U1 carries the same column set, confirming these five columns as the business-key candidate and the effective-dating anchor (HIST_START_DT) that distinguishes successive versions of the same relationship.

Common Use Cases and Queries

Typical uses include reconstructing the program unit level of a unit version as of a given date, auditing who changed level assignments and when, and reporting curriculum changes for accreditation or academic review. A point-in-time query uses the effective-dating bounds:

• SELECT unit_cd, version_number, course_cd, course_version_number, unit_level
• FROM igs_ps_unit_lvl_hist_all
• WHERE TRUNC(:as_of_date) BETWEEN hist_start_dt AND NVL(hist_end_dt, :as_of_date)
• AND org_id = :org_id;

An audit-history pattern orders rows by HIST_START_DT for a single unit version and course version, exposing the sequence of UNIT_LEVEL changes and the responsible HIST_WHO user. Reporting on weighting uses WAM_WEIGHTING joined to unit-level descriptions for outcome and assessment analysis.

Related Objects

• IGS_PS_UNIT_VER_ALL — joined on UNIT_CD, VERSION_NUMBER; the parent unit version master.
• IGS_PS_VER_ALL — joined on COURSE_CD, COURSE_VERSION_NUMBER; the course version master.
• IGS_PS_UNIT_LEVEL_ALL — joined on UNIT_LEVEL; provides the unit-level description.
• IGS_PS_TYPE_ALL — joined on COURSE_TYPE; provides course type classification.

These four are the directly documented foreign-key references and are the principal objects used alongside the history table.