Search Results hesa_st_prog_id




Overview
Explore in AI Chat

Overview

IGS_HE_ST_PROG_ALL is a table owned by the IGS schema within the Oracle E-Business Suite Student System (IGS) product family. Its documented purpose is to store Program Version HESA (Higher Education Statistics Agency) details, meaning it holds the statutory statistical reporting attributes that describe a program version for HESA returns. In Oracle EBS 12.1.1 and 12.2.2 the table is registered as VALID and comprises 41 columns, all of which relate to the reporting characteristics of a specific course and version combination rather than to transactional student activity.

The table is therefore best understood as a descriptive attribute store: it extends the core program version record held in IGS_PS_VER_ALL with the HESA-specific fields required to produce statutory returns such as the Student Record and related teaching-funding submissions. Notably, the metadata includes ORG_ID, which indicates the table is partitioned by operating unit under Multi-Org rules, so queries should generally be constrained by the appropriate operating unit.

Heuristic Data Vault classification mined from the foreign-key structure labels this object as satellite-leaning. From a modeling perspective, this suggests IGS_HE_ST_PROG_ALL behaves as a satellite surrounding the program version hub, keyed by its own surrogate HESA_ST_PROG_ID and linked to IGS_PS_VER_ALL through the COURSE_CD and VERSION_NUMBER business key.

Key Information Stored

The surrogate primary key is HESA_ST_PROG_ID, enforced by the unique index IGS_HE_ST_PROG_ALL_PK. A second unique index, IGS_HE_ST_PROG_ALL_U1, is also defined on HESA_ST_PROG_ID, while IGS_HE_ST_PROG_ALL_U2 defines the composite business key of COURSE_CD and VERSION_NUMBER. The composite key mirrors the parent program version and is the column pair used to join back to IGS_PS_VER_ALL.

Common Use Cases and Queries

Typical usage centers on statutory reporting, funding reconciliation, and program setup validation. A lookup by surrogate key is the standard pattern:

  • SELECT * FROM igs.igs_he_st_prog_all WHERE hesa_st_prog_id = :p_id;
  • SELECT * FROM igs.igs_he_st_prog_all WHERE course_cd = :course AND version_number = :version AND org_id = :org;

Joining to the parent program version resolves descriptive course titles alongside HESA attributes:

  • SELECT h.hesa_st_prog_id, p.course_title, h.fee_band, h.fundability FROM igs.igs_he_st_prog_all h, igs.igs_ps_ver_all p WHERE h.course_cd = p.course_cd AND h.version_number = p.version_number;

Reporting scenarios include extracting teacher training records by ITT phase, summarizing franchised provision via FRANCHISING_ACTIVITY and FRANCH_PARTNER_CD, validating that FEE_BAND and FUNDABILITY are populated for fundable programs, and reconciling IMPLIED_FUND_RATE against actual funding. MISSING records are equally diagnostic, as an absent row indicates that HESA attributes have not yet been defined for a program version.

Related Objects

The dominant relationship is the foreign key from IGS_HE_ST_PROG_ALL to IGS_PS_VER_ALL, carried on the COURSE_CD and VERSION_NUMBER columns. Because IGS_PS_VER_ALL is the program version entity, it supplies the core course and version definition that the HESA satellite enriches, and it is the primary join target for reporting queries. Setups that drive the HESA values referenced here, including award, fee band, funding level, and subject lookups, are typically maintained through the Student System program version setup forms that write to the parent version record. The unique indexes IGS_HE_ST_PROG_ALL_PK and IGS_HE_ST_PROG_ALL_U1 on HESA_ST_PROG_ID, together with IGS_HE_ST_PROG_ALL_U2 on COURSE_CD and VERSION_NUMBER, are the principal constraints governing access paths. In practice, the most significant dependency chain is IGS_HE_ST_PROG_ALL → IGS_PS_VER_ALL, with IGS_HE_ST_PROG_ALL acting as the reporting-detail satellite for the program version hub.