Search Results ams_act_metrics_all




Overview
Explore in AI Chat

Overview

The AMS_ACT_METRICS_ALL table is a core transactional entity within the Oracle E-Business Suite Marketing (AMS) module, present in both releases 12.1.1 and 12.2.2. It serves as the central repository for storing the current, tracked value of a metric associated with a specific marketing entity. As defined in the ETRM, its primary role is to hold values for metrics being tracked against marketing entities, such as the current Return on Investment (ROI) figure for a specific marketing campaign. This table is fundamental to the performance measurement and analytical capabilities of the Marketing module, enabling the quantitative assessment of campaigns, events, deliverables, and other marketing objects.

Key Information Stored

The table's structure is designed to link a metric value to its context. The primary key is ACTIVITY_METRIC_ID, a unique identifier for each metric record. Critical foreign key columns define this context. The METRIC_ID links to AMS_METRICS_ALL_B, defining what is being measured (e.g., Cost, Revenue, ROI). The ACT_METRIC_USED_BY_ID is a polymorphic foreign key that identifies the specific marketing object (campaign, event, deliverable, list, etc.) to which the metric value applies, as evidenced by its relationships to numerous tables like AMS_CAMPAIGNS_ALL_B and AMS_EVENT_HEADERS_ALL_B. Other significant columns include SCENARIO_ID (linking to AMS_MET_SCENARIOS for planning scenarios), TRANSACTION_CURRENCY_CODE and FUNCTIONAL_CURRENCY_CODE (for financial metrics), and HIERARCHY_ID (for organizational context). The table also includes standard EBS columns such as CREATION_DATE, LAST_UPDATE_DATE, and CREATED_BY for auditing.

Common Use Cases and Queries

This table is central to marketing performance reporting and operational analysis. A common use case is generating a campaign performance dashboard, querying for all metric values associated with a specific campaign or a set of campaigns within a date range. Another scenario involves auditing metric history or verifying data inputs for a financial reconciliation process. A typical query pattern joins AMS_ACT_METRICS_ALL to the metric definition and the parent marketing object.

  • Sample Query for Campaign Metrics:
    SELECT cam.CAMPAIGN_NAME, met.METRIC_NAME, actm.METRIC_VALUE, actm.TRANSACTION_CURRENCY_CODE
    FROM AMS_ACT_METRICS_ALL actm,
    AMS_METRICS_ALL_B met,
    AMS_CAMPAIGNS_ALL_B cam
    WHERE actm.METRIC_ID = met.METRIC_ID
    AND actm.ACT_METRIC_USED_BY_ID = cam.CAMPAIGN_ID
    AND cam.CAMPAIGN_ID = :p_campaign_id
    AND actm.SCENARIO_ID IS NULL; -- For actuals, not planning scenarios

Related Objects

