Search Results holdback_formula_id

Overview

APPS.OZF_FUND_ALLOCTREE_V is a reporting view in the Oracle E-Business Suite 12.1.1 and 12.2.2 environments, owned by the APPS schema and registered under FND Design Data as OZF.OZF_FUND_ALLOCTREE_V. It presents allocation and holdback fund distribution data in a hierarchical (tree) structure, joining allocation fact information with activity metric and territory hierarchy data. The view is designed to expose both the transactional allocation and holdback amounts alongside their associated version numbers, formula references, and node-level hierarchy attributes. Because it consolidates fact-level and hierarchy-level information into a single queryable object, it is typically used for reporting, analysis, and integration rather than as a transactional base table. The view status is VALID, and it is not referenced by any other database object, which indicates that it functions as a leaf-level reporting object within the OZF (Trade Management) module family.

Underlying Base Objects

Per the documented ETRM metadata, OZF_FUND_ALLOCTREE_V is defined over four referenced objects within the APPS schema: AMS_ACT_METRICS_ALL, AMS_ACT_METRIC_FACTS_ALL, AMS_ACT_METRIC_FORMULAS, and AMS_TERR_V. The first three are exposed as synonyms pointing to the underlying activity metrics, activity metric facts, and activity metric formula definitions that belong to the Oracle Marketing (AMS) foundation. AMS_TERR_V is a view supplying territory hierarchy information. Together these objects provide the metric definitions, the calculated fact values (allocation and holdback amounts), the formula metadata that drove those calculations, and the territory tree used for tree-based rollups. The view therefore bridges fund allocation facts with the metric and territory framework that governs them.

Key Columns

• ALLOCATION_FACT_ID — Identifier of the allocation fact record, linking to AMS_ACT_METRIC_FACTS_ALL.
• HOLDBACK_FACT_ID — Identifier of the corresponding holdback fact record.
• ACT_METRIC_USED_BY_ID — Reference to the activity metric consumer/usage definition.
• ACTIVITY_METRIC_ID — Foreign key to the activity metric (AMS_ACT_METRICS_ALL) driving the calculation.
• ALLOCATION_FORMULA_ID / HOLDBACK_FORMULA_ID — References to the formulas (AMS_ACT_METRIC_FORMULAS) used to compute allocation and holdback values.
• ALLOCATION_AMOUNT — The computed allocation amount, the value most commonly searched by users.
• HOLDBACK_AMOUNT — The computed holdback amount retained from the allocation.
• ALLOCATION_VERSION_NUMBER / HOLDBACK_VERSION_NUMBER — Version identifiers supporting historical or re-run calculations.
• HIERARCHY_ID — Identifier of the territory hierarchy in use.
• LEVEL_DEPTH — Depth of the node within the hierarchy tree.
• NODE_ID / NODE_VALUE — Numeric identifier and descriptive value of the hierarchy node.
• PARENT_NODE_ID — Parent node reference, enabling recursive tree traversal.

Common Use Cases and Queries

The view is most frequently queried to retrieve allocation amounts for a given metric, formula, or territory node, and to reconstruct hierarchy rollups using NODE_ID and PARENT_NODE_ID. A typical query filters on ALLOCATION_AMOUNT or ACTIVITY_METRIC_ID:

• SELECT ALLOCATION_FACT_ID, ACTIVITY_METRIC_ID, ALLOCATION_AMOUNT, ALLOCATION_VERSION_NUMBER FROM APPS.OZF_FUND_ALLOCTREE_V WHERE ALLOCATION_AMOUNT IS NOT NULL;
• SELECT NODE_ID, NODE_VALUE, LEVEL_DEPTH, PARENT_NODE_ID, ALLOCATION_AMOUNT FROM APPS.OZF_FUND_ALLOCTREE_V WHERE HIERARCHY_ID = :p_hierarchy_id ORDER BY LEVEL_DEPTH;
• SELECT ACTIVITY_METRIC_ID, SUM(ALLOCATION_AMOUNT) FROM APPS.OZF_FUND_ALLOCTREE_V GROUP BY ACTIVITY_METRIC_ID;

These patterns support reconciliation of allocation versus holdback amounts, verification of formula-driven results, and territory-based fund distribution reporting.