Search Results override_title




Overview
Explore in AI Chat

Overview

IGS_AS_SUSAH_V is a view in the Oracle EBS Student System (IGS) product family, owned by the APPS schema. It exposes the history records of Student Unit Set Attempt (SUSAH) data, providing a denormalized, history-aware presentation of unit set attempt information for each student. The view's underlying select combines an alias susah1 (the history table) with susa1 (the current/base record), resolving values from the history row first and falling back to the current row where the history value is null. Because it surfaces both the current and historical state of an attempt in a single row, the view is well suited for audit, point-in-time reporting, and integration scenarios where the effective attribute values must be reconstructed as of a given history end date (hist_end_dt).

Underlying Base Objects

The documented metadata lists no referenced base objects, but the view text makes the source objects evident. The primary source is referenced through the alias susah1, which corresponds to the history table IGS_AS_SUSAH (Student Unit Set Attempt History). The view also references a second source through the alias susa1, corresponding to IGS_AS_SUSA (Student Unit Set Attempt), which supplies the fallback values. In addition, the view calls three helper objects: IGS_AU_GEN_003.audp_get_susah_col, a package function used to retrieve an attribute value from the audit/history context; IGS_GE_DATE.IGSDATE, which converts a character representation into a date; and the sequence-based identity of the SUSAH record. This layered NVL construct means that for any attribute, the view returns the explicitly stored history value when present, otherwise the audit-derived value, and finally the current record value from susa1.

Key Columns

  • person_id – The student's identifier, forming part of the composite key of the attempt record.
  • course_cd, unit_set_cd, sequence_number – Together with person_id, these identify the specific unit set attempt and its ordinal position.
  • hist_start_dt, hist_end_dt, hist_who – History framing columns indicating the validity window and the user responsible for the history row.
  • selection_dt – The date on which the unit set attempt was selected; resolved from the history row, the audit function, or the current record.
  • us_version_number – The version number of the unit set attempt record.
  • student_confirmed_ind, primary_set_ind, voluntary_end_ind – Indicator flags describing confirmation, primacy of the set, and voluntary end status.
  • end_dt – The end date of the attempt.
  • parent_unit_set_cd, parent_sequence_number – References to the parent unit set and its sequence, supporting hierarchical set structures.

Common Use Cases and Queries

The view is typically queried for point-in-time reconstruction of unit set attempts and for audit reporting. Because selection_dt is a commonly filtered attribute, a typical query retrieves attempts selected within a date range:

SELECT person_id
     , course_cd
     , unit_set_cd
     , sequence_number
     , selection_dt
     , student_confirmed_ind
  FROM apps.igs_as_susah_v
 WHERE selection_dt BETWEEN :start_date AND :end_date
   AND course_cd = :course_cd;

A second common pattern resolves the effective state of an attempt as it stood on a particular date by constraining on hist_end_dt:

SELECT person_id
     , unit_set_cd
     , us_version_number
     , selection_dt
     , end_dt
     , voluntary_end_ind
  FROM apps.igs_as_susah_v
 WHERE person_id = :person_id
   AND hist_end_dt = :as_of_date;

Integrations that load student attempt data into downstream warehouses frequently select the full set of columns exposed by the view, relying on its NVL-based resolution to avoid additional joins against the current attempt table. When filtering on selection_dt, note that the value may originate from the history row, the audit function, or the current record, so reports should not assume a single physical storage location for the column.