Results for “igs_ps_unit_ref_hist_all”

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

Overview

IGS_PS_UNIT_REF_HIST_ALL is a table in the IGS (Student System) product schema of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. It describes the history of changes to a unit version's reference codes. In the Oracle Student System data model, a "unit" represents a catalog offering (analogous to a course), and each unit may have multiple versions across academic periods. Reference codes are the configurable classification values attached to those unit versions — for example, fee category, funding source, delivery mode, or ownership. The table preserves every change to those codes over time, providing an auditable, point-in-time record rather than only the current state.

From a heuristic Data Vault modeling perspective, this object most closely resembles a link entity. Its key structure joins a unit version (UNIT_CD, VERSION_NUMBER) to a reference code type and value (REFERENCE_CD_TYPE, REFERENCE_CD), with HIST_START_DT providing temporal qualification. It therefore records the association between a unit version and its reference codes as a time-stamped relationship, rather than serving as a standalone hub or a pure descriptive satellite.

Key Information Stored

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

• UNIT_CD — the unit (catalog item) code, part of the composite business key.
• VERSION_NUMBER — identifies the specific version of the unit, also part of the business key.
• REFERENCE_CD_TYPE — the category or type of reference code being tracked (for example, a lookup type).
• REFERENCE_CD — the actual reference value assigned to the unit version for that type.
• HIST_START_DT — the effective start date of the assignment, which is also part of the business key.
• HIST_END_DT — the effective end date, marking when the assignment ceased to be current. A null end date typically indicates the active record.
• HIST_WHO — the user or process that made the change.
• DESCRIPTION — free-text description associated with the reference code assignment.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle WHO columns for audit and concurrency.
• ORG_ID — the operating unit / organization context, supporting multi-org data access.

The documented surrogate primary key is IGS_PS_UNIT_REF_HIST_PK, defined over (UNIT_CD, VERSION_NUMBER, REFERENCE_CD_TYPE, REFERENCE_CD, HIST_START_DT). The unique index IGS_PS_UNIT_REF_HIST_ALL_U1 covers the same column set, confirming this combination as the business-key candidate.

Common Use Cases and Queries

Typical reporting needs center on the current reference codes for a unit version and the historical trail of changes. To retrieve active reference codes for a unit version:

SELECT reference_cd_type, reference_cd, hist_start_dt
FROM   igs_ps_unit_ref_hist_all
WHERE  unit_cd = :unit_cd
AND    version_number = :version
AND    hist_end_dt IS NULL;

To reconstruct the state of a unit version as of a given date:

SELECT reference_cd_type, reference_cd
FROM   igs_ps_unit_ref_hist_all
WHERE  unit_cd = :unit_cd
AND    version_number = :version
AND    hist_start_dt <= :as_of_dt
AND   (hist_end_dt IS NULL OR hist_end_dt > :as_of_dt);

Audit and change-tracking reports group by HIST_START_DT and HIST_WHO to identify who reclassified a unit and when. Multi-org queries should filter on ORG_ID when operating across organizations. These queries commonly feed catalog maintenance reports, fee and funding audits, and data migration validation routines.

Related Objects

The table participates in two documented foreign-key relationships:

• IGS_PS_UNIT_VER_ALL — the parent unit version table, joined on UNIT_CD and VERSION_NUMBER. This is the primary parent providing unit version context.
• IGS_GE_REF_CD_TYPE_ALL — the reference code type definition table, joined on REFERENCE_CD_TYPE, supplying the valid type categories and their descriptive attributes.

Reports and APIs typically traverse from IGS_PS_UNIT_VER_ALL through this history table to IGS_GE_REF_CD_TYPE_ALL, joining IGS_PS_UNIT_REF_HIST_ALL on UNIT_CD, VERSION_NUMBER, and REFERENCE_CD_TYPE respectively to present current and prior reference code assignments for catalog maintenance and academic reporting.