Search Results fii_ar_prod_rev_fcv

Overview

FII_AR_PROD_REV_FCV is an internal Oracle EBS view delivered within the Financial Intelligence (FII) product family. Its documented purpose is to support the Product Revenue Portlet, one of the analytical components surfaced through the Financial Intelligence dashboards and Oracle Business Intelligence integration layer. The view presents Accounts Receivable distribution activity in a denormalized, conformed format suitable for loading into the Enterprise Data Warehouse (EDW) or for direct consumption by BI portlet queries.

The suffix "FCV" (Fact Collection View) indicates that this object functions as a staging or collection view whose rows will ultimately be materialized as facts within the FII analytical schema. Each row represents a general ledger distribution line tied to an AR customer transaction line, enriched with set of books, operating unit, customer site, and item dimensions. A notable characteristic confirmed in the metadata is that the view is documented as "Not implemented in this database" in the reference environment, meaning it may not be deployed in every instance and typically appears only where the Financial Intelligence product is licensed and configured.

Underlying Base Objects

The ETRM metadata for this object does not enumerate a formal list of referenced base tables, but the embedded view text exposes the complete join structure. The view is defined over the following Oracle EBS base tables and public views:

• RA_CUST_TRX_LINE_GL_DIST_ALL — the AR distribution lines, aliased CTLGD, which supply the accounted amount and GL date.
• RA_CUSTOMER_TRX_LINES_ALL — transaction lines (CTL), providing item and interface line context.
• RA_CUSTOMER_TRX_ALL — transaction headers (CT), supplying transaction date, bill-to site, and completion status.
• GL_CODE_COMBINATIONS — the accounting flexfield combination (GCC), linking distributions to company cost center segments.
• GL_SETS_OF_BOOKS — the ledger definition (SOB), supplying the functional currency code.
• EDW_LOCAL_INSTANCE — a Financial Intelligence instance-registration object (LI) that stamps each row with the source instance code.

The view is a UNION ALL construct, with the first branch covering standard AR distributions and a second branch covering adjustments. Filtering is applied so that only completed transactions (COMPLETE_FLAG = 'Y'), non-accounting distributions (ACCOUNT_SET_FLAG = 'N'), and non-zero amounts are returned. Interface lines originating from Order Entry or GEMMS source systems are conditionally included based on attribute parsing, which prevents duplication of revenue already captured through other FII collection paths.

Key Columns

• BASE_CURRENCY — derived from GL_SETS_OF_BOOKS.CURRENCY_CODE; the functional currency of the set of books. Because the distribution amount is taken from ACCTD_AMOUNT, all amounts are expressed in this base (functional) currency, and no conversion is required by downstream consumers.
• VIEW_TYPE — a literal value ('NON-GEMMS') identifying the collection path, used to segregate facts from alternative revenue sources.
• SET_OF_BOOKS_FK / SET_OF_BOOKS_ID — the conformed ledger key, concatenated with the instance code for cross-instance uniqueness.
• OU_ORG_FK — the operating unit key, defaulted to 'NA_EDW' when no org identifier is available.
• BILL_TO_CUSTOMER_FK — the customer site use key, suffixed with 'CUST_SITE_USE' to denote the dimension type.
• COMPANY_CC_ORG_FK — the company cost center organization key derived from the accounting flexfield.
• GL_DATE / GL_DATE_FILTER — the truncated accounting date used for period-based reporting.
• INVOICE_DATE — the truncated transaction date.
• ITEM_FK, INSTANCE_FK — the inventory item and source instance identifiers, with 'NA_EDW' as the placeholder for unknown items.
• AMT_B — the accounted amount in base currency; this is the primary revenue measure of the fact row.

Common Use Cases and Queries

Because this view underpins the Product Revenue Portlet, primary consumption occurs through the FII extract process rather than ad hoc SQL. Analysts may nonetheless query it for reconciliation between AR distributions and reported product revenue, or to validate that base currency amounts align with ledger balances.

• Revenue by base currency and ledger: SELECT base_currency, set_of_books_id, SUM(amt_b) FROM fii_ar_prod_rev_fcv GROUP BY base_currency, set_of_books_id;
• Product revenue trend by item for a fiscal period, filtering on gl_date_filter.
• Reconciliation of AR distribution totals to the GL for a given set of books and operating unit.
• Cross-instance aggregation for multi-org, multi-ledger deployments using instance_fk.

When the view is unavailable (as in instances where it is not implemented), equivalent reporting should be constructed directly against RA_CUST_TRX_LINE_GL_DIST_ALL joined to GL_SETS_OF_BOOKS, applying the same completion, accounting-set, and non-zero amount predicates documented above.