Search Results start_node

Overview

AMS_P_ACT_METRICS_V is a public Oracle E-Business Suite view owned by the APPS schema in the AMS (Marketing) product family. It returns the metrics that will be tracked for marketing activities, joining the transactional activity-metric assignments held in AMS_ACT_METRICS_ALL to the metric definitions in AMS_METRICS_VL and the categorization hierarchy in AMS_CATEGORIES_VL. The view is documented as VALID in both EBS 12.1.1 and 12.2.2, and because it is a public (APPS-owned) view, it is safe for customer reporting, custom concurrent programs, and integration layers that must not query the underlying AMS base tables directly.

The view supports reporting on forecasted, committed, and actual metric values in both transaction and functional currencies, along with the calculation metadata (function name, accrual type, value type) that drives how each metric is derived. The column ACTIVITY_METRIC_ORIGIN_ID, which prompted this search, identifies the origin of the activity-metric assignment and is exposed directly from the base table without further derivation.

Underlying Base Objects

The documented base objects for this view are:

• AMS_ACT_METRICS_ALL (SYNONYM) — the primary driving table, aliased as B. It contains one row per activity-metric assignment, including all transactional values, currency amounts, rollup parameters, descriptive flexfields, and the ORG_ID used for multi-org security.
• AMS_METRICS_VL (VIEW) — aliased as M, supplies the metric definition: name, calculation type, category and sub-category, accrual type, value type, function name/type, parent and summary metric relationships.
• AMS_CATEGORIES_VL (VIEW) — joined twice (C1 for the metric category, C2 for the metric sub-category) to resolve category names.

The join condition is B.METRIC_ID = M.METRIC_ID, with the category views joined on the respective category identifiers. The view also exposes B.ROWID as ROW_ID, enabling row-level identification of the underlying base record for update or diagnostic purposes.

Key Columns

• ACTIVITY_METRIC_ID — primary identifier of the activity-metric assignment row.
• ACTIVITY_METRIC_ORIGIN_ID — identifies the origin of the activity-metric record, distinguishing how the metric assignment was created.
• METRIC_ID / METRICS_NAME — the metric definition and its displayed name from AMS_METRICS_VL.
• METRIC_CALCULATION_TYPE, FUNCTION_NAME, FUNCTION_TYPE, PRE_FUNCTION_NAME, POST_FUNCTION_NAME — control how the metric value is computed.
• CATEGORY_ID / METRIC_CATEGORY, SUB_CATEGORY_ID / METRIC_SUB_CATEGORY — resolved category and sub-category names from AMS_CATEGORIES_VL.
• VARIABLE_FLAG, RATIO_FLAG — DECODE-derived flags indicating variable accrual and ratio-based value types.
• TRANS_FORECASTED_VALUE, TRANS_COMMITTED_VALUE, TRANS_ACTUAL_VALUE — transaction-currency metric amounts.
• FUNC_FORECASTED_VALUE, FUNC_COMMITTED_VALUE, FUNC_ACTUAL_VALUE — functional-currency equivalents.
• ORG_ID — operating unit, required for multi-org filtering.
• DIRTY_FLAG, LAST_CALCULATED_DATE, DIFFERENCE_SINCE_LAST_CALC, DAYS_SINCE_LAST_REFRESH — calculation status and staleness indicators.
• START_NODE, FROM_LEVEL, TO_LEVEL, FROM_DATE, TO_DATE, HIERARCHY_ID — rollup and summary parameters.
• AMOUNT1–AMOUNT3, PERCENT1–PERCENT3, PUBLISHED_FLAG — auxiliary metric inputs and publication status.
• ATTRIBUTE1–ATTRIBUTE15, ATTRIBUTE_CATEGORY — descriptive flexfield columns.

Common Use Cases and Queries

Typical uses include marketing activity performance reporting, reconciliation of committed versus actual metric values, identification of metrics awaiting recalculation, and integration extracts into external analytics platforms. Because the view is public and multi-org enabled, queries should always constrain ORG_ID.

Sample query listing tracked metrics for an operating unit:

SELECT activity_metric_id, metrics_name, metric_category,
       func_forecasted_value, func_committed_value, func_actual_value
FROM   apps.ams_p_act_metrics_v
WHERE  org_id = :p_org_id
AND    NVL(dirty_flag,'N') = 'Y';

Sample query isolating records by origin:

SELECT activity_metric_id, activity_metric_origin_id, metrics_name,
       calculation_type, last_calculated_date
FROM   apps.ams_p_act_metrics_v
WHERE  org_id = :p_org_id
AND    activity_metric_origin_id IS NOT NULL
ORDER  BY last_calculated_date DESC;

Both queries rely solely on documented columns and demonstrate the view's utility as a read-only reporting interface over the AMS activity-metric model.