Search Results igs_he_st_unt_vs_all_u2

Overview

The table IGS.IGS_HE_ST_UNT_VS_ALL is a transactional storage object within the Oracle E-Business Suite IGS schema (the Student Systems / Higher Education product family). Its documented purpose is to store Higher Education Statistics Agency (HESA) information on Units — specifically Unit Version records that feed the statutory HESA return for United Kingdom higher education institutions. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and holds installation-specific data rather than seed or reference data.

From a Data Vault modelling perspective, the mined relationship structure classifies this object as satellite-leaning. It carries a surrogate identifier plus descriptive, version-specific attributes that describe a Unit Version at a point in time, with an explicit foreign key to the Unit Version master. It is not itself a hub or a link; it is best modelled as a satellite attached to the Unit Version hub, and it is registered in FND Design Data as IGS.IGS_HE_ST_UNT_VS_ALL.

Key Information Stored

The physical schema documents fifteen columns. The most significant are summarised below.

• HESA_ST_UNT_VS_ID (NUMBER) — the surrogate primary key, populated from the sequence IGS_HE_ST_UNT_VS_ALL_S. It is the column behind unique index IGS_HE_ST_UNT_VS_ALL_U1 and the table's declared primary key IGS_HE_ST_UNT_VS_ALL_PK.
• UNIT_CD (VARCHAR2 10) and VERSION_NUMBER (NUMBER) — the business-key candidate pair, together forming unique index IGS_HE_ST_UNT_VS_ALL_U2. UNIT_CD joins to the Unit Version master IGS_PS_UNIT_VER_ALL; VERSION_NUMBER disambiguates successive versions of the same Unit.
• ORG_ID (NUMBER 15) — the multi-org organisation identifier, applying row-level operating unit security to the record.
• PROP_OF_TEACHING_IN_WELSH (NUMBER) — the proportion of teaching delivered through the medium of Welsh, a HESA statutory return field.
• CREDIT_TRANSFER_SCHEME (VARCHAR2 30) — the credit accumulation and transfer scheme applicable to the Unit Version.
• MODULE_LENGTH (NUMBER) and PROPORTION_OF_FTE (NUMBER) — quantitative measures of module duration and the Unit Version's share of full-time equivalent study.
• LOCATION_CD (VARCHAR2 10) — the code of the location at which the Unit Version is delivered.
• EXCLUDE_FLAG (VARCHAR2) — a flag identifying records to be excluded from the generated HESA extract, allowing institutions to suppress specific rows without deleting them.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns present on all transactional EBS tables.

Common Use Cases and Queries

Typical uses centre on HESA statutory reporting, data quality validation, and reconciliation between the institutional Unit Version record and the submitted return. A basic extract follows the documented query text:

• Retrieve all non-excluded rows for a reporting period: SELECT HESA_ST_UNT_VS_ID, UNIT_CD, VERSION_NUMBER, CREDIT_TRANSFER_SCHEME, MODULE_LENGTH, PROPORTION_OF_FTE FROM IGS.IGS_HE_ST_UNT_VS_ALL WHERE EXCLUDE_FLAG IS NULL;
• Reconcile against the Unit Version master: join on UNIT_CD and VERSION_NUMBER to IGS_PS_UNIT_VER_ALL, or on the documented foreign key UNIT_CD alone, to detect Unit Versions that lack a HESA attributes row.
• Validate location and Welsh-medium teaching data: group by LOCATION_CD and aggregate PROP_OF_TEACHING_IN_WELSH to confirm proportional values total correctly.
• Detect duplicate or orphaned business keys: query the U2 columns to confirm uniqueness of UNIT_CD plus VERSION_NUMBER within each ORG_ID.
• Audit who last altered HESA attributes, using LAST_UPDATED_BY and LAST_UPDATE_DATE.

Related Objects

• IGS.IGS_PS_UNIT_VER_ALL — the Unit Version master, referenced by the UNIT_CD foreign key; the primary join target for this satellite.
• IGS.IGS_HE_ST_UNT_VS_ALL_S — the sequence supplying HESA_ST_UNT_VS_ID.
• IGS_HE_ST_UNT_VS_ALL_PK — the primary key constraint on HESA_ST_UNT_VS_ID.
• IGS_HE_ST_UNT_VS_ALL_U1 and IGS_HE_ST_UNT_VS_ALL_U2 — the unique indexes on HESA_ST_UNT_VS_ID and on (UNIT_CD, VERSION_NUMBER) respectively, both in APPS_TS_TX_IDX.
• APPS.IGS_HE_ST_UNT_VS_ALL — the APPS-layer synonym through which forms, concurrent programs, and HESA extract routines access the table.
• IGS.IGS_PS_UNIT_ALL — the parent Unit definition, indirectly related through the Unit Version master.

The table references no further database objects beyond IGS_PS_UNIT_VER_ALL; all other dependencies are inbound, driven by the HESA extract and validation processes that consume these rows.