Search Results ams_discoverer_sql




Overview
Explore in AI Chat

Overview

The AMS_DISCOVERER_SQL table is a core data repository within the Oracle E-Business Suite Marketing (AMS) module. It functions as the persistent storage layer for SQL statements generated and saved by the Oracle Discoverer reporting tool. These stored SQL statements correspond directly to specific worksheets and workbooks created within Discoverer. The primary role of this table is to support critical marketing automation processes, namely target list generation and campaign execution, by providing a reusable, database-resident definition of complex customer or prospect selection logic defined by business users through a graphical interface.

Key Information Stored

The table's structure is designed to uniquely identify and store the SQL for a Discoverer worksheet. Its primary key is the system-generated identifier DISCOVERER_SQL_ID. A unique key constraint ensures the precise identification of a worksheet through a combination of columns: WORKBOOK_OWNER_NAME, WORKBOOK_NAME, WORKSHEET_NAME, and SEQUENCE_ORDER. This combination allows the system to manage multiple worksheets within workbooks owned by different users. The table's central column, not explicitly named in the metadata but implied by the description, holds the actual SQL statement text. The SEQUENCE_ORDER column suggests the capability to store SQL statements that may be segmented or ordered across multiple rows for a single worksheet.

Common Use Cases and Queries

The principal use case is the execution of a saved Discoverer worksheet query to populate a marketing target list. A marketing manager might define a customer segment using Discoverer, save it, and then select that saved worksheet within a campaign to generate the recipient list. Common administrative queries include auditing usage and diagnosing issues. For instance, identifying all worksheets owned by a specific user or locating the SQL for a particular campaign activity. A sample query to retrieve the foundational SQL for a target list might be:

Performance tuning may also involve querying this table to analyze and optimize the underlying SQL of frequently used, resource-intensive target lists.

Related Objects

The AMS_DISCOVERER_SQL table is integral to the marketing module's data model, as evidenced by its foreign key relationships. The AMS_ACT_DISCOVERER_ALL table references it via the DISCOVERER_SQL_ID column, linking saved SQL to specific marketing activities. More significantly, the AMS_LIST_SELECT_ACTIONS table references it through the INCL_OBJECT_ID column, which directly ties the stored SQL to the mechanics of list selection and generation for campaigns. These relationships confirm the table's operational purpose: the SQL definitions stored within AMS_DISCOVERER_SQL are invoked and executed by the campaign engine to build dynamic target audiences.