Results for “transaction_currency_code”

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

Overview

The view AMS_FUND_ALLOCATIONS_V is an Oracle Applications (APPS) database object owned by the APPS schema and defined within the AMS – Marketing product module. It exposes a filtered, consolidated projection of marketing activity metric facts that are specifically associated with fund allocation processing. In Oracle EBS 12.1.1 and 12.2.2, this view serves as a reporting and integration layer over the underlying activity metric fact storage, allowing downstream concurrent programs, reports, and interfaces to retrieve allocation-related metrics without directly navigating the base transaction tables.

The view is constrained so that only records whose associated activity metric is used by "FUND" and whose metric calculation type is "ALLOCATION" are returned. This makes it a purpose-built data source for marketing fund allocation analysis, where budget or fund amounts are distributed across activities, geographies, products, or hierarchy nodes.

Underlying Base Objects

According to the documented ETRM 12.2.2 metadata, AMS_FUND_ALLOCATIONS_V is defined over three referenced synonyms that resolve to base application tables:

• AMS_ACT_METRIC_FACTS_ALL – the primary fact table supplying the measured or forecasted values.
• AMS_ACT_METRICS_ALL – supplies activity metric definitions and the ARC_ACT_METRIC_USED_BY attribute.
• AMS_METRICS_ALL_B – supplies metric-level attributes including METRIC_CALCULATION_TYPE.

The join conditions link ACTIVITY_METRIC_ID between the facts and activity metrics tables, and METRIC_ID between the activity metrics and metrics base tables. The filters ARC_ACT_METRIC_USED_BY = 'FUND' and METRIC_CALCULATION_TYPE = 'ALLOCATION' restrict the result set to fund allocation facts. Because the view is defined with an inner join against AMS_METRICS_ALL_B, only metrics with a matching base metric record are returned.

The _ALL suffix on the fact and activity metric tables indicates they are multi-organization (operating unit) enabled, and the view exposes the ORG_ID column accordingly.

Key Columns

The view projects a broad set of columns from the underlying fact table. Notable columns include:

• ACTIVITY_METRIC_FACT_ID – primary identifier of the metric fact row.
• ACTIVITY_METRIC_ID – foreign key to the activity metric definition.
• FACT_VALUE – the core numeric allocation value for the fact; this is the column most often queried for fund allocation amounts.
• FACT_PERCENT – the percentage representation of the allocation, where applicable.
• VALUE_TYPE – indicates the nature or classification of the value.
• TRANSACTION_CURRENCY_CODE and TRANS_FORECASTED_VALUE – transaction-level currency and forecasted amount.
• FUNCTIONAL_CURRENCY_CODE and FUNC_FORECASTED_VALUE – functional currency and converted forecasted amount.
• FROM_DATE and TO_DATE – effective date range of the allocation fact.
• HIERARCHY_ID, NODE_ID, and LEVEL_DEPTH – hierarchy context for the allocation.
• ORG_ID – operating unit identifier.
• Standard audit columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

This view is typically used for fund allocation reporting, reconciliation of forecasted versus functional values, and integration extracts feeding marketing analytics. A representative query retrieving allocation facts by operating unit and date range is:

• SELECT activity_metric_id, fact_value, fact_percent, trans_forecasted_value, func_forecasted_value, from_date, to_date FROM ams_fund_allocations_v WHERE org_id = :p_org_id AND from_date >= :p_from_date AND to_date <= :p_to_date;

To aggregate allocated values by activity metric:

• SELECT activity_metric_id, SUM(fact_value) total_alloc, SUM(func_forecasted_value) total_func FROM ams_fund_allocations_v GROUP BY activity_metric_id;

Because the view already filters to METRIC_CALCULATION_TYPE = 'ALLOCATION', callers do not need to reapply that predicate. Standard EBS multi-org security policies should be enforced through the ORG_ID column when querying in a multi-organization context.