Search Results ra_mc_customer_trx




Overview
Explore in AI Chat

Overview

RA_MC_CUSTOMER_TRX is a Receivables (AR) module table in the Oracle E-Business Suite schema AR, valid in releases 12.1.1 and 12.2.2. The ETRM documentation describes it as holding "currency-related information about invoice, debit memo, commitment, and credit memo header information." The "MC" prefix denotes a multi-currency companion table: the transactional header itself, including the functional (ledger) amounts and the transaction currency amounts, is persisted in RA_CUSTOMER_TRX and its distribution lines, while the currency conversion attributes used to translate between the two are captured here, keyed by transaction and ledger.

The documented physical schema contains five columns and is secured by the primary key RA_MC_CUSTOMER_TRX_PK on (CUSTOMER_TRX_ID, SET_OF_BOOKS_ID). A unique index, RA_MC_CUSTOMER_TRX_U1, covers the identical column pair. Heuristic Data Vault classification derived from the foreign-key structure identifies this object as standalone; in Data Vault modeling terms it is therefore best treated as a satellite (or, optionally, an extended hub-and-satellite combination) attached to the transaction business key, since it records descriptive currency attributes that change over the life of a transaction rather than defining new relationships between business entities.

Key Information Stored

All five documented columns are significant; the table is narrow by design and each column serves a specific conversion purpose.

  • CUSTOMER_TRX_ID — Surrogate identifier of the Receivables transaction header. Part of the composite primary key RA_MC_CUSTOMER_TRX_PK and of the unique index RA_MC_CUSTOMER_TRX_U1. It is the join back to RA_CUSTOMER_TRX.
  • SET_OF_BOOKS_ID — The ledger (set of books) for which the currency attributes apply. Part of both the primary key and the unique index. Because the pair defines uniqueness, a single transaction may carry one row per ledger in which it is processed.
  • EXCHANGE_RATE_TYPE — The Oracle General Ledger rate type used for conversion, such as Corporate, Spot, or User-defined. It governs which daily rates are selected at conversion time.
  • EXCHANGE_DATE — The effective date used to determine the conversion rate for the transaction. It is typically the transaction, accounting, or receipt date, depending on the transaction type and system options.
  • EXCHANGE_RATE — The numeric multiplier between the entered (foreign) currency and the functional currency of the specified ledger, preserving the exact rate applied so that amounts can be reproduced and audited.

The composite key distinguishes the transactional surrogate (CUSTOMER_TRX_ID) from the business-key candidates documented by the unique index (CUSTOMER_TRX_ID plus SET_OF_BOOKS_ID).

Common Use Cases and Queries

The table is principally queried to verify or reconstruct currency conversion for a transaction, to drive multi-currency reporting and reconciliation, and to validate rate types and dates used in translation. Typical SQL patterns include joining to the transaction header to display converted amounts, and joining to ledger-based views for period reporting.

  • Retrieve the conversion attributes for a specific transaction and ledger: 
    SELECT customer_trx_id, set_of_books_id, exchange_rate_type,
       exchange_date, exchange_rate
FROM   ar.ra_mc_customer_trx
WHERE  customer_trx_id = :trx_id
AND    set_of_books_id = :ledger_id;
  • Report all transactions using a non-standard rate type for audit review, joining to RA_CUSTOMER_TRX for the transaction number and customer.
  • Reconcile foreign-currency invoices and credit memos against General Ledger daily rates for the recorded exchange date, using the transaction header as the driving table.
  • Compare exchange rates captured at creation against daily rates for aging or period-end revaluation analysis, especially for commitments raised without a rate at entry time.

Related Objects

The table's relationships are dictated by the documented key columns (CUSTOMER_TRX_ID, SET_OF_BOOKS_ID).

  • RA_CUSTOMER_TRX — The transaction header (invoice, debit memo, credit memo, commitment). Joins on CUSTOMER_TRX_ID; supplies transaction number, currency, and amounts.
  • RA_CUSTOMER_TRX_LINES — Transaction lines; join through RA_CUSTOMER_TRX.CUSTOMER_TRX_ID to obtain line-level currency detail.
  • RA_CUST_TRX_LINE_GL_DIST — Accounting distributions; join via transaction and check for the same ledger for translated functional amounts.
  • GL_DAILY_CONVERSION_TYPES and GL_DAILY_RATES — General Ledger rate type definition and daily rates, joined on EXCHANGE_RATE_TYPE and EXCHANGE_DATE to validate or recompute the stored EXCHANGE_RATE.
  • FND_CURRENCIES — Currency definition, joined indirectly through the transaction header's currency code.
  • GL_SETS_OF_BOOKS / GL_LEDGERS — Ledger definition, joined on SET_OF_BOOKS_ID to resolve ledger name and functional currency.
  • AR_INVOICE_API_PUB and related Receivables AutoInvoice interfaces — populate this table when transactions are imported or created with conversion attributes.