Search Results ar_mc_payment_schedules




Overview
Explore in AI Chat

Overview

AR_MC_PAYMENT_SCHEDULES is a Receivables (AR) table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the reporting-currency (multiple-currency) counterpart of payment schedule information. Its documented purpose is to hold currency-related information for all transactions except adjustments and miscellaneous cash receipts. The table is owned by the AR schema under the ETRM documentation and carries a VALID status.

In Oracle Receivables, the base payment schedule table AR_PAYMENT_SCHEDULES_ALL maintains one row per installment per transaction in the functional currency of the set of books. When a transaction is denominated in a foreign currency, or when multiple reporting currencies are enabled, AR_MC_PAYMENT_SCHEDULES maintains a parallel row per installment per set of books, carrying amounts expressed in that set of books' currency together with the exchange rate attributes used to convert them. This makes it the reporting-currency "shadow" of the payment schedule and the table that feeds multi-currency aging, revenue, and collections reporting.

The heuristic Data Vault classification mined from the foreign-key structure is a link. From a modeling perspective, this is consistent with the table's function: it associates a payment schedule installment, a transaction, and a set of books, rather than representing an independent business entity in its own right. It should therefore be treated as a relationship or intersection object between the transaction, the installment, and the ledger context.

Key Information Stored

The table contains eight documented columns. The most important are summarized below.

  • SET_OF_BOOKS_ID — Identifies the set of books (ledger) in whose currency the foreign-currency amounts in the row are expressed.
  • PAYMENT_SCHEDULE_ID — The installment identifier that ties the reporting-currency row back to the corresponding base row in AR_PAYMENT_SCHEDULES_ALL.
  • CUSTOMER_TRX_ID — The transaction (invoice, debit memo, credit memo, chargeback, or deposit) to which the payment schedule row belongs.
  • EXCHANGE_RATE_TYPE — The rate type (for example, Spot, Corporate, or User-defined) applied for conversion.
  • EXCHANGE_DATE — The date on which the exchange rate was effective for this row.
  • EXCHANGE_RATE — The conversion rate applied between the transaction currency and the set of books currency.
  • ACCTD_AMOUNT_DUE_REMAINING — The remaining amount due, expressed in the set of books (accounted) currency.
  • AMOUNT_DUE_REMAINING — The remaining amount due in the transaction or reporting currency of the row.

The surrogate primary key is documented as AR_MC_PAYMENT_SCHEDULES_PK, defined over (SET_OF_BOOKS_ID, PAYMENT_SCHEDULE_ID). A unique index, AR_MC_PAYMENT_SCHEDULES_U1, is defined over the same two columns in the reverse order (PAYMENT_SCHEDULE_ID, SET_OF_BOOKS_ID). Both are compound keys; the combination of installment and ledger is the true business-key candidate, and neither column is uniquely identifying on its own.

Common Use Cases and Queries

The primary use case is multi-currency and multiple-reporting-currency reporting: reconciling transaction-currency balances against reporting-currency balances for the same installment, auditing exchange rates applied to specific installments, and producing aged trial balances in a reporting currency. A typical query joining the base and reporting-currency rows is:

  • SELECT ps.customer_trx_id, ps.payment_schedule_id, ps.amount_due_remaining base_amt, mc.amount_due_remaining mc_amt, mc.acctd_amount_due_remaining acctd_amt, mc.exchange_rate, mc.exchange_rate_type FROM ar_payment_schedules_all ps, ar_mc_payment_schedules mc WHERE ps.payment_schedule_id = mc.payment_schedule_id AND ps.set_of_books_id = mc.set_of_books_id.
  • Filtering by mc.set_of_books_id allows reporting for a specific ledger, while joining to ra_customer_trx_all via customer_trx_id supports transaction-level reporting.
  • Reconciliation queries comparing mc.exchange_rate and mc.exchange_date against the transaction's rate attributes help identify rate mismatches between currencies.

Related Objects

The following objects are the most significant dependents and related entities, based on the documented foreign-key relationships.

  • AR_PAYMENT_SCHEDULES_ALL — the base payment schedule table; joined on PAYMENT_SCHEDULE_ID via the documented foreign key.
  • RA_CUSTOMER_TRX_ALL — the transaction header table; joined on CUSTOMER_TRX_ID via the documented foreign key.
  • GL_SETS_OF_BOOKS — the ledger definition; joined on SET_OF_BOOKS_ID via the documented foreign key.
  • RA_CUSTOMER_TRX_LINES_ALL — transaction lines that feed the schedule and are frequently aggregated alongside it.
  • AR_CASH_RECEIPTS_ALL and AR_RECEIVABLE_APPLICATIONS_ALL — receipts and applications that reduce the remaining amounts tracked in the schedule.
  • AR_MC_CASH_RECEIPTS and related multiple-currency AR tables — parallel reporting-currency objects used in the same reporting patterns.