Search Results ams_lookups




Overview
Explore in AI Chat

Overview

AMS_LOOKUPS is a Marketing (AMS) module view owned by the APPS schema. Its purpose is to expose Oracle Marketing–specific lookup values by filtering the generic EBS lookup repository. The view satisfies the standard Fast Formula and code convention of providing a product-scoped lookup view over FND_LOOKUP_VALUES, ensuring that consumer logic only sees lookup types and codes registered to the Marketing application.

In Oracle EBS reporting and integration contexts, AMS_LOOKUPS functions as a read-only reference source. Reports, concurrent programs, forms, and external interfaces query it to translate stored lookup codes (such as campaign status, channel type, or activity outcome) into user-facing meanings without having to duplicate the filtering logic for application ID and language. Because the view applies USERENV('LANG'), returned MEANING values automatically respect the session language, which is essential for multilingual deployments.

Underlying Base Objects

The view is defined over a single documented base object: FND_LOOKUP_VALUES, accessed through its APPS synonym. FND_LOOKUP_VALUES is the core EBS table that stores every lookup code for every application, partitioned by VIEW_APPLICATION_ID, LANGUAGE, and SECURITY_GROUP_ID.

The view text applies three deterministic filters:

  • VIEW_APPLICATION_ID = 530 — the application ID assigned to Oracle Marketing (AMS).
  • SECURITY_GROUP_ID = 0 — restricts to the standard, non-secured lookup rows.
  • LANGUAGE = USERENV('LANG') — returns only the row set matching the current session language.

These predicates mean AMS_LOOKUPS is a strict subset of FND_LOOKUP_VALUES. No join to FND_APPLICATION or any other table occurs inside the view; the application-ID filter is hard-coded rather than resolved dynamically. Consequently, any change to a Marketing lookup in the base table is immediately visible through the view, and no materialization or caching layer intervenes.

Key Columns

The view projects eight columns, mirroring the standard lookup interface:

  • LOOKUP_TYPE — the grouping key (for example, a campaign or channel-related lookup type).
  • LOOKUP_CODE — the stored, language-independent code value.
  • MEANING — the translatable display text for the current language; this is what end users typically see.
  • DESCRIPTION — optional longer explanatory text.
  • ENABLED_FLAG — 'Y' or 'N'; indicates whether the lookup value is active. Only enabled rows should normally be presented.
  • START_DATE_ACTIVE and END_DATE_ACTIVE — the effective date range governing when the lookup value may be used.
  • TAG — a free-form attribute used by some products for additional classification.

Note that the surrogate primary key (LOOKUP_CODE_ID) of FND_LOOKUP_VALUES is not exposed, so the view is unsuitable as a foreign-key target in custom data models.

Common Use Cases and Queries

Typical uses include report parameter lists, integration mapping tables, and validation logic within Marketing-adjacent customizations. A standard query retrieving active Marketing lookups of a given type is:

SELECT LOOKUP_CODE, MEANING, DESCRIPTION
FROM APPS.AMS_LOOKUPS
WHERE LOOKUP_TYPE = :p_lookup_type
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE)
ORDER BY MEANING;

A join pattern pairing stored codes with their meanings is equally common:

SELECT t.campaign_code, l.MEANING
FROM my_campaign_table t, APPS.AMS_LOOKUPS l
WHERE l.LOOKUP_TYPE = 'MY_CAMPAIGN_TYPE'
AND l.LOOKUP_CODE = t.campaign_code;

Because the view is date- and language-sensitive, callers must apply the ENABLED_FLAG and effective-date predicates explicitly; the view itself does not restrict to currently active rows. For cross-product reporting that must span Marketing and non-Marketing lookups, querying FND_LOOKUP_VALUES directly with the desired VIEW_APPLICATION_ID remains the recommended alternative.