Search Results igs_he_ad_dtl




Overview
Explore in AI Chat

Overview

IGS_HE_AD_DTL is a reporting and integration view in the Oracle E-Business Suite Student System (IGS) product, owned by the APPS schema. It exposes records described in the electronic technical reference manual (ETRM) as "Hesa Admission Details," documenting data elements that support the Higher Education Statistics Agency (HESA) statutory returns for student admissions. Institutions subject to HESA reporting use this information to populate regulatory submissions covering applicant, admission, and demographic attributes captured in the student system.

The view's role is to provide a controlled, read-only projection over the underlying admission-detail data while enforcing Oracle's multi-organization security model. Rather than exposing the full base table directly, it applies a row-level filter based on the current organization context derived from the user's client information, so that queries return only records relevant to the active organization. This makes it suitable for operational reporting, extracts, and integration interfaces that must respect organizational partitioning.

Underlying Base Objects

Per the documented view text, IGS_HE_AD_DTL is defined over a single base object: IGS_HE_AD_DTL_ALL. Although the ETRM metadata records "Referenced base objects: none documented" at the 12.2.2 level, the view definition explicitly selects from the _ALL table, which is the organization-partitioned table in the IGS data model. The view aliases that table as A and projects its columns under business-friendly or technical names.

The multitenancy filter is implemented in the WHERE clause. It compares the value of ORG_ID on the base row (supplied through an NVL default of -99) against the organization identifier parsed from USERENV('CLIENT_INFO'). The parsing logic inspects the first character of CLIENT_INFO; if it is a space, no organization is applied, and the comparison resolves to the default value. This is the standard EBS multi-org security pattern used across IGS and other product views.

Key Columns

The view exposes the following columns, each mapped from the underlying _ALL table:

Notably, ORG_ID itself is not projected; it is used solely in the filter predicate.

Common Use Cases and Queries

The view is typically used for HESA extract preparation, admission data reconciliation, and integration where organization-scoped admission detail is required. Because the view filters by organization, the querying session must have CLIENT_INFO set correctly (as it is in standard EBS forms sessions); otherwise the predicate falls back to -99.

A representative query retrieving admission details for a person is:

  • SELECT hesA_sequence_id, person_id, admission_appl_number, nominated_course_cd, occupation_cd, domicile_cd, social_class_cd, special_student_cd
  • FROM apps.igs_he_ad_dtl
  • WHERE person_id = :p_person_id
  • ORDER BY sequence_number;

An aggregate extract for HESA submission might group by course and domicile:

  • SELECT nominated_course_cd, domicile_cd, social_class_cd, COUNT(*)
  • FROM apps.igs_he_ad_dtl
  • GROUP BY nominated_course_cd, domicile_cd, social_class_cd;

For change tracking, integrate on LAST_UPDATE_DATE:

  • SELECT * FROM apps.igs_he_ad_dtl WHERE last_update_date >= :since_date;

Because the view enforces multi-org security, reporting tools and interfaces should invoke it within a session whose organization context matches the intended data set, and joins to PERSON_ID and ADMISSION_APPL_NUMBER should be used to enrich the HESA-oriented codes with descriptive lookups.