Search Results occupation_cd




Overview
Explore in AI Chat

Overview

IGS_HE_AD_DTL2_V is a reporting view within the Oracle E-Business Suite (EBS) IGS – Student System product, documented in ETRM as "Hesa Admission Details." HESA refers to the Higher Education Statistics Agency, the body that mandates standardized returns from UK higher education institutions. This view exposes admission-related statistical attributes captured against applicant and student records so that institutions can satisfy statutory HESA reporting obligations without querying the transactional admission schema directly.

The view does not introduce new data; it denormalizes coded values into human-readable descriptions by joining the admission detail table to a shared code-values lookup. This design pattern is characteristic of EBS reporting views, which exist to simplify extraction and to insulate downstream reports and integrations from surrogate identifier lookups. The metadata explicitly flags the IGS – Student System module as obsolete in ETRM 12.2.2, so the view should be regarded as legacy. It is documented as "Not implemented in this database," meaning it exists in the object definition catalog but is not present in the reference environment used to produce the metadata.

Underlying Base Objects

The view is defined over two documented objects. The primary base table is IGS_HE_AD_DTL, which stores the admission-level HESA attributes keyed by HESA_SEQUENCE_ID and PERSON_ID. The secondary object is IGS_HE_CODE_VALUES, referenced four times with the aliases B, C, D, and E, once per coded column.

All four code-value joins are outer joins, denoted by the (+) operator on the lookup side. Each is further constrained by a CODE_TYPE filter: 'OSS_DOM' for domicile, 'OSS_OCC' for occupation, 'OSS_SOC' for social class, and 'OSS_SPEC_STUD' for special student status. Because the joins are outer, an admission row is never suppressed when a code value is missing; the corresponding description column simply returns null. No other base objects, and no database links or synonyms, are documented for this view.

Key Columns

Common Use Cases and Queries

The principal use case is extraction of HESA admission statistics, where decoded descriptions are consumed directly by reports rather than resolving codes at report runtime. A second use case is data-quality auditing, identifying admission records with null or unmapped codes.

Retrieve decoded admission detail for a person:

SELECT person_id, admission_appl_number, nominated_course_cd,
       domicile_desc, occupation_desc, social_class_desc,
       special_student_desc
FROM   igs_he_ad_dtl2_v
WHERE  person_id = :p_person_id;

Audit rows where a HESA code has no matching lookup value:

SELECT hesa_sequence_id, person_id, domicile_cd, occupation_cd
FROM   igs_he_ad_dtl2_v
WHERE  domicile_desc IS NULL
   OR  occupation_desc IS NULL;

Count admissions by domicile for an extraction year:

SELECT domicile_desc, COUNT(*) admission_count
FROM   igs_he_ad_dtl2_v
GROUP  BY domicile_desc
ORDER  BY admission_count DESC;

Because the module is obsolete, any new development should confirm availability in the target instance before relying on this view.