Search Results gem_lookups




Overview
Explore in AI Chat

Overview

GEM_LOOKUPS is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It belongs to the GMA product family — Process Manufacturing Systems (OPM) — and is documented as a consolidated "View of all OPM Lookups." Its purpose is to expose reference and validation data that Oracle Process Manufacturing uses throughout its forms, concurrent programs, and integrations, without requiring callers to query the underlying Oracle Common Application Components (FND) lookup table directly.

In EBS reporting and integration scenarios, GEM_LOOKUPS functions as a filtered, language-aware projection of lookup values. Rather than retrieving every lookup defined anywhere in the applications, it restricts output to lookup types belonging to the OPM application range, making it a convenient and safe source for process manufacturing reference data. Its status is VALID in both the 12.1.1 and 12.2.2 releases, and the documented view text is consistent across those versions.

Underlying Base Objects

The view is defined over a single base object: FND_LOOKUP_VALUES, referenced through a synonym in the APPS schema. FND_LOOKUP_VALUES is the standard Oracle Applications table that stores all lookup codes, their meanings, descriptions, effective dates, and enabled status, keyed by lookup type and language.

GEM_LOOKUPS does not join or aggregate; it is a simple restricted projection. Three predicates define its scope:

  • Language filtering — LANGUAGE = USERENV('LANG') returns lookup descriptions in the session's current language, so reports automatically reflect the user's assigned language.
  • Application scoping — VIEW_APPLICATION_ID BETWEEN 550 AND 559 limits results to the application ID range assigned to the OPM/GMA product family.
  • Security group — SECURITY_GROUP_ID = 0 returns only the standard, non-seeded-alternate lookup values.

Key Columns

The view exposes seven columns, all drawn from FND_LOOKUP_VALUES:

  • LOOKUP_TYPE — The lookup category (for example, a quality or inventory-related type). Used to group related codes.
  • LOOKUP_CODE — The stored value referenced by OPM transactions and concurrent programs. This is the code persisted on transactional records.
  • MEANING — The user-facing, translatable label corresponding to the code.
  • DESCRIPTION — Optional extended text describing the lookup value.
  • ENABLED_FLAG — 'Y' or 'N'; indicates whether the value is currently active for selection.
  • START_DATE_ACTIVE — The date from which the lookup value becomes valid.
  • END_DATE_ACTIVE — The date on which the lookup value expires; null when open-ended.

Common Use Cases and Queries

Typical uses include building reporting LOVs, cross-referencing stored codes to their meanings, validating integration payloads, and auditing which OPM lookups are currently enabled.

Listing all active OPM lookups for a specific type:

  • SELECT lookup_code, meaning, description FROM apps.gem_lookups WHERE lookup_type = :p_type AND enabled_flag = 'Y' AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE)) AND NVL(end_date_active, TRUNC(SYSDATE));

Resolving a stored code to its meaning for a report join:

  • SELECT t.transaction_id, t.status_code, l.meaning AS status_meaning FROM opm_transactions t, apps.gem_lookups l WHERE l.lookup_type = 'OPM_STATUS' AND l.lookup_code = t.status_code;

Because the view already applies language and application filters, queries written against GEM_LOOKUPS avoid the pitfalls of unfiltered FND_LOOKUP_VALUES access and remain portable across EBS 12.1.1 and 12.2.2.