Search Results igs_uc_app_referees




Overview
Explore in AI Chat

Overview

IGS_UC_APP_REFEREES is a transactional table in the IGS (Student System) product family of Oracle E-Business Suite, owned by the IGS schema. It stores reference information submitted through the UCAS (Universities and Colleges Admissions Service) interface for applicants applying to higher education institutions in the United Kingdom. Each row represents a single referee attached to a UCAS application, capturing the referee's identity, contact details, institutional affiliation, and the supporting statement they provided on the applicant's behalf.

From a data modeling perspective, the ETRM metadata classifies this object as standalone under the heuristic Data Vault classification, meaning no foreign key relationships to parent hubs were detected in the documented schema. In Data Vault terms, IGS_UC_APP_REFEREES would most naturally be modeled as a satellite attached to an applicant hub (keyed by APP_NO), since it holds descriptive attributes that change over time and describe an applicant-related event. The absence of explicit FK constraints in the dictionary should be treated as a modeling suggestion only; application logic in IGS still enforces referential integrity with the applicant record.

Key Information Stored

The table contains 18 documented columns. The composite primary key, IGS_UC_APP_REFEREES_PK, is defined on (APP_NO, REFEREE_NAME). Both columns function as business-key candidates rather than a single surrogate key: APP_NO identifies the UCAS application, and REFEREE_NAME distinguishes multiple referees within that application. Most significant columns include:

  • APP_NO — UCAS application number; part of the primary key and the principal join column to the applicant record.
  • REFEREE_NAME — name of the referee; part of the primary key.
  • REFEREE_POST — job title or position held by the referee.
  • ESTAB_NAME — name of the establishment or institution the referee represents.
  • ADDRESS1–ADDRESS4 — multi-line postal address of the referee or establishment.
  • TELEPHONE, FAX, EMAIL — contact channels for correspondence with the referee.
  • STATEMENT — the free-text reference or supporting narrative supplied by the referee.
  • PREDICTED_GRADES — predicted academic grades the referee attributes to the applicant.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS WHO columns providing audit and concurrency tracking.

A supplementary index, SYS_IL0000328082C00012$$, exists as a LOB index supporting a large-object column (typically STATEMENT).

Common Use Cases and Queries

Admissions and registry staff use this data to review, validate, and report on references received through the UCAS feed. Typical scenarios include verifying referee credentials, auditing whether references arrived for a given applicant, and extracting referee statements for admissions panels.

  • Retrieving all referees for an applicant: SELECT referee_name, referee_post, estab_name, email FROM igs.igs_uc_app_referees WHERE app_no = :app_no;
  • Counting references per application for completeness checks: SELECT app_no, COUNT(*) FROM igs.igs_uc_app_referees GROUP BY app_no HAVING COUNT(*) < 2;
  • Locating references containing a keyword in the narrative: SELECT app_no, referee_name FROM igs.igs_uc_app_referees WHERE UPPER(statement) LIKE '%OUTSTANDING%';
  • Audit of recently updated references: SELECT app_no, referee_name, last_updated_by, last_update_date FROM igs.igs_uc_app_referees WHERE last_update_date >= :since_date;

Related Objects

Although the ETRM metadata records no formal foreign keys, application relationships are keyed through APP_NO. The most significant related objects are:

  • IGS_UC_APPLICANTS (or the equivalent UCAS applicant table in IGS) — joined on APP_NO.
  • IGS_UC_APPLICANT_DETAILS — extended applicant attributes, joined on APP_NO.
  • IGS_UC_CHOICES — UCAS course choices associated with the same application, joined on APP_NO.
  • IGS_UC_QUALIFICATIONS — applicant qualifications, joined on APP_NO.
  • IGS_AD_APPLICATIONS — internal admissions application records populated from UCAS data, keyed through the applicant.
  • UCAS import concurrent programs and interface APIs that populate this table during the admissions feed.