Search Results igs_he_st_spa_all_pk

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:

• HESA_ST_SPA_ID – the surrogate primary key uniquely identifying each HESA student program attempt record.
• PERSON_ID – foreign key linking the record to the student in IGS_EN_STDNT_PS_ATT_ALL; part of unique index IGS_HE_ST_SPA_ALL_U2.
• COURSE_CD – the course code for the program attempt; also part of unique index IGS_HE_ST_SPA_ALL_U2.
• ORG_ID – the operating unit/organization context, enabling multi-org data segregation.
• VERSION_NUMBER – supports versioning of HESA return records across submission cycles.
• DOMICILE_CD and INST_LAST_ATTENDED – domicile and previous institution attributes used in HESA reporting.
• COMMENCEMENT_DT – the date the program attempt commenced.
• HIGHEST_QUAL_ON_ENTRY and A_LEVEL_POINT_SCORE – prior qualification and tariff data.
• STUDENT_INST_NUMBER – the institution-assigned student identifier.
• NHS_FUNDING_SOURCE, NHS_EMPLOYER, and NHS_REG_NUM – National Health Service funding and registration attributes.
• HESA_RETURN_NAME, HESA_RETURN_ID, and HESA_SUBMISSION_NAME – identify the specific HESA return and submission.
• RETURN_TYPE and CALCULATED_FTE – return classification and derived full-time equivalent value.
• EXCLUDE_FLAG – indicates whether the record is excluded from return processing.
• Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN).

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.