Results for “igs_uc_ref_codes”

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

Overview

IGS_UC_REF_CODES is a reference (lookup) table within the IGS product family — the Oracle Student System module of Oracle E-Business Suite. It is owned by the IGS schema in both EBS 12.1.1 and 12.2.2. As stated in its ETRM documentation, the table "holds the list of reference codes used in other tables and their descriptions for the purpose of display in forms." In practice, it functions as the display-value backbone for UCAS-related data captured in the student system, allowing forms and concurrent programs to translate stored short codes into human-readable descriptions without hard-coding literals.

Because its rows are descriptive attributes of a parent code type rather than independent business entities, the heuristic Data Vault classification of this object is satellite-leaning. Under a Data Vault model, IGS_UC_REF_CODES would most naturally be modeled as a satellite hanging off the code-type hub (IGS_UC_REF_CODETYPS), not as a hub itself. Each row is a dependent, descriptive record identified by the combination of a code type and a code value, and it carries few independent relationships beyond its parent.

Key Information Stored

The documented physical schema contains nine columns. The most important are:

• CODE_TYPE — the high-level category of the reference; part of the composite primary key and a foreign key to IGS_UC_REF_CODETYPS. It groups codes into logical sets used by forms.
• CODE — the short reference value stored in transactional tables; the second component of the composite primary key.
• CODE_TEXT — the human-readable description shown to users in forms and reports. This is the primary display attribute.
• IMPORTED — a flag indicating whether the row was loaded through an external import process rather than entered manually.
• CREATED_BY, CREATION_DATE — standard WHO audit columns capturing user and timestamp of row creation.
• LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns capturing the most recent update user, timestamp, and login session.

The surrogate primary key is enforced by IGS_UC_REF_CODES_PK on (CODE_TYPE, CODE). A unique index, IGS_UC_REF_CODES_U1, is defined over the same (CODE_TYPE, CODE) pair, confirming that this combination is the business-key candidate. Notably, the primary key is composite rather than a single generated surrogate, so CODE_TYPE + CODE uniquely identifies any row.

Common Use Cases and Queries

The table is most commonly joined to resolve a stored code into its display text. A typical reporting query retrieves the description for exam-level or reference-type values:

• Decoding reference values: SELECT code, code_text FROM igs_uc_ref_codes WHERE code_type = :p_code_type — used to populate a form LOV or reporting parameter list.
• Resolving values in transactional data: join IGS_UC_EXAM_SCORES to IGS_UC_REF_CODES on REF_CODE_TYPE = CODE_TYPE and EXAM_LEVEL = CODE to obtain CODE_TEXT for reporting.
• Auditing imports: filter rows where IMPORTED is set to distinguish externally loaded reference data from manually maintained entries.
• Change tracking: use LAST_UPDATED_BY and LAST_UPDATE_DATE to identify recently modified reference values for reconciliation.

Related Objects

• IGS_UC_REF_CODETYPS — parent table; IGS_UC_REF_CODES.CODE_TYPE references it, defining the valid code categories.
• IGS_UC_EXAM_SCORES — child table; IGS_UC_EXAM_SCORES.REF_CODE_TYPE and EXAM_LEVEL together reference IGS_UC_REF_CODES (CODE_TYPE, CODE), making it the principal dependent transaction table.
• IGS_UC_REF_CODES_PK — the primary key constraint on (CODE_TYPE, CODE).
• IGS_UC_REF_CODES_U1 — the unique index supporting the business key.

Because IGS_UC_REF_CODES is a lookup table, any form based on IGS_UC_EXAM_SCORES or other UCAS-referencing tables derives its list-of-values behaviour here, and the IGS_UC_REF_CODETYPS classification governs which CODE_TYPE sets are available.