Results for “hesa_ad_dtl_id”

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

Overview

IGS_HE_AD_DTL_ALL is a transactional table within the Oracle EBS Student System (IGS) product family, owned by the IGS schema. Its documented purpose is to store Student Admission Application HESA details — that is, the Higher Education Statistics Agency (HESA) statistical and regulatory attributes associated with a student's admission application. In Oracle EBS 12.1.1 and 12.2.2, this table sits within the admissions sub-module and supports statutory returns and reporting obligations for institutions operating in the United Kingdom higher education sector.

From the mined foreign-key structure, the table can be characterised as satellite-leaning in Data Vault modeling terms. It carries descriptive HESA context that hangs off a parent application record rather than acting as an independent hub or a pure transactional link. This classification is a heuristic modelling suggestion based on the FK topology, not a documented architectural designation.

Key Information Stored

The table is defined with fifteen documented columns in the ETRM 12.1.1 physical schema. The most significant are described below.

• HESA_AD_DTL_ID — the surrogate primary key, enforced by IGS_HE_AD_DTL_ALL_PK and reinforced by the unique index IGS_HE_AD_DTL_ALL_U1. It uniquely identifies each HESA detail row.
• PERSON_ID — foreign key to the parent admission application instance table (IGS_AD_PS_APPL_INST_ALL), linking the HESA detail to a specific person and application.
• ADMISSION_APPL_NUMBER — the admission application number, part of the business-key candidate set.
• NOMINATED_COURSE_CD — the nominated course code for the application, also part of the business-key candidate set.
• SEQUENCE_NUMBER — a sequencing element that, together with PERSON_ID, ADMISSION_APPL_NUMBER and NOMINATED_COURSE_CD, forms the unique index IGS_HE_AD_DTL_ALL_U2.
• OCCUPATION_CD — the HESA occupation classification for the applicant.
• DOMICILE_CD — the applicant's domicile classification.
• SOCIAL_CLASS_CD — the HESA social class code.
• SPECIAL_STUDENT_CD — the special student category code.
• ORG_ID — the operating unit identifier supporting multi-org security.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN provide standard WHO audit tracking.

The distinction between the surrogate key (HESA_AD_DTL_ID) and the business-key candidate (PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD, SEQUENCE_NUMBER) is important when deduplicating or reconciling load processes.

Common Use Cases and Queries

Typical requirements centre on HESA statutory reporting, admissions analytics, and applicant equity reporting by domicile or social class. A common pattern joins the HESA detail to its parent application instance:

SELECT h.hesa_ad_dtl_id, h.person_id, h.admission_appl_number, h.nominated_course_cd, h.domicile_cd, h.social_class_cd FROM igs.igs_he_ad_dtl_all h WHERE h.org_id = :p_org_id AND h.admission_appl_number = :p_appl_number;

Reporting queries frequently aggregate counts by DOMICILE_CD, SOCIAL_CLASS_CD, OCCUPATION_CD or SPECIAL_STUDENT_CD across an admissions cycle. Because several of the descriptive columns carry HESA code values, joins to IGS lookup or reference tables are commonly required to obtain meaningful labels. Change-tracking queries can leverage LAST_UPDATE_DATE for incremental extraction into a reporting warehouse.

Related Objects

The principal relationship is the foreign key from IGS_HE_AD_DTL_ALL to IGS_AD_PS_APPL_INST_ALL, sharing PERSON_ID, ADMISSION_APPL_NUMBER, NOMINATED_COURSE_CD and SEQUENCE_NUMBER. Key related objects include:

• IGS_AD_PS_APPL_INST_ALL — the parent admission application instance table; the primary join target.
• IGS_HE_AD_DTL_ALL_PK — the primary key constraint.
• IGS_HE_AD_DTL_ALL_U1 — unique index on HESA_AD_DTL_ID.
• IGS_HE_AD_DTL_ALL_U2 — composite unique index on the four business-key columns.
• IGS student/admissions views and APIs — public synonyms and PL/SQL packages built over IGS_AD_PS_APPL_INST_ALL typically propagate to this HESA detail table for statutory uploads.

Consultants should treat the FK to IGS_AD_PS_APPL_INST_ALL as the authoritative parent-child link and validate ORG_ID against the caller's operating unit context in multi-org environments.