Results for “itt_phase_scope”

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

Overview

IGSFV_ST_PROG_ATT_HESA_DETAILS is a full (non-incremental) view within the Oracle E-Business Suite Student System (IGS) product family, a module that is documented as obsolete in Oracle EBS 12.1.1 and 12.2.2. The view consolidates Student Program Attempt (SPA) records with the Higher Education Statistics Agency (HESA) statutory reporting attributes associated with each student and program attempt. Its declared purpose — "Full View for Student Program Attempt HESA Details" — indicates that it is a reporting and integration object rather than a transactional entity, designed to present one row per HESA Student Program Attempt for downstream extraction, validation, and regulatory submission (for example, HESA student returns in the UK higher-education sector).

As documented in the ETRM metadata, the view is not implemented in the referenced database instance. This is significant: querying IGSFV_ST_PROG_ATT_HESA_DETAILS in an environment where it has not been deployed will fail with an ORA-00942 (table or view does not exist). Consultants should confirm deployment status in the target instance before relying on the object in reports or extracts.

Underlying Base Objects

The ETRM metadata records no referenced base objects, though the documented view text demonstrates that the view is defined primarily over the Student Program Attempt table, aliased as SPA, with supporting joins to person and program tables. The visible SELECT list references:

• IGS_PE_ALT_PERS_ID — alternate person identifiers, queried via a correlated subquery to retrieve the HUSID value where the identifier type is 'HUSID'.
• IGS_HE_CODE_VALUES — HESA code value descriptions, used to resolve FE student markers, special student codes, and highest qualification-on-entry grading schemas.
• IGS_AS_GRD_SCH_GRADE — grading schema grades, joined to derive the highest-qualification-on-entry description.
• IGS_LOOKUP_VALUES — referenced through the descriptive flexfield-style notation for the HESA return type meaning.
• Person and program tables — supplying party number, surname, given names, full name, program code, title, short title, and abbreviation.

Key Columns

• HESA_STATS_SPA_ID / PERSON_ID / PERSON_NUMBER — the HESA program attempt identifier, internal person identifier, and external party number.
• HUSID — the HESA unique student identifier retrieved from alternate person identifiers, filtered by the date-range predicates START_DT and NVL(END_DT, TRUNC(SYSDATE)). This is the column most directly associated with the search term "end_dt": the identifier is considered valid when the current date falls between its start date and its end date (or end date is null).
• PROGRAM_CODE, PROGRAM_TITLE, PROGRAM_VERSION_NUMBER — program identifiers and versioning.
• HESA_RETURN_TYPE and HESA_RETURN_TYPE_DESC — the return type classification and its decoded description.
• FE_STUDENT_MARKER, SPECIAL_STUDENT_CODE, STUDENT_QUAL_AIM_AWD — HESA-specific student attribute flags with accompanying descriptions.
• COMMENCEMENT_DATE, TOTAL_UCAS_TARIFF, UCAS_TARIFF_CALC_LAST_RUN_DATE — commencement and UCAS tariff calculation data.

Common Use Cases and Queries

Typical usage is extraction and reconciliation of HESA student returns, including validation of expiring identifiers.

SELECT person_number, person_surname, husid
FROM   igsfv_st_prog_att_hesa_details
WHERE  husid IS NOT NULL;

Because identifier validity depends on END_DT, auditors commonly query which alternate identifiers have lapsed:

SELECT pe_person_id, api_person_id, start_dt, end_dt
FROM   igs_pe_alt_pers_id
WHERE  person_id_type = 'HUSID'
AND    end_dt IS NOT NULL
AND    TRUNC(SYSDATE) > end_dt;

Where the view is not deployed, the same logic must be reconstructed from IGS_PE_ALT_PERS_ID and the SPA record directly, applying the NVL(END_DT, TRUNC(SYSDATE)) predicate to reproduce the view's HUSID derivation.