Search Results ozf_funds_utilized_vl




Overview
Explore in AI Chat

Overview

OZF_FUNDS_UTILIZED_VL is a bilingual (language) view in the Oracle E-Business Suite Trade Management (OZF) module, owned by the APPS schema. It presents accrued and consumed fund utilization records — the transactional detail that links trade promotion funds, budgets, and plans to the objects that consume them, such as orders, invoices, and activities. The "_VL" suffix denotes a view that joins a base (non-translated) table with its translation table while applying Multi-Org and language security predicates. In 12.1.1 and 12.2.2 the view is reported as VALID, meaning the underlying synonyms and tables resolve correctly in a standard ETRM installation. The view is the primary reporting and integration surface for fund consumption data: it exposes the raw utilization rows together with the translated adjustment description, making it suitable for concurrent programs, OBIEE/BIP extracts, and custom code that must respect operating unit and language context.

Underlying Base Objects

The documented base objects are OZF_FUNDS_UTILIZED (synonym, resolving to OZF_FUNDS_UTILIZED_ALL_B) and OZF_FUNDS_UTILIZED_ALL_TL (synonym). The view text confirms the join: OZF_FUNDS_UTILIZED_ALL_B B is joined to OZF_FUNDS_UTILIZED_ALL_TL T on UTILIZATION_ID, with an additional ORG_ID match using NVL(T.ORG_ID,-99)=NVL(B.ORG_ID,-99). Two WHERE predicates enforce security: the ORG_ID filter derived from USERENV('CLIENT_INFO') scopes rows to the current operating unit (or all orgs when CLIENT_INFO is blank), and T.LANGUAGE = USERENV('LANG') restricts translation rows to the session language. The "_B" table holds the base transactional columns and the standard EBS WHO/audit and descriptive flexfield columns; the "_TL" table supplies ADJUSTMENT_DESC and language metadata. All columns from B are projected, and only ADJUSTMENT_DESC, LANGUAGE, and SOURCE_LANG are taken from T.

Key Columns

Common Use Cases and Queries

Typical usage includes reconciling fund balances, auditing accrual versus actual consumption, and feeding downstream analytics. Because the view enforces operating unit and language context, queries should be run from a session whose CLIENT_INFO and LANG are set appropriately (as EBS concurrent managers and forms do automatically).

List utilization by fund within the current operating unit:

SELECT utilization_id, fund_id, plan_type, plan_id,
       utilization_type, amount, amount_remaining,
       currency_code, gl_date
FROM   apps.ozf_funds_utilized_vl
WHERE  fund_id = :p_fund_id
ORDER  BY gl_date, utilization_id;

Summarize consumption against order and invoice documents:

SELECT object_type, order_id, invoice_id,
       SUM(amount) AS consumed_amount,
       SUM(amount_remaining) AS remaining_amount
FROM   apps.ozf_funds_utilized_vl
WHERE  fund_id = :p_fund_id
  AND  adjustment_type IS NULL
GROUP  BY object_type, order_id, invoice_id;

Retrieve adjusted lines with the translated description:

SELECT utilization_id, adjustment_type_id,
       adjustment_date, adjustment_desc,
       amount, acctd_amount
FROM   apps.ozf_funds_utilized_vl
WHERE  fund_id = :p_fund_id
  AND  adjustment_type_id IS NOT NULL;