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(
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
''' + INTERID + '''
' + INTERID + '.dbo.MC40000
LEN(FUNLCURR) = 0'
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
INSERT INTO #FunctionalCurrencies
EXEC sys.sp_executesql @SQL
SELECT * FROM #FunctionalCurrencies
DROP TABLE #FunctionalCurrencies
This suggestion is one from MVP Mariano Gomez to improve the payment run process by allowing multicurrency invoices to be included.
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:
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:
The operation could not be completed due to a problem in the data provider framework.
Continue reading “Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework””
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
DECLARE @SQL_Statement VARCHAR(1000)
SELECT INTERID FROM SY01500
FETCH NEXT FROM
WHILE (@@FETCH_STATUS <> -1)
IF (@@FETCH_STATUS <> -2)
SET @SQL_Statement = 'INSERT INTO #FUNLCURR (INTERID,FUNLCURR) VALUES (''' + @INTERID + ''','''')'
SET @SQL_Statement = 'UPDATE #FUNLCURR SET FUNLCURR = (SELECT FUNLCURR FROM ' + RTRIM(@INTERID) + '.dbo.MC40000 MC) WHERE INTERID = ''' + @INTERID + ''''
FETCH NEXT FROM
ON SY01500.INTERID = #FUNLCURR.INTERID
LEN(FUNLCURR) = ''*/
DROP TABLE #FUNLCURR
This is a script I hope not to need in future, but as I have had to write it, I figured I might as well post it.
When creating an exchange rate table in Multicurrency Exchange Rate Table Setup (Rate Calculation Method which is set to either Divide or Multiply.
)several fields need to be defined. One of them is the
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.
RTCLCMTD = 1
EXGTBLID IN ('USD','EURO')
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.
I was working with a client the other day with Integration Manager to import some purchase orders when we received the following error on some of the purchase orders:
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.
The Dynamics Financial Reporting blog today announced the release of Management reporter 2012 CU8.
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
Management Reporter 2012 CU8 can be downloaded from here for the US and all other countries here (CustomerSource or ParnerSource login required).
I typically like to see rollups come out as they tend to fix bugs and add new features but yesterday I advised a customer to install rollup 1 for Management Reporter.
Hopefully, I can catch them first thing in the morning and get them onto rollup 2 instead, of which Sivakumar has just announced the release on the Dynamics Partner Technical Services team blog.
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.