Results for “ams_metrics_vl”

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

Overview

AMS_METRICS_VL is a bilingual (VL) view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS – Marketing product family. It exposes the details of metrics measures that can be associated with an activity, effectively serving as the reporting and integration layer over the underlying marketing metrics definition tables. In Oracle EBS 12.1.1 and 12.2.2, this view is reported as VALID in the ETRM repository.

The view's principal function is to join the transactional definition of a metric with its translated, language-specific descriptive text. It returns one row per metric for the session language, so that reports, concurrent programs, and API-driven integrations present metric names and descriptions in the user's own locale rather than in the base language. Because it is a VL (translated) view, it is the recommended access point for read-only queries against marketing metric definitions, in preference to querying the underlying _B and _TL tables directly.

Underlying Base Objects

The view is defined over two base objects, both referenced in the ETRM metadata as synonyms:

• AMS_METRICS_ALL_B — the base (non-translated) table holding the metric definition itself, including identifiers, calculation attributes, flags, and formula metadata.
• AMS_METRICS_ALL_TL — the translation table holding the language-specific METRICS_NAME, DESCRIPTION, and FORMULA_DISPLAY columns.

The join is performed on METRIC_ID, filtered by T.LANGUAGE = USERENV('LANG'), which restricts the result set to the current session language. The SELECT list draws almost all functional columns from the _B table and only the descriptive columns from the _TL table. The view also synthesizes a ROW_ID column from B.ROW_ID, providing a pseudo-row identifier for the metric definition.

Key Columns

• METRIC_ID — primary identifier of the metric; the join key between the base and translation tables.
• METRICS_NAME and DESCRIPTION — the translated display name and long description of the metric.
• METRIC_CALCULATION_TYPE, COMPUTE_USING_FUNCTION, FUNCTION_NAME, SET_FUNCTION_NAME, and FUNCTION_TYPE — control how the metric is calculated or sourced.
• METRIC_CATEGORY, METRIC_SUB_CATEGORY, and VALUE_TYPE — classification and value semantics of the metric.
• FORMULA and FORMULA_DISPLAY — the stored formula expression and its translated presentation form.
• DEFAULT_UOM_CODE, UOM_TYPE, and DENORM_CODE — unit-of-measure and denormalization attributes.
• METRIC_PARENT_ID, SUMMARY_METRIC_ID, and HIERARCHY_ID — define metric roll-up and hierarchy relationships.
• ENABLED_FLAG, SENSITIVE_DATA_FLAG, TARGET_TYPE, DISPLAY_TYPE, and ARC_METRIC_USED_FOR_OBJECT — status, security, and usage controls.
• OBJECT_VERSION_NUMBER and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) — concurrency and audit tracking.

Common Use Cases and Queries

Typical scenarios include listing all enabled metrics available to activities, retrieving a metric description in the user's language, and driving dynamic LOV or reporting queries in custom extensions.

Listing enabled metrics with translated text:

• SELECT metric_id, metrics_name, description, metric_category, default_uom_code FROM ams_metrics_vl WHERE enabled_flag = 'Y' AND language-independent filtering is unnecessary ORDER BY metrics_name;

Retrieving a specific metric definition:

• SELECT metric_id, metrics_name, description, formula_display, compute_using_function FROM ams_metrics_vl WHERE metric_id = :p_metric_id;

Filtering by category for reporting:

• SELECT metric_category, metric_sub_category, metrics_name FROM ams_metrics_vl WHERE enabled_flag = 'Y' ORDER BY metric_category, metrics_name;

Because the LANGUAGE predicate is embedded in the view, no additional language filter is required; results automatically reflect the session language. For multi-language extraction, query AMS_METRICS_ALL_TL directly.