Results for “ams_metric_value_type”

14 results




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

Overview

APPS.AMS_P_METRICS_V is a reporting and integration view in the Oracle E-Business Suite 12.1.1 and 12.2.2 Oracle Marketing (AMS) schema. It presents the denormalized definition of marketing metrics — the measurable quantities such as revenue, response counts, campaign cost, and conversion rates — that drive Oracle Marketing analytics, campaign scoring, and metrics calculations. The view consolidates the metrics definition entity (AMS_METRICS_VL) with its category and subcategory descriptions and with the decoded lookup meaning for the metric value type. It is a public, APPS-owned synonym-backed view and is intended for concurrent programs, Discoverer workbooks, OBIEE extracts, and custom SQL that must resolve human-readable metric metadata rather than raw identifiers.

The view is documented in Oracle's ETRM (E-Business Suite Technical Reference Manual) for release 12.2.2, and its definition is unchanged in 12.1.1 given the identical AMS table structure. Users searching for "ams_metric_value_type" are typically directed here because the view decodes VALUE_TYPE through the AMS_LOOKUPS lookup type 'AMS_METRIC_VALUE_TYPE'.

Underlying Base Objects

The view is defined over four base objects, three of which are themselves views:

  • AMS_METRICS_VL — the multi-language metrics definition view; aliased B. Supplies all core metric attributes (METRIC_ID, VALUE_TYPE, FORMULA, categories, flags, and so on) with the current-language METRICS_NAME and DESCRIPTION.
  • AMS_CATEGORIES_VL — the multi-language categories view, joined twice: alias C1 on METRIC_CATEGORY and alias C2 on METRIC_SUB_CATEGORY. Both joins are outer joins (C1.CATEGORY_ID(+) = B.METRIC_CATEGORY, C2.CATEGORY_ID(+) = B.METRIC_SUB_CATEGORY), so metrics without a category or subcategory are still returned.
  • AMS_LOOKUPS — the FND-style lookup view, aliased L. The join is an inner join constrained to LOOKUP_TYPE = 'AMS_METRIC_VALUE_TYPE' and LOOKUP_CODE = B.VALUE_TYPE, which supplies the L.MEANING column that decodes the value type.

All four are documented as referenced base objects for this view in the 12.2.2 metadata. The inner join to AMS_LOOKUPS has an important implication: only metrics whose VALUE_TYPE has a matching seeded lookup code in AMS_METRIC_VALUE_TYPE are returned.

Key Columns

Common Use Cases and Queries

Typical scenarios include validating which metrics exist and are enabled, decoding value types for integration extracts, and resolving category hierarchies for reporting.

Enabled metrics with decoded value type:

SELECT metric_id, metrics_name, value_type, meaning,
       metric_category, metric_sub_category
  FROM apps.ams_p_metrics_v
 WHERE enabled_flag = 'Y'
 ORDER BY metrics_name;

Filtering on the value-type lookup meaning:

SELECT metrics_name, formula, default_uom_code
  FROM apps.ams_p_metrics_v
 WHERE meaning = 'Numeric';

Category-joined metric inventory:

SELECT c1.category_name AS category,
       c2.category_name AS sub_category,
       m.metrics_name
  FROM apps.ams_p_metrics_v m
 ORDER BY 1, 2;

Because the lookup join is inner and the category joins are outer, queries expecting metrics with unseeded value types or null categories should be validated carefully against base tables such as AMS_METRICS_B and FND_LOOKUP_VALUES when reconciliation is required.