Search Results ams_act_metric_formulas




Overview
Explore in AI Chat

Overview

The AMS_ACT_METRIC_FORMULAS table is a core data structure within the Oracle E-Business Suite Marketing (AMS) module, specifically for releases 12.1.1 and 12.2.2. It serves as the repository for the calculation logic applied to marketing activity metrics. In the context of Oracle Marketing, metrics are quantifiable measures used to assess the performance and effectiveness of marketing campaigns and activities. This table stores the specific formulas that are executed at various hierarchical levels, enabling the system to compute derived metric values from raw data. Its primary role is to support the flexible and hierarchical definition of performance calculations, which is essential for complex marketing analytics and reporting.

Key Information Stored

The table's central column is FORMULA_ID, which serves as the primary key and uniquely identifies each formula record. The ACTIVITY_METRIC_ID column is a critical foreign key that links each formula to its parent metric definition in the AMS_ACT_METRICS_ALL table. The PARENT_FORMULA_ID column establishes a self-referential hierarchical relationship within the table itself, allowing for nested or multi-level formula calculations. While the provided metadata does not list all columns, the table's description implies it contains columns to store the formula expression (e.g., SQL or a parsed representation), the hierarchy level at which it executes, and potentially sequencing or weighting information. This structure allows a single marketing metric to have different calculation rules applied depending on the roll-up level (e.g., campaign, business unit, or enterprise).

Common Use Cases and Queries

The primary use case is the runtime calculation of marketing performance metrics during report generation or dashboard refreshes. Administrators may query this table to audit or manage the calculation logic tied to specific metrics. A common reporting query would join this table to the metric definitions to list all formulas for a given activity. For example:

  • SELECT m.METRIC_NAME, f.* FROM AMS_ACT_METRIC_FORMULAS f, AMS_ACT_METRICS_ALL m WHERE f.ACTIVITY_METRIC_ID = m.ACTIVITY_METRIC_ID AND m.METRIC_CODE = '&METRIC_CODE';

Another typical scenario involves tracing the formula hierarchy for a complex metric using a self-join:

  • SELECT child.FORMULA_ID, parent.FORMULA_ID AS PARENT_ID FROM AMS_ACT_METRIC_FORMULAS child LEFT JOIN AMS_ACT_METRIC_FORMULAS parent ON child.PARENT_FORMULA_ID = parent.FORMULA_ID START WITH child.PARENT_FORMULA_ID IS NULL CONNECT BY PRIOR child.FORMULA_ID = child.PARENT_FORMULA_ID;

Technical consultants may also query this table when debugging incorrect metric values by verifying the underlying stored calculation logic.

Related Objects

The table maintains defined relationships with several other AMS objects, as documented in the provided foreign key metadata. The primary relationship is with the metric definition table: AMS_ACT_METRICS_ALL, via the ACTIVITY_METRIC_ID column. It has a self-referential relationship for hierarchy management through the PARENT_FORMULA_ID column, linking back to the AMS_ACT_METRIC_FORMULAS table itself. Furthermore, it is referenced by the AMS_ACT_METRIC_FORM_ENT table via the FORMULA_ID column, which likely stores the individual formula entries or components that make up the complete calculation. These relationships underscore its position as a central hub connecting metric definitions to their executable calculation rules and constituent parts.