Search Results igs_uc_app_stats_u2

Overview

The IGS_UC_APP_STATS table is a core data repository within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2, specifically for the IGS (iGraduate) product family. Its primary role is to store Higher Education Statistics Agency (HESA)-related applicant data, facilitating mandatory reporting for UK educational institutions. The table acts as a synchronization point between the EBS application and external UCAS (Universities and Colleges Admissions Service) data feeds, specifically the 'ivStarH' and 'ivStarX' views. It serves as the system of record for demographic and socio-economic information collected during the university admissions process for statutory reporting.

Key Information Stored

The table's structure is designed to capture applicant statistics and synchronization metadata. The primary unique identifier is the APP_STAT_ID column. It links to a specific applicant via the APP_ID foreign key and stores the official APP_NO (UCAS application number). The core data comprises two parallel sets of columns for HESA reporting: one set synchronized from the UCAS ivStarH view (e.g., STARH_ETHNIC, STARH_SOCIAL_CLASS, STARH_POCC) and another from the ivStarX view (e.g., STARX_ETHNIC, STARX_POCC). These columns hold critical codes for ethnicity, parental occupation, last educational institution, and local education authority. Timestamp columns (IVSTARH_TIMESTAMP, IVSTARX_TIMESTAMP) track synchronization, though they are noted as obsolete. The SENT_TO_HESA flag indicates whether the record has been exported for official reporting.

Common Use Cases and Queries

The primary use case is generating HESA compliance reports on applicant demographics. System processes populate and update this table via interfaces with UCAS. Common queries involve extracting data for submissions or auditing synchronization status. For example, to retrieve key statistics for a reporting cycle:

• SELECT app_no, starh_ethnic, starh_social_class, starh_pocc_text FROM igs_uc_app_stats WHERE sent_to_hesa = 'N';

Another typical pattern is joining to the main applicant table to create a comprehensive applicant profile for analysis:

• SELECT a.app_no, s.starh_ethnic, s.starx_ethnic FROM igs_uc_app_stats s, igs_uc_applicants a WHERE s.app_id = a.app_id;

Data integrity checks often focus on verifying that all applicants have corresponding statistical records.

Related Objects

IGS_UC_APP_STATS is centrally linked to the applicant master table through a documented foreign key relationship. Its primary key is enforced by the constraint IGS_UC_APP_STATS_PK on the APP_STAT_ID column. The key dependencies are:

• Primary Key: IGS_UC_APP_STATS_PK (APP_STAT_ID)
• Foreign Key (References): The column APP_ID is a foreign key referencing the IGS_UC_APPLICANTS table. This establishes a direct, mandatory link between an applicant's core record and their associated HESA statistics.
• Indexes: Unique performance is ensured by IGS_UC_APP_STATS_U1 (on APP_STAT_ID) and IGS_UC_APP_STATS_U2 (on APP_NO).

This structure ensures data consistency, where every record in IGS_UC_APP_STATS corresponds to one and only one applicant in the system.