Search Results ams_list_queries_tl




Overview
Explore in AI Chat

Overview

AMS_LIST_QUERIES_TL is a translation (TL) table in the AMS (Marketing) schema of Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. It stores the language-specific name and description of predefined SQL criteria used for list creation within Oracle Marketing and Oracle Trade Management list generation. Each row represents the translated attribute text for a query definition held in the corresponding base table, AMS_LIST_QUERIES_ALL. The table is delivered as Oracle proprietary and confidential, and it forms part of the metadata layer that describes the selectable query criteria presented to end users when building marketing lists, target groups, and audience segments.

From a heuristic Data Vault modeling perspective, this object is best classified as a standalone satellite. It carries descriptive, language-dependent attributes (name and description) that hang off a parent key represented by the list query identifier, with no independent business identity of its own and no downstream foreign keys pointing into it. The classification is a suggestion derived from the mined FK structure; the table is documented as standalone, meaning it is not a link or a hub.

Key Information Stored

The eleven documented columns split into a surrogate/business key group, descriptive text, and audit columns.

  • LIST_QUERY_ID — the identifier of the parent query definition. It is a foreign key to AMS_LIST_QUERIES_ALL.LIST_QUERY_ID and the leading column of the table's unique index.
  • LANGUAGE — the language code for the translated row. Together with LIST_QUERY_ID it forms the natural business key of a translation record.
  • SOURCE_LANG — the language in which the source name and description were originally entered, used by the translation framework to determine default display values.
  • ZD_EDITION_NAME — the edition discriminator that supports the multi-tenant, edition-based redefinition model introduced for 12.2.x. The unique index AMS_LIST_QUERIES_TL_U1 covers (LIST_QUERY_ID, LANGUAGE, ZD_EDITION_NAME), making this trio the documented business-key candidate and the correct lookup path for any query against the table.
  • NAME and DESCRIPTION — the translated display text for the predefined SQL criteria. These are the payload attributes surfaced in the list-building user interface and in reporting on available query definitions.

Five standard audit columns complete the structure: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. These are the conventional Oracle Applications who-columns and should not be treated as business attributes. Note that no surrogate single-column primary key is documented separately from LIST_QUERY_ID; the row identity is effectively driven by the combination of LIST_QUERY_ID, LANGUAGE, and ZD_EDITION_NAME.

Common Use Cases and Queries

The table is consumed primarily through the AMS list-generation flow, where it supplies localized labels for predefined queries. Typical query patterns join it to the base table and filter by language and edition.

  • Retrieving active translations for a query: select LIST_QUERY_ID, NAME, DESCRIPTION, LANGUAGE from AMS_LIST_QUERIES_TL where LIST_QUERY_ID = :p_id and LANGUAGE = userenv('LANG'). Filtering by LANGUAGE is essential; omitting it returns one row per installed language.
  • Joining to the base definition: join on AMS_LIST_QUERIES_ALL a, AMS_LIST_QUERIES_TL t where t.LIST_QUERY_ID = a.LIST_QUERY_ID to retrieve the query logic alongside its translated name.
  • Reporting on translation coverage: count distinct LANGUAGE values per LIST_QUERY_ID to detect queries missing a translation for a required language — a common pre-go-live validation for multi-language deployments.
  • Audit and change tracking: use LAST_UPDATE_DATE and LAST_UPDATED_BY to identify recently modified labels, for example where LAST_UPDATE_DATE > sysdate - 30.

In 12.2.x environments, queries should also be mindful of ZD_EDITION_NAME so that rows from the correct edition are returned.

Related Objects

The documented relationship data shows one outbound FK and characterizes the table as standalone, so the related-object list is short.

  • AMS_LIST_QUERIES_ALL — the parent base table holding the query definitions; joined via LIST_QUERY_ID and the authoritative source of the query itself.
  • AMS_LIST_QUERIES_TL_U1 — the unique index on (LIST_QUERY_ID, LANGUAGE, ZD_EDITION_NAME) and the principal access path for lookups.
  • AMS_LIST_HEADERS_ALL and related list base tables — the marketing list structures whose criteria ultimately resolve to these predefined queries, joined through the list-to-query configuration in the AMS schema.
  • FND_LANGUAGES — the language reference underlying the LANGUAGE and SOURCE_LANG columns.
  • AMS list-building concurrent programs and the Marketing list creation UI — the runtime consumers that read localized NAME and DESCRIPTION values.