Results for “fii_ar_net_rec_base_mv_f_v”

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

Overview

FII_AR_NET_REC_BASE_MV_F_V is an APPS-owned database view within the Oracle E-Business Suite Financial Intelligence (FII) product family. In EBS 12.1.1 and 12.2.2, FII delivers the Receivables and Collections analytics that feed Oracle Business Intelligence and the embedded Daily Business Intelligence (DBI) dashboards. The suffix pattern _MV_F_V indicates this is a view layered over a materialized view, with the "_F" component conventionally denoting a "functional currency" or "fact" presentation and "_V" confirming the object is a view rather than the physical container.

The view's role is to expose pre-aggregated net receivables and collections metrics at a dimensional grain that reporting tools can consume directly. Rather than recomputing aging buckets, receipt activity, and discount statistics from the AR transaction tables at query time, the view surfaces values already consolidated by the materialized view beneath it. This makes it suitable for high-volume dashboards, ad hoc analysis by collectors and credit managers, and as a data source for custom extracts feeding a warehouse.

Underlying Base Objects

The documented view text shows a straightforward projection from a single materialized view:

• FROM FII_AR_NET_REC_BASE_MV — the sole referenced object. The view selects a fixed list of columns from this materialized view without joins, filters, or expressions.

The naming convention implies that FII_AR_NET_REC_BASE_MV is itself the base materialized view built over the underlying AR tables (such as RA_CUSTOMER_TRX_ALL, AR_CASH_RECEIPTS_ALL, AR_PAYMENT_SCHEDULES_ALL, and related party, site, and collector entities). The ETRM metadata does not document those physical base tables explicitly, so any deeper lineage assertion should be verified against the materialized view definition in the target instance. Practically, the view exists to decouple report SQL from the materialized view, allowing Oracle to revise the physical MV without breaking dependent reports.

Key Columns

The view exposes a wide fact-and-dimension surface. Important columns include:

• Dimensional keys: TIME_ID, PERIOD_TYPE_ID, PARENT_PARTY_ID, PARTY_ID, CUST_ACCOUNT_ID, COLLECTOR_ID, ORG_ID, CLASS_CODE, CLASS_CATEGORY. These define the grain — time period, customer hierarchy, collector, operating unit, and customer class.
• HEADER_FILTER_DATE: supports date-based slicing of the fact set.
• Aging buckets: CURRENT_BUCKET_1..3 and PAST_DUE_BUCKET_1..7, each paired with an _AMOUNT_FUNC and _COUNT column, providing the standard receivables aging ladder.
• Open balances: CURRENT_OPEN_AMOUNT_FUNC, CURRENT_OPEN_COUNT, PAST_DUE_OPEN_AMOUNT_FUNC, PAST_DUE_COUNT, TOTAL_OPEN_AMOUNT_FUNC, TOTAL_OPEN_COUNT.
• Days-sales-outstanding metrics: WTD_TERMS_OUT_OPEN_NUM_FUNC, WTD_TERMS_OUT_CURRENT_NUM_FUNC, WTD_DDSO_DUE_NUM_FUNC, AVG_DD_NUM_FUNC, WTD_DAYS_PAID_NUM_FUNC, WTD_TERMS_PAID_NUM_FUNC.
• Transaction activity: INV_AMOUNT_FUNC (invoices), DM_AMOUNT_FUNC (debit memos), CB_AMOUNT_FUNC (chargebacks), BR_AMOUNT_FUNC (credit memos), DEP_AMOUNT_FUNC, ON_ACCOUNT_CREDIT_AMOUNT_FUNC, UNAPP_DEP_AMOUNT_FUNC.
• Receipts and discounts: APP_AMOUNT_FUNC, APP_COUNT, ON_ACCOUNT_CASH_AMOUNT_FUNC, PREPAYMENT_AMOUNT_FUNC, TOTAL_RECEIPT_AMOUNT_FUNC, TOTAL_RECEIPT_COUNT, EARNED_DISCOUNT_AMOUNT_FUNC, UNEARNED_DISCOUNT_AMOUNT_FUNC, CLAIM_AMOUNT_FUNC, REV_AMOUNT_FUNC, REV_COUNT.
• Billing: BILLED_AMOUNT_FUNC, BILLING_ACTIVITY_AMOUNT_FUNC, BILLING_ACTIVITY_COUNT.
• Technical: GID and UMARKER, used for load/refresh tracking of the materialized view.

Columns carrying the _FUNC suffix hold amounts in the ledger's functional currency, which is important for multi-currency implementations where transactional amounts differ from functional balances.

Common Use Cases and Queries

Typical scenarios include collector performance reporting, aging analysis by customer class, and feeding external BI models. Because the view is pre-aggregated, queries should apply dimension filters to leverage the materialized view's indexes.

• Aging by customer account for a period:
  SELECT cust_account_id, collector_id, current_bucket_1_amount_func, past_due_bucket_1_amount_func, past_due_bucket_2_amount_func, total_open_amount_func FROM fii_ar_net_rec_base_mv_f_v WHERE time_id = :p_time_id AND org_id = :p_org_id;
• DSO trend:
  SELECT time_id, AVG(wtd_ddso_due_num_func) dso FROM fii_ar_net_rec_base_mv_f_v GROUP BY time_id ORDER BY time_id;
• Receipts versus billing activity:
  SELECT time_id, SUM(total_receipt_amount_func) receipts, SUM(billing_activity_amount_func) billing FROM fii_ar_net_rec_base_mv_f_v GROUP BY time_id;

Before relying on the object, DBAs should confirm its status is VALID relative to the current FII patch level and verify materialized view refresh schedules, since stale MV data propagates directly into every query against this view.