A client reported the following error on the posting journals when trying to post both Receivables and Payables Transactions. “This transaction contains multi-currency error(s)”
This was followed by a message informing them that the batch had gone to recovery.
I checked the usual things like exchange rate expiration dates however everything seemed fine.
The client then explained they had overwritten some exchange rates accidentally but had since changed these back however the error persisted.
I delved a little deeper using SQL and noticed that although the dates and rates were the same the TIME1 value in the MC020102 table for the transactions differed slightly from the TIME1 value in the DYNAMICS..MC00101 for the exchange rate in question.
As a test I changed one of the transactions by updating the TIME1 value in the MC020102 table to match the relevant TIME1 row in MC00100 using the script below.
UPDATE MCTRX SET MCtrx.time1 = MC.time1 FROM mc020102 MCTRX INNER JOIN dyn2018r2..mc00100 mc ON mc.exgtblid = MCTRX.exgtblid AND mc.xchgrate = MCTRX.xchgrate AND mc.exchdate = MCTRX.exchdate WHERE MCTRX.docnumbr = 'SALES00000001004'
I then printed an edit list of the batch and the error had gone.
Now I knew how to fix the issue I realised I could also resolve this by just opening the transactions in the entry window and re-selecting the exchange rate however there were 1000’s of affected transactions so I used the script below. (this was affecting all multi currency transactions in WORK batches so I just had to filter on DCSTATUS)
UPDATE MCTRX SET MCtrx.time1 = MC.time1 FROM mc020102 MCTRX INNER JOIN dyn2018r2..mc00100 mc ON mc.exgtblid = MCTRX.exgtblid AND mc.xchgrate = MCTRX.xchgrate AND mc.exchdate = MCTRX.exchdate WHERE mc.time1 <> MCTRX.time1 AND DCSTATUS = 1 --add this so only WORK trx are affected
After running this script all sales transactions posted fine.
Next I turned to the payables transactions which just involved a small tweak to the original script and also an update to a different table to correct currencies for the multi currency payables payment batch header.
--First fix Payables multi currency trx UPDATE MCTRX SET MCtrx.time1 = MC.time1 FROM mc020103 MCTRX --Changed to MC020103 for payables trx INNER JOIN dyn2018r2..mc00100 mc ON mc.exgtblid = MCTRX.exgtblid AND mc.xchgrate = MCTRX.xchgrate AND mc.exchdate = MCTRX.exchdate WHERE mc.time1 <> MCTRX.time1 AND DCSTATUS = 1 --Also fix the batch header currency values for payment batches UPDATE batch SET batch.time1 = mc.time1 FROM mc00500 batch --batch header multi currency table INNER JOIN dynamics..mc00100 mc ON mc.exgtblid = batch.exgtblid AND mc.xchgrate = batch.xchgrate AND mc.exchdate = batch.exchdate WHERE mc.time1 <> batch.time1
Once I’d ran through all this the transactions posted fine.
Hopefully this will help someone in the future who may face a similar issue however always remember to have a good backup of your data before running any SQL scripts and test whenever possible.
Thanks for reading