Search Results ams_attb_lov

Overview

AMS.AMS_LIST_QUERIES_PARAM is a transactional configuration table in the Oracle E-Business Suite 12.1.1 and 12.2.2 Advanced Marketing (AMS) schema. It stores the individual parameter definitions associated with list queries created in the Audience Workbench, the marketing module used to build targeted audience lists from customer, prospect, and transaction data. Each row represents one parameter belonging to a saved list query, capturing the parameter's name, ordering position, comparison value, and optional list of values binding.

The object resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. Under the heuristic Data Vault classification derived from its foreign key structure, the table is assessed as standalone. In practice, however, the presence of a foreign key to AMS_LIST_QUERIES_ALL and a unique surrogate key suggests it would most naturally be modeled as a satellite attached to the list query hub or link, since it holds descriptive, mutable attributes of a parent query rather than independent business entities.

Key Information Stored

• LIST_QUERY_PARAM_ID — Surrogate primary key and the single column of unique index AMS_LIST_QUERIES_PARAM_U1. Uniquely identifies each parameter row.
• LIST_QUERY_ID — Foreign key to AMS_LIST_QUERIES_ALL. Identifies the parent list query to which the parameter belongs; the principal join column in most reporting queries.
• PARAMETER_NAME — VARCHAR2(240) holding the display name of the query parameter.
• PARAMETER_ORDER — Numeric position controlling the sequence in which parameters appear in the query definition.
• PARAMETER_VALUE — VARCHAR2(150) storing the primary value supplied for the parameter.
• PARAM_VALUE_2 — VARCHAR2(150) holding the second value when the condition is a range (for example, a BETWEEN clause).
• CONDITION_VALUE — VARCHAR2(300) recording the comparison operator applied, such as between, >, or <.
• ATTB_LOV_ID — Foreign key to AMS_ATTB_LOV; populated when the parameter is bound to a list of values.
• OBJECT_VERSION_NUMBER — Optimistic locking column used during concurrent updates.
• WHO columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard audit lineage.

Three non-unique indexes support access paths: AMS_LIST_QUERIES_PARAM_N1 on PARAMETER_ORDER, LIST_QUERY_ID, and PARAMETER_VALUE; AMS_LIST_QUERIES_PARAM_N2 on LIST_QUERY_ID; and AMS_LIST_QUERIES_PARAM_N3 on ATTB_LOV_ID. In the documented 12.2.2 physical schema, a fifteenth column, ZD_EDITION_NAME, appears in the unique index and supports edition-based redefinition.

Common Use Cases and Queries

The table is typically queried to audit or reconstruct the runtime behavior of a list query: Which parameters does a given audience query use? What values and operators were configured? Which parameters are backed by a list of values?

A representative pattern retrieves all parameters for a query in display order:

• SELECT PARAMETER_NAME, PARAMETER_VALUE, PARAM_VALUE_2, CONDITION_VALUE FROM AMS.AMS_LIST_QUERIES_PARAM WHERE LIST_QUERY_ID = :p_query_id ORDER BY PARAMETER_ORDER;
• Joining back to AMS_LIST_QUERIES_ALL to move from a query name to its parameters.
• Joining ATTB_LOV_ID to AMS_ATTB_LOV to resolve the source list of values for a bound parameter.

Reporting uses include parameter usage analysis across all list queries, identifying queries with range conditions, and migration or reconciliation scripts when moving audience definitions between environments.

Related Objects

• AMS.AMS_LIST_QUERIES_ALL — Parent table referenced by LIST_QUERY_ID; the primary dependency of this object.
• AMS.AMS_ATTB_LOV — Referenced by ATTB_LOV_ID; supplies list-of-values definitions for parameters.
• AMS.AMS_LIST_QUERIES_PARAM_U1 / _N1 / _N2 / _N3 — Indexes that support uniqueness, ordering lookups, and LOV resolution.
• Audience Workbench UI — The functional entry point that creates and maintains rows in this table.
• AMS_LIST_QUERIES_PARAM query view — The documented SELECT projection over all fifteen columns used by ETRM developers.