Results for “igs_ps_unit_ref_hist”

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

Overview

IGS_PS_UNIT_REF_HIST is a reporting view owned by the APPS schema within the Oracle E-Business Suite Student System (IGS) product family. It exposes historical reference data associated with program units of study—the coded reference values (for example, classification, ownership, or categorization codes) that have been attached to a unit version over time. The view is a secured (organization-filtered) presentation layer rather than a physical table, and its status is documented as VALID in the ETRM repository for release 12.2.2.

Its principal role is reporting and integration. Because historical reference rows carry effective start and end dates (HIST_START_DT and HIST_END_DT), the view supports point-in-time analysis of how a unit's reference attributes changed across versions. Downstream reports, extracts, and interfaces can consume the view instead of the underlying _ALL table, inheriting the multi-org security predicate automatically.

Underlying Base Objects

The view is defined over a single documented base object, IGS_PS_UNIT_REF_HIST_ALL. The definition selects the ROWID of the base row as ROW_ID and projects every significant business and audit column, including ORG_ID. The WHERE clause implements the standard multi-org (MOAC) security filter, comparing the caller's organization context—derived from USERENV('CLIENT_INFO') and converted with TO_NUMBER/DECODE—against the row's ORG_ID using NVL logic that defaults to -99 when no client information is set. This is the classic "secured view over an _ALL table" pattern used throughout IGS, and it means the same view returns only rows visible to the current organization context of the session. The metadata excerpt records no further documented base objects, indicating a direct, single-table view with no joins.

Key Columns

• ROW_ID — the ROWID of the underlying base row; used for row-level addressing in update or drill-down logic.
• UNIT_CD — the unit of study code identifying the program unit to which the reference belongs.
• VERSION_NUMBER — the version of the unit, allowing reference history to be tied to a specific versioned offering.
• REFERENCE_CD_TYPE — the reference code type category, distinguishing the kind of reference being recorded.
• REFERENCE_CD — the specific reference code value assigned.
• HIST_START_DT / HIST_END_DT — the effective date range of the historical record, enabling point-in-time reconstruction.
• HIST_WHO — identifies the user or process responsible for the historical entry.
• DESCRIPTION — descriptive text for the reference record.
• ORG_ID — the operating unit to which the row belongs and the basis for the MOAC security filter.
• Audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — standard "WHO" columns for traceability.

Common Use Cases and Queries

Typical scenarios include auditing changes to unit reference codes, producing effective-dated extracts for data warehousing, and validating that reference attributes were correct for a given academic period.

• List all reference history for a specific unit and version:

SELECT unit_cd, version_number, reference_cd_type, reference_cd,
       hist_start_dt, hist_end_dt, hist_who
FROM   apps.igs_ps_unit_ref_hist
WHERE  unit_cd = :unit_cd
AND    version_number = :version
ORDER BY hist_start_dt;

• Retrieve reference values effective on a given date:

SELECT unit_cd, reference_cd_type, reference_cd
FROM   apps.igs_ps_unit_ref_hist
WHERE  :as_of_date BETWEEN hist_start_dt AND NVL(hist_end_dt, :as_of_date);

• Audit recent changes by user and date:

SELECT unit_cd, reference_cd, last_updated_by, last_update_date
FROM   apps.igs_ps_unit_ref_hist
WHERE  last_update_date >= :since_date;

Queries execute against the APPS owner and return only rows matching the session organization context; callers requiring cross-org access must set the client information appropriately or query the _ALL base table, which is not recommended for secured reporting.