Search Results gem_lookup_types

Overview

GEM_LOOKUP_TYPES is the Oracle Process Manufacturing (OPM) lookup type definition table, historically known as the System Types table. It belongs to the GMA (Process Manufacturing Systems) product family and serves as the master registry of all lookup categories used throughout the OPM application suite. Each row defines a distinct lookup type, providing a controlled vocabulary container that the rest of the application references when validating coded values.

In practical terms, GEM_LOOKUP_TYPES corresponds to the generic lookup mechanism used by OPM to categorize valid code values — for example, quality statuses, batch statuses, or inventory transaction reasons. The companion table GEM_LOOKUP_VALUES stores the individual permitted values that belong to each type, so GEM_LOOKUP_TYPES acts as the parent or header of a parent-child relationship.

From a data-modeling standpoint, the metadata classifies this object as hub-leaning within a Data Vault heuristic model. This suggests treating LOOKUP_TYPE as a durable business key housed in a hub entity, with descriptive attributes optionally modeled as a satellite. The classification is a modeling suggestion derived from the foreign-key topology rather than a documented physical artifact.

Key Information Stored

The documented metadata exposes a deliberately narrow column set, anchored on the primary key.

• LOOKUP_TYPE — the single documented column and the sole component of the primary key GEM_LOOKUP_TYPES_PK. It holds the alphanumeric identifier that uniquely names each lookup category (for instance, a status class or reason category).

Because the excerpt lists only LOOKUP_TYPE, no separate surrogate key column is documented; the primary key is entirely business-natured. LOOKUP_TYPE therefore functions simultaneously as the primary key and the principal business-key candidate. In the wider OPM schema, such setup tables typically also carry descriptive attributes (a display name, description, and enablement flag), but those columns are not confirmed in the provided metadata and should be verified against the 12.1.1 / 12.2.2 dictionary before use.

The metadata records that the table is not implemented in the surveyed database, meaning the object is defined in the application's logical model but absent from that particular instance. Its structure remains valid as a reference definition.

Common Use Cases and Queries

The table is primarily used to enumerate and validate the lookup categories available to a given OPM installation. Typical reporting joins it to its child to count values per type or to find types with no values assigned.

• List all lookup types: SELECT LOOKUP_TYPE FROM GEM_LOOKUP_TYPES ORDER BY LOOKUP_TYPE;
• Count values per type: SELECT t.LOOKUP_TYPE, COUNT(v.LOOKUP_TYPE) FROM GEM_LOOKUP_TYPES t LEFT JOIN GEM_LOOKUP_VALUES v ON v.LOOKUP_TYPE = t.LOOKUP_TYPE GROUP BY t.LOOKUP_TYPE;
• Find orphaned values (referential-integrity audit): SELECT v.* FROM GEM_LOOKUP_VALUES v WHERE NOT EXISTS (SELECT 1 FROM GEM_LOOKUP_TYPES t WHERE t.LOOKUP_TYPE = v.LOOKUP_TYPE);

These patterns support setup validation, data migration reconciliation, and configuration review ahead of an upgrade from 12.1.1 to 12.2.2.

Related Objects

The documented foreign-key relationship identifies one dependent object:

• GEM_LOOKUP_VALUES — references GEM_LOOKUP_TYPES via the column pair GEM_LOOKUP_VALUES.LOOKUP_TYPE → GEM_LOOKUP_TYPES.LOOKUP_TYPE. This is the child table holding the actual permitted values for each type.

Together, GEM_LOOKUP_TYPES and GEM_LOOKUP_VALUES form the OPM generic lookup framework. Application screens, concurrent programs, and validation routines across the GMA modules resolve codes by joining these two tables on LOOKUP_TYPE.