Results for “reference_cd”

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

Overview

IGS_PS_UNIT_REF_HIST_V is a view owned by the APPS schema in the Oracle E-Business Suite Student System (IGS) product. Its purpose is to merge unit reference code history with current unit reference code details so that all attribute values can be accessed across time, up to and including the current day. Every row returned represents the full set of column values applicable during a defined time period, allowing historical and current state to be queried through a single object rather than joining two separate sources.

Within the ETRM 12.2.2 metadata the object is documented as VALID with the object name IGS_PS_UNIT_REF_HIST_V and the type VIEW. It plays a reporting and integration role: it presents a unified, time-sliced picture of unit reference codes (such as awarding-body codes, fee codes, or other reference-code categories attached to a unit version) so that downstream reports, extracts, and interfaces do not need to reconcile the history table and the current table independently.

Underlying Base Objects

The view definition references three sources. The primary driver is IGS_PS_UNIT_REF_HIST (aliased URCH1/URCH2), which stores the historical rows for unit reference codes. Current reference code data is held in IGS_PS_UNIT_REF_CD (aliased URC1/URC2), and the unit version context is supplied by IGS_PS_UNIT_VER (aliased UV). The metadata excerpt lists no referenced base objects, but the view text confirms these tables.

The view is constructed as a UNION ALL of two blocks. The first block selects from IGS_PS_UNIT_REF_HIST joined to IGS_PS_UNIT_VER on UNIT_CD and VERSION_NUMBER, with an outer join to IGS_PS_UNIT_REF_CD. The second block selects from IGS_PS_UNIT_REF_CD outer-joined to IGS_PS_UNIT_REF_HIST, producing the "current" row set. The union therefore yields closed historical periods plus an open-ended current row per reference code.

Key Columns

• UNIT_CD — the unit code identifying the unit of study.
• VERSION_NUMBER — the version of the unit, distinguishing iterations of the same unit code.
• REFERENCE_CD_TYPE — the category of reference code (for example, awarding body or fee type).
• REFERENCE_CD — the specific reference code value within that type.
• HIST_START_DT / HIST_END_DT — the start and end dates of the historical period. The current-day row carries a NULL HIST_END_DT in the second union block.
• HIST_WHO — the user associated with the history record.
• DESCRIPTION — resolved as URCH1.DESCRIPTION, falling back to a value from IGS_AU_GEN_004.AUDP_GET_URCH_COL, then to URC1.DESCRIPTION. This fallback chain preserves the description that applied in the period even after the current record has changed.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

Common Use Cases and Queries

Typical uses include auditing how a reference code changed over time for a given unit version, retrieving the reference code that was effective on a particular date, and feeding legislative or awarding-body extracts that require as-at reporting.

• Current reference codes for a unit version:

SELECT unit_cd, version_number, reference_cd_type, reference_cd, description FROM apps.igs_ps_unit_ref_hist_v WHERE unit_cd = :unit AND version_number = :ver AND hist_end_dt IS NULL;

• Historical rows effective on a given date:

SELECT unit_cd, version_number, reference_cd_type, reference_cd, description, hist_start_dt, hist_end_dt FROM apps.igs_ps_unit_ref_hist_v WHERE unit_cd = :unit AND :asat BETWEEN hist_start_dt AND NVL(hist_end_dt, :asat);

• Full change history ordered by period:

SELECT reference_cd_type, reference_cd, description, hist_start_dt, hist_end_dt FROM apps.igs_ps_unit_ref_hist_v WHERE unit_cd = :unit AND version_number = :ver ORDER BY reference_cd_type, hist_start_dt;

Because the view mints a NULL end date for the live row, queries should use NVL or an IS NULL predicate when filtering for the current effective value.