Search Results igs_uc_app_names_pk

Overview

IGS_UC_APP_NAMES is a table in the IGS (Student System) product schema of Oracle E-Business Suite, validated in release 12.1.1 and 12.2.2. It holds UCAS (Universities and Colleges Admissions Service) applicant name details. UCAS is the United Kingdom centralised admissions service through which applicants apply to higher education programmes; institutions using Oracle Student System typically load UCAS application extracts into the IGS schema, and this table stores the personal-name portion of that inbound applicant data. The table is therefore a staging and reference object for admissions processing, positioned upstream of the formal student/admissions record structures, and it is populated and reconciled by UCAS interface concurrent programs and batch imports.

The documented physical schema comprises 14 columns and is defined as VALID in the ETRM registry. The Data Vault classification derived heuristically from the FK structure is standalone, meaning no documented foreign-key relationships radiate from or into this table. From a modelling perspective, this suggests the object functions as an independent hub-like entity keyed by the applicant identifier rather than as a link or satellite dependent on another parent. Where referential integrity to an applicant master is required, it should be validated at the interface layer rather than assumed from a database constraint.

Key Information Stored

The surrogate primary key is APP_NO, enforced by the unique index IGS_UC_APP_NAMES_PK. APP_NO is also the sole documented business-key candidate, and it is the column most commonly used to join this table to other UCAS and applicant structures.

• APP_NO — primary key; the UCAS applicant number identifying the individual applicant.
• CHECK_DIGIT — the validation digit associated with the applicant identifier, used to verify the integrity of the applicant number on load.
• TITLE — honorific or courtesy title captured on the UCAS application.
• FORE_NAMES — the applicant's given name or names, as transmitted by UCAS.
• SURNAME — the applicant's family name; the principal search and matching attribute for admissions staff.
• BIRTH_DATE — date of birth, used for identity confirmation and age-related eligibility checks.
• SEX — recorded sex of the applicant, held as a coded value.
• NAME_CHANGE_DATE — the effective date on which the name details were amended, supporting change history rather than overwrite-only semantics.
• SENT_TO_OSS_FLAG — indicator that the applicant name record has been transmitted onward to the Oracle Student System target structures, supporting interface reconciliation and reprocessing decisions.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard Oracle EBS WHO columns auditing record creation and amendment.

Common Use Cases and Queries

Typical scenarios include identifying applicants whose details have not yet propagated downstream, matching applicants by name and date of birth, and extracting name and demographic statistics for admissions reporting.

To list applicants not yet sent onward:

• SELECT APP_NO, TITLE, FORE_NAMES, SURNAME, BIRTH_DATE FROM IGS.IGS_UC_APP_NAMES WHERE NVL(SENT_TO_OSS_FLAG,'N') = 'N';

To retrieve a single applicant by identifier, exploiting the primary key:

• SELECT * FROM IGS.IGS_UC_APP_NAMES WHERE APP_NO = :app_no;

To search on name for duplicate or verification checks:

• SELECT APP_NO, SURNAME, FORE_NAMES, BIRTH_DATE, SEX FROM IGS.IGS_UC_APP_NAMES WHERE UPPER(SURNAME) = UPPER(:surname) AND TRUNC(BIRTH_DATE) = TRUNC(:dob);

Change tracking over a load window relies on NAME_CHANGE_DATE and LAST_UPDATE_DATE. Because the table is standalone, joins to applicant or admission records must be driven from APP_NO against the corresponding applicant key in the interface or admissions tables.

Related Objects

The metadata documents no foreign-key relationships, so the following are the conventional companions of this object by key and function rather than by enforced constraint:

• IGS_UC_APP_NAMES_PK — the unique index on APP_NO that supplies row identity and the fastest access path.
• Other IGS_UC_* interface tables holding UCAS application data, related by APP_NO (for example applicant address, choice, and qualification extracts).
• The Oracle Student System applicant/admissions structures, taking APP_NO and the name attributes once SENT_TO_OSS_FLAG is set.
• UCAS import and reconciliation concurrent programs that insert and update rows, and set SENT_TO_OSS_FLAG.
• Admissions enquiry and reporting extracts that present TITLE, FORE_NAMES, SURNAME, BIRTH_DATE, and SEX.