Search Results igs_he_st_spa_all_u2

Overview

IGS.IGS_HE_ST_SPA_ALL is a transactional table in the Oracle E-Business Suite IGS (Higher Education / Student System) schema. Its documented purpose is to store HESA (Higher Education Statistics Agency) information on a student program attempt. In practice, each row captures the statutory return attributes associated with a single student's attempt at a program of study, typically for a given HESA reporting year and institution. The table resides in the APPS_TS_TX_DATA tablespace, is owned by IGS, and holds 65 columns in the 12.1.1 documented schema.

The object's heuristic Data Vault classification is satellite-leaning. This is a modeling suggestion rather than a physical implementation: the table is keyed to a parent student attempt (via PERSON_ID) and carries descriptive, time-variant attributes about that attempt, which is the characteristic shape of a satellite rather than a business hub or a linking table. The dominant relationship is to IGS_EN_STDNT_PS_ATT_ALL through PERSON_ID.

Key Information Stored

The surrogate primary key is HESA_ST_SPA_ID, a NUMBER populated from the sequence IGS_HE_ST_SPA_ALL_S and enforced by unique index IGS_HE_ST_SPA_ALL_U1. Two documented business-key candidates exist: IGS_HE_ST_SPA_ALL_U1 on HESA_ST_SPA_ID and IGS_HE_ST_SPA_ALL_U2 on the composite (PERSON_ID, COURSE_CD). The user's search term, igs_he_st_spa_all_u2, refers to that composite unique index.

The most significant documented columns include:

• PERSON_ID — the person/student identifier, and the FK link to the student program attempt records.
• COURSE_CD — the program code; together with PERSON_ID it forms the U2 business key.
• ORG_ID — the organization (institution) context for multi-org reporting.
• VERSION_NUMBER — versioning of the HESA attempt record.
• COMMENCEMENT_DT — the date the program attempt commenced.
• DOMICILE_CD and INST_LAST_ATTENDED — domicile and prior institution details.
• HIGHEST_QUAL_ON_ENTRY and DATE_QUAL_ON_ENTRY_CALC — entry qualification and its calculation date.
• A_LEVEL_POINT_SCORE, HIGHERS_POINTS_SCORES, TOTAL_UCAS_TARIFF — tariff/points metrics.
• STUDENT_QUAL_AIM and STUDENT_FE_QUAL_AIM — qualification aims.
• ITT_PHASE, TEACHER_TRAIN_PROG_ID, TEACHING_QUAL_GAIN_SECTOR and related SUBJ1–SUBJ3 columns — initial teacher training attributes.
• HESA_RETURN_NAME, HESA_RETURN_ID, HESA_SUBMISSION_NAME, RETURN_TYPE — HESA return context.
• EXCLUDE_FLAG — used to exclude records from a return.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — standard audit columns.

Common Use Cases and Queries

Principal reporting use cases center on HESA statutory returns and institutional analytics of student intakes. Typical access patterns retrieve all attempt records for a person, resolve the U2 business key, or filter an active return.

• Attempt lookup by business key:
  SELECT * FROM igs.igs_he_st_spa_all WHERE person_id = :p AND course_cd = :c;
• Person-level history:
  SELECT hesa_st_spa_id, course_cd, commencement_dt, student_qual_aim FROM igs.igs_he_st_spa_all WHERE person_id = :p ORDER BY commencement_dt;
• Return extract:
  SELECT * FROM igs.igs_he_st_spa_all WHERE hesa_return_name = :ret AND NVL(exclude_flag,'N') = 'N';

Because ORG_ID is present, queries should include the operating unit predicate where multi-org security applies. For bulk extraction, driving off IGS_HE_ST_SPA_ALL_U2 supports efficient joins to the parent attempt and HESA return staging tables.

Related Objects

The most significant related objects, derived from the documented relationship data, are:

• IGS.IGS_EN_STDNT_PS_ATT_ALL — parent student program attempt; joined on PERSON_ID, the documented FK target.
• IGS.IGS_HE_ST_SPA_ALL_U1 — unique index on HESA_ST_SPA_ID.
• IGS.IGS_HE_ST_SPA_ALL_U2 — composite unique index on PERSON_ID, COURSE_CD.
• IGS.IGS_HE_ST_SPA_ALL_PK — primary key constraint on HESA_ST_SPA_ID.
• IGS.IGS_HE_ST_SPA_ALL_S — source sequence for the surrogate key.

HESA return validation and extract routines in the IGS schema typically read this table in conjunction with the student attempt parent, using PERSON_ID and COURSE_CD as the join drivers.