Search Results igs_au_gen_001




Overview
Explore in AI Chat

Overview

The view APPS.IGS_AD_PS_APLINSTHST_HISTORY_V is a historical admissions view shipped within the Oracle E-Business Suite Student System (IGS) product family. It presents historical rows from the applicant instance history entity, exposing the temporal (date-effective) state of admission application data for nominated courses. The _HISTORY_V suffix indicates that the view is designed to return past as well as current iterations of a record, keyed by hist_start_dt and hist_end_dt rather than merely the currently active row. In reporting and integration terms, the view serves as the reverse-engineered, denormalised surface through which applicant instance history can be queried without directly navigating the underlying history tables and the associated audit API.

The view is named after the user search term context igs_au_gen_001, which is the audit package referenced directly in the view's SQL text. This confirms the object is an audit-enabled history view rather than a simple projection, and that its resolution logic depends on the generic history retrieval function in the IGS audit toolkit.

Underlying Base Objects

The documented ETRM metadata lists no explicit referenced base objects for this view, which is typical for IGS history views whose definitions are partly constructed at runtime. The view text itself reveals two principal aliases: aaav1, representing the applicant instance history source (the applicant admission application instance history structure), and acaiv1, representing the corresponding admissions course application instance entity. The NVL layering in the SELECT list indicates that aaav1 is the primary historical driver, while acaiv1 supplies fallback values where the historical row does not carry a column.

The audit relationship is expressed through repeated calls to IGS_AU_GEN_001.audp_get_acaih_col, a packaged function that resolves a named column value for a specific application instance history record identified by person_id, admission_appl_number, nominated_course_cd, sequence_number and hist_end_dt. Each call is wrapped in SUBSTR for character columns and TO_NUMBER for numeric columns, enforcing type consistency.

Key Columns

  • person_id — The HZ person identifier of the applicant.
  • admission_appl_number — The admission application number, forming part of the composite history key.
  • nominated_course_cd — The nominated course against which the application instance is held.
  • sequence_number — The instance sequence, defaulted via NVL from the history row or the fallback entity.
  • hist_start_dt, hist_end_dt — The date-effective bounds of the historical row; hist_end_dt is also passed as an input to the audit function resolution.
  • hist_who — The user or process responsible for the historical change.
  • adm_cal_type, adm_ci_sequence_number — Admission calendar and calendar instance, resolved historically or via the audit API.
  • course_cd, crv_version_number, location_cd — Course and delivery attributes at the point in history.
  • attendance_mode, attendance_type, unit_set_cd — Attendance and unit set characteristics, each subject to the same three-tier NVL resolution.

Common Use Cases and Queries

The view is typically used for admissions audit reporting, point-in-time reconciliation of applicant course offers, and feeder extracts into student records or data warehouses. A representative query retrieving the current and prior states for an applicant is shown below.

SELECT person_id, admission_appl_number, nominated_course_cd,
  sequence_number, hist_start_dt, hist_end_dt, hist_who,
  adm_cal_type, course_cd, crv_version_number, location_cd
FROM apps.igs_ad_ps_aplinsthst_history_v
WHERE person_id = :p_person_id
  AND admission_appl_number = :p_appl_number
ORDER BY nomination_course_cd, hist_end_dt DESC;

Because the audit function is invoked per column and per row, the view carries a performance cost. Filtering on person_id and admission_appl_number is recommended, and reports should avoid unrestricted full scans. Where only the current instance is required, joining to or filtering against IGS_AD_PS_APLINSTNC_ALL is preferable to resolving history through this view.