Results for “formula_type”

50+ results




Overview
Explore in AI Chat

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

Overview

AMS_ACT_METRIC_FORMULAS is a Marketing (AMS) module table within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It stores the formulas that are executed at each hierarchy level for a metric. In the context of Oracle Marketing activity metrics, this table defines the computational logic applied to aggregate or derive metric values as data is rolled up or drilled down through a metric hierarchy. Each row represents a single formula definition bound to a metric, with a designated depth level and an optional parent formula that establishes the hierarchical structure.

From a dimensional modelling perspective, the FK topology mined from the ETRM metadata suggests a satellite-leaning classification. The table is dominated by descriptive and structural attributes anchored to a parent hub (AMS_ACT_METRICS_ALL via ACTIVITY_METRIC_ID), with a self-referencing hierarchy (PARENT_FORMULA_ID) rather than a many-to-many link structure. This classification is a heuristic suggestion and should be validated against the intended reporting grain before being treated as a formal Data Vault model.

Key Information Stored

The documented physical schema for 12.2.2 contains 12 columns. The most significant are:

  • FORMULA_ID — The surrogate primary key, enforced by AMS_ACT_METRIC_FORMULAS_PK. It is also the single-column unique index AMS_ACT_METRIC_FORMULAS_U1, making it the business-key candidate in the documented metadata.
  • ACTIVITY_METRIC_ID — Foreign key to AMS_ACT_METRICS_ALL, identifying the metric to which the formula belongs. This is the primary anchor column for most joins.
  • LEVEL_DEPTH — Indicates the hierarchy level at which the formula is executed, allowing different computations per depth.
  • PARENT_FORMULA_ID — Self-referencing foreign key to AMS_ACT_METRIC_FORMULAS, defining the parent-child relationship between formulas and enabling multi-level formula hierarchies.
  • FORMULA_TYPE — Classifies the formula, distinguishing how the formula is interpreted or executed.
  • SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, controlling row-level access consistent with Oracle EBS multi-org security conventions.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the OA Framework and concurrent processing to detect stale updates.

The remaining documented columns are standard EBS audit columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include retrieving all formulas for a given metric, traversing the formula hierarchy, and reporting formula definitions by depth. A baseline query joining formulas to their metric is:

  • SELECT f.FORMULA_ID, f.ACTIVITY_METRIC_ID, f.LEVEL_DEPTH, f.FORMULA_TYPE FROM AMS.AMS_ACT_METRIC_FORMULAS f WHERE f.ACTIVITY_METRIC_ID = :metric_id ORDER BY f.LEVEL_DEPTH;
  • Hierarchy traversal via a self-join or CONNECT BY: SELECT FORMULA_ID, PARENT_FORMULA_ID, LEVEL_DEPTH FROM AMS.AMS_ACT_METRIC_FORMULAS START WITH PARENT_FORMULA_ID IS NULL CONNECT BY PRIOR FORMULA_ID = PARENT_FORMULA_ID;
  • Root-level formulas for a metric: filter on PARENT_FORMULA_ID IS NULL combined with the metric identifier.
  • Security-scoped reporting: join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to restrict output to authorized groups.

These patterns support metric configuration validation, migration auditing between 12.1.1 and 12.2.2, and diagnostic reporting when metric rollups produce unexpected results.

Related Objects

  • AMS_ACT_METRICS_ALL — Referenced through ACTIVITY_METRIC_ID; the parent metric definition table.
  • AMS_ACT_METRIC_FORMULAS — Self-reference through PARENT_FORMULA_ID, forming the formula hierarchy.
  • AMS_ACT_METRIC_FORM_ENT — References this table through FORMULA_ID, holding formula entity or expression details.
  • FND_SECURITY_GROUPS — Referenced through SECURITY_GROUP_ID for row-level security.

Together these objects form the metric-formula configuration model used by Oracle Marketing activity metric processing.