Search Results igs_he_st_spa_all




Overview
Explore in AI Chat

Overview

IGS_HE_ST_SPA_ALL is a Student System (IGS) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores Higher Education Statistics Agency (HESA) information associated with a student program attempt. In the Oracle EBS data model, the IGS product supports the administrative lifecycle of students, program applications, registrations, and statutory returns. This table acts as a HESA-specific extension of the student program attempt record, holding the statistical and funding-related attributes that institutions in the United Kingdom are required to report to HESA.

The object resides in the IGS schema and is documented as VALID in the ETRM repository, with a physical schema of 65 columns in Release 12.1.1. Its primary key, IGS_HE_ST_SPA_ALL_PK, is defined on the surrogate column HESA_ST_SPA_ID. From a heuristic Data Vault classification standpoint, the metadata suggests a satellite-leaning profile: the table carries descriptive, attribute-rich measurement columns tied to a single parent (the student program attempt) and does not primarily resolve many-to-many relationships. This should be treated as a modeling suggestion rather than a definitive classification.

Key Information Stored

The most significant columns include:

Common Use Cases and Queries

This table is typically queried during HESA statutory return preparation, student funding analysis, and institutional reporting on student program attempts. A common pattern joins the table to its parent attempt record to retrieve course and person detail:

SELECT h.HESA_ST_SPA_ID, h.PERSON_ID, h.COURSE_CD, h.COMMENCEMENT_DT, h.CALCULATED_FTE, h.RETURN_TYPE
FROM IGS.IGS_HE_ST_SPA_ALL h
WHERE h.EXCLUDE_FLAG = 'N' AND h.RETURN_TYPE = :p_return_type;

Because PERSON_ID and COURSE_CD are constrained by the unique index IGS_HE_ST_SPA_ALL_U2, lookups scoped to a student and course can rely on that composite key. Reporting teams frequently filter by ORG_ID and HESA_RETURN_ID to isolate records for a specific submission cycle, and aggregate on CALCULATED_FTE to compute funded FTE. NHS-funded program analysis uses NHS_FUNDING_SOURCE and NHS_EMPLOYER in conjunction with STUDENT_INST_NUMBER.

Related Objects

  • IGS_EN_STDNT_PS_ATT_ALL – the parent student program attempt table; joined via IGS_HE_ST_SPA_ALL.PERSON_ID = IGS_EN_STDNT_PS_ATT_ALL.PERSON_ID and COURSE_CD.
  • IGS_HE_ST_SPA_ALL_PK – the primary key index on HESA_ST_SPA_ID.
  • IGS_HE_ST_SPA_ALL_U1 – unique index on HESA_ST_SPA_ID.
  • IGS_HE_ST_SPA_ALL_U2 – unique business-key index on PERSON_ID, COURSE_CD.
  • HESA return and submission objects referenced by HESA_RETURN_ID, HESA_SUBMISSION_NAME, and HESA_RETURN_NAME.
  • Referential lookup tables for DOMICILE_CD, OCCUPATION_CODE, and SOCIAL_CLASS_IND.