Results for “ozf_act_metric_formulas”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The OZF_ACT_METRIC_FORMULAS table is a Trade Management (OZF) data object that stores the formulas executed at each hierarchy level for a metric. In Oracle EBS Trade Management, activity metrics are calculated across organizational and customer hierarchy levels — for example, aggregating promotional spend or claim balances from a leaf node up through parent levels. This table defines which formula runs at each level of that hierarchy, making it the configuration backbone for metric roll-up and calculation logic.

The table is owned by the OZF schema and is present and valid in both Oracle EBS 12.1.1 and 12.2.2. Its documented physical schema contains 12 columns. Based on a heuristic Data Vault classification derived from the foreign key structure, the table is satellite-leaning: it primarily holds descriptive and contextual attributes that depend on a parent business key (the activity metric) rather than acting as an independent hub or a pure many-to-many link. This classification should be treated as a modeling suggestion rather than a strict architectural constraint.

Key Information Stored

The most significant columns in the documented schema include:

• FORMULA_ID — The surrogate primary key, defined by the constraint OZF_ACT_METRIC_FORMULAS_PK. It also appears as the single unique index candidate OZF_ACT_METRIC_FORMULAS_U1, making it both the technical identifier and the business-key candidate in the documented metadata.
• ACTIVITY_METRIC_ID — Foreign key to OZF_ACT_METRICS_ALL, identifying the parent metric to which this formula belongs. This is the principal linkage that establishes the satellite relationship.
• LEVEL_DEPTH — Indicates the hierarchy level at which the formula executes, controlling roll-up behaviour across levels.
• PARENT_FORMULA_ID — Self-referencing column that establishes parent-child relationships between formulas, enabling formula hierarchies that mirror the metric's level structure.
• FORMULA_TYPE — Classifies the formula (for example, the calculation or aggregation category) applied at the given level.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, enforcing multi-org / data-security visibility for the formula definition.
• OBJECT_VERSION_NUMBER — Optimistic locking column supporting concurrent update control.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns recording insert and update provenance.

The distinction between the surrogate key and business keys is straightforward here: FORMULA_ID serves as the single-column primary key and the sole documented unique index. The true business context is supplied by ACTIVITY_METRIC_ID combined with LEVEL_DEPTH and FORMULA_TYPE.

Common Use Cases and Queries

Typical scenarios involve retrieving the formulas configured for a metric and reconstructing hierarchy roll-up logic. Queries frequently join this table to OZF_ACT_METRICS_ALL to resolve metric names, and to OZF_ACT_METRIC_FORM_ENT to obtain the actual formula expressions.

• Listing all formulas for a metric: SELECT formula_id, level_depth, formula_type FROM ozf.ozf_act_metric_formulas WHERE activity_metric_id = :metric_id ORDER BY level_depth;
• Retrieving formula definitions with their expressions via the entry table on FORMULA_ID.
• Reconstructing the parent-child formula tree using PARENT_FORMULA_ID for recursive hierarchy reports.
• Auditing configuration changes by filtering on LAST_UPDATE_DATE and LAST_UPDATED_BY.
• Security-filtered reporting constrained by SECURITY_GROUP_ID.

Related Objects

• OZF_ACT_METRICS_ALL — Parent metric table; joined via OZF_ACT_METRIC_FORMULAS.ACTIVITY_METRIC_ID = OZF_ACT_METRICS_ALL.ACTIVITY_METRIC_ID.
• OZF_ACT_METRIC_FORM_ENT — Details table referencing OZF_ACT_METRIC_FORMULAS.FORMULA_ID = OZF_ACT_METRIC_FORM_ENT.FORMULA_ID; holds the formula entry content.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID for data-security partitioning.