Results for “ams_act_metric_formulas_v”

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

Overview

The AMS_ACT_METRIC_FORMULAS_V view is an Oracle E-Business Suite dictionary object owned by the APPS schema and defined within the AMS (Marketing) product family. It presents the complete decomposition of the formula that governs how an activity metric is evaluated. In Oracle Marketing and Trade Management (ETRM), an activity metric captures a measurable outcome of a marketing activity; the metric is not necessarily a static column, but is frequently computed through a formula composed of one or more entries referencing other metrics, columns, constants, or operators. This view exposes that formula structure in a fully flattened, row-per-formula-entry form so that reports, integrations, and diagnostic queries can reconstruct the calculation logic without joining the underlying normalized tables individually.

Because the view joins the activity metric definition, the formula header, and the formula entry detail, it is the primary reference for anyone auditing how a particular activity metric is derived. A user searching for formula_entry_value will find that this column is exposed directly by the view, making it the appropriate entry point when tracing an operand value back to its owning metric and formula.

Underlying Base Objects

The view is defined over three documented base objects, all referenced through APPS synonyms:

• AMS_ACT_METRICS_ALL (aliased AMC) — the activity metric header, supplying ACTIVITY_METRIC_ID, ARC_ACT_METRIC_USED_BY, ACT_METRIC_USED_BY_ID, and METRIC_ID.
• AMS_ACT_METRIC_FORMULAS (aliased MFM) — the formula header, supplying FORMULA_ID, PARENT_FORMULA_ID, FORMULA_TYPE, and LEVEL_DEPTH.
• AMS_ACT_METRIC_FORM_ENT (aliased MFE) — the formula entry detail, supplying FORMULA_ENTRY_ID, ORDER_NUMBER, FORMULA_ENTRY_TYPE, FORMULA_ENTRY_VALUE, FORMULA_ENTRY_OPERATOR, and METRIC_COLUMN_VALUE.

The join is an equi-join on AMC.ACTIVITY_METRIC_ID = MFM.ACTIVITY_METRIC_ID and MFM.FORMULA_ID = MFE.FORMULA_ID. The _ALL suffix on the metrics table indicates it is a multi-organization (MLS/partitioned) object, so the view inherits organization-sensitive behavior from that base table. No filter predicates are applied inside the view text, so it returns every formula entry for every activity metric visible to the querying session.

Key Columns

• ACTIVITY_METRIC_ID — identifies the parent activity metric to which the formula belongs.
• ARC_ACT_METRIC_USED_BY and ACT_METRIC_USED_BY_ID — describe the entity type and identifier that consumes or owns the metric.
• METRIC_ID — the metric being calculated.
• FORMULA_ID / PARENT_FORMULA_ID — the formula header and its parent, supporting nested formula hierarchies.
• FORMULA_TYPE and LEVEL_DEPTH — classify the formula and indicate its depth in the nesting tree.
• FORMULA_ENTRY_ID and ORDER_NUMBER — uniquely identify each entry and give its sequence within the formula.
• FORMULA_ENTRY_TYPE — denotes whether the entry is an operand, an operator, a constant, etc.
• FORMULA_ENTRY_VALUE — the literal or reference value of the entry; central to tracing the calculation.
• FORMULA_ENTRY_OPERATOR — the arithmetic/logical operator applied at this position.
• METRIC_COLUMN_VALUE — the associated metric column value used in the expression.

Common Use Cases and Queries

Typical scenarios include auditing metric calculation logic before migration, validating that formula entries are ordered and complete, and feeding downstream reporting tools. A representative query isolating the searched term is:

SELECT amc.activity_metric_id,
       mfm.formula_id,
       mfe.order_number,
       mfe.formula_entry_type,
       mfe.formula_entry_value,
       mfe.formula_entry_operator
FROM   ams_act_metric_formulas_v
WHERE  activity_metric_id = :p_activity_metric_id
ORDER  BY formula_id, order_number;

To locate a specific operand across all metrics, filter on formula_entry_value. To reconstruct nested formulas, group by formula_id and order by level_depth and order_number. Because the view applies no org or date predicates, callers should constrain it by the calling module's organization context.