The ETRM metadata reveals an extensive network of relationships, underscoring the table's importance. It is a parent table to several key entities: AMS_ACT_METRIC_FACTS_ALL (for detailed fact data), AMS_ACT_METRIC_FORMULAS, AMS_ACT_METRIC_HST (for historical tracking), and AMS_ACT_OBJ_METRICS. It is also referenced by AMS_TRIGGERS and AMS_TRIGGER_CHECKS for automated marketing actions. As a child table, it draws definitional data from AMS_METRICS_ALL_B and links to numerous marketing object headers via ACT_METRIC_USED_BY_ID, including campaigns (AMS_CAMPAIGNS_ALL_B), events (AMS_EVENT_HEADERS_ALL_B), deliverables (AMS_DELIVERABLES_ALL_B), and lists (AMS_LIST_HEADERS_ALL). This dense relational model positions AMS_ACT_METRICS_ALL as a critical hub in the AMS data architecture.

  • 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 , 

  • 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 , 

  • Table: AMS_TRIGGER_CHECKS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_TRIGGER_CHECKS,  object_name:AMS_TRIGGER_CHECKS,  status:VALID,  product: AMS - Marketing ,  description: AMS_TRIGGER_CHECKS stores all checks that should be run when a datetime(specified in the AMS_TRIGGERS table) is reached. ,  implementation_dba_data: AMS.AMS_TRIGGER_CHECKS , 

  • Table: AMS_TRIGGER_CHECKS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_TRIGGER_CHECKS,  object_name:AMS_TRIGGER_CHECKS,  status:VALID,  product: AMS - Marketing ,  description: AMS_TRIGGER_CHECKS stores all checks that should be run when a datetime(specified in the AMS_TRIGGERS table) is reached. ,  implementation_dba_data: AMS.AMS_TRIGGER_CHECKS , 

  • Table: AMS_MET_SCENARIOS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_MET_SCENARIOS,  object_name:AMS_MET_SCENARIOS,  status:VALID,  product: AMS - Marketing ,  description: This table stores the different scenarios that a metrics could belong to. ,  implementation_dba_data: AMS.AMS_MET_SCENARIOS , 

  • 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_ACT_OBJ_METRICS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_OBJ_METRICS,  object_name:AMS_ACT_OBJ_METRICS,  status:VALID,  product: AMS - Marketing ,  description: Store what Metrics will be used to measure Objectives set up for an activity. ,  implementation_dba_data: AMS.AMS_ACT_OBJ_METRICS , 

  • Table: AMS_ACT_METRIC_FACTS_ALL 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRIC_FACTS_ALL,  object_name:AMS_ACT_METRIC_FACTS_ALL,  status:VALID,  product: AMS - Marketing ,  description: Store metric values to allow for forecasting over time by certain dimensions. ,  implementation_dba_data: AMS.AMS_ACT_METRIC_FACTS_ALL , 

  • Table: AMS_ACT_METRIC_FORMULAS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRIC_FORMULAS,  object_name:AMS_ACT_METRIC_FORMULAS,  status:VALID,  product: AMS - Marketing ,  description: Stores the formulas to be executed at each hierarchy level for a metric. ,  implementation_dba_data: AMS.AMS_ACT_METRIC_FORMULAS , 

  • Table: AMS_ACT_METRIC_FORMULAS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRIC_FORMULAS,  object_name:AMS_ACT_METRIC_FORMULAS,  status:VALID,  product: AMS - Marketing ,  description: Stores the formulas to be executed at each hierarchy level for a metric. ,  implementation_dba_data: AMS.AMS_ACT_METRIC_FORMULAS , 

  • 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_ACT_METRIC_FACTS_ALL 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_METRIC_FACTS_ALL,  object_name:AMS_ACT_METRIC_FACTS_ALL,  status:VALID,  product: AMS - Marketing ,  description: Store metric values to allow for forecasting over time by certain dimensions. ,  implementation_dba_data: AMS.AMS_ACT_METRIC_FACTS_ALL , 

  • Table: AMS_CAMPAIGN_SCHEDULES 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_CAMPAIGN_SCHEDULES,  object_name:AMS_CAMPAIGN_SCHEDULES,  status:VALID,  product: AMS - Marketing ,  description: This table is obsoleted and no longer in use. ,  implementation_dba_data: AMS.AMS_CAMPAIGN_SCHEDULES , 

  • 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_TRIGGERS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_TRIGGERS,  object_name:AMS_TRIGGERS,  status:VALID,  product: AMS - Marketing ,  description: AMS_TRIGGERS stores Triggers to start execution of certain Activity at a certain time. ,  implementation_dba_data: AMS.AMS_TRIGGERS , 

  • Table: AMS_OBJECT_ASSOCIATIONS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_OBJECT_ASSOCIATIONS,  object_name:AMS_OBJECT_ASSOCIATIONS,  status:VALID,  product: AMS - Marketing ,  description: AMS_OBJECT_ASSOCIATIONS is the cross reference between the Campaigns or Events or Creatives the that are running. ,  implementation_dba_data: AMS.AMS_OBJECT_ASSOCIATIONS , 

  • Table: AMS_OBJECT_ASSOCIATIONS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_OBJECT_ASSOCIATIONS,  object_name:AMS_OBJECT_ASSOCIATIONS,  status:VALID,  product: AMS - Marketing ,  description: AMS_OBJECT_ASSOCIATIONS is the cross reference between the Campaigns or Events or Creatives the that are running. ,  implementation_dba_data: AMS.AMS_OBJECT_ASSOCIATIONS , 

  • View: AMS_ACT_METRIC_FORMULAS_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_ACT_METRIC_FORMULAS_V,  object_name:AMS_ACT_METRIC_FORMULAS_V,  status:VALID,  product: AMS - Marketing ,  description: This View returns all formula details for an activity metric. ,  implementation_dba_data: APPS.AMS_ACT_METRIC_FORMULAS_V , 

  • Table: AMS_ACT_OBJ_METRICS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_ACT_OBJ_METRICS,  object_name:AMS_ACT_OBJ_METRICS,  status:VALID,  product: AMS - Marketing ,  description: Store what Metrics will be used to measure Objectives set up for an activity. ,  implementation_dba_data: AMS.AMS_ACT_OBJ_METRICS , 

  • Table: AMS_MET_SCENARIOS 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_MET_SCENARIOS,  object_name:AMS_MET_SCENARIOS,  status:VALID,  product: AMS - Marketing ,  description: This table stores the different scenarios that a metrics could belong to. ,  implementation_dba_data: AMS.AMS_MET_SCENARIOS , 

  • 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 , 

  • Table: AMS_CAMPAIGN_SCHEDULES 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_CAMPAIGN_SCHEDULES,  object_name:AMS_CAMPAIGN_SCHEDULES,  status:VALID,  product: AMS - Marketing ,  description: This table is obsoleted and no longer in use. ,  implementation_dba_data: AMS.AMS_CAMPAIGN_SCHEDULES , 

  • View: AMS_ACT_METRIC_FORMULAS_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_ACT_METRIC_FORMULAS_V,  object_name:AMS_ACT_METRIC_FORMULAS_V,  status:VALID,  product: AMS - Marketing ,  description: This View returns all formula details for an activity metric. ,  implementation_dba_data: APPS.AMS_ACT_METRIC_FORMULAS_V , 

  • Table: AMS_TRIGGERS 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_TRIGGERS,  object_name:AMS_TRIGGERS,  status:VALID,  product: AMS - Marketing ,  description: AMS_TRIGGERS stores Triggers to start execution of certain Activity at a certain time. ,  implementation_dba_data: AMS.AMS_TRIGGERS , 

  • Table: AMS_LIST_HEADERS_ALL 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_LIST_HEADERS_ALL,  object_name:AMS_LIST_HEADERS_ALL,  status:VALID,  product: AMS - Marketing ,  description: This table stores the name and other details about a target list. ,  implementation_dba_data: AMS.AMS_LIST_HEADERS_ALL , 

  • 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_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_IMP_LIST_HEADERS_ALL 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_IMP_LIST_HEADERS_ALL,  object_name:AMS_IMP_LIST_HEADERS_ALL,  status:VALID,  product: AMS - Marketing ,  description: This table stores the user entered name and other details about lists imported into Oracle Marketing from external sources. ,  implementation_dba_data: AMS.AMS_IMP_LIST_HEADERS_ALL , 

  • Table: AMS_IMP_LIST_HEADERS_ALL 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_IMP_LIST_HEADERS_ALL,  object_name:AMS_IMP_LIST_HEADERS_ALL,  status:VALID,  product: AMS - Marketing ,  description: This table stores the user entered name and other details about lists imported into Oracle Marketing from external sources. ,  implementation_dba_data: AMS.AMS_IMP_LIST_HEADERS_ALL , 

  • Table: AMS_DELIVERABLES_ALL_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_DELIVERABLES_ALL_B,  object_name:AMS_DELIVERABLES_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: This table stores the details about marketing deliverables/collaterals, collaterals are uniquely identified by deliverable name, language and version. ,  implementation_dba_data: AMS.AMS_DELIVERABLES_ALL_B , 

  • 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_LIST_HEADERS_ALL 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_LIST_HEADERS_ALL,  object_name:AMS_LIST_HEADERS_ALL,  status:VALID,  product: AMS - Marketing ,  description: This table stores the name and other details about a target list. ,  implementation_dba_data: AMS.AMS_LIST_HEADERS_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 , 

  • Table: AMS_EVENT_HEADERS_ALL_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_EVENT_HEADERS_ALL_B,  object_name:AMS_EVENT_HEADERS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Stores the definition of a Marketing Event. (Master Event or Event Template) ,  implementation_dba_data: AMS.AMS_EVENT_HEADERS_ALL_B , 

  • Table: AMS_DELIVERABLES_ALL_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_DELIVERABLES_ALL_B,  object_name:AMS_DELIVERABLES_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: This table stores the details about marketing deliverables/collaterals, collaterals are uniquely identified by deliverable name, language and version. ,  implementation_dba_data: AMS.AMS_DELIVERABLES_ALL_B , 

  • Table: AMS_EVENT_OFFERS_ALL_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_EVENT_OFFERS_ALL_B,  object_name:AMS_EVENT_OFFERS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Stores information about an Event Offering. .This table also holds the data for all the Agenda Items for the Event Offerings. ,  implementation_dba_data: AMS.AMS_EVENT_OFFERS_ALL_B , 

  • Table: AMS_EVENT_HEADERS_ALL_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_EVENT_HEADERS_ALL_B,  object_name:AMS_EVENT_HEADERS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Stores the definition of a Marketing Event. (Master Event or Event Template) ,  implementation_dba_data: AMS.AMS_EVENT_HEADERS_ALL_B , 

  • Table: AMS_CAMPAIGNS_ALL_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_CAMPAIGNS_ALL_B,  object_name:AMS_CAMPAIGNS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Base table to store the details of a campaign. ,  implementation_dba_data: AMS.AMS_CAMPAIGNS_ALL_B , 

  • Table: AMS_EVENT_OFFERS_ALL_B 12.2.2

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_EVENT_OFFERS_ALL_B,  object_name:AMS_EVENT_OFFERS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Stores information about an Event Offering. .This table also holds the data for all the Agenda Items for the Event Offerings. ,  implementation_dba_data: AMS.AMS_EVENT_OFFERS_ALL_B , 

  • Table: AMS_CAMPAIGNS_ALL_B 12.1.1

    owner:AMS,  object_type:TABLE,  fnd_design_data:AMS.AMS_CAMPAIGNS_ALL_B,  object_name:AMS_CAMPAIGNS_ALL_B,  status:VALID,  product: AMS - Marketing ,  description: Base table to store the details of a campaign. ,  implementation_dba_data: AMS.AMS_CAMPAIGNS_ALL_B , 

  • View: AMS_P_ACT_METRICS_V 12.2.2

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_P_ACT_METRICS_V,  object_name:AMS_P_ACT_METRICS_V,  status:VALID,  product: AMS - Marketing ,  description: This public view returns the metrics that will be tracked for marketing activities. ,  implementation_dba_data: APPS.AMS_P_ACT_METRICS_V , 

  • View: AMS_P_ACT_METRICS_V 12.1.1

    owner:APPS,  object_type:VIEW,  fnd_design_data:AMS.AMS_P_ACT_METRICS_V,  object_name:AMS_P_ACT_METRICS_V,  status:VALID,  product: AMS - Marketing ,  description: This public view returns the metrics that will be tracked for marketing activities. ,  implementation_dba_data: APPS.AMS_P_ACT_METRICS_V ,