Results for “fact_value”

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

Overview

The AMS_ACT_METRIC_FACTS_ALL_V view is an Oracle E-Business Suite database object owned by the APPS schema and validated under the AMS (Marketing) product module. It is delivered for both EBS 12.1.1 and 12.2.2 and is classified as a VIEW in the ETRM repository. Functionally, the view returns all allocation amounts made from a fund against a sales territory hierarchy. In marketing analytics and trade fund management, allocations of budget from a parent fund are distributed across territory nodes, products, and geographic areas, and are recorded as metric facts. This view consolidates those metric fact rows with their governing metric definitions and provides a reporting and integration surface for querying forecasted allocation values. The presence of the FUNC_FORECASTED_VALUE column — the object matched by the user search term func_forecasted_value — makes the view particularly relevant to queries that evaluate forecast amounts expressed in the functional (ledger) currency rather than the transaction currency. Because it is exposed in the APPS schema, the view is accessible to standard EBS responsibilities, concurrent programs, Reports, Discoverer workbooks, and external integration layers that read through the Apps database account.

Underlying Base Objects

The view text joins three documented base objects, each of which is presented to the view as a synonym in the APPS schema. The driving table is AMS_ACT_METRIC_FACTS_ALL, aliased AMF, which stores the individual metric fact rows that carry the forecasted values, percentages, and territory/hierarchy context. It is joined to AMS_ACT_METRICS_ALL (AMC) on AMF.ACTIVITY_METRIC_ID = AMC.ACTIVITY_METRIC_ID, which links each fact to its activity metric instance. A second join to AMS_METRICS_ALL_B (MAL) on MAL.METRIC_ID = AMC.METRIC_ID resolves the underlying metric definition. This three-way structure means the view projects fact-level measures alongside the metric identity that qualifies them, while preserving every fact row — the joins are equi-joins on non-null identifiers and the view issues no aggregation, so the row grain remains one row per metric fact. All columns are projected directly from AMF; no columns are drawn from AMC or MAL, so those objects act as referential filters that restrict output to facts whose activity metric and metric definition both exist.

Key Columns

• ACTIVITY_METRIC_FACT_ID — Primary identifier for the metric fact row.
• FUNC_FORECASTED_VALUE — The forecasted allocation amount expressed in the functional currency; the column most frequently retrieved when searching on func_forecasted_value.
• TRANS_FORECASTED_VALUE — The same forecasted amount expressed in the transaction currency.
• FUNCTIONAL_CURRENCY_CODE and TRANSACTION_CURRENCY_CODE — Currency qualifiers corresponding to the two forecasted value columns.
• FACT_VALUE and FACT_PERCENT — The actual metric measure and its percentage weighting.
• ACTIVITY_METRIC_ID, DE_METRIC_ID, FORMULA_ID — Foreign keys tying the fact to its metric, denominator metric, and calculation formula.
• NODE_ID, LEVEL_DEPTH, HIERARCHY_ID — Territory hierarchy positioning of the allocation.
• DE_GEOGRAPHIC_AREA_ID, DE_GEOGRAPHIC_AREA_TYPE, DE_INVENTORY_ITEM_ID, DE_INVENTORY_ITEM_ORG_ID — Geographic and product dimensions.
• ORG_ID — Operating unit, enforcing multi-org security on the fact.
• FROM_DATE, TO_DATE — Effective period for the fact.
• VALUE_TYPE, ARC_ACT_METRIC_USED_BY — Classification of the value source.
• Standard WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical scenarios include reconciling fund allocations to territory hierarchies, reporting forecasted marketing spend by operating unit, and feeding allocation data into custom analytics or integration extracts. Because the view is not itself org-striped, queries should filter on ORG_ID where multi-org isolation is required, and should constrain by date using FROM_DATE/TO_DATE to bound the reporting period.

Retrieve forecasted functional values for a territory node within a period:

SELECT ACTIVITY_METRIC_FACT_ID,
       NODE_ID,
       LEVEL_DEPTH,
       FUNCTIONAL_CURRENCY_CODE,
       FUNC_FORECASTED_VALUE,
       FACT_VALUE,
       FACT_PERCENT
FROM   APPS.AMS_ACT_METRIC_FACTS_ALL_V
WHERE  ORG_ID = :p_org_id
AND    NODE_ID = :p_node_id
AND    TRUNC(FROM_DATE) >= :p_from
AND    TRUNC(TO_DATE)   <= :p_to;

Aggregate forecasted allocation by hierarchy level and operating unit:

SELECT ORG_ID,
       HIERARCHY_ID,
       LEVEL_DEPTH,
       FUNCTIONAL_CURRENCY_CODE,
       SUM(FUNC_FORECASTED_VALUE) forecasted_total
FROM   APPS.AMS_ACT_METRIC_FACTS_ALL_V
GROUP  BY ORG_ID, HIERARCHY_ID, LEVEL_DEPTH, FUNCTIONAL_CURRENCY_CODE;

Compare transaction and functional forecasted values to surface currency conversion effects:

SELECT ACTIVITY_METRIC_FACT_ID,
       TRANSACTION_CURRENCY_CODE,
       FUNCTIONAL_CURRENCY_CODE,
       TRANS_FORECASTED_VALUE,
       FUNC_FORECASTED_VALUE,
       TRANS_FORECASTED_VALUE - FUNC_FORECASTED_VALUE AS conversion_delta
FROM   APPS.AMS_ACT_METRIC_FACTS_ALL_V
WHERE  TRANSACTION_CURRENCY_CODE <> FUNCTIONAL_CURRENCY_CODE;

These queries illustrate the view's principal reporting role: exposing fund allocation facts, including the functional-currency forecasted value, in a form suitable for territory, product, and geographic analysis.