Results for “igs_uc_app_choices_u2”

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

Overview

The IGS.IGS_UC_APP_CHOICES table is a transactional data store within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 environments, owned by the IGS schema and registered under FND Design Data as IGS.IGS_UC_APP_CHOICES. It holds applicant choice details and related offer details originating from the UCAS (Universities and Colleges Admissions Service) integration. The table serves two explicit synchronization roles: it synchronizes with the UCAS view ivOffer and with the UCAS view ivStarC. The LAST_CHANGE column is documented specifically as the choice-level timestamp used to synchronize with ivStarC, and the IVSTARG_TIMESTAMP column (the object referenced in the originating search) forms part of the same synchronization family, tracking the ivStarC/StarG refresh cycle. IGS_UC_APP_CHOICES stores child records only when its parent table, IGS_UC_APPLICANTS, has APP_NO populated (not null), establishing the referential prerequisite for all downstream processing.

From a Data Vault modeling perspective, the heuristic classification supplied in the metadata is satellite-leaning. This is consistent with the table's structure: it carries a surrogate primary key (APP_CHOICE_ID), descriptive and timestamped attributes driven by source-system synchronization, and foreign key references to hub-like parent entities. Practitioners modeling this table for a warehouse should therefore treat it as a satellite attached to an applicant and application-choice business key rather than as an independent hub.

Key Information Stored

The physical schema documents 63 columns. The most operationally significant are summarized below.

• APP_CHOICE_ID (NUMBER) — the surrogate primary key, defined by unique index IGS_UC_APP_CHOICES_U1. This is the unique record identifier and the target of the child table IGS_UC_APP_CHO_CNDS.
• APP_NO, CHOICE_NO, UCAS_CYCLE — the composite business-key candidates defined by unique index IGS_UC_APP_CHOICES_U2. Together they identify a UCAS application choice within a given admissions cycle.
• APP_ID (NUMBER) — the unique applicant identifier, joining back to IGS_UC_APPLICANTS.
• LAST_CHANGE (DATE) — the choice-level timestamp used to synchronize with the UCAS view ivStarC.
• IVSTARG_TIMESTAMP — the ivStarG synchronization timestamp, central to the search that surfaced this object.
• IVOFFER_TIMESTAMP — timestamp governing synchronization with the UCAS view ivOffer.
• INSTITUTE_CODE, UCAS_PROGRAM_CODE, CAMPUS, FACULTY, ENTRY_YEAR, ENTRY_MONTH, POINT_OF_ENTRY, HOME, DEFERRED — UCAS-context attributes that synchronize with ivStarC.
• OSS_PROGRAM_CODE, OSS_PROGRAM_VERSION, OSS_ATTENDANCE_TYPE, OSS_ATTENDANCE_MODE, OSS_LOCATION — the mapped OSS (Oracle Student System) equivalents of the UCAS choice.
• DECISION, DECISION_DATE, DECISION_NUMBER, REPLY, CONDITION_CATEGORY, CONDITION_CODE, SUMMARY_OF_COND — offer and decision outcome data, with CONDITION_CATEGORY referencing IGS_UC_OFFER_CONDS.
• EXPORT_TO_OSS_STATUS, ERROR_CODE, REQUEST_ID, BATCH_ID — integration control columns; EXPORT_TO_OSS_STATUS is indexed by IGS_UC_APP_CHOICES_N1 for non-unique lookups.
• SYSTEM_CODE — foreign key to IGS_UC_CRSE_DETS.

Common Use Cases and Queries

Primary use cases include auditing UCAS offer synchronization, reconciling applicant choices against their mapped OSS programs, and monitoring the export pipeline to Oracle Student System. A representative query to find choices awaiting OSS export is:

• SELECT app_no, choice_no, ucas_cycle, export_to_oss_status, error_code FROM igs.igs_uc_app_choices WHERE export_to_oss_status IS NULL OR export_to_oss_status = 'N';
• Reconciliation of ivStarG synchronization: compare ivstarg_timestamp and last_change to identify stale choices.
• Reporting offer outcomes joined with conditions: SELECT c.app_no, c.decision, o.condition_category FROM igs.igs_uc_app_choices c, igs.igs_uc_offer_conds o WHERE c.condition_category = o.condition_category;
• Cycle-level analysis filtering on ucas_cycle, entry_year, and deferred.

Related Objects

• IGS_UC_APPLICANTS — parent table; joined on APP_ID. The presence of a non-null APP_NO in the parent governs whether child choice records exist here.
• IGS_UC_CRSE_DETS — referenced via SYSTEM_CODE for course detail resolution.
• IGS_UC_OFFER_CONDS — referenced via CONDITION_CATEGORY for offer condition lookup.
• IGS_UC_APP_CHO_CNDS — child table; joined on APP_CHOICE_ID, storing choice-level conditions.
• UCAS view ivOffer — synchronized via IVOFFER_TIMESTAMP.
• UCAS view ivStarC — synchronized via LAST_CHANGE and the ivStarG-related timestamps.