When creating a new vendor there is a fair bit of information which needs to be entered and it is sometimes nice to reduce the amount which needs to be done manually; in the last post I had a script to enable the email document for Check Remittances.
The below script creates a trigger on the SY04905 table to automatically flag the purchase orders to be emailed out in PDF format and using a Message ID of PURCHASEORDER respectively.
/* 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 TRIGGER utr_AZRCRV_Update_SY04905_Activate_PO ON dbo.SY04905 AFTER Insert AS -- Enable Purchase Order UPDATE Email SET EmailDocumentEnabled = 1 ,EmailMessageID = 'PURCHASEORDER' ,EmailDocumentFormat = 3 FROM SY04905 AS Email INNER JOIN inserted ON Email.EmailDictionaryID = 0 AND Email.EmailSeriesID = 4 AND Email.MODULE1 = 12 AND Email.EmailCardID = inserted.EmailCardID AND Email.EmailDocumentID = 1 GO
The below script creates a trigger on the SY04905 table to automatically flag the remittance to be emailed out in PDF format and using a Message ID of REMITTANCE.
/* 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 TRIGGER utr_AZRCRV_Update_SY04905_Activate_Remittance ON SY04905 AFTER Insert AS -- Enable Remittance UPDATE Email SET EmailDocumentEnabled = 1 ,EmailMessageID = 'REMITTANCE' ,EmailDocumentFormat = 3 FROM SY04905 AS Email INNER JOIN inserted ON Email.EmailDictionaryID = 0 AND Email.EmailSeriesID = 4 AND Email.MODULE1 = 19 AND Email.EmailCardID = inserted.EmailCardID AND Email.EmailDocumentID = 6 GO
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
We are currently in the process of upgrading a large 20 site client from Microsoft Dynamics GP 9 to a later version (unfortunately not 2015 due to third party add-ons which are not supported on this version). The client previously had standalone implementations at each site, but we are aggregating them into one implementation for ease of maintenance and also to reduce licensing costs.
The first few we centralised went fine, but we then ran into problems when we did the head office companies. When they ran the GL Summary TB on the old and new systems there was a mismatch between them.
After investigating, the support team established that the problem was down to some corruption in the Account Index Master table (GL00105) where some of the accounts had segments separated with dots instead of dashes as defined in Account Format Setup ().
The solution they followed was fairly simple, and in three steps:
- Run a script to clear the Account Index Master table:
DELETE FROM GL00105
- Run Check Links ( ) on Account Master.
- Run the General Ledger Reconcile utility ( ) on all years starting with the oldest and working forward.
After this was done, the report produced from the upgraded system matched the old one.
I’ve created a number of small views to report on vendor related information (such as email remittance and email addresses) and have recently followed up with another script which returns bank details for vendors:
CREATE VIEW uv_AZRCRV_VendorAddressElectronicTransferMaster AS SELECT ['Address Electronic Transfer Master'].VENDORID AS 'Vendor ID' ,['Address Electronic Transfer Master'].ADRSCODE AS 'Address Code' ,['Address Electronic Transfer Master'].BANKNAME AS 'Bank Name' ,['Address Electronic Transfer Master'].EFTBankCode AS 'Bank Sort Code' ,['Address Electronic Transfer Master'].EFTBankAcct AS 'Bank Account' FROM SY06000 AS ['Address Electronic Transfer Master'] WHERE ['Address Electronic Transfer Master'].SERIES = 4 GO GRANT SELECT ON uv_AZRCRV_VendorAddressElectronicTransferMaster TO DYNGRP GO
This script returns the fields I was interested in for a client, but could easily be extended to include additional fields from the EFT table.
We recently upgraded a client with well over a hundred companies from Microsoft Dynamics GP 10 to GP 2015; one of the new pieces of functionality they wanted to start using was the Budget Transactions.
One problem is that the keep history checkbox for Budget Transactions in General Ledger Setup () is unmarked.
Enabling it for this many companies was going to take a substantial amount of time (even if we generated a macro to do this in automatically) as the client has over 120 companies in Dynamics GP. It is a simple setting in one table which needs to be updated, so instead of using a macro I created a SQL script containing a cursor to loop through all of the databases attached to the system database and enable this setting.
As with all scripts which change data, please make sure you have a good backup of your database (or in this case all of your company databases) before running the script.
DECLARE @IntercompanyID VARCHAR(5) DECLARE @SQLStatement AS VARCHAR(2000) DECLARE cursor_InterID CURSOR FOR SELECT INTERID FROM DYNAMICS..SY01500 INNER JOIN master..sysdatabases ON name = INTERID OPEN cursor_InterID FETCH NEXT FROM cursor_InterID INTO @IntercompanyID WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000 SET KPBUDTRXHIST = 1 WHERE KPBUDTRXHIST = 0' EXEC (@SQLStatement) FETCH NEXT FROM cursor_InterID INTO @IntercompanyID END CLOSE cursor_InterID DEALLOCATE cursor_InterID
You will need to amend the script if your System Database is not called DYNAMICS.
This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.
SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.
While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.
In this case the view I created works only for transactions which are at a status of work:
CREATE VIEW uv_PI_SOPCustomerItemLink AS SELECT SOP101.CUSTNMBR ,SOP102.ITEMNMBR ,SOP603.CUSTITEMNMBR ,SOP603.CUSTITEMDESC FROM SOP10200 AS SOP102 INNER JOIN SOP10100 AS SOP101 ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPOwner = SOP102.SOPOwner INNER JOIN SOP60300 AS SOP603 ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR GO GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP GO
The SQL above includes the Grant statement used to add select permissions for the DYNGRP.
One client who was using Inventory was entering a lot of user categories, mistakenly entered the description into the Image field. In that case I did not know that they were populating the User Categories or I would have offered this script to them to save time.
To use the script you need a CSV file with four columns: User Category Value (the ID of the item you want to load), User Category Number (which of the user categories into which the row is to be loaded), Image URL and Description:
CREATE TABLE #UploadData (USCATVAL VARCHAR(100) ,USCATNUM VARCHAR(1) ,Image_URL VARCHAR(300) ,UserCatLongDescr VARCHAR(300)) GO BULK INSERT #UploadData FROM 'c:\temp\UserCategories.csv' WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n') GO INSERT INTO IV40600 (USCATVAL ,USCATNUM ,Image_URL ,UserCatLongDescr) (SELECT LEFT(UD.USCATVAL, 10) ,LEFT(UD.USCATNUM, 1) ,LEFT(UD.Image_URL, 254) ,LEFT(UD.UserCatLongDescr, 254) FROM #UploadData AS UD WHERE (SELECT COUNT(IV.USCATVAL) FROM IV40600 AS IV WHERE IV.USCATVAL = UD.USCATVAL AND IV.USCATNUM = UD.USCATNUM) = 0) GO DROP TABLE #UploadData GO
You will need to change the highlighted line to the location of your CSV file. As always before running a script on live, test it in a test company first and have a good backup of your database.
Unfortunately, when they configured the User Categories in Inventory Control Setup () the description for the category was entered in the Image field instead of the Description field.
Rather than making them go through and manually change all of the categories that had been defined, I put together a small script to do the job.
The script checks if the length of the entry in the Image field is greater than zero and that the Description has not been set before doing the update.
UPDATE IV40600 SET UserCatLongDescr = Image_URL ,Image_URL = '' WHERE LEN(RTRIM(Image_URL)) > 0 AND LEN(RTRIM(UserCatLongDescr)) = 0 GO
As I was writing this script we encountered the same setup issue on a client installation we took over from another partner so this looks like it may be more common that I thought it would be; caused I think be the fact the window has the Image field visible by default and not the Description which people expect.
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.