Search Results earned_discount_amount_trx

Overview

FII.FII_AR_RECAPP_MERGE_GT is a global temporary table owned by the FII (Financial Intelligence for Receivables) schema in Oracle E-Business Suite. It functions as an intermediate staging structure used by the Receivables application-merge reconciliation programs that populate the FII receivables fact and dimension tables. The table carries a data duration of SYS$SESSION; rows inserted by a session are visible only to that session and are purged automatically when the session terminates. This makes it unsuitable for persistent storage but ideal for in-memory processing of application and receipt-application rows prior to load or merge.

From a Data Vault modeling perspective, the mined heuristic classification for this object is standalone. Because the table is a transient global temporary structure rather than a persistent entity, treating it as an isolated staging construct—rather than as a hub, link, or satellite—is the appropriate suggestion. The only documented referential constraint is on RECEIPT_METHOD_ID, which points to AR_RECEIPT_METHODS, though in practice many of the ID columns reference Receivables transactional entities.

Key Information Stored

The table contains 53 documented columns spanning application, receipt, discount, and audit attributes. The most significant columns are:

• RECEIVABLE_APPLICATION_ID – Surrogate key of the underlying Receivables application row; the de facto primary key for staging purposes.
• CASH_RECEIPT_ID and RECEIPT_NUMBER – Identify the receipt applied against the transaction.
• APPLIED_CUSTOMER_TRX_ID and CUSTOMER_TRX_ID – Reference the transaction being applied and the transaction originating the application.
• APPLIED_PAYMENT_SCHEDULE_ID and PAYMENT_SCHEDULE_ID – Link the staged row to the payment schedules involved.
• AMOUNT_APPLIED_RCT, AMOUNT_APPLIED_TRX – Applied amounts in receipt and transaction currencies respectively.
• EARNED_DISCOUNT_AMOUNT_TRX, EARNED_DISCOUNT_AMOUNT_FUNC, EARNED_DISCOUNT_AMOUNT_PRIM, EARNED_DISCOUNT_AMOUNT_SEC – The earned discount captured at transaction, functional, primary, and secondary currency levels; the column the user searched for.
• UNEARNED_DISCOUNT_AMOUNT_TRX and its FUNC/PRIM/SEC counterparts – Discount amounts taken but not yet earned.
• APPLY_DATE, GL_DATE – Application and accounting dates.
• APPLICATION_STATUS, HEADER_STATUS, APPLICATION_TYPE – Status and type descriptors.
• CURRENCY_CODE, ORG_ID – Currency and operating unit context.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard audit columns.

No unique index is documented beyond the application identifier, so RECEIVABLE_APPLICATION_ID combined with CASH_RECEIPT_ID serves as the practical business-key candidate for staging joins.

Common Use Cases and Queries

The primary use case is reconciliation of earned and unearned discount amounts across currencies before they are merged into FII fact tables. A typical query aggregates earned discount by transaction and operating unit:

SELECT ORG_ID, CURRENCY_CODE,
       SUM(EARNED_DISCOUNT_AMOUNT_TRX)  AS earned_trx,
       SUM(EARNED_DISCOUNT_AMOUNT_FUNC) AS earned_func
FROM   FII.FII_AR_RECAPP_MERGE_GT
WHERE  APPLY_DATE BETWEEN :p_from AND :p_to
GROUP  BY ORG_ID, CURRENCY_CODE;

Other uses include comparing AMOUNT_APPLIED_TRX against payment-schedule expectations, validating GL_DATE ranges, and isolating rows where unearned discount remains outstanding for period-end accruals. Because the table is session-scoped, any query must run in the same session that populated it.

Related Objects

• AR_RECEIPT_METHODS – Referenced by RECEIPT_METHOD_ID; join to resolve receipt method names.
• AR_CASH_RECEIPTS – Source of CASH_RECEIPT_ID, RECEIPT_NUMBER, and RECEIPT_DATE.
• AR_RECEIVABLE_APPLICATIONS – Source of application identifiers and applied amounts.
• RA_CUSTOMER_TRX and RA_CUSTOMER_TRX_LINES – Transaction context for CUSTOMER_TRX_ID and APPLIED_CUSTOMER_TRX_ID.
• AR_PAYMENT_SCHEDULES – Source of PAYMENT_SCHEDULE_ID and APPLIED_PAYMENT_SCHEDULE_ID.
• FII_AR_RECAPP_MERGE_FACT – Target fact table populated from this staging table.

These relationships make FII_AR_RECAPP_MERGE_GT the transient bridge between Receivables transactional tables and the FII analytical layer.