Case 00168810 – AR MRC Reporting Currency Tables Missing Records — AR MC Population from Primary Tables (11i)

AR MRC Reporting Currency Tables Missing Records — AR MC Population from Primary Tables (11i)

Case Number	00168810
Oracle Module	Accounts Receivable (AR) / Multiple Reporting Currencies (MRC)
EBS Version	11i (11.5.10.2)
Functional Area	Multi-Currency Reporting / AR Transactions / Cash Receipts
Related Patches	None

Symptoms

AR transactions, cash receipts, and payment schedules exist in the primary AR tables but are missing from the corresponding MRC (Multiple Reporting Currencies) shadow tables for the reporting set of books.
MRC reporting currency reports show incomplete or missing AR balances for the affected operating unit.
Queries using MINUS between the primary tables and the MC tables return rows, indicating records present in the primary system that have no corresponding MC reporting record.

Root Cause

AR transactions, transaction line GL distributions, payment schedules, cash receipts, and cash receipt history records were created in the primary set of books (functional currency) but the corresponding MRC shadow table records were not generated for the reporting set of books. This can occur when the MRC conversion process fails, is skipped, or when records are imported or inserted directly without triggering the MRC population logic. The fix uses MINUS set operations to identify the missing records and inserts them into the MRC tables with converted amounts derived from GL_DAILY_RATES using the Corporate exchange rate type.

Resolution

Step 1 – Identify and insert missing RA_MC_CUSTOMER_TRX records

Run data_fix.sql. For each CUSTOMER_TRX_ID present in RA_CUSTOMER_TRX_ALL but absent from RA_MC_CUSTOMER_TRX for the affected org, insert a row into RA_MC_CUSTOMER_TRX with the reporting set of books ID and the Corporate exchange rate from GL_DAILY_RATES for the transaction date (from functional currency to reporting currency).

Step 2 – Identify and insert missing RA_MC_TRX_LINE_GL_DIST records

For each CUST_TRX_LINE_GL_DIST_ID present in RA_CUST_TRX_LINE_GL_DIST_ALL for the affected org but absent from RA_MC_TRX_LINE_GL_DIST, insert a row copying the amount, account class, GL posted date, posting control ID, and accounted amount to the MC table with the reporting set of books ID.

Step 3 – Identify and insert missing AR_MC_PAYMENT_SCHEDULES records (invoices)

For invoice-class payment schedules (CLASS = 'INV') present in AR_PAYMENT_SCHEDULES_ALL but absent from AR_MC_PAYMENT_SCHEDULES, insert rows with the Corporate exchange rate and converted accounted/remaining amounts.

Step 4 – Commit intermediate inserts

Commit after the transaction, distribution, and invoice payment schedule inserts.

Step 5 – Identify and insert missing AR_MC_CASH_RECEIPTS records

For each CASH_RECEIPT_ID present in AR_CASH_RECEIPTS_ALL for the affected org with the primary set of books ID but absent from AR_MC_CASH_RECEIPTS for the reporting set of books ID, insert a row with the Corporate exchange rate from GL_DAILY_RATES for the receipt date.

Step 6 – Identify and insert missing AR_MC_CASH_RECEIPT_HIST records

For each CASH_RECEIPT_HISTORY_ID present in AR_CASH_RECEIPT_HISTORY_ALL for the affected org but absent from AR_MC_CASH_RECEIPT_HIST for the reporting set of books ID, insert a row with converted accounted amounts calculated by multiplying the functional currency amount by the Corporate exchange rate from GL_DAILY_RATES for the transaction date.

Step 7 – Identify and insert missing AR_MC_PAYMENT_SCHEDULES records (payments)

For payment-class payment schedules (CLASS = 'PMT') present in AR_PAYMENT_SCHEDULES_ALL but absent from AR_MC_PAYMENT_SCHEDULES for the reporting set of books, insert rows with converted amounts using the Corporate rate and transaction date.

Step 8 – Final commit

Commit all remaining inserts. Verify row counts for each MC table match expectations.

Note: This fix applies to the 11i MRC (Multiple Reporting Currencies) architecture, which uses dedicated _MC_ shadow tables populated in parallel with the primary tables. In R12, MRC was replaced by the Reporting Currencies feature using XLA_AE_LINES with multiple ledger rows. The GL_DAILY_RATES table must have Corporate exchange rates defined for the conversion date range covering the affected transactions. Missing rates will cause the subquery to return NULL, resulting in NULL converted amounts on the inserted rows.

Script Inventory

File	Description
data_fix.sql	PL/SQL anonymous block (11i MRC). Uses MINUS set operations to identify AR records missing from MRC shadow tables. Inserts missing rows into RA_MC_CUSTOMER_TRX, RA_MC_TRX_LINE_GL_DIST, AR_MC_PAYMENT_SCHEDULES (INV and PMT class), AR_MC_CASH_RECEIPTS, and AR_MC_CASH_RECEIPT_HIST with Corporate exchange rates from GL_DAILY_RATES.

Key Tables Affected

Table	Action
RA_MC_CUSTOMER_TRX	Insert missing AR transaction MC records
RA_MC_TRX_LINE_GL_DIST	Insert missing AR transaction line GL distribution MC records
AR_MC_PAYMENT_SCHEDULES	Insert missing AR payment schedule MC records (INV and PMT class)
AR_MC_CASH_RECEIPTS	Insert missing AR cash receipt MC records
AR_MC_CASH_RECEIPT_HIST	Insert missing AR cash receipt history MC records
GL_DAILY_RATES	Read only — source for Corporate exchange rates