Search Results ams_metrics_all_b

Overview

The AMS_METRICS_ALL_B table is a core data repository within the Oracle E-Business Suite (EBS) Marketing (AMS) module. It serves as the master definition table for all flexible metrics configured in the system. In the context of Oracle EBS 12.1.1 and 12.2.2, metrics are quantifiable measures used to track and analyze marketing performance, such as campaign response rates, lead conversion costs, or return on investment. This table stores the foundational metadata that defines each metric's characteristics, enabling the creation, calculation, and reporting of complex marketing analytics across the enterprise. Its role is central to the AMS module's analytical capabilities, providing the structural framework for performance measurement.

Key Information Stored

The table's primary key is the METRIC_ID, which uniquely identifies each metric definition. Key structural columns include METRIC_PARENT_ID and SUMMARY_METRIC_ID, which establish hierarchical relationships between metrics, allowing for the creation of derived or roll-up metrics. The METRIC_CATEGORY and METRIC_SUB_CATEGORY columns classify metrics, referencing the AMS_CATEGORIES_B table for standardized categorization. The HIERARCHY_ID links to AMS_HIERARCHIES_ALL_B, enabling metric organization within defined structures. The APPLICATION_ID column, referencing FND_APPLICATION, associates the metric with a specific EBS application. Other critical attributes stored for each definition include the metric's calculation status, data type, and configuration flags that control its behavior in formulas and reports.

Common Use Cases and Queries

A primary use case is the generation of reports on available metric definitions for system administration or user reference. Developers often query this table to understand metric relationships when building custom calculations or integrations. A common query retrieves all active metrics within a specific category:

• SELECT metric_id, name FROM ams_metrics_all_b WHERE metric_category = '&CATEGORY' AND status = 'ACTIVE';

Another frequent pattern involves tracing metric hierarchies to understand derivation logic, using self-joins on METRIC_PARENT_ID or SUMMARY_METRIC_ID. When troubleshooting data issues in marketing activity metrics (AMS_ACT_METRICS_ALL), queries join back to this table to validate the underlying metric definition's properties and status. The table is also central for any custom seeding or migration scripts involving metric setup data.

Related Objects

The AMS_METRICS_ALL_B table has extensive relationships within the AMS schema. The translated descriptions for metrics are stored in AMS_METRICS_ALL_TL. Key transactional child tables include AMS_ACT_METRICS_ALL and AMS_ACT_METRIC_HST, which store the actual calculated metric values for marketing activities. The AMS_METRIC_FORMULAS table references METRIC_ID to define calculation rules and can also use it as a SOURCE_ID. Template details for metrics are stored in AMS_MET_TPL_DETAILS. As indicated by the foreign keys, it maintains referential integrity with master tables AMS_CATEGORIES_B (for categories), FND_APPLICATION, and AMS_HIERARCHIES_ALL_B. The table also has recursive foreign key relationships to itself to support metric hierarchies.