Results for “igs_he_st_spa_ut”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The IGS_HE_ST_SPA_UT view belongs to the Oracle E-Business Suite IGS – Student System product family, a module documented in ETRM as Obsolete in the 12.1.1 and 12.2.2 releases. Its description, "Student Program Attempt UCAS Tariff," identifies it as a reporting artifact that exposes data related to student program attempts and their associated UCAS (Universities and Colleges Admissions Service) tariff scores. In the UK higher education model, a UCAS tariff converts qualifying achievements—such as A-levels, Scottish Highers, and other recognised qualifications—into a numeric points score used for admissions decisions. This view therefore supports reporting and integration scenarios where institutions must present or transmit a student's accumulated tariff points alongside the qualification levels that contributed to the score.

The view is marked "Not implemented in this database" in the supplied metadata, indicating that the object is documented for reference but is not instantiated in the target environment. This is characteristic of legacy Higher Education statistics objects that were shipped as part of the IGS schema before the product line was superseded.

Underlying Base Objects

The documented base object referenced by this view is IGS_HE_ST_SPA_UT_ALL, which is the multi-organisation (partitioned by ORG_ID) table holding student program attempt UCAS tariff records. No other base objects are enumerated in the ETRM metadata. The view selects twelve business and audit columns from the _ALL table and derives an additional pseudo-column, ROW_ID, from the table's native ROWID.

A defining characteristic of the view is its organisation security predicate. Using USERENV('CLIENT_INFO'), the view extracts the first ten characters of the client information string as the active ORG_ID, substituting -99 when the client context is empty (flagged by a leading space or blank). Only rows whose ORG_ID matches this derived value, or whose ORG_ID is null, are returned. This enforces operating unit level security at the view layer, consistent with other multi-org secured IGS views.

Key Columns

• ROW_ID – The physical row identifier of the underlying _ALL record, exposed for row-level referencing.
• HESA_ST_SPAU_ID – Primary key of the student program attempt UCAS tariff record; "HESA" denotes the Higher Education Statistics Agency data model origin.
• PERSON_ID – Foreign key to the person (student) on the HZ/PER party model.
• COURSE_CD – The course code associated with the program attempt.
• VERSION_NUMBER – Version of the course or program attempt record, supporting the IGS versioning convention.
• QUALIFICATION_LEVEL – The level of the qualification contributing to the tariff (the column matched by the user's search term).
• NUMBER_OF_QUAL – The count of qualifications at that level used in the tariff calculation.
• TARIFF_SCORE – The numeric UCAS points total for the attempt.
• Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical usage involves extracting tariff data for admissions reporting, HESA statutory returns, or integration with external clearing systems. Because the view applies the ORG_ID predicate implicitly, queries return only records visible to the session's current operating unit context.

Retrieve tariff scores by qualification level for a specific student:

SELECT QUALIFICATION_LEVEL, NUMBER_OF_QUAL, TARIFF_SCORE
FROM   IGS_HE_ST_SPA_UT
WHERE  PERSON_ID = :p_person_id
ORDER  BY QUALIFICATION_LEVEL;

Aggregate total tariff points per student and course:

SELECT PERSON_ID, COURSE_CD, VERSION_NUMBER, SUM(TARIFF_SCORE) TOTAL_TARIFF
FROM   IGS_HE_ST_SPA_UT
GROUP  BY PERSON_ID, COURSE_CD, VERSION_NUMBER;

Report distinct qualification levels present in the operating unit:

SELECT DISTINCT QUALIFICATION_LEVEL
FROM   IGS_HE_ST_SPA_UT
WHERE  QUALIFICATION_LEVEL IS NOT NULL;

Because the view is documented as obsolete and not implemented, these queries should be validated against the actual schema; equivalent data may reside in successor Student System tables or be retained only for historical reference.