Results for “summ_of_cond”

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

Overview

IGS_UC_OFFER_CONDS is the master reference table of the generic offer condition library in the Oracle E-Business Suite Student System (IGS) module. It resides in the IGS schema and stores the condition category and condition code that uniquely identify each offer condition together with its defining attributes. In the context of UCAS (Universities and Colleges Admissions Service) processing, this table serves as the authoritative catalog from which conditional offers, default condition templates, and application choice conditions draw their valid values.

Functionally, the table behaves as a controlled vocabulary: every offer condition used elsewhere in the IGS data model must resolve to a row in this table before it can be attached to an application choice or elaborated through condition details. Because it holds the canonical list of condition categories and names, it acts as the anchor for condition-related referential integrity across the module.

From a Data Vault modeling perspective, the heuristic classification is hub-leaning. The composite unique key on CONDITION_CATEGORY and CONDITION_NAME represents a natural business key, and the table's role as a static, shared reference leads downstream consumers to treat it as a hub from which other condition records derive their identity.

Key Information Stored

The documented physical schema for 12.1.1 contains 14 columns. The most significant are:

• CONDITION_CATEGORY — one half of the composite primary key; classifies the condition (for example, qualification-based, non-academic, or English language categories).
• CONDITION_NAME — the second half of the primary key; the human-readable code identifying the specific offer condition within its category.
• EFFECTIVE_FROM and EFFECTIVE_TO — the date range over which the condition definition is valid; used to retire obsolete conditions without deleting history.
• STATUS — indicates whether the condition is currently active and selectable in offer entry.
• MARVIN_CODE — the external code used for MARVIN/UCAS data exchange, mapping the internal condition to the format transmitted to UCAS.
• SUMM_OF_COND — the summarised condition text, typically the short form presented on offers and correspondence.
• LETTER_TEXT — the longer descriptive text merged into applicant letters for the condition.
• DECISION — the decision outcome associated with the condition definition.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard Oracle who-columns providing audit and change tracking.

The surrogate primary key is defined by IGS_UC_OFFER_CONDS_PK over (CONDITION_CATEGORY, CONDITION_NAME). A separate unique index, IGS_UC_OFFER_CONDS_U1, covers the same two columns and is documented as the business-key candidate.

Common Use Cases and Queries

The table is queried when validating offer conditions, populating condition selection lists in offer entry forms, and reporting on which conditions are active. A typical lookup returns active conditions within a category:

SELECT CONDITION_CATEGORY, CONDITION_NAME, SUMM_OF_COND
FROM   IGS_UC_OFFER_CONDS
WHERE  STATUS = 'A'
AND    TRUNC(SYSDATE) BETWEEN EFFECTIVE_FROM AND EFFECTIVE_TO;

A common reporting pattern joins this table to application choices to count offers by condition:

SELECT c.CONDITION_CATEGORY, c.CONDITION_NAME, COUNT(*) offer_count
FROM   IGS_UC_APP_CHOICES a, IGS_UC_OFFER_CONDS c
WHERE  a.CONDITION_CATEGORY = c.CONDITION_CATEGORY
AND    a.CONDITION_CODE     = c.CONDITION_NAME
GROUP BY c.CONDITION_CATEGORY, c.CONDITION_NAME;

Integration extracts targeting UCAS submissions typically select MARVIN_CODE alongside the keys to build outbound condition messages. Validation queries join to IGS_UC_COND_DETAILS to confirm that each offer condition has the required detail attributes configured before the condition can be offered.

Related Objects

The following tables reference IGS_UC_OFFER_CONDS through documented foreign keys:

• IGS_UC_APP_CHOICES — joins on CONDITION_CATEGORY and CONDITION_CODE; attaches conditions to applicant choices.
• IGS_UC_COND_DETAILS — joins on CONDITION_CATEGORY and CONDITION_NAME; stores the detailed attributes that elaborate a master condition.
• IGS_AD_DFLT_OUDTLS — references the master through UCAS_COND_CATEGORY and UCAS_COND_NAME; supplies default UCAS condition values.

These relationships confirm the hub-like position of the table: application choices, condition details, and default (OU/DTLS) setup all resolve their condition identity through this central condition library.