Search Results igs_lookups_val




Overview
Explore in AI Chat

Overview

IGS_LOOKUPS_VAL is a validation and extensibility table owned by the IGS schema within the Oracle EBS Student System (Higher Education) product family. It stores the set of permitted values — the "value set" — that qualifies each lookup type defined in the Student System. Where FND_LOOKUPS supplies the standard Oracle Application Object Library lookup codes used across the E-Business Suite, IGS_LOOKUPS_VAL extends those codes with Student System–specific attributes that control how each code behaves during enrollment, tracking, transcript generation, and completion processing.

The table carries a documented foreign key relationship to FND_LOOKUPS_VAL, joined on the column pair (LOOKUP_TYPE, LOOKUP_CODE). This confirms that IGS_LOOKUPS_VAL does not define its own lookup taxonomy; it decorates and validates codes that already exist in the common FND lookup dictionary. The primary key is IGS_LOOKUPS_VAL_PK, comprising (LOOKUP_CODE, LOOKUP_TYPE). A second unique index, IGS_LOOKUPS_VAL_U1, is defined on the same two columns in reversed order (LOOKUP_TYPE, LOOKUP_CODE), giving a business-key candidate oriented toward type-first access paths.

From a Data Vault modeling perspective, the mined classification for this object is standalone. On the documented evidence — a composite natural key, descriptive and indicator attributes, and audit columns — the appropriate modeling suggestion is a satellite attached to a lookup-code hub keyed on (LOOKUP_TYPE, LOOKUP_CODE); the absence of outgoing foreign keys other than the FND reference makes a link classification unnecessary.

Key Information Stored

The physical schema documented for ETRM 12.1.1 lists 29 columns. The most significant for functional and reporting purposes are:

The surrogate/primary key is IGS_LOOKUPS_VAL_PK on (LOOKUP_CODE, LOOKUP_TYPE); the unique business-key candidate is IGS_LOOKUPS_VAL_U1 on (LOOKUP_TYPE, LOOKUP_CODE).

Common Use Cases and Queries

Typical usage centers on validating and presenting Student System lookup values. A frequent query retrieves all active codes for a given lookup type in display order:

  • SELECT lookup_code, display_name, display_order FROM igs_lookups_val WHERE lookup_type = :p_type AND NVL(closed_ind,'N') = 'N' ORDER BY NVL(display_order, default_display_seq);
  • Joining to the common lookup table to confirm that every Student System code has a parent definition: SELECT a.lookup_type, a.lookup_code, a.display_name FROM igs_lookups_val a, fnd_lookups_val b WHERE a.lookup_type = b.lookup_type AND a.lookup_code = b.lookup_code;
  • Identifying orphaned or undefined codes with an outer join on FND_LOOKUPS_VAL to detect configuration gaps.
  • Reporting on tracking and outcome configuration by filtering on S_TRACKING_TYPE, UNIT_OUTCOME_IND, or FINAL_RESULT_IND.
  • Auditing changes via LAST_UPDATED_BY and LAST_UPDATE_DATE for setup migration and reconciliation.

Related Objects

  • FND_LOOKUPS_VAL — the parent lookup validation table; joined on LOOKUP_TYPE and LOOKUP_CODE, the documented foreign key target.
  • FND_LOOKUPS — the base Application Object Library lookup dictionary supplying code definitions and meanings.
  • IGS_LOOKUPS_VAL_PK — the primary key constraint (LOOKUP_CODE, LOOKUP_TYPE).
  • IGS_LOOKUPS_VAL_U1 — the unique index on (LOOKUP_TYPE, LOOKUP_CODE), the principal access path for type-first lookups.
  • IGS_ENROLLMENTS and other IGS transaction tables — consume lookup codes for status, outcome, and tracking values.
  • IGS_SS_* Student System setup tables — reference lookup codes when configuring tracking types and completion rules.

Because the table is classified as standalone, it does not fan out to dependent child tables through its own foreign keys; its influence is expressed through the code values consumed elsewhere in the Student System.