Results for “igs_ps_unit_lvl_hist”

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

Overview

IGS_PS_UNIT_LVL_HIST is a reporting view owned by the APPS schema within the IGS (Student System) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the historical, effective-dated records of program unit level assignments, allowing institutions to track how a unit of study has been classified over time with respect to unit level and weighting. The view is defined over the base table IGS_PS_UNIT_LVL_HIST_ALL and exists primarily to enforce multi-organization (Operating Unit) security at query time, so that a given session only sees history rows belonging to the operating unit currently set in the user's context.

From a functional standpoint, the view supports academic reporting, audit, and integration scenarios where consumers need to reconstruct the level (for example, undergraduate or postgraduate) and weighting (for example, WAM contribution) that applied to a unit at a given point in time, rather than only its current state. Its status is documented as VALID, confirming the object compiles and is available for use in the APPS schema.

Underlying Base Objects

The documented base object is IGS_PS_UNIT_LVL_HIST_ALL, which stores the complete, unfiltered history of unit level and weighting records across all operating units. The view text defines a simple wrapper: it selects an explicit column list from the TAB alias of IGS_PS_UNIT_LVL_HIST_ALL and applies an ORG_ID predicate. The metadata notes that no other base objects are documented, but the presence of COURSE_CD and COURSE_VERSION_NUMBER in the select list indicates that those columns are sourced from the same underlying table.

The WHERE clause implements the standard Oracle EBS multi-org pattern. It compares the row's ORG_ID to the operating unit value derived from USERENV('CLIENT_INFO'). Specifically, it reads the first ten bytes of CLIENT_INFO, discards the value when the first byte is a space, and defaults missing values to -99 via NVL and TO_NUMBER with DECODE. This ensures that rows without an explicit ORG_ID (treated as -99) remain visible where appropriate, while organization-specific rows are restricted to the active operating unit.

Key Columns

• ROW_ID — the physical ROWID of the base table row, useful for uniqueness and direct row reference.
• UNIT_CD, VERSION_NUMBER — identify the unit of study and its version.
• COURSE_TYPE, COURSE_CD, COURSE_VERSION_NUMBER — contextual links to the associated course offering and its type.
• HIST_START_DT, HIST_END_DT — the effective date range during which the level and weighting values were valid.
• HIST_WHO — records the user or process responsible for the historical change.
• UNIT_LEVEL — the classification level assigned to the unit.
• WAM_WEIGHTING — the weighting applied to the unit within the Weighted Average Mark calculation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle EBS audit columns.
• ORG_ID — the operating unit to which the history row belongs; drives the view's security filter.

Common Use Cases and Queries

The view is typically used for point-in-time reporting, reconciliation of historical level changes, and integrations that must honor operating unit security automatically. A common query retrieves the level effective on a specific date for a unit and version:

SELECT unit_cd,
       version_number,
       unit_level,
       wam_weighting,
       hist_start_dt,
       hist_end_dt
FROM   igs_ps_unit_lvl_hist
WHERE  unit_cd = :unit_cd
AND    version_number = :version_number
AND    :report_date BETWEEN hist_start_dt AND NVL(hist_end_dt, :report_date);

Another routine scenario lists all changes made to a unit's level, ordered chronologically for audit purposes:

SELECT unit_cd,
       version_number,
       unit_level,
       wam_weighting,
       hist_start_dt,
       hist_end_dt,
       hist_who
FROM   igs_ps_unit_lvl_hist
WHERE  unit_cd = :unit_cd
ORDER BY hist_start_dt;

Because the view filters by ORG_ID, callers do not need to add their own operating unit predicate, provided the CLIENT_INFO context is correctly initialized through standard EBS multi-org setup. Where cross-organization reporting is required, querying IGS_PS_UNIT_LVL_HIST_ALL directly is the appropriate alternative.