Search Results ozf_act_metric_formulas




Overview
Explore in AI Chat

Overview

The OZF_ACT_METRIC_FORMULAS table is a core repository within the Oracle Trade Management (OZF) module of Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It is designed to store the specific calculation formulas that are executed for performance metrics at different levels of a marketing or trade promotion hierarchy. This table enables the flexible and hierarchical definition of how key performance indicators (KPIs) are computed, supporting complex incentive and trade fund management processes. Its role is to provide the rule-based engine that drives the quantitative assessment of activities, ensuring that metric calculations are consistent, configurable, and tied to the appropriate organizational or promotional structure.

Key Information Stored

The table's primary function is to link a metric to its executable formula logic across hierarchy levels. While the full column list is not detailed in the provided metadata, the documented primary and foreign keys reveal its critical structure. The FORMULA_ID column serves as the unique primary key for each formula record. The ACTIVITY_METRIC_ID is a foreign key column that links each formula definition to a specific metric in the OZF_ACT_METRICS_ALL table. This relationship allows a single activity metric to have multiple associated formulas, each potentially applicable to a different node or level within a hierarchy (e.g., region, district, product line). The table likely contains additional columns to specify the hierarchy level, the sequence of execution, and references to the actual formula logic or components stored in related entities.

Common Use Cases and Queries

This table is central to the setup and execution of trade promotion analytics and performance tracking. A common use case involves administrators configuring how a metric like "Actual Spend vs. Budget" is calculated differently at a national level versus a regional level. For reporting and troubleshooting, common queries include retrieving all formulas for a specific metric or validating formula assignments. A sample SQL pattern to list formulas for a given metric would be:

  • SELECT f.formula_id, f.hierarchy_level, m.metric_name FROM ozf_act_metric_formulas f, ozf_act_metrics_all m WHERE f.activity_metric_id = m.activity_metric_id AND m.metric_name = '&METRIC_NAME';

Data from this table is also critical for batch processes that compute and roll up metric values across the defined hierarchies for incentive compensation or fund utilization reports.

Related Objects

The OZF_ACT_METRIC_FORMULAS table sits at a junction between metric definitions and the detailed formula components. As per the documented foreign key relationships:

  • OZF_ACT_METRICS_ALL: This is the parent table. The OZF_ACT_METRIC_FORMULAS.ACTIVITY_METRIC_ID column references OZF_ACT_METRICS_ALL, linking each formula to its base metric definition.
  • OZF_ACT_METRIC_FORM_ENT: This is a child table. It references OZF_ACT_METRIC_FORMULAS via the OZF_ACT_METRIC_FORM_ENT.FORMULA_ID column. This relationship suggests that OZF_ACT_METRIC_FORM_ENT stores the individual elements or parameters (entities) that constitute the formula defined in the parent record.

This structure indicates that a metric (OZF_ACT_METRICS_ALL) can have multiple formulas (OZF_ACT_METRIC_FORMULAS), and each formula can be composed of multiple entities or components (OZF_ACT_METRIC_FORM_ENT).