Case 00026054 – AP MRC Check Exchange Rate and Base Amount Incorrect in AP_MC

AP MRC Check Exchange Rate and Base Amount Incorrect in AP_MC_CHECKS (11i)

Case Number	00026054
Oracle Module	Accounts Payable (AP) / Multiple Reporting Currencies (MRC)
EBS Version	11i (11.5.10.2)
Functional Area	Payment Processing / Multi-Currency Reporting / MRC Check Accounting
Related Patches	None

Symptoms

AP checks appear with incorrect exchange rates and base amounts in the MRC reporting currency tables — the exchange_rate and base_amount in AP_MC_CHECKS do not match the corresponding values in AP_MC_INVOICE_PAYMENTS for the same set of books.
MRC reporting shows incorrect payment amounts in the reporting currency for the affected checks.
For instances on pre-11i.AP.L patch levels, the AP_CHECKS_ALL.mrc_base_amount and mrc_exchange_rate columns are also out of sync with the corrected values.

Root Cause

The AP_MC_CHECKS table for the affected checks has exchange_rate and base_amount values that differ from those in the corresponding AP_MC_INVOICE_PAYMENTS rows. The correct check-level base amount should be the sum of invoice payment base amounts for the check, and the correct exchange rate should match the invoice payment exchange rate. The fix recalculates these values from AP_MC_INVOICE_PAYMENTS and updates AP_MC_CHECKS accordingly. For older patch levels, it also updates the denormalized mrc_base_amount and mrc_exchange_rate columns in AP_CHECKS_ALL.

Resolution

Step 1 – Identify affected checks and create backup tables

Run 01_setup_tables.sql. The script queries for checks where the exchange_rate in AP_MC_CHECKS differs from the exchange_rate in the corresponding AP_MC_INVOICE_PAYMENTS rows (joined via AP_CHECKS_ALL and AP_INVOICE_PAYMENTS_ALL). Two backup tables are created:

RSI_AP_CHECKS_00026054_A — all AP_CHECKS_ALL rows for identified affected checks
RSI_AP_CHECKS_00026054_B — AP_MC_CHECKS rows for the specific affected check IDs

The script also resets the affected AP_MC_CHECKS rows by deleting and re-inserting from a pre-existing backup table (capturing the state before any prior fix attempts).

Step 2 – Recalculate and apply correct exchange rate and base amount

Run 02_apply_fix.sql. For each affected check (identified from the backup table in Step 1), the script:

Calculates the correct base_amount as the sum of AP_MC_INVOICE_PAYMENTS.payment_base_amount for all invoice payments on the check, grouped by check ID and set of books ID.
Derives the correct exchange_rate as the maximum exchange_rate from the invoice payments for that check and set of books.
Updates AP_MC_CHECKS with the recalculated exchange_rate and base_amount.
For instances on patch levels below 11i.AP.L: also constructs the formatted mrc_base_amount and mrc_exchange_rate strings and updates the denormalized columns in AP_CHECKS_ALL.

Note: This fix applies to the 11i MRC architecture. The AP_MC_CHECKS table is 11i-specific and does not exist in R12. The patch level check (fnd_product_installations.patch_level < '11i.AP.L') determines whether the denormalized MRC columns in AP_CHECKS_ALL also require updating — this step applies only to older 11i patch levels that use the mrc_base_amount / mrc_exchange_rate concatenated-string format.

Script Inventory

File	Description
01_setup_tables.sql	Identifies affected AP checks by comparing AP_MC_CHECKS.exchange_rate with AP_MC_INVOICE_PAYMENTS.exchange_rate. Creates backup tables RSI_AP_CHECKS_00026054_A and RSI_AP_CHECKS_00026054_B. Resets AP_MC_CHECKS from a prior backup.
02_apply_fix.sql	PL/SQL anonymous block. For each affected check, calculates the correct exchange_rate and base_amount from AP_MC_INVOICE_PAYMENTS; updates AP_MC_CHECKS. For pre-11i.AP.L patch levels, also updates AP_CHECKS_ALL.mrc_base_amount and mrc_exchange_rate.

Key Tables Affected

Table	Action
AP_MC_CHECKS	Update exchange_rate and base_amount from AP_MC_INVOICE_PAYMENTS
AP_CHECKS_ALL	Update mrc_base_amount and mrc_exchange_rate (pre-11i.AP.L patch levels only)
AP_MC_INVOICE_PAYMENTS	Read only — source for recalculated exchange rate and base amount