| Case Number | 00168831 |
| Oracle Module | Accounts Payable (AP) / Multiple Reporting Currencies (MRC) |
| EBS Version | 11i (11.5.10.2) |
| Functional Area | Multi-Currency Reporting / AP Invoices / Payments |
| Related Patches | None |
AP_MC_INVOICES, AP_MC_INVOICE_DISTS, AP_MC_INVOICE_PAYMENTS, AP_MC_PAYMENT_HISTORY, and AP_MC_CHECKS tables do not contain rows for invoices that exist in the primary tables.AP invoices, invoice distributions, invoice payments, payment history, and check records were created in the primary set of books but the corresponding MRC shadow table records were not generated for the reporting set of books. This can occur when records are imported or inserted bypassing normal API processing, or when the MRC population process fails for specific transactions. The fix uses a cursor over the affected invoice IDs to insert the missing MC records with converted base amounts derived from GL_DAILY_RATES using the Corporate exchange rate type (functional currency to reporting currency).
Run data_fix.sql. For each affected invoice ID, insert a row into AP_MC_INVOICES selecting from AP_INVOICES_ALL with the reporting set of books ID, the Corporate exchange rate from GL_DAILY_RATES for the invoice date (from functional currency to reporting currency), and the calculated base amount (INVOICE_AMOUNT * CONVERSION_RATE, rounded to 2 decimal places).
For each affected invoice, insert rows into AP_MC_INVOICE_DISTS for all distributions in AP_INVOICE_DISTRIBUTIONS_ALL. The base amount is calculated by multiplying the distribution amount by the Corporate exchange rate from GL_DAILY_RATES for the distribution accounting date. Include distribution line number, dist code combination ID, amount, exchange date, exchange rate, exchange rate type, and line type lookup code.
For each affected invoice, insert rows into AP_MC_INVOICE_PAYMENTS from AP_INVOICE_PAYMENTS_ALL with the reporting set of books ID, preserving the existing exchange date, exchange rate, exchange rate type, gain/loss CCIDs, invoice base amount, and payment base amount.
For each affected invoice, insert rows into AP_MC_PAYMENT_HISTORY from AP_PAYMENT_HISTORY_ALL for all check IDs associated with the invoice's payments, with the reporting set of books ID and existing exchange rate details (PMT-to-base and bank-to-base exchange rate columns, base amounts for transaction, errors, and charges).
For each affected invoice, insert rows into AP_MC_CHECKS from AP_CHECKS_ALL for all associated check IDs, with the reporting set of books ID and all cleared and maturity exchange rate, amount, and base amount columns.
Commit after each invoice loop iteration (or at the end of processing). Verify row counts for each MC table.
AP_MC_* tables are 11i-specific MRC shadow tables; this fix is not applicable to R12. The GL_DAILY_RATES table must contain Corporate exchange rates for the invoice dates and accounting dates of all affected invoices. The script processes invoices one at a time in a cursor loop — for large invoice volumes, consider batching the commits. See also case 00168810 for the equivalent AR MRC population fix.
| File | Description |
|---|---|
| data_fix.sql | PL/SQL anonymous block (11i MRC). Cursor over affected invoice IDs. For each invoice, inserts missing rows into AP_MC_INVOICES (with Corporate rate-converted base amount), AP_MC_INVOICE_DISTS (all distributions with converted base amounts), AP_MC_INVOICE_PAYMENTS, AP_MC_PAYMENT_HISTORY, and AP_MC_CHECKS from their respective primary tables. Uses GL_DAILY_RATES for Corporate rate lookups. |
| Table | Action |
|---|---|
| AP_MC_INVOICES | Insert missing AP invoice MC records with converted base amounts |
| AP_MC_INVOICE_DISTS | Insert missing AP invoice distribution MC records with converted base amounts |
| AP_MC_INVOICE_PAYMENTS | Insert missing AP invoice payment MC records |
| AP_MC_PAYMENT_HISTORY | Insert missing AP payment history MC records |
| AP_MC_CHECKS | Insert missing AP check MC records |
| GL_DAILY_RATES | Read only — source for Corporate exchange rates |