Results for “igs_uc_ucapch_ossappl_v”

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

Overview

IGS_UC_UCAPCH_OSSAPPL_V is a reporting view belonging to the IGS — Student System product family, which was the Oracle E-Business Suite foundation for the UK higher-education UCAS admissions process. The view exposes a unified result set combining UCAS Application Choice records with the corresponding Oracle Student System (OSS) application instances maintained for UCAS applicants. Its stated purpose is to "select the UCAS Application Choice records as well as the OSS Application Instances for the UCAS Applicants," enabling institutions to reconcile data received from the UCAS clearing house against applications, offers, decisions, and replies recorded natively within EBS.

Within ETRM 12.2.2 the object is classified as obsolete: the documentation records "Not implemented in this database." This is significant because IGS — Student System functionality was progressively superseded by Oracle Student Cloud and by institution-specific extensions. Installations on EBS 12.1.1 or 12.2.2 that still carry historical admissions data may encounter the view only in legacy schemas, archive copies, or during migration discovery exercises.

Underlying Base Objects

The view is defined as a UNION ALL of two projections. The first projection draws from UCAS-side tables: IGS_UC_APPLICANTS (alias UCAP), IGS_UC_APP_CHOICES (UCAPCH), IGS_UC_DEFAULTS (UCADM), IGS_PS_VER (PSVER), and two instances of IGS_LOOKUP_VALUES (LKDCSN for decision meanings, LKRPLY for reply meanings). Joins are made on APP_NO between applicants and choices, on SYSTEM_CODE between choices and defaults, and outer-joined to programme versions and lookup values so that unmapped codes are preserved.

The second projection draws from OSS-side tables: IGS_AD_PS_APPL_INST_ALL (APPLINST), IGS_AD_CODE_CLASSES (ACC), IGS_PS_VER, IGS_AD_OU_STAT (OUSTAT), and IGS_AD_OFR_RESP_STAT, substituting literal values such as 'OSS' for the UCAS-specific columns. No base objects are formally documented for retention, but the view text itself identifies the authoritative sources. The '+' outer-join syntax reflects the Oracle 8i/9i-style join notation used throughout IGS.

Key Columns

• SYSTEM_CODE / NAME — identifies the UCAS system or flags an OSS-only row.
• PERSONAL_ID / OSS_PERSON_ID / PERSON_ID — links the applicant across UCAS and OSS person models.
• APP_NO / ADMISSION_APPL_NUMBER — the UCAS application number or the OSS equivalent.
• CHOICE_NO — the UCAS choice position (1–6) for the cycle.
• UCAS_CYCLE — the admissions cycle year.
• INSTITUTE_CODE, UCAS_PROGRAM_CODE, CAMPUS — institution and programme identifiers as received from UCAS.
• OSS_PROGRAM_CODE / NOMINATED_COURSE_CD, OSS_PROGRAM_VERSION / CRV_VERSION_NUMBER — the mapped EBS programme and version.
• OSS_ATTENDANCE_TYPE, OSS_ATTENDANCE_MODE, OSS_LOCATION — delivery attributes of the mapped programme.
• DECISION / MEANING — UCAS decision code and its decoded meaning (or ADM_OUTCOME_STATUS/OUSTAT description for OSS rows).
• REPLY / MEANING — applicant reply code and decoded meaning, or ADM_OFFER_RESP_STATUS.
• CHOICE_CANCELLED — flags withdrawn UCAS choices.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical reporting includes reconciliation of UCAS-managed choices against institutional applications, audit of decision and reply processing, and data extraction ahead of migration to a successor student system. A representative query filtering a single cycle is:

SELECT APP_NO, CHOICE_NO, UCAS_PROGRAM_CODE, OSS_PROGRAM_CODE, DECISION, REPLY, CHOICE_CANCELLED FROM IGS_UC_UCAPCH_OSSAPPL_V WHERE UCAS_CYCLE = 2011 AND SYSTEM_CODE = 'UCAS' ORDER BY APP_NO, CHOICE_NO;

Because the view unions UCAS and OSS projections, institutions should filter on SYSTEM_CODE where only one source is required. Row counts will differ between the two halves, and OSS-only rows carry NULL for UCAS-specific columns, a behaviour that must be handled in any downstream ETL or report layout.