When we investigated the issue, it turned out that the field was disabled in those companies which had a Payables Transaction Approval workflow active; I did additional testing and the Payables Batch Approval workflow does not have the same effect.
All of the transactions which were in the functional currency, imported without a problem, but all of those in other currencies failed.
The cause of the problem was actually quite a simple one, and one I have seen before with other clients. On the Multicurrency Exchange Rate Maintenance window, there is an Expiration date field:
This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ CREATE TABLE #FunctionalCurrencies( INTERID VARCHAR(5) ,FUNLCURR VARCHAR(20) ) GO DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = STUFF(( SELECT CHAR(13) + 'SELECT ''' + INTERID + ''' ,FUNLCURR FROM ' + INTERID + '.dbo.MC40000 WHERE LEN(FUNLCURR) = 0' FROM DYNAMICS.dbo.SY01500 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') INSERT INTO #FunctionalCurrencies EXEC sys.sp_executesql @SQL GO SELECT * FROM #FunctionalCurrencies GO DROP TABLE #FunctionalCurrencies GO
The below is the SQL view which was produced. It includes the PO and Receipt Numbers as well as the Originating Currency (with symbol) and exchange rate; the Functional Currency was left without a symbol so it can be easily totalled in Excel. Where the transaction does not have an Originating Currency I am outputting the Functional Currency.
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ CREATE VIEW uv_AZRCRV_PurchasingTrxList AS SELECT PM.VCHRNMBR AS 'Voucher Number' ,CASE WHEN PM.DOCTYPE = 1 THEN PM401.PMTRXDSC_1 WHEN PM.DOCTYPE = 2 THEN PM401.PMTRXDSC_2 WHEN PM.DOCTYPE = 3 THEN PM401.PMTRXDSC_3 WHEN PM.DOCTYPE = 4 THEN PM401.PMTRXDSC_4 WHEN PM.DOCTYPE = 5 THEN PM401.PMTRXDSC_5 WHEN PM.DOCTYPE = 6 THEN PM401.PMTRXDSC_6 WHEN PM.DOCTYPE = 7 THEN PM401.PMTRXDSC_7 WHEN PM.DOCTYPE = 8 THEN PM401.PMTRXDSC_8 ELSE 'Unknown' END AS 'Document Type' ,PM004.DOCTYPE ,CASE WHEN PM004.DCSTATUS = 1 THEN 'Work' WHEN PM004.DCSTATUS = 2 THEN 'Open' WHEN PM004.DCSTATUS = 3 THEN 'History' ELSE 'Unknown' END AS 'Document Status' ,PM004.DCSTATUS ,PM.PORDNMBR AS 'PO Number' ,ISNULL(POP303.POPRCTNM,'') AS 'Receipt Number' ,PM.VENDORID AS 'Creditor Name' ,PM002.VENDNAME AS 'Creditor ID' ,CONVERT(VARCHAR,PM.DOCDATE,103) AS 'Document Date' ,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END + ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) + CAST(CAST(ISNULL(MC.OPURAMT,PM.PRCHAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Purchasing Amount' ,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END + ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) + CAST(CAST(ISNULL(MC.ORTAXAMT,PM.TAXAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Tax Amount' ,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END + ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) + CAST(CAST(ISNULL(MC.ORDOCAMT,PM.DOCAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Document Amount' ,CASE WHEN MC.XCHGRATE IS NULL THEN 0 ELSE MC.XCHGRATE END AS 'Exchange Rate' ,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.PRCHAMNT ELSE PM.PRCHAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Purchasing Amount' ,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.TAXAMNT ELSE PM.TAXAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Tax Amount' ,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.DOCAMNT ELSE PM.DOCAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Document Amount' FROM ( -- Transaction History SELECT VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT FROM PM30200 WITH (NOLOCK) UNION ALL -- Transaction Open SELECT VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT FROM PM20000 WITH (NOLOCK) UNION ALL -- Transaction Work SELECT VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT FROM PM10000 WITH (NOLOCK) UNION ALL -- Payment Work SELECT VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,CHEKTOTL FROM PM10300 WITH (NOLOCK) UNION ALL -- Manual Payment Work SELECT VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,DOCAMNT FROM PM10400 WITH (NOLOCK) ) AS PM INNER JOIN PM00400 PM004 WITH (NOLOCK) ON PM004.CNTRLNUM = PM.VCHRNMBR AND PM004.DOCTYPE = PM.DOCTYPE INNER JOIN PM00200 AS PM002 WITH (NOLOCK) ON PM002.VENDORID = PM.VENDORID LEFT JOIN MC020103 AS MC WITH (NOLOCK) ON MC.VCHRNMBR = PM.VCHRNMBR AND MC.DOCTYPE = PM.DOCTYPE LEFT JOIN POP30300 AS POP303 WITH (NOLOCK) ON POP303.VCHRNMBR = PM.VCHRNMBR LEFT JOIN DYNAMICS..MC40200 AS MC402 WITH (NOLOCK) ON MC402.CURRNIDX = MC.CURRNIDX INNER JOIN MC40000 AS MC400 WITH (NOLOCK) ON MC400.FUNLCURR = MC400.FUNLCURR INNER JOIN DYNAMICS..MC40200 AS MC402F WITH (NOLOCK) ON MC402F.CURRNIDX = MC400.FUNCRIDX INNER JOIN PM40100 AS PM401 WITH (NOLOCK) ON PM401.UNIQKEY = PM401.UNIQKEY ORDER BY PM.DOCDATE GO GRANT SELECT ON uv_AZRCRV_PurchasingTrxList TO DYNGRP GO
However, if it returns a fail flag for this check, it doesn’t actually tell you which company (or companies) has failed the check. And when your client has well over a hundred companies you need an alternative method to going through each one manually.
That alternative method is the script below; it uses a cursor to look through the company databases and returns a list of all companies and their functional currency. To only see those companies without a functional currency, there is a where clause at the bottom which is currently commented out.
CREATE TABLE #FUNLCURR ( INTERID VARCHAR(5) ,FUNLCURR VARCHAR(15) ) DECLARE @SQL_Statement VARCHAR(1000) DECLARE cursor_InterID CURSOR FOR SELECT INTERID FROM SY01500 OPEN cursor_InterID DECLARE @INTERID VARCHAR(100) FETCH NEXT FROM cursor_InterID INTO @INTERID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQL_Statement = 'INSERT INTO #FUNLCURR (INTERID,FUNLCURR) VALUES (''' + @INTERID + ''','''')' exec (@SQL_Statement) SET @SQL_Statement = 'UPDATE #FUNLCURR SET FUNLCURR = (SELECT FUNLCURR FROM ' + RTRIM(@INTERID) + '.dbo.MC40000 MC) WHERE INTERID = ''' + @INTERID + '''' exec (@SQL_Statement) FETCH NEXT FROM cursor_InterID INTO @INTERID END CLOSE cursor_InterID DEALLOCATE cursor_InterID SELECT #FUNLCURR.INTERID ,SY01500.CMPNYNAM ,#FUNLCURR.FUNLCURR FROM #FUNLCURR INNER JOIN SY01500 ON SY01500.INTERID = #FUNLCURR.INTERID /*WHERE LEN(FUNLCURR) = ''*/ ORDER BY #FUNLCURR.INTERID DROP TABLE #FUNLCURR
When creating an exchange rate table in Multicurrency Exchange Rate Table Setup ()several fields need to be defined. One of them is the Rate Calculation Method which is set to either Divide or Multiply.
This field can be changed until there are rates entered. The reason this script was created was for a client who create several currencies, entered quite a few rates and then realised that the flag for Rate Calculation Method was set to Multiply instead of Divide.
The choices were either to delete all rates or to change the data behind the scenes; the script took five minutes to write and test, whereas doing the update manually would have taken near an hour.
So script it was. Before I ran the update, I double checked to make sure there were no transactions and also that there was a good backup of the system database.
UPDATE MC40300 SET RTCLCMTD = 1 WHERE EXGTBLID IN ('USD','EURO') GO
The yellow highlighted section is the list of currencies to update.
This script should NOT be run when transactions for the currency being updated have been entered.
5. 820909 Insert Failed 0. Seconds
DOC 4 ERROR: An item with the same key has already been added.
The vast majority of the documents integrated without problem. When we examined the documents which failed, we determined that those which failed were those which had a currency of Euro.
Integration Manager errors are typically very unhelpful and this was no different; the first thing we checked was to see if the PO Number existed in GP, but as we expected it did not.
We did some investigating and found that the Euro Exchange Rate’s Transaction Rate Default in GP was defined as Exact Date; I was able to update this to Next Date after which the integration stopped producing errors.