Search Results igs_he_code_assoc_u1

Overview

IGS.IGS_HE_CODE_ASSOC is a seed data table in the Oracle E-Business Suite IGS (Higher Education / Student Systems) schema. It holds all Association Codes defined for use within the HESA (Higher Education Statistics Agency) reporting framework, providing the controlled vocabulary that classifies how an HESA-coded entity relates to the institution or to another coded entity in the student records model. Records are stored in the APPS_TS_SEED tablespace, confirming the table's role as reference data loaded at implementation and refreshed through seed data patches rather than through day-to-day transactional entry.

From a Data Vault modeling perspective the mined FK structure classifies this object as hub-leaning. The single-column unique key ASSOCIATION_CODE behaves as a natural business key, making the table a plausible hub candidate whose descriptive columns (DESCRIPTION, DISPLAY_TITLE) could alternatively be modeled as an attached satellite. The three dependent tables that carry ASSOCIATION_CODE as a foreign key act as link or satellite candidates that resolve their association semantics against this hub.

Key Information Stored

• ASSOCIATION_CODE (VARCHAR2(30), mandatory) — the primary business key and physical primary key (IGS_HE_CODE_ASSOC_PK). It also backs the unique index IGS_HE_CODE_ASSOC_U1, so it is both the primary key and the sole documented business-key candidate. It stores the association code name as defined in the HESA system.
• DESCRIPTION (VARCHAR2(240)) — the full textual description of each defined association code, used for display and reporting where the code alone is insufficient.
• DISPLAY_TITLE (VARCHAR2(80)) — the short display title for each association code. This is the column most commonly surfaced in list-of-values and lookup implementations, since it is deliberately sized for on-screen presentation rather than full documentation.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard WHO audit columns. In the case of seed data these typically reflect the patching or data-load user rather than an end user, which matters when reconciling the provenance of HESA reference values.

The documented 12.1.1 physical schema comprises eight columns, and the table exposes no foreign keys of its own: it references no database object and instead functions as the referenced parent.

Common Use Cases and Queries

The most frequent use is resolving an association code to its displayable text for reports and HESA returns, and driving lookups in concurrent program parameters. A basic lookup query is:

• SELECT association_code, display_title FROM igs.igs_he_code_assoc ORDER BY association_code;
• SELECT association_code, description, display_title FROM igs.igs_he_code_assoc WHERE association_code = :p_code;
• Validating staged HESA data: SELECT h.association_code FROM igs.igs_he_code_map_val h WHERE NOT EXISTS (SELECT 1 FROM igs.igs_he_code_assoc a WHERE a.association_code = h.association_code);
• Auditing seed currency after a patch is applied: SELECT association_code, last_update_date, last_updated_by FROM igs.igs_he_code_assoc WHERE last_update_date > :p_since;

Because the table is small and fully cached in practice, joins to it are inexpensive and are typically performed as outer joins so that unrecognized codes remain visible during exception reporting.

Related Objects

Three documented dependent tables carry ASSOCIATION_CODE as a foreign key to this hub:

• IGS.IGS_HE_CODE_ASS_VAL — joins on ASSOCIATION_CODE; holds the valid values permitted for each association code, forming the child validation set.
• IGS.IGS_HE_CODE_MAP_VAL — joins on ASSOCIATION_CODE; maps incoming HESA codes to the association values used internally.
• IGS.IGS_HE_SYS_RT_CL_ASS — joins on ASSOCIATION_CODE; associates association codes with system return classifications for statutory returns.

The APPS synonym IGS_HE_CODE_ASSOC provides the standard access path used by forms, reports, and PL/SQL within the EBS application layer, so queries should generally be written against the APPS synonym rather than the IGS base table.