Search Results start_node

Overview

OZF_FUND_ALLOCATIONS_V is an APPS-owned view within the Oracle Trade Management (OZF) module, exposing the relationship between trade promotion funds and the activity metrics that allocate or measure them. It is built by joining the fund definition layer to the activity-metric layer in Oracle Marketing (AMS), producing a single denormalized row per fund-to-metric association. In ETRM 12.1.1 and 12.2.2 this view serves reporting and integration consumers that need to answer which metrics — and at which hierarchy level, node, and date range — are attached to a given fund, without navigating the AMS activity-metric tables directly.

The view is defined with a fixed filter, ARC_ACT_METRIC_USED_BY = 'FUND'. Because of this predicate, only activity metrics whose "used by" context is a fund appear in the resultset; metrics consumed by promotions, offers, or other objects are excluded. The join to the fund side is made through ACT_METRIC_USED_BY_ID = FND.FUND_ID, giving the view its fund-centric perspective.

Underlying Base Objects

The view is defined over two documented base objects:

• AMS_ACT_METRICS_ALL (alias MTR, exposed as a synonym) — the activity-metric fact source. It supplies the metric identity, hierarchy context, volume level, date effectiveness, and actual value columns.
• OZF_FUND_DETAILS_V (alias FND) — a fund-details view that supplies fund identity, name, active dates, category, and fund type.

Functionally, OZF_FUND_DETAILS_V acts as the restricted fund master, while AMS_ACT_METRICS_ALL provides the measurable allocation detail. The equality join on the "used by" identifier and the 'FUND' literal ties the two together, so the grain of the view is one row per fund/activity-metric pairing.

Key Columns

• FUND_ID — Primary fund identifier; the join key to the fund side and the column most consumers filter on.
• SHORT_NAME — Fund short name, useful for user-facing reports and list-of-values displays.
• START_DATE_ACTIVE / END_DATE_ACTIVE — Active date range of the fund itself.
• CATEGORY_ID / CATEGORY / FUND_TYPE — Fund classification attributes used for grouping and segmentation.
• ACTIVITY_METRIC_ID / METRIC_ID — Identifiers for the activity metric attached to the fund.
• HIERARCHY_ID, FROM_LEVEL, TO_LEVEL — The hierarchy and the level range over which the metric is applied.
• START_NODE — The starting node of the hierarchy branch against which the metric is evaluated; this is the field most frequently referenced when searching for allocation scope.
• FROM_DATE / TO_DATE — Effective date range of the metric allocation, which may differ from the fund's own active dates.
• FUNC_ACTUAL_VALUE, METRIC_UOM_CODE — The functional actual value and its unit of measure, used in metric-vs-target analysis.
• ACT_METRIC_USED_BY_ID, ARC_ACT_METRIC_USED_BY — The polymorphic "used by" keys; ARC_ACT_METRIC_USED_BY is always 'FUND' here, and ACT_METRIC_USED_BY_ID equals FUND_ID.

Common Use Cases and Queries

Typical uses include fund allocation reporting, metric coverage audits, and integration extracts that feed downstream trade-spend analytics. The following example returns the metric scope for a specific fund:

SELECT fund_id, short_name, activity_metric_id, hierarchy_id,
       from_level, to_level, start_node, from_date, to_date,
       func_actual_value, metric_uom_code
FROM   ozf_fund_allocations_v
WHERE  fund_id = :p_fund_id
ORDER  BY from_date, activity_metric_id;

To enumerate allocations that begin at a particular hierarchy node:

SELECT fund_id, short_name, hierarchy_id, from_level, to_level,
       start_node, from_date, to_date
FROM   ozf_fund_allocations_v
WHERE  start_node = :p_start_node;

Because the view joins AMS and OZF objects, queries should always constrain on FUND_ID, START_NODE, or date columns to limit row counts. Joining back to OZF_FUND_DETAILS_V or the underlying fund tables is unnecessary, since fund attributes are already projected.