Results for “igs_uc_applicants”

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

Overview

IGS_UC_APPLICANTS is the central applicant and application table within the Oracle E-Business Suite Student System (IGS) product, owned by the IGS schema. It records the identity, qualifications, choices, and processing status of applicants submitted through the Universities and Colleges Admissions Service (UCAS) interface. The table synchronizes with several UCAS external views, including ivStatement, ivQualification, ivStarC, ivStark, and ivStarN, making it the primary staging and persistence point for admissions data flowing into EBS.

The object is documented as VALID in both ETRM 12.1.1 and 12.2.2, containing 118 columns. Its heuristic Data Vault classification is hub-leaning, suggesting it functions primarily as a business entity hub anchored by the surrogate key APP_ID, with associated descriptive attributes that behave satellite-like. Modelers integrating this table into a warehouse should treat APP_ID as the durable business key and isolate volatile attributes (such as status timestamps and reply codes) into satellite structures.

Key Information Stored

The surrogate primary key is APP_ID, enforced by the constraint IGS_UC_APPLICANTS_PK. Two unique indexes identify business-key candidates: IGS_UC_APPLICANTS_U1 (APP_ID) and IGS_UC_APPLICANTS_U2 (APP_NO), where APP_NO represents the externally recognizable application number. The most significant columns include:

• APP_NO – UCAS-issued application number; the primary human-facing identifier.
• OSS_PERSON_ID – foreign key linking to HZ_PARTIES, tying the applicant to a TCA party record.
• SYSTEM_CODE – foreign key to IGS_UC_DEFAULTS, identifying the UCAS system context.
• APPLICATION_DATE / APPLICATION_SENT_DATE – lifecycle timestamps for submission.
• FIRM_REPLY / INSURANCE_REPLY – firm and insurance choice response codes.
• STATUS / EXTRA_STATUS – current application processing state.
• WITHDRAWN / WITHDRAWN_DATE – withdrawal flags and dates.
• STUDENT_SUPPORT / FEE_PAYER_CODE / FEE_TEXT – funding and fee classification.
• DOMICILE_APR / RESIDENTIAL_CATEGORY – residency and domicile classification.
• PERSONAL_STATEMENT – free-text applicant statement.
• IVQUAL_TIMESTAMP, IVSTARC_TIMESTAMP, IVSTARK_TIMESTAMP, IVSTARN_TIMESTAMP – synchronization markers for UCAS views.
• SENT_TO_HESA / SENT_TO_OSS – downstream transmission indicators.
• CREATION_DATE / LAST_UPDATE_DATE / LAST_UPDATED_BY – standard EBS audit columns.

Common Use Cases and Queries

Typical reporting includes applicant pipelines, UCAS synchronization health, and admissions funnel analysis. A simple applicant lookup:

• SELECT app_id, app_no, status, application_date FROM igs_uc_applicants WHERE app_no = :app_no;
• Joining to person data: SELECT a.app_no, p.party_name FROM igs_uc_applicants a, hz_parties p WHERE a.oss_person_id = p.party_id;
• Monitoring view synchronization: query rows where IVQUAL_TIMESTAMP or IVSTARC_TIMESTAMP is older than a threshold.
• Withdrawal analysis: filter on WITHDRAWN = 'Y' grouped by WITHDRAWN_DATE.

Related Objects

The table is both a child and a parent in the IGS admissions model. It references:

• IGS_UC_DEFAULTS via SYSTEM_CODE.
• HZ_PARTIES via OSS_PERSON_ID.

It is referenced by several child tables through APP_ID:

• IGS_UC_APP_CHOICES – applicant course choices.
• IGS_UC_APP_CLEARING – clearing entries.
• IGS_UC_APP_RESULTS – examination results.
• IGS_UC_APP_STATS – statistical snapshots.
• CCT_AGENT_RT_STATS and FND_IMP_MENU_DEP2 – dependent operational tables.