Search Results ams_act_metrics_all

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.