Search Results gms_lookups




Overview
Explore in AI Chat

Overview

GMS_LOOKUPS is a VALID database view owned by the APPS schema within Oracle E-Business Suite. It belongs to the GMS - Grants Accounting product family and serves as a curated, application-scoped window onto the shared Oracle EBS lookup repository. Rather than exposing the entire FND_LOOKUPS / FND_LOOKUP_VALUES data set, the view restricts output to lookup values that belong specifically to the Grants Accounting application context. This makes it the canonical reference point for any report, concurrent program, form, or integration that needs to enumerate the valid lookup codes GMS relies upon without having to hard-code the owning application identifier.

Because it is a view rather than a table, GMS_LOOKUPS stores no data of its own. It is a read-only projection, which means all values returned reflect the live, current state of the underlying lookup definitions at query time. This is important for reporting accuracy: if a lookup value is disabled or its active date range changes, subsequent queries against the view immediately reflect that change without any materialization or refresh step.

Underlying Base Objects

The view is defined over a single documented base object, FND_LOOKUP_VALUES, which is referenced in the Apps schema through a synonym. FND_LOOKUP_VALUES is the standard EBS table that holds individual lookup code entries, including their meaning, description, enabled status, and effective date range, for every lookup type registered across the suite.

The view text applies three deterministic filters to narrow that broad table down to Grants Accounting scope:

  • Language filter: LV.LANGUAGE = USERENV('LANG') restricts rows to the language of the current session, so users see meanings and descriptions in their own language rather than in every installed language.
  • Application filter: LV.VIEW_APPLICATION_ID = 8402 scopes the results to the Grants Accounting application ID (8402).
  • Security filter: LV.SECURITY_GROUP_ID = 0 confines output to the standard, non-secured lookup set.

Note that the view does not join to FND_LOOKUP_TYPES; the lookup type is carried directly on each value row, which keeps the definition simple and efficient for high-volume reporting.

Key Columns

The view exposes a compact, seven-column projection:

  • LOOKUP_TYPE — the functional category of the lookup (for example, a GMS-specific code set), used to group related values.
  • LOOKUP_CODE — the stored, language-independent code value; this is what is typically persisted on transactional records.
  • MEANING — the user-facing, translatable label displayed for the code.
  • DESCRIPTION — supplementary free-text detail about the lookup value.
  • ENABLED_FLAG — indicates whether the value is currently active (Y) or disabled (N).
  • START_DATE_ACTIVE — the date from which the value becomes selectable.
  • END_DATE_ACTIVE — the date after which the value is no longer selectable; a null value indicates no expiry.

Common Use Cases and Queries

The view is most often used to translate a stored GMS lookup code into its display meaning in reports and extracts, and to populate selection lists in custom concurrent programs. A typical query joins the view to a transactional table on LOOKUP_CODE, filtering by the relevant LOOKUP_TYPE and by current active dates to avoid retiring values.

A basic enumeration of enabled values for a given lookup type:

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

A decoding join against a transactional source:

SELECT t.award_id, l.meaning AS status_meaning
FROM apps.gms_awards t, apps.gms_lookups l
WHERE t.status_code = l.lookup_code
AND l.lookup_type = 'GMS_AWARD_STATUS';

As with all applications-owned views, access should be granted indirectly through the APPS schema, and dependent reports should account for the language and enabled-flag filters already applied internally by the view.