Search Results ozf_funds_utilized

Overview

APPS.OZF_FUNDS_UTILIZED_VL is a bilingual (language-specific) presentation view in the Oracle E-Business Suite Trade Management (formerly Oracle Advanced Marketing / Oracle Funds) module. It exposes the transactional records that track how funds — accruals, budgets, and offers — have been consumed or drawn down against marketing activities, orders, and invoices. In EBS 12.1.1 and 12.2.2 the view is owned by APPS and is intended for reporting, inquiry, and integration rather than for direct DML.

The "_VL" suffix denotes a view that joins a base transactional table to a translated table so that a user-facing description is returned in the session's language. The view resolves the language via userenv('LANG'), meaning each query returns descriptive text appropriate to the logged-in user's locale. This makes it the preferred source for OBIEE, Discoverer, custom concurrent programs, and SOA/interface extracts that must present adjustment descriptions in the correct language.

Underlying Base Objects

The view is defined over two documented base objects:

• OZF_FUNDS_UTILIZED (referenced as synonym B) — the primary transactional table holding every fund-utilization record and its foreign keys, amounts, and descriptive flexfield attributes.
• OZF_FUNDS_UTILIZED_ALL_TL (referenced as synonym T) — the translation table supplying the language-dependent adjustment_desc column.

The join condition is B.utilization_id = T.utilization_id AND T.LANGUAGE = userenv('LANG'), with B.ROWID selected to preserve row identity for tools that require it. Because it is a view rather than a table, no data is stored; all columns are derived at runtime from the two base synonyms.

Key Columns

• utilization_id — Primary key identifying a single utilization record.
• utilization_type / adjustment_type / adjustment_type_id — Classify the nature of the utilization or adjustment, with the description resolved from the translation table as adjustment_desc.
• fund_id, plan_type, plan_id, component_type, component_id — Establish the fund, plan, and component context to which the utilization belongs.
• object_type, object_id, order_id, order_line_id, invoice_id — Point to the transaction that consumed the funds.
• amount, amount_remaining, acctd_amount, acctd_amount_remaining — Entered and accounted balances, plus their remaining (unconsumed) values.
• currency_code, exchange_rate_type, exchange_rate_date, exchange_rate — Currency and conversion context.
• plan_curr_amount, plan_curr_amount_remaining, univ_curr_amount, univ_curr_amount_remaining — Plan-currency and universal-currency equivalents for cross-currency reporting.
• scan_unit, scan_unit_remaining, activity_product_id, product_id, product_level_type, volume_offer_tiers_id — Volume/scan-based offer tracking columns.
• attribute_category, attribute1–attribute15 — Descriptive flexfield segments.
• org_id — Multi-org operating unit identifier, essential for RLS-secured queries.
• gl_posted_flag, gl_date — Accounting status and date.
• reference_type, reference_id, orig_utilization_id, price_adjustment_id, camp_schedule_id, billto_cust_account_id, ship_to_site_use_id, bill_to_site_use_id — Supporting references to campaigns, schedules, customers, and sites.
• Standard WHO columns (creation_date, created_by, last_update_date, last_updated_by, last_update_login, request_id, program_application_id, program_id, program_update_date) and object_version_number for audit and concurrency.

Common Use Cases and Queries

Typical scenarios include analyzing funds consumed versus remaining, reconciling budget accruals against orders and invoices, and extracting utilization detail for external reporting. A representative query is:

SELECT utilization_id, fund_id, adjustment_type, adjustment_desc,
       amount, amount_remaining, currency_code, org_id
FROM   apps.ozf_funds_utilized_vl
WHERE  org_id = :p_org_id
AND    creation_date >= :p_from_date;

Aggregations reconcile consumed balances per fund:

SELECT fund_id, currency_code, SUM(amount) used, SUM(amount_remaining) remaining
FROM   apps.ozf_funds_utilized_vl
GROUP  BY fund_id, currency_code;

Because multi-org security and language translation apply, BI Publisher reports and interfaces should always bind org_id and rely on the view's built-in userenv('LANG') filter for correct descriptive text. Direct updates should target the base table, not the view.