Search Results fii_ar_trx_dist_f




Overview
Explore in AI Chat

Overview

FII.FII_AR_TRX_DIST_F is the Accounts Receivable Revenue Fact table within the Oracle E-Business Suite Financial Intelligence (FII) product family. It is a materialized analytical structure that consolidates revenue distribution data drawn from Oracle Receivables transaction distributions, enriched with dimensions spanning customers, sales representatives, products, campaigns, channels, projects, and GL accounting flexfields. In Oracle EBS 12.1.1 and 12.2.2, the table carries a status of VALID and is owned by the FII schema, exposing 134 documented columns. It functions as the primary fact source for AR revenue analytics, subledger reconciliation, margin analysis, and period-over-period revenue reporting.

The ETRM relationship metadata classifies this object heuristically as standalone under the Data Vault modeling heuristic. In Data Vault terms, that classification suggests the table is best modeled as a fact or satellite-style structure rather than as a hub or link, because it does not participate in a dense foreign-key mesh within the documented schema. Only one foreign key is documented, from INVOICE_LINE_ID to FTE_INVOICE_LINES, which reinforces the fact-table interpretation: dimension references are carried as denormalized _FK_KEY surrogate columns rather than enforced relational constraints.

Key Information Stored

The table stores revenue at the invoice distribution grain, keyed by INVOICE_ID and INVOICE_DIST_ID, with INVOICE_LINE_ID providing the linkage back to the source invoice line. Two unique indexes are documented as business-key candidates: FII_AR_TRX_DIST_F_U1 on INVOICE_PK and FII_AR_TRX_DIST_F_U2 on INVOICE_PK_KEY. These serve as the surrogate/business-key anchors for the fact rows.

Common Use Cases and Queries

Typical usage centers on revenue reporting, subledger-to-GL reconciliation, and revenue decomposition by customer, product, sales representative, or campaign. Analysts filter by GL_DATE or INVOICE_DATE for period reporting and use AMT_G as the functional-currency measure. A representative query aggregates revenue by ledger, period, and account segment:

  • Revenue by period and set of books: SELECT SET_OF_BOOKS_FK_KEY, GL_DATE, SUM(AMT_G) FROM FII_AR_TRX_DIST_F GROUP BY SET_OF_BOOKS_FK_KEY, GL_DATE;
  • Revenue by account: join on GL_ACCT1_FK_KEY–GL_ACCT10_FK_KEY keys and the corresponding FII GL account dimension.
  • Customer revenue: filter SOLD_TO_CUSTOMER_FK_KEY or BILL_TO_CUSTOMER_FK_KEY and group by date.
  • Credit and return analysis: filter on TRANSACTION_CLASS or INVOICE_REASON to isolate credits.
  • Lineage drill-down: join to FTE_INVOICE_LINES on INVOICE_LINE_ID to reach the source transaction line.

Related Objects

Because the documented FK graph is sparse, the most significant dependencies are the single documented FK target plus the FII dimension tables pointed to by the _FK_KEY columns.