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.
I’ve been doing some work with a US-based company as they implement Microsoft Dynamics GP in Europe for one of their subsidiaries (we do a fair bit of this type of work for expertise with VAT and Intrastat). One of the issues they asked for assistance with was a problem they had while importing transactions.
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:
I posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.
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 (http://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
Currently, the payment selection process only allows vouchers issued in the batch currency to be selected and added to the check run. If the user wants to add foreign currency vouchers, he/she needs to use the Edit Checks window to add such vouchers. It would be nice to be able to have an option to include functional currency vouchers in the case of a foreign currency batch, or foreign currency vouchers in the case of a functional currency batch.
This is the use case:
|Vendor A||Invoice 1||CA$10.00|
|Vendor A||Invoice 2||US$20.00|
|Vendor A||Invoice 3||US$30.00|
If a payment selection is performed and the batch currency is CA$, only the first voucher will be selected. To add Invoice 2 and 3, you would have to use the Edit Checks window to include both vouchers.
Conversely, if the batch currency is US$ and the payment selection for vendor A is performed, only invoices 2 and 3 would be selected and you would have to use the Edit Checks window to include Invoice 1.
What Mariano would like to see is all invoices selected (based on selection rules) and let the user deal with the exceptions, as it should be.
As mentioned, all the logic is already there via the Edit Checks window and it would be nice to incorporate as part of the payment selection process. As others have mentioned, having multicurrency vouchers included could be a payables configuration item or simply an additional option in the Select Payments (Select Checks) window.
Mark Polino has also suggested that this functionality be optioned with Rod O’Connor further suggesting the option be on the Select Checks window itself to allow maximum flexibility. Both of these seem like a good addition to Mariano’s idea.
The suggestion can be voted on here.
A bit like Integration Manager, Management Reporter often presents highly generic meaningless error messages to users. The error message below was provided to me by a client a while ago (but I have just stumbled across the screenshots again), which I was then able to reproduce just be ruunning the report:
I have done a fair bit of work recently for clients with the Web Services for Microsoft Dynamics GP. One of the checks the configuration tool does is to check if all of the companies have a functional currency defined.
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.
Full details can be found at the link above, but here’s a quick summary of some of the new features:
- Dynamic calculated column headers…no more hard coding of Months within the column definition
- Currency translation for Dynamics GP 2013 using DDM Data Mart provider
- Ability to create a side by side balance sheet by using a section break option in row definition
- Option to include line numbers on generated reports
- Ability to set all columns in definition to Autofit by default
- Collection of Excel formatting improvements
Rollup 2 can be downloaded from here (CustomerSource or PartnerSource login required).
This one adds support for SQL Server 2012 as well as other bug fixes and enhancements to the currency translation.