Search Results end_user_customer_fk_key

Overview

FII.FII_AR_TRX_DIST_F is the Accounts Receivable Revenue Fact table in the Oracle E-Business Suite Financial Intelligence (FII) analytics schema. It stores detailed invoice distribution line information, where each row represents an individual debit or credit account entry generated during invoice creation. The table is populated by the FII ETL processes that extract from Oracle Receivables and transform subledger accounting distributions into a dimensional fact structure used by Oracle Daily Business Intelligence, Oracle BI Applications, and custom revenue analytics.

The object resides in the APPS_TS_SUMMARY tablespace with PCT Free 10, is owned by the FII schema, and carries 134 documented columns in ETRM 12.1.1. Heuristic Data Vault classification derived from the foreign key structure treats this object as standalone; from a dimensional modeling perspective it is best understood as a fact table surrounded by foreign key keys rather than a strict hub, link, or satellite construct. Two unique indexes, FII_AR_TRX_DIST_F_U1 on INVOICE_PK and FII_AR_TRX_DIST_F_U2 on INVOICE_PK_KEY, establish business-key candidates distinct from the internal surrogate key.

Key Information Stored

The most consequential columns fall into identifier, accounting, customer, and measure groups:

• INVOICE_PK / INVOICE_PK_KEY — surrogate and business-key candidates enforced by the two unique indexes; the primary join keys to the invoice-level dimension of the fact model.
• INVOICE_ID, INVOICE_LINE_ID, INVOICE_DIST_ID — transactional identifiers linking back to the source Receivables invoice, line, and distribution records.
• INVOICE_NUMBER, INVOICE_LINE_NUMBER, ORIGINAL_INVOICE_NUM — human-readable document references for reporting.
• ACCOUNT_ID, ACCOUNT_TYPE, ACCOUNT_CLASS — GL account identification and classification for the debit or credit entry.
• GL_ACCT1_FK_KEY through GL_ACCT10_FK_KEY — the ten accounting flexfield segment keys, each indexed by its own bitmap index.
• GL_DATE_FK_KEY, GL_DATE, GL_POSTED_DATE — accounting date dimension and posted-date attributes.
• END_USER_CUSTOMER_FK_KEY, SOLD_TO_CUSTOMER_FK_KEY, BILL_TO_CUSTOMER_FK_KEY, SHIP_TO_CUSTOMER_FK_KEY, RESELLER_CUSTOMER_FK_KEY — customer party references by role.
• AMT_B, AMT_G, AMT_T — base, global (functional), and transaction currency amounts representing the distribution value.
• FUNCTIONAL_CURRENCY_FK_KEY, TRANSACTION_CURRENCY_FK_KEY, EXCHANGE_RATE, EXCHANGE_RATE_TYPE — currency and conversion context.
• UNIT_SELLING_PRICE, SO_LINE_SELLING_PRICE, INVOICE_LINE_QTY, QTY_CREDITED, ORDER_LINE_QTY — pricing and quantity measures.
• SET_OF_BOOKS_FK_KEY, ORGANIZATION_FK_KEY, LINE_TYPE, TRANSACTION_STATUS — ledger, operating unit, and status context.

Common Use Cases and Queries

The most frequent use case for FII_AR_TRX_DIST_F is revenue and receivables analytics: aggregating posted distributions by GL account, customer, product, sales channel, and period. Because the table is denormalized with bitmap foreign keys, it supports star-schema style queries driven by outer joins to the FII dimension tables.

A typical pattern filters on the searched index column, END_USER_CUSTOMER_FK_KEY, using the bitmap index FII_AR_TRX_DIST_F_N1:

SELECT d.invoice_number,
       d.gl_date,
       d.amt_g
FROM   fii.fii_ar_trx_dist_f d
WHERE  d.end_user_customer_fk_key = :p_customer_key
AND    d.gl_date BETWEEN :p_start AND :p_end;

Other common scenarios include revenue by GL account (joining on GL_ACCT1_FK_KEY through GL_ACCT10_FK_KEY), analysis of credit memos versus invoices through ACCOUNT_TYPE and TRANSACTION_CLASS, and period-over-period comparisons based on GL_DATE_FK_KEY. Analysts also aggregate AMT_B by SET_OF_BOOKS_FK_KEY for ledger-level reconciliation with the General Ledger. Because PostgreSQL-style analytics aside, Oracle bitmap indexes are effective here for low-cardinality filter columns, the WHERE clause patterns above align with the existing index set.

Related Objects

The single documented foreign key, FII_AR_TRX_DIST_F.INVOICE_LINE_ID, references FTE_INVOICE_LINES, providing the bridge to the invoice-line fact grain. Beyond this, the table participates in a broader star schema through its key columns:

• FTE_INVOICE_LINES — joined on INVOICE_LINE_ID; supplies line-level attributes.
• Oracle Receivables (AR) base tables — RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL — the operational source of invoice, line, and distribution data that FII extracts transform.
• GL_ACCT* dimension keys — each GL_ACCT1_FK_KEY through GL_ACCT10_FK_KEY resolves to the FII account / accounting flexfield dimension, allowing account rollups.
• Customer dimensions — END_USER_CUSTOMER_FK_KEY, SOLD_TO_CUSTOMER_FK_KEY, BILL_TO_CUSTOMER_FK_KEY, SHIP_TO_CUSTOMER_FK_KEY, and RESELLER_CUSTOMER_FK_KEY each map to FII customer or party dimensions.
• Currency, date, and organization dimensions — FUNCTIONAL_CURRENCY_FK_KEY, TRANSACTION_CURRENCY_FK_KEY, GL_DATE_FK_KEY, INVOICE_DATE_FK_KEY, and ORGANIZATION_FK_KEY provide the conformed dimensional context shared with other FII fact tables.

Collectively these relationships position FII_AR_TRX_DIST_F as the distribution-grain fact within the FII Receivables star, queried almost exclusively through joins against the dimension tables referenced by its bitmap foreign keys.