Search Results ap_mc_invoice_payments_u1

Overview

AP.AP_MC_INVOICE_PAYMENTS is a Multiple Reporting Currencies (MRC) table in the Oracle Payables (AP) module of Oracle E-Business Suite. It stores reporting currency information for each payment recorded in Oracle Payables, allowing a single transactional payment to be reported in multiple reporting sets of books in addition to the primary functional currency set of books. The table is owned by the AP schema and is registered under FND Design Data as SQLAP.AP_MC_INVOICE_PAYMENTS with a status of VALID.

The table maintains an optional one-to-many relationship with AP_INVOICE_PAYMENTS_ALL. For each invoice payment row in AP_INVOICE_PAYMENTS_ALL, several rows may exist in AP_MC_INVOICE_PAYMENTS sharing the same INVOICE_PAYMENT_ID value but distinguished by SET_OF_BOOKS_ID. The number of rows stored for a given INVOICE_PAYMENT_ID corresponds to the number of reporting sets of books defined in the instance. Data is stored in the APPS_TS_TX_DATA tablespace with PCT Free of 10. From a Data Vault modeling perspective, the metadata heuristically classifies this object as standalone, though its structure—a composite surrogate key combining a transactional identifier with a set of books identifier, and numeric measures such as amounts and rates—makes it a strong candidate for modeling as a satellite or link positioned between the invoice payment transaction and the reporting set of books dimension.

Key Information Stored

The table contains nine documented columns. The composite primary key AP_MC_INVOICE_PAYMENTS_PK consists of INVOICE_PAYMENT_ID and SET_OF_BOOKS_ID. The unique index AP_MC_INVOICE_PAYMENTS_U1, defined on the same two columns (INVOICE_PAYMENT_ID, SET_OF_BOOKS_ID) in the APPS_TS_TX_IDX tablespace, is the principal business-key candidate enforcing the one-row-per-reporting-book rule.

• INVOICE_PAYMENT_ID (NUMBER, 15, mandatory) — Surrogate identifier of the payment, joining back to AP_INVOICE_PAYMENTS_ALL. Part of the primary key and the unique index.
• SET_OF_BOOKS_ID (NUMBER, 15, mandatory) — Identifies the reporting set of books for which the reporting currency amounts are held. Part of the primary key and unique index.
• EXCHANGE_DATE (DATE) — The date that determines the currency conversion rate used to translate transaction currency amounts into reporting currency amounts.
• EXCHANGE_RATE (NUMBER) — The conversion rate applied to translate the transactional currency amount into the reporting currency amount.
• EXCHANGE_RATE_TYPE (VARCHAR2, 30) — The rate type (for example, Spot or Corporate) used for the currency conversion.
• PAYMENT_BASE_AMOUNT (NUMBER) — The payment amount expressed in the reporting currency base for the associated set of books.
• INVOICE_BASE_AMOUNT (NUMBER) — The invoice amount in functional currency, used only for foreign currency invoices.
• GAIN_CODE_COMBINATION_ID (NUMBER, 15) — Accounting Flexfield identifier for the account to which realized exchange rate gains are posted.
• LOSS_CODE_COMBINATION_ID (NUMBER, 15) — Accounting Flexfield identifier for the account to which realized exchange rate losses are posted.

Common Use Cases and Queries

The most frequent use of this table is reporting translated payment amounts in reporting currencies, reconciling realized gain and loss postings, and auditing the rate context applied to each payment. A typical query joining the MRC rows to the base payment table retrieves translated amounts per reporting book:

• Select mcinv.INVOICE_PAYMENT_ID, mcinv.SET_OF_BOOKS_ID, mcinv.EXCHANGE_RATE, mcinv.PAYMENT_BASE_AMOUNT from AP_MC_INVOICE_PAYMENTS mcinv where mcinv.INVOICE_PAYMENT_ID = :p_payment_id.
• Join to AP_INVOICE_PAYMENTS_ALL on INVOICE_PAYMENT_ID to compare base functional amounts against reporting currency amounts and to confirm the appropriate one-to-many cardinality.
• Report realized exchange gain and loss activity by grouping on GAIN_CODE_COMBINATION_ID and LOSS_CODE_COMBINATION_ID for a given accounting period.
• Validate conversion integrity by comparing EXCHANGE_RATE and EXCHANGE_RATE_TYPE against currency rate tables for the EXCHANGE_DATE.

Related Objects

• AP_INVOICE_PAYMENTS_ALL — Parent transactional table; joined on INVOICE_PAYMENT_ID. Defines the optional one-to-many relationship into this MRC table.
• AP_INVOICE_PAYMENTS — The non-ALL variant providing a filtered view of payment records consistent with the base table.
• AP_INVOICES_ALL — Supplies invoice header context when correlating translated payments to invoices, typically via invoice identifiers carried on the payment row.
• AP_CHECKS_ALL — Payment document information joined through the payment identifier to enrich MRC reporting.
• GL_SETS_OF_BOOKS — Resolves SET_OF_BOOKS_ID to reporting ledger name and currency.
• FND_CURRENCIES — Provides reporting currency attributes for interpretation of EXCHANGE_RATE_TYPE and amounts.
• GL_DAILY_CONVERSION_TYPES — Validates EXCHANGE_RATE_TYPE usage against the applicable conversion rate type.