Search Results igs_he_code_values_u1

Overview

IGS.IGS_HE_CODE_VALUES is a reference (lookup) table in the Oracle E-Business Suite IGS schema, the schema that underpins the Oracle Student System and its statutory returns processing for the UK Higher Education Statistics Agency (HESA). The table holds the individual code values that belong to each HESA code type defined elsewhere in the system, providing the enumerated value set against which student, programme, and institution records are validated and decoded.

The table sits at the leaf end of the IGS reference-data hierarchy. Its single documented foreign key establishes a parent-child relationship with IGS_HE_CODE_TYPES: CODE_TYPE references the code type definition, and each row supplies one permissible value under that type together with a human-readable description. Its role is therefore that of a controlled vocabulary store, not a transactional or relationship-bearing entity.

Based on the mined foreign-key structure, the heuristic Data Vault classification for this object is satellite-leaning. Under that modeling suggestion, CODE_TYPE and VALUE would function as the business key inherited from the parent code type, while descriptive attributes such as VALUE_DESCRIPTION and CLOSED_IND behave as satellite payload. Practitioners should treat this as a suggested classification rather than a normative statement, since the table is a natural composite-key reference table rather than a Data Vault artifact.

Key Information Stored

The documented physical schema for release 12.1.1 contains nine columns. The business-key candidate is exposed through the unique index IGS_HE_CODE_VALUES_U1, which covers CODE_TYPE and VALUE; the same two columns constitute the primary key constraint IGS_HE_CODE_VALUES_PK. Together they uniquely identify each row and represent the parent-plus-value composite that users query by.

• CODE_TYPE (VARCHAR2, 30, mandatory) — the HESA code name under which the value is grouped; the foreign key to IGS_HE_CODE_TYPES and the first component of both the primary key and the unique index.
• VALUE (VARCHAR2, 30, mandatory) — the actual code value permitted for the given code type; the second component of the primary key and unique index.
• VALUE_DESCRIPTION (VARCHAR2, 240) — the descriptive text that decodes VALUE for reporting and user display.
• CLOSED_IND (VARCHAR2) — indicates whether a code value is closed; accepts Y (yes) and N (no). Closed values are retained for historical decoding but excluded from new data entry.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the five standard WHO audit columns recording row creation and last modification.

No surrogate sequence-generated identifier is documented; identity is entirely business-key based.

Common Use Cases and Queries

The primary use case is decoding a stored code into readable text for reports and interfaces, and validating incoming data against the permitted value set. A typical decode joins the value table to the transaction table on CODE_TYPE and VALUE and projects VALUE_DESCRIPTION. Restricting to active values is accomplished by filtering CLOSED_IND = 'N'; historical extracts omit that filter so retired codes still resolve.

A common validation pattern confirms that a submitted code exists and is open:

• SELECT VALUE_DESCRIPTION FROM IGS.IGS_HE_CODE_VALUES WHERE CODE_TYPE = :p_code_type AND VALUE = :p_value AND NVL(CLOSED_IND,'N') = 'N';

Reporting and reconciliation use cases include generating the permitted value list for a single code type for user documentation, auditing which code types carry an excessive or sparse number of values, and extracting the full reference set for downstream data warehouse loads. Because the storage tablespace is APPS_TS_TX_DATA and the unique index resides in APPS_TS_TX_IDX, bulk reference extracts are best performed with an ORDER BY CODE_TYPE, VALUE to preserve deterministic output.

Related Objects

The dependency data documents the following relationships; other IGS reference consumers may join on the same key columns.

• IGS.IGS_HE_CODE_TYPES — parent of this table via the foreign key IGS_HE_CODE_VALUES.CODE_TYPE → IGS_HE_CODE_TYPES; supplies the code type definition and description.
• APPS.IGS_HE_CODE_VALUES — the APPS-synonymed view through which the table is normally accessed in EBS, so application SQL should reference the APPS synonym rather than the IGS base object.
• Downstream IGS tables that store HESA codes (student, programme, and returns-related entities) join on CODE_TYPE and VALUE to resolve descriptions.

Because IGS_HE_CODE_VALUES is a reference table, it is populated through the application's reference-data maintenance functions rather than by direct DML; changes to VALUE_DESCRIPTION or CLOSED_IND flow immediately into all decoding queries.