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
- ROW_ID, METRIC_ID — the surrogate row identifier and the functional metric primary key.
- METRICS_NAME, DESCRIPTION — the translated name and description inherited from AMS_METRICS_VL.
- VALUE_TYPE and MEANING — VALUE_TYPE is the stored lookup code; MEANING is the decoded, user-facing text. For example, numeric, currency, or character metric value types are distinguished here. This pair is the primary reason the view is consulted by users searching on "ams_metric_value_type".
- METRIC_CATEGORY, CATEGORY_NAME, METRIC_SUB_CATEGORY, CATEGORY_NAME (second instance) — category identifiers and their translated names, resolved through AMS_CATEGORIES_VL.
- METRIC_CALCULATION_TYPE, ACCRUAL_TYPE, COMPUTE_USING_FUNCTION, SET_FUNCTION_NAME, FUNCTION_NAME, FORMULA — metadata governing how the metric is computed.
- DEFAULT_UOM_CODE, UOM_TYPE — unit of measure defaults for the metric.
- SENSITIVE_DATA_FLAG, ENABLED_FLAG, ARC_METRIC_USED_FOR_OBJECT — security, activation, and usage-object flags.
- METRIC_PARENT_ID, SUMMARY_METRIC_ID, HIERARCHY_ID — hierarchical and rollup relationships.
- Standard WHO/audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER, APPLICATION_ID) are also exposed.
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.
-
VIEW: APPS.AMS_P_METRICS_V 12.1.1
-
VIEW: APPS.AMS_P_METRICS_V 12.2.2
-
View: AMS_P_METRICS_V 12.1.1
This public view returns the details of Metrics measures that can be used by an activity.
APPS.AMS_P_METRICS_V·↳ AMS_CATEGORIES_VL·↳ AMS_LOOKUPS·↳ AMS_METRICS_VL·Explore AMS module →
-
View: AMS_METRICS_INFO_V 12.2.2
APPS.AMS_METRICS_INFO_V·↳ AMS_CATEGORIES_VL·↳ AMS_LOOKUPS·↳ AMS_METRICS_ALL_B·Explore AMS module →
-
View: AMS_P_METRICS_V 12.2.2
This public view returns the details of Metrics measures that can be used by an activity.
APPS.AMS_P_METRICS_V·↳ AMS_CATEGORIES_VL·↳ AMS_LOOKUPS·↳ AMS_METRICS_VL·Explore AMS module →
-
View: AMS_METRICS_INFO_V 12.1.1
APPS.AMS_METRICS_INFO_V·↳ AMS_CATEGORIES_VL·↳ AMS_LOOKUPS·↳ AMS_METRICS_VL·Explore AMS module →
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2