Search Results ams_act_discoverer_all




Overview
Explore in AI Chat

Overview

The table AMS_ACT_DISCOVERER_ALL is a core data object within the Oracle E-Business Suite Marketing (AMS) module, specifically for versions 12.1.1 and 12.2.2. It functions as a repository for metadata that defines analytical components, known as Cells, within the context of Oracle Discoverer workbooks. Its primary role is to store the mapping between a marketing Cell and the specific Discoverer workbooks and worksheets that constitute its analytical definition. This table is integral to the integration between the Marketing module's activity management and the Oracle Discoverer business intelligence tool, enabling the execution and reporting of complex marketing analyses directly within the EBS framework.

Key Information Stored

The table stores identifiers and descriptive attributes that link Discoverer objects to marketing entities. The primary columns include the surrogate key ACTIVITY_DISCOVERER_ID. A critical unique key constraint (AMS_ACT_DISCOVERER_ALL_UK1) is defined on a combination of columns that uniquely identifies a workbook's usage: ACT_DISCOVERER_USED_BY_ID (the ID of the associated Cell), ARC_ACT_DISCOVERER_USED_BY (likely a discriminator column indicating the object type), and WORKBOOK_NAME. The foreign key column DISCOVERER_SQL_ID links to the AMS_DISCOVERER_SQL table, which presumably stores the underlying SQL or query definitions for the Discoverer objects, thereby separating the logical workbook definition from its physical implementation.

Common Use Cases and Queries

This table is central to queries that audit or troubleshoot the Discoverer-based components of marketing campaigns and activities. A common use case involves identifying all Discoverer workbooks associated with a specific marketing Cell for impact analysis during upgrades or migrations. For example, to list the workbook definitions for a given Cell ID, one might execute:

  • SELECT workbook_name, discoverer_sql_id FROM ams_act_discoverer_all WHERE act_discoverer_used_by_id = <cell_id>;

Another typical scenario is generating a report of all Cells and their dependent Discoverer objects for documentation or validation purposes, requiring a join to AMS_CELLS_ALL_B to resolve the Cell's name or code.

Related Objects

As documented in the ETRM metadata, AMS_ACT_DISCOVERER_ALL has defined relationships with two key tables via foreign key constraints:

  • AMS_DISCOVERER_SQL: Joined via the DISCOVERER_SQL_ID column. This table stores the SQL query definitions or metadata for the Discoverer components referenced in the workbooks.
  • AMS_CELLS_ALL_B: Joined via the ACT_DISCOVERER_USED_BY_ID column. This is the master table for marketing Cells, and this relationship establishes which Cell a given Discoverer workbook definition belongs to.

These relationships are essential for constructing complete queries that retrieve not only the workbook names but also the underlying SQL logic and the full descriptive details of the associated marketing Cell.