Search Results ams_discoverer_sql

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:

SELECT sql_statement_text FROM ams_discoverer_sql WHERE workbook_owner_name = 'MARKETING_USER' AND workbook_name = 'Q4_CAMPAIGN' AND worksheet_name = 'PREMIUM_CUSTOMERS' ORDER BY sequence_order;

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.

Table: AMS_DISCOVERER_SQL 12.2.2
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_DISCOVERER_SQL, object_name:AMS_DISCOVERER_SQL, status:VALID, product: AMS - Marketing , description: This table stores all SQL statements saved into the database by the Discoverer tool, these SQL statements correspond to specific worksheets/workbooks created through the tool. This table is used by target list generation and the cell defi , implementation_dba_data: AMS.AMS_DISCOVERER_SQL ,
Table: AMS_DISCOVERER_SQL 12.1.1
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_DISCOVERER_SQL, object_name:AMS_DISCOVERER_SQL, status:VALID, product: AMS - Marketing , description: This table stores all SQL statements saved into the database by the Discoverer tool, these SQL statements correspond to specific worksheets/workbooks created through the tool. This table is used by target list generation and the cell defi , implementation_dba_data: AMS.AMS_DISCOVERER_SQL ,
View: AMS_P_DISCOVERER_SQL_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:AMS.AMS_P_DISCOVERER_SQL_V, object_name:AMS_P_DISCOVERER_SQL_V, status:VALID, product: AMS - Marketing , description: This public view returns the distinct set of WorkBook and WorkSheet names which are stored in the AMS_DISCOVERER_SQL table. , implementation_dba_data: APPS.AMS_P_DISCOVERER_SQL_V ,
View: AMS_P_DISCOVERER_SQL_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:AMS.AMS_P_DISCOVERER_SQL_V, object_name:AMS_P_DISCOVERER_SQL_V, status:VALID, product: AMS - Marketing , description: This public view returns the distinct set of WorkBook and WorkSheet names which are stored in the AMS_DISCOVERER_SQL table. , implementation_dba_data: APPS.AMS_P_DISCOVERER_SQL_V ,
Table: AMS_ACT_DISCOVERER_ALL 12.2.2
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_ACT_DISCOVERER_ALL, object_name:AMS_ACT_DISCOVERER_ALL, status:VALID, product: AMS - Marketing , description: Store all workbooks/sheets that together define a Cell. , implementation_dba_data: AMS.AMS_ACT_DISCOVERER_ALL ,
Table: AMS_ACT_DISCOVERER_ALL 12.1.1
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_ACT_DISCOVERER_ALL, object_name:AMS_ACT_DISCOVERER_ALL, status:VALID, product: AMS - Marketing , description: Store all workbooks/sheets that together define a Cell. , implementation_dba_data: AMS.AMS_ACT_DISCOVERER_ALL ,
Table: AMS_LIST_SELECT_ACTIONS 12.2.2
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_LIST_SELECT_ACTIONS, object_name:AMS_LIST_SELECT_ACTIONS, status:VALID, product: AMS - Marketing , description: This table stores the set of discoverer workbook, target segments and existing lists which are used to generate the set of list entries. , implementation_dba_data: AMS.AMS_LIST_SELECT_ACTIONS ,
Table: AMS_LIST_SELECT_ACTIONS 12.1.1
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_LIST_SELECT_ACTIONS, object_name:AMS_LIST_SELECT_ACTIONS, status:VALID, product: AMS - Marketing , description: This table stores the set of discoverer workbook, target segments and existing lists which are used to generate the set of list entries. , implementation_dba_data: AMS.AMS_LIST_SELECT_ACTIONS ,