Results for “ozf_act_metrics_all”

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

Overview

OZF_ACT_METRICS_ALL is a Trade Management (OZF) transactional table within Oracle EBS 12.1.1 and 12.2.2 that stores the calculated and staged values for budget or quota allocation metrics. It acts as the central repository where activity-level metrics — forecasted, committed, and actual amounts — are recorded in both transaction and functional currencies, and subsequently consumed by allocation, accrual, and settlement logic in Trade Management. Each row represents a single activity metric instance, keyed by ACTIVITY_METRIC_ID, and links that metric to the fund, application, scenario, and hierarchy context in which it was computed.

The ETRM metadata classifies this object heuristically as satellite-leaning under a Data Vault interpretation. In practice, the table behaves as a descriptive satellite attached to the business key ACTIVITY_METRIC_ID, carrying measured values and calculated attributes that change over time as refresh, rollup, and recalculation processes execute. It also exhibits link characteristics through its foreign-key references to funds, currencies, applications, metrics, and security groups.

Key Information Stored

The table contains 82 documented columns. The most operationally significant are listed below.

• ACTIVITY_METRIC_ID — surrogate primary key, enforced by OZF_ACT_METRICS_ALL_PK and also captured by unique index OZF_ACT_METRICS_ALL_U1, making it the primary business-key candidate.
• BUDGET_ID and METRIC_ID — identify the associated budget and the metric definition (METRIC_ID references AMS_METRICS_ALL_B).
• ACT_METRIC_USED_BY_ID — references OZF_FUNDS_ALL_B, indicating the fund that consumes the metric.
• APPLICATION_ID — references FND_APPLICATION, indicating the owning application context.
• TRANSACTION_CURRENCY_CODE and FUNCTIONAL_CURRENCY_CODE — both reference FND_CURRENCIES and drive the dual-currency value columns.
• TRANS_FORECASTED_VALUE, TRANS_COMMITTED_VALUE, TRANS_ACTUAL_VALUE — currency-amount measures in transaction currency.
• FUNC_FORECASTED_VALUE, FUNC_COMMITTED_VALUE, FUNC_ACTUAL_VALUE — corresponding functional-currency measures.
• VARIABLE_VALUE, FORECASTED_VARIABLE_VALUE, and COMPUTED_USING_FUNCTION_VALUE — derived or user-entered metric inputs.
• LAST_CALCULATED_DATE and DIFFERENCE_SINCE_LAST_CALC — recency and delta tracking for refresh logic.
• ORG_ID — operating-unit partitioning for multi-org access.
• SECURITY_GROUP_ID — references FND_SECURITY_GROUPS and governs row-level security.
• STATUS_CODE, ACTION_CODE, and METHOD_CODE — workflow and calculation-state indicators.

Descriptive and extensible columns include DESCRIPTION, ACT_METRIC_DATE, FROM_DATE, TO_DATE, START_PERIOD_NAME, END_PERIOD_NAME, and the fifteen ATTRIBUTE columns (ATTRIBUTE_CATEGORY through ATTRIBUTE15) for client extensions. The standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER support audit and optimistic locking.

Common Use Cases and Queries

Typical scenarios include budget-versus-actual reporting, quota allocation validation, accrual reconciliation, and diagnosis of stale metrics. A common pattern joins the table to its fund and metric parents, filtering by org and currency, for example:

SELECT m.activity_metric_id, m.budget_id, m.metric_id,
       m.trans_actual_value, m.func_actual_value, m.status_code
FROM   ozf_act_metrics_all m
WHERE  m.org_id = :p_org_id
AND    m.last_calculated_date >= :p_from_date;

Analysts frequently isolate rows needing recalculation using DIRTY_FLAG or a large DIFFERENCE_SINCE_LAST_CALC, and use DAYS_SINCE_LAST_REFRESH to identify stale aggregates. Rollup and hierarchy analysis leverages HIERARCHY_ID, START_NODE, FROM_LEVEL, TO_LEVEL, and the SUMMARIZE_TO_METRIC / ROLLUP_TO_METRIC columns. Because AMOUNT1–AMOUNT3 and PERCENT1–PERCENT3 carry flexible measure slots, reports often pivot on these when custom metric configurations are in use.

Related Objects

• OZF_ACT_METRIC_FACTS_ALL — child table; joins on ACTIVITY_METRIC_ID, storing fact-level detail for each metric.
• OZF_ACT_METRIC_FORMULAS — child table; joins on ACTIVITY_METRIC_ID, holding formula definitions applied to the metric.
• OZF_FUNDS_ALL_B — parent; joined via ACT_METRIC_USED_BY_ID to resolve the consuming fund.
• AMS_METRICS_ALL_B — parent; joined via METRIC_ID for metric master definitions.
• FND_CURRENCIES — joined via TRANSACTION_CURRENCY_CODE and FUNCTIONAL_CURRENCY_CODE.
• FND_APPLICATION — joined via APPLICATION_ID for application context.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for row-level security enforcement.

Together these relationships confirm the table's central role in the Trade Management metric calculation and allocation chain.