Results for “warehouse”

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

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.

• INVOICE_PK / INVOICE_PK_KEY — unique business-key candidates (per U1 and U2).
• INVOICE_ID, INVOICE_DIST_ID, INVOICE_LINE_ID, INVOICE_LINE_NUMBER — grain and source identifiers.
• INVOICE_NUMBER, ORIGINAL_INVOICE_NUM, ORIGINAL_INVOICE_LINE_NUM, INVOICE_REASON — invoice and credit context.
• AMT_B, AMT_G, AMT_T — revenue amounts in entered, functional, and transaction currency.
• EXCHANGE_RATE, EXCHANGE_RATE_TYPE, EXCHANGE_DATE — currency conversion attributes.
• FUNCTIONAL_CURRENCY_FK_KEY, TRANSACTION_CURRENCY_FK_KEY, SET_OF_BOOKS_FK_KEY — ledger and currency dimensions.
• INVOICE_DATE, GL_DATE, GL_POSTED_DATE, SALES_ORDER_DATE — date dimensions.
• GL_ACCT1_FK_KEY through GL_ACCT10_FK_KEY — GL accounting flexfield segment references.
• SOLD_TO_CUSTOMER_FK_KEY, BILL_TO_CUSTOMER_FK_KEY, SHIP_TO_CUSTOMER_FK_KEY, END_USER_CUSTOMER_FK_KEY, RESELLER_CUSTOMER_FK_KEY — customer dimensions.
• ITEM_FK_KEY, PARENT_ITEM_FK_KEY, ORGANIZATION_FK_KEY, UOM_FK_KEY — product dimensions.
• INVOICE_LINE_QTY, ORDER_LINE_QTY, QTY_CREDITED, UNIT_SELLING_PRICE, SO_LINE_SELLING_PRICE — quantity and price measures.
• TRANSACTION_STATUS, TRANSACTION_CLASS, LINE_TYPE, ACCOUNT_TYPE, ACCOUNT_CLASS — classification attributes.
• SALES_ORDER_NUMBER, ORDER_LINE_ID, SALES_ORDER_LINE_NUMBER, SALES_ORDER_SOURCE — order capture lineage.
• USER_ATTRIBUTE1 through USER_ATTRIBUTE25 and USER_MEASURE1 through USER_MEASURE5 — extensibility slots.
• CREATION_DATE, LAST_UPDATE_DATE — audit timestamps.

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.

• FTE_INVOICE_LINES — referenced via FII_AR_TRX_DIST_F.INVOICE_LINE_ID (the only documented foreign key).
• FII customer dimension, joined on SOLD_TO_CUSTOMER_FK_KEY, BILL_TO_CUSTOMER_FK_KEY, SHIP_TO_CUSTOMER_FK_KEY, END_USER_CUSTOMER_FK_KEY, RESELLER_CUSTOMER_FK_KEY.
• FII product/item dimension, joined on ITEM_FK_KEY, PARENT_ITEM_FK_KEY, ORGANIZATION_FK_KEY, UOM_FK_KEY.
• FII GL account dimension, joined on GL_ACCT1_FK_KEY–GL_ACCT10_FK_KEY.
• FII sales representative dimension, joined on SALESREP_FK_KEY, PRIM_SALESREP_FK_KEY, SALESRESOURCE_FK_KEY, PRIM_SALESRESOURCE_FK_KEY.
• FII date dimension, joined on INVOICE_DATE_FK_KEY, GL_DATE_FK_KEY, SALES_ORDER_DATE_FK_KEY.
• Oracle Receivables base tables (RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL) as the ultimate source of the materialized distribution rows.