Results for “igs_uc_app_stats”

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

Overview

IGS_UC_APP_STATS is a legacy table within the Oracle E-Business Suite IGS (Student System) module, classified in ETRM documentation as Obsolete. It holds applicant data required for HESA (Higher Education Statistics Agency) statutory reporting purposes. The table is documented as synchronizing with the "ivStarH" and "ivStarX" integration views, which serve as the data exchange mechanism between the operational student records and the HESA return submissions.

The ETRM implementation notes state "Not implemented in this database," indicating that in the current 12.1.1 / 12.2.2 environment the object is retained for reference or upgrade lineage rather than active transactional use. The table is owned by the IGS schema and documents 31 physical columns.

The heuristic Data Vault classification mined from the foreign key structure is satellite-leaning. In a Data Vault 2.0 modeling exercise this suggests the table behaves as a descriptive satellite attached to a parent hub, in this case keyed through the applicant identifier (APP_ID) that references IGS_UC_APPLICANTS. The satellite classification is consistent with its content: timestamps, coded attribute values, and change-tracking fields that describe an applicant at a point in time rather than defining a new independent business entity.

Key Information Stored

The table's primary surrogate key is APP_STAT_ID, enforced by the IGS_UC_APP_STATS_PK constraint and additionally backed by the unique index IGS_UC_APP_STATS_U1. A second unique index, IGS_UC_APP_STATS_U2, is defined on APP_NO, which functions as the alternate business-key candidate. The foreign key APP_ID links each row to a parent applicant record in IGS_UC_APPLICANTS.

• APP_STAT_ID – surrogate primary key uniquely identifying each statistics row.
• APP_ID – foreign key to IGS_UC_APPLICANTS, anchoring the row to its applicant.
• APP_NO – applicant number, the alternate business key.
• IVSTARH_TIMESTAMP / IVSTARX_TIMESTAMP – synchronization timestamps for the ivStarH and ivStarX interfaces.
• STARH_ETHNIC / STARX_ETHNIC – ethnic origin codes captured for HESA returns.
• STARH_SOCIAL_CLASS / STARH_SOCIO_ECONOMIC / STARX_SOCIO_ECONOMIC – social class and socio-economic classification attributes.
• STARH_POCC / STARH_POCC_TEXT / STARX_POCC / STARX_POCC_TEXT – parent occupation code values and their textual descriptions.
• STARH_POCC_EDU_CHG_DT / STARX_POCC_EDU_CHG – flags and dates recording whether parental education changed.
• STARH_LAST_EDU_INST, STARH_EDU_LEAVE_DATE, STARH_LEA – prior institution, leaving date, and local education authority.
• SENT_TO_HESA – indicator recording whether the row has been transmitted in a HESA submission.
• IVSTARH_DEPENDANTS / IVSTARX_DEPENDANTS, IVSTARH_MARRIED / IVSTARX_MARRIED – dependant and marital status attributes.
• STARX_OCC_BACKGROUND, IVSTARX_RELIGION – occupational background and religious affiliation codes.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN – standard Oracle WHO audit columns.

Common Use Cases and Queries

Because the table is documented as obsolete and not implemented, its principal uses in 12.1.1 / 12.2.2 are historical enquiry, upgrade impact analysis, and HESA reconciliation where legacy extracts are retained. A typical reconstruction query joins the satellite to its parent applicant:

SELECT s.app_stat_id, s.app_no, a.* , s.starh_ethnic, s.starx_socio_economic, s.sent_to_hesa FROM igs_uc_app_stats s, igs_uc_applicants a WHERE s.app_id = a.app_id AND s.app_no = :app_no;

Reporting scenarios include identifying applicants not yet transmitted to HESA (SENT_TO_HESA is null or 'N'), auditing synchronization lag between the ivStarH and ivStarX timestamps, and validating completeness of ethnicity, socio-economic, and parental occupation coding against the HESA return specification. Extraction patterns key on APP_STAT_ID or APP_NO, and date-range filters should normally use IVSTARH_TIMESTAMP or IVSTARX_TIMESTAMP rather than the WHO audit columns when measuring interface activity.

Related Objects

• IGS_UC_APPLICANTS – parent table; joined via IGS_UC_APP_STATS.APP_ID = IGS_UC_APPLICANTS.APP_ID. This is the only documented foreign key relationship.
• ivStarH view – synchronization source referenced in the object description; supplies STARH_* attributes.
• ivStarX view – companion synchronization source supplying STARX_* attributes.
• IGS_UC_APP_STATS_PK – primary key constraint on APP_STAT_ID.
• IGS_UC_APP_STATS_UK / _U1 / _U2 – unique constraints and indexes on APP_STAT_ID and APP_NO.
• HESA return extracts and legacy IGS applicant reporting views, which consume SENT_TO_HESA and the STARH_*/STARX_* coded attributes.