Search Results gem_lookup_values




Overview
Explore in AI Chat

Overview

GEM_LOOKUP_VALUES is a reference data table within the Oracle E-Business Suite Process Manufacturing Systems module (GMA). It stores the individual list of values associated with each lookup type defined in the Process Manufacturing environment. Conceptually, it functions as the value-level counterpart to GEM_LOOKUP_TYPES: where a lookup type defines a category or classification, GEM_LOOKUP_VALUES enumerates the permissible codes that belong to that category. These coded values drive picklists, validation lists, and coded references throughout Process Manufacturing forms and concurrent programs.

In Data Vault modeling terms, the heuristic classification mined from the foreign key structure places this table as satellite-leaning. Because GEM_LOOKUP_VALUES carries descriptive, attribute-like content (the enumerated lookup codes) keyed by a parent business key (LOOKUP_TYPE), it behaves much like a satellite attached to the GEM_LOOKUP_TYPES hub. Analysts designing a dimensional or Data Vault representation should treat it accordingly, with LOOKUP_TYPE as the driving business key and LOOKUP_CODE as the granular descriptive element.

Key Information Stored

The metadata documents a compact structure centered on two business-meaningful columns that together form the primary key:

  • LOOKUP_TYPE — Identifies the parent lookup type to which the value belongs. This column is also the sole documented foreign key, referencing GEM_LOOKUP_TYPES.LOOKUP_TYPE, which enforces referential integrity between a value and its category.
  • LOOKUP_CODE — The coded value itself, representing an individual permissible entry within the associated lookup type.

The primary key GEM_LOOKUP_VALUES_PK is a composite key defined on (LOOKUP_TYPE, LOOKUP_CODE). Notably, the metadata does not document a separate surrogate primary key column or any additional unique indexes beyond this composite business key. Both LOOKUP_TYPE and LOOKUP_CODE therefore serve as business-key candidates. Additional descriptive attributes commonly found in EBS lookup structures, such as enabled flags, display descriptions, or effective dating, are not enumerated in the provided metadata and should be confirmed against the physical table definition before being relied upon.

Common Use Cases and Queries

The principal use case is resolving coded values into human-readable selections and validating incoming data against the permitted set for a given lookup type. A typical retrieval pattern joins the values table to its parent type table:

  • Listing all values for a specific type: SELECT lookup_code FROM gem_lookup_values WHERE lookup_type = :type
  • Joining to the parent type: SELECT lt.lookup_type, lv.lookup_code FROM gem_lookup_types lt JOIN gem_lookup_values lv ON lt.lookup_type = lv.lookup_type

Reporting scenarios include populating selection lists in custom concurrent programs, auditing which codes are configured for a given type, and cross-referencing coded Process Manufacturing records against their allowed value sets. Because the table is reference-oriented and relatively static, it is frequently joined into analytical extracts to translate stored codes into descriptive labels.

Related Objects

The most significant related object is the parent table in the documented foreign key relationship:

  • GEM_LOOKUP_TYPES — joined on GEM_LOOKUP_VALUES.LOOKUP_TYPE = GEM_LOOKUP_TYPES.LOOKUP_TYPE. This parent table defines the categories that qualify the values stored here and is the primary dependency.

Beyond this documented relationship, GEM_LOOKUP_VALUES is typically consumed by Process Manufacturing forms, validation routines, and inquiry screens that reference the GMA lookup framework. The provided metadata does not enumerate additional dependent views or APIs, so integrators should verify downstream consumers against the application's lookup framework rather than assuming a fixed list. Note that the implementation section states this table is not implemented in the reference database, indicating availability depends on the specific Process Manufacturing configuration.