Results for “igs_uc_app_choices”

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

Overview

IGS_UC_APP_CHOICES is a table in the IGS schema (Oracle Student System / Student Information product family) within Oracle E-Business Suite 12.1.1 and 12.2.2. It stores applicant choice details together with the corresponding offer information, and it serves as the synchronization staging point between EBS and UCAS (the UK Universities and Colleges Admissions Service). In practice, this table is populated by data loaded from the UCAS external views ivOffer and ivStarC, and its records are subsequently used to feed institutional admissions processing. The documentation explicitly notes that child records exist in this table only when the parent record in IGS_UC_APPLICANTS carries a populated App_No, confirming a strict parent/child dependency on the applicant header.

The ETRM relationship metadata classifies this object heuristically as satellite-leaning within a Data Vault model. It carries descriptive, time-stamped attributes attached to an applicant hub and a course-detail hub rather than acting as a pure hub or a resolving link; the timestamp columns IVOFFER_TIMESTAMP and IVSTARG_TIMESTAMP reinforce this satellite interpretation.

Key Information Stored

The table contains 63 documented columns. The surrogate primary key is APP_CHOICE_ID, enforced by the index IGS_UC_APP_CHOICES_PK and the unique index IGS_UC_APP_CHOICES_U1. A second unique index, IGS_UC_APP_CHOICES_U2, defines the business key as the combination of APP_NO, CHOICE_NO, and UCAS_CYCLE — that is, an applicant's numbered choice within a given admissions cycle.

• APP_CHOICE_ID — surrogate primary key for the choice record.
• APP_ID — foreign key to IGS_UC_APPLICANTS, linking the choice to its applicant.
• APP_NO, CHOICE_NO, UCAS_CYCLE — business-key attributes identifying the applicant, the ordinal choice, and the application cycle.
• INSTITUTE_CODE, UCAS_PROGRAM_CODE, CAMPUS, OSS_PROGRAM_CODE, OSS_PROGRAM_VERSION — course and institution identifying data for the chosen programme.
• CONDITION_CATEGORY, CONDITION_CODE, SUMMARY_OF_COND — offer condition data, joined to IGS_UC_OFFER_CONDS.
• DECISION, DECISION_DATE, DECISION_NUMBER, REPLY — the admission decision and applicant reply status.
• OFFER_COURSE, OFFER_CAMPUS, OFFER_TEXT, OFFER_ENTRY_MONTH, OFFER_ENTRY_YEAR — the offer detail returned by UCAS.
• IVOFFER_TIMESTAMP, IVSTARG_TIMESTAMP — synchronization timestamps for the UCAS ivOffer and ivStarC feeds.
• EXPORT_TO_OSS_STATUS, ERROR_CODE, REQUEST_ID, BATCH_ID — interface processing and error-tracking columns.

Common Use Cases and Queries

Typical usage centers on admissions reporting, UCAS interface reconciliation, and offer-condition tracking. A common pattern joins the choice to its applicant and course details:

• Listing all choices for an applicant cycle: SELECT app_no, choice_no, decision, reply FROM igs_uc_app_choices WHERE ucas_cycle = :cycle ORDER BY app_no, choice_no;
• Reconciling interface errors: filter on ERROR_CODE or EXPORT_TO_OSS_STATUS to identify records that failed the OSS export.
• Tracking unsynchronized rows by comparing IVOFFER_TIMESTAMP and IVSTARG_TIMESTAMP against the last interface run.
• Reporting conditional offers by joining CONDITION_CATEGORY/CONDITION_CODE to IGS_UC_OFFER_CONDS and grouping by DECISION and SUMMARY_OF_COND.

Because the table holds child records, reporting should always be driven from a parent applicant join on APP_ID to avoid orphaned or superceded rows.

Related Objects

• IGS_UC_APPLICANTS — parent table; joined via APP_CHOICE_ID.APP_ID to IGS_UC_APPLICANTS. Populates child rows only when App_No is present.
• IGS_UC_CRSE_DETS — course detail table referenced by SYSTEM_CODE, UCAS_PROGRAM_CODE, INSTITUTE_CODE, and CAMPUS.
• IGS_UC_OFFER_CONDS — offer condition definitions referenced by CONDITION_CATEGORY and CONDITION_CODE.
• IGS_UC_APP_CHO_CNDS — child table referencing APP_CHOICE_ID; stores the individual conditions attached to each choice.
• UCAS external views ivOffer and ivStarC — synchronization sources tracked by the timestamp columns.

Together these objects form the UCAS admissions interface chain: applicant header, choice and offer detail, condition breakdown, and course reference data.