Search Results node_value

Overview

The view OZF_FUND_ALLOCTREE_V is an Oracle E-Business Suite Release 12.1.1 / 12.2.2 database object owned by the APPS schema and delivered as part of the OZF – Trade Management product family. It exposes fund allocation and holdback information organized along the trade management territory/activity hierarchy. The view is classified as VALID in the ETRM metadata and is a read-only, non-materialized view constructed entirely from select statements over base tables and supporting views within the Advanced Marketing (AMS) schema family.

The view's purpose is to present, for each ACTIVITY_METRIC_FACT_ID, the aligned pair of an "allocation" fact and its corresponding "holdback" fact at the same hierarchy node and depth level. It is therefore used in reporting and integration scenarios that require allocation amounts and their associated holdback reserves to be reported side-by-side, keyed by the hierarchy structure defined in AMS_TERR_V. The user search term "level_depth" is directly relevant: LEVEL_DEPTH is a first-class column of this view and is central to its self-join logic, participating in the join predicates that link facts to territory nodes and allocation facts to holdback facts.

Underlying Base Objects

The view is defined over four documented referenced base objects, all resolved through synonyms owned by APPS:

• AMS_ACT_METRICS_ALL (SYNONYM) — aliased as MTR; supplies the activity metric definition and the start node (START_NODE) used to null out the parent identifier for the root node.
• AMS_ACT_METRIC_FACTS_ALL (SYNONYM) — aliased as F (allocation fact) and F1 (holdback fact); the primary fact table, restricted to ARC_ACT_METRIC_USED_BY = 'FUND'.
• AMS_ACT_METRIC_FORMULAS (SYNONYM) — aliased as FO and FO1; filters FORMULA_TYPE = 'ALLOCATION' and FORMULA_TYPE = 'HOLDBACK' respectively.
• AMS_TERR_V (VIEW) — aliased as N; supplies the territory hierarchy node context, including NODE_VALUE and PARENT_ID.

The join structure pairs a fund allocation fact with a fund holdback fact sharing the same ACT_METRIC_USED_BY_ID, ACTIVITY_METRIC_ID, HIERARCHY_ID, LEVEL_DEPTH and NODE_ID.

Key Columns

• ALLOCATION_FACT_ID / HOLDBACK_FACT_ID — the ACTIVITY_METRIC_FACT_ID values for the allocation and holdback facts.
• ALLOCATION_AMOUNT / HOLDBACK_AMOUNT — the corresponding FACT_VALUE columns.
• ALLOCATION_FORMULA_ID / HOLDBACK_FORMULA_ID — formula identifiers for each side.
• HIERARCHY_ID, LEVEL_DEPTH, NODE_ID, NODE_VALUE, PARENT_NODE_ID — hierarchy placement; PARENT_NODE_ID is nulled at the metric start node.
• ACT_METRIC_USED_BY_ID, ACTIVITY_METRIC_ID — the metric linkage keys.
• ALLOCATION_VERSION_NUMBER / HOLDBACK_VERSION_NUMBER — object version numbers.

Common Use Cases and Queries

Typical uses include reconciliation of fund allocation against holdback by hierarchy node, depth-level reporting of trade funds, and integration extracts keying on LEVEL_DEPTH.

SELECT hierarchy_id, level_depth, node_value,
       allocation_amount, holdback_amount
  FROM apps.ozf_fund_alloctree_v
 WHERE hierarchy_id = :p_hierarchy_id
   AND level_depth  = :p_level_depth;

SELECT node_value,
       SUM(allocation_amount) total_alloc,
       SUM(holdback_amount)   total_holdback
  FROM apps.ozf_fund_alloctree_v
 GROUP BY node_value
 ORDER BY node_value;