Results for “ams_list_fields_vl”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_LIST_FIELDS_VL is a translatable (VL) view in the Oracle E-Business Suite Marketing (AMS) module, owned by the APPS schema and located in the Oracle EBS 12.1.1 and 12.2.2 environments. The view returns the translated fields for a list, presenting a language-aware, denormalized projection of list field metadata that blends the base transactional definition with its translated description. Its primary role is to serve as a reporting and integration access point that abstracts the underlying _B (base) and _TL (translation) table pair, exposing a single, translation-resolved row per list field for the current session language.

Because it is a VL view, it isolates consumers from the complexity of the multi-language table design. Applications such as Oracle Marketing, Oracle CRM, and any custom concurrent programs, OAF pages, or Discoverer reports can query AMS_LIST_FIELDS_VL and receive the description appropriate to the logged-in user's language without writing their own joins or USERENV logic. Status is documented as VALID, indicating the view is compiled and usable in the target release.

Underlying Base Objects

The ETRM metadata identifies two referenced base objects, both surfaced through synonyms in the APPS schema:

• AMS_LIST_FIELDS_B — the base table holding the non-translated, structural definition of each list field.
• AMS_LIST_FIELDS_TL — the translation table holding language-specific descriptive text.

The view is defined as an inner join between these two objects on LIST_FIELD_ID, filtered by T.LANGUAGE = USERENV('LANG'). This join restricts the result set to the row whose translation matches the language of the current database session, returning every descriptive (non-translatable) attribute from the base table alongside the translated DESCRIPTION. The ROW_ID exposed is the ROWID of the base table row (aliased B), which uniquely identifies the underlying physical row.

Key Columns

• ROW_ID — the ROWID of the AMS_LIST_FIELDS_B record, usable as a surrogate identifier in updates or drill-downs.
• LIST_FIELD_ID — the primary key linking base and translation records; the natural join key.
• FIELD_TABLE_NAME / FIELD_COLUMN_NAME — identify the underlying table and column the list field maps to, defining the field's data source.
• COLUMN_DATA_TYPE / COLUMN_DATA_LENGTH — metadata describing the stored value's datatype and length, useful for validation and dynamic query generation.
• ENABLED_FLAG — indicates whether the list field is active and available for use.
• LIST_TYPE_FIELD_APPLY_ON — defines the context or list type to which the field applies.
• DESCRIPTION — the translated display text drawn from AMS_LIST_FIELDS_TL.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER provide standard EBS audit and optimistic-locking support.

Common Use Cases and Queries

Typical scenarios include building LOVs for list field selection, generating validation logic for list upload interfaces, exposing field metadata to custom Marketing reports, and integrating list field definitions into external systems.

A straightforward lookup of enabled fields:

SELECT LIST_FIELD_ID, FIELD_TABLE_NAME, FIELD_COLUMN_NAME, DESCRIPTION
FROM   APPS.AMS_LIST_FIELDS_VL
WHERE  ENABLED_FLAG = 'Y';

Retrieving descriptive metadata for a known field:

SELECT LIST_FIELD_ID, COLUMN_DATA_TYPE, COLUMN_DATA_LENGTH, DESCRIPTION
FROM   APPS.AMS_LIST_FIELDS_VL
WHERE  LIST_FIELD_ID = :p_list_field_id;

Joining to list type usage:

SELECT LF.DESCRIPTION, LF.LIST_TYPE_FIELD_APPLY_ON
FROM   APPS.AMS_LIST_FIELDS_VL LF
WHERE  LF.ENABLED_FLAG = 'Y'
ORDER  BY LF.DESCRIPTION;

Because the view filters on the session language, no additional language predicate is required. Organizations operating in multiple languages receive translated descriptions automatically, making AMS_LIST_FIELDS_VL a reliable and preferred interface for querying Marketing list field definitions.