Search Results ams_metrics_all_b




Overview
Explore in AI Chat

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.

  • Table: AMS_METRICS_ALL_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_METRICS_ALL_B,  object_name:AMS_METRICS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: This table stores all the flexible metric definitions. ,  implementation_dba_data: AMS.AMS_METRICS_ALL_B , 

  • Table: AMS_METRICS_ALL_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_METRICS_ALL_B,  object_name:AMS_METRICS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: This table stores all the flexible metric definitions. ,  implementation_dba_data: AMS.AMS_METRICS_ALL_B , 

  • Table: AMS_METRIC_FORMULAS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_METRIC_FORMULAS,  object_name:AMS_METRIC_FORMULAS,  status:VALID,  product: AMS - Marketing ,  description: Metric Formula Definitions ,  implementation_dba_data: AMS.AMS_METRIC_FORMULAS , 

  • Table: AMS_METRIC_FORMULAS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_METRIC_FORMULAS,  object_name:AMS_METRIC_FORMULAS,  status:VALID,  product: AMS - Marketing ,  description: Metric Formula Definitions ,  implementation_dba_data: AMS.AMS_METRIC_FORMULAS , 

  • Table: AMS_CATEGORIES_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_CATEGORIES_B,  object_name:AMS_CATEGORIES_B,  status:VALID,  product: AMS - Marketing ,  description: This table stores the definition for categories and is used by Events, Metrics and the Deliverables area. ,  implementation_dba_data: AMS.AMS_CATEGORIES_B , 

  • Table: AMS_CATEGORIES_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_CATEGORIES_B,  object_name:AMS_CATEGORIES_B,  status:VALID,  product: AMS - Marketing ,  description: This table stores the definition for categories and is used by Events, Metrics and the Deliverables area. ,  implementation_dba_data: AMS.AMS_CATEGORIES_B , 

  • Table: AMS_HIERARCHIES_ALL_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_HIERARCHIES_ALL_B,  object_name:AMS_HIERARCHIES_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Stores all hierarchies supported by Oracle Marketing ,  implementation_dba_data: AMS.AMS_HIERARCHIES_ALL_B , 

  • Table: AMS_MET_TPL_DETAILS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_MET_TPL_DETAILS,  object_name:AMS_MET_TPL_DETAILS,  status:VALID,  product: AMS - Marketing ,  description: This table stores the details on the metrics contained inside a metrics grouping. ,  implementation_dba_data: AMS.AMS_MET_TPL_DETAILS , 

  • Table: AMS_METRICS_ALL_TL 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_METRICS_ALL_TL,  object_name:AMS_METRICS_ALL_TL,  status:VALID,  product: AMS - Marketing ,  description: This table stores all translated columns for flexible metrics definition. ,  implementation_dba_data: AMS.AMS_METRICS_ALL_TL , 

  • Table: AMS_ACT_METRIC_HST 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRIC_HST,  object_name:AMS_ACT_METRIC_HST,  status:VALID,  product: AMS - Marketing ,  description: This table stores the historical value for a metrics being tracked against a marketing entity e.g. date and value for metrics 'ROI' for a specific marketing campaign. ,  implementation_dba_data: AMS.AMS_ACT_METRIC_HST , 

  • Table: AMS_HIERARCHIES_ALL_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_HIERARCHIES_ALL_B,  object_name:AMS_HIERARCHIES_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Stores all hierarchies supported by Oracle Marketing ,  implementation_dba_data: AMS.AMS_HIERARCHIES_ALL_B , 

  • Table: AMS_MET_TPL_DETAILS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_MET_TPL_DETAILS,  object_name:AMS_MET_TPL_DETAILS,  status:VALID,  product: AMS - Marketing ,  description: This table stores the details on the metrics contained inside a metrics grouping. ,  implementation_dba_data: AMS.AMS_MET_TPL_DETAILS , 

  • Table: AMS_METRICS_ALL_TL 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_METRICS_ALL_TL,  object_name:AMS_METRICS_ALL_TL,  status:VALID,  product: AMS - Marketing ,  description: This table stores all translated columns for flexible metrics definition. ,  implementation_dba_data: AMS.AMS_METRICS_ALL_TL , 

  • Table: AMS_ACT_METRIC_HST 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRIC_HST,  object_name:AMS_ACT_METRIC_HST,  status:VALID,  product: AMS - Marketing ,  description: This table stores the historical value for a metrics being tracked against a marketing entity e.g. date and value for metrics 'ROI' for a specific marketing campaign. ,  implementation_dba_data: AMS.AMS_ACT_METRIC_HST , 

  • View: AMS_FUND_ALLOCATIONS_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_FUND_ALLOCATIONS_V,  object_name:AMS_FUND_ALLOCATIONS_V,  status:VALID,  product: AMS - Marketing ,  implementation_dba_data: APPS.AMS_FUND_ALLOCATIONS_V , 

  • View: AMS_METRICS_VL 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_METRICS_VL,  object_name:AMS_METRICS_VL,  status:VALID,  product: AMS - Marketing ,  description: This view returns the details of Metrics measures that can be used by an activity. ,  implementation_dba_data: APPS.AMS_METRICS_VL , 

  • View: AMS_METRICS_VL 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_METRICS_VL,  object_name:AMS_METRICS_VL,  status:VALID,  product: AMS - Marketing ,  description: This view returns the details of Metrics measures that can be used by an activity. ,  implementation_dba_data: APPS.AMS_METRICS_VL , 

  • View: AMS_ACT_METRIC_FACTS_ALL_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_ACT_METRIC_FACTS_ALL_V,  object_name:AMS_ACT_METRIC_FACTS_ALL_V,  status:VALID,  product: AMS - Marketing ,  description: This View returns all allocation amounts made from a fund on a sales territory hierarchy. ,  implementation_dba_data: APPS.AMS_ACT_METRIC_FACTS_ALL_V , 

  • Table: AMS_ACT_METRICS_ALL 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRICS_ALL,  object_name:AMS_ACT_METRICS_ALL,  status:VALID,  product: AMS - Marketing ,  description: This table stores the value for a metrics being tracked against a marketing entity e.g. the current value for metrics 'ROI' for a specific marketing campaign. ,  implementation_dba_data: AMS.AMS_ACT_METRICS_ALL , 

  • View: AMS_FUND_ALLOCATIONS_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_FUND_ALLOCATIONS_V,  object_name:AMS_FUND_ALLOCATIONS_V,  status:VALID,  product: AMS - Marketing ,  implementation_dba_data: APPS.AMS_FUND_ALLOCATIONS_V , 

  • View: AMS_ACT_METRIC_FACTS_ALL_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_ACT_METRIC_FACTS_ALL_V,  object_name:AMS_ACT_METRIC_FACTS_ALL_V,  status:VALID,  product: AMS - Marketing ,  description: This View returns all allocation amounts made from a fund on a sales territory hierarchy. ,  implementation_dba_data: APPS.AMS_ACT_METRIC_FACTS_ALL_V , 

  • Table: AMS_ACT_METRICS_ALL 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRICS_ALL,  object_name:AMS_ACT_METRICS_ALL,  status:VALID,  product: AMS - Marketing ,  description: This table stores the value for a metrics being tracked against a marketing entity e.g. the current value for metrics 'ROI' for a specific marketing campaign. ,  implementation_dba_data: AMS.AMS_ACT_METRICS_ALL ,