Search Results ams_list_queries_all




Overview
Explore in AI Chat

Overview

The AMS_LIST_QUERIES_ALL table is a core data object within the Oracle E-Business Suite (EBS) Marketing (AMS) module, specifically in versions 12.1.1 and 12.2.2. As documented in the ETRM, its primary function is to store the predefined criteria for list creation. This table acts as the central repository for saved query definitions that marketing users leverage to segment and target customers, prospects, or other entities for campaigns, communications, and analytics. Its role is foundational to the list management and audience targeting capabilities of Oracle Marketing, enabling the systematic and reusable definition of target populations based on complex business rules and attribute filters.

Key Information Stored

The table's structure is designed to persist the essential components of a saved list query. The primary key column, LIST_QUERY_ID, uniquely identifies each predefined criteria set. While the full column list is not detailed in the provided excerpt, based on its described purpose, the table typically stores metadata such as the query name, description, creation date, last update date, and the user who created it. Crucially, it stores the structured definition of the selection criteria itself, which may include references to database views, logical conditions, joined attributes, and sorting instructions. This allows the marketing application to reconstruct and execute the segmentation logic on demand without requiring users to re-specify complex filters manually.

Common Use Cases and Queries

The primary use case is the creation and execution of targeted marketing lists. A common operational flow involves a user creating a list within the AMS application interface, which results in an INSERT into this table. Subsequent list refreshes or campaign launches will query this table to retrieve the criteria definition for execution. From a reporting and administrative perspective, common SQL queries might include auditing all saved queries in the system or identifying queries created by a specific user. A sample query pattern would be: SELECT list_query_id, name, creation_date, last_update_date FROM ams_list_queries_all WHERE created_by = :user_id ORDER BY creation_date DESC;. Data warehouse extracts for marketing performance analysis may also join this table to list execution history tables to correlate list criteria with campaign outcomes.

Related Objects

As per the documented relationship data, AMS_LIST_QUERIES_ALL has defined foreign key dependencies. The table's primary key, LIST_QUERY_ID, is referenced by at least one other critical table in the AMS schema:

  • AMS_LIST_SELECT_ACTIONS: This table references AMS_LIST_QUERIES_ALL via the foreign key column INCL_OBJECT_ID. This relationship suggests that saved list queries (LIST_QUERY_ID) are used as inclusion rules or selection steps within broader list generation or campaign action workflows. A typical join for analysis would be: SELECT * FROM ams_list_queries_all q, ams_list_select_actions a WHERE q.list_query_id = a.incl_object_id.
This relationship underscores the table's integration into the operational workflow of building and executing marketing lists, where a saved query becomes a reusable component in a larger selection process.