SQL Trigger To Automatically Enable Email Documents In Purchasing – Purchase Orders

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPWhen 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
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Trigger To Automatically Enable Email Documents In Purchasing – Remittance

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPWhen 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.

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
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Return Functional Currencies For All Companies

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPI 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
● Categories: Dynamics, GP, Microsoft, Multicurrency, Web Services ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

General Ledger Summary Trial Balance Returning Incorrect Data

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPWe 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 (Administration area page ¯ Setup ¯ Company ¯ Account Format).

The solution they followed was fairly simple, and in three steps:

  1. Run a script to clear the Account Index Master table:

    DELETE FROM GL00105
  2. Run Check Links (Microsoft Dynamics GP menu ¯ Maintenance ¯ Check Links) on Account Master.
  3. Run the General Ledger Reconcile utility (Financials area page ¯ Utilities ¯ Reconcile) 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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View On Vendor EFT

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPI’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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Set Budget Transaction History To Be Kept In All Companies

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPWe 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 (Financials ¯ Setup ¯ General Ledger) 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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Customer Item Link From SOP Transaction Line

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPThis 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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Insert Inventory User Categories From CSV

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPI have had this script for quite a while and have used it a number of times for different clients when implementing the Inventory Control module in Microsoft Dynamics GP.

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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Update Setup Mistake On Inventory User Categories

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPWe implemented Microsoft Dynamics GP for a client a while ago; they are quite heavy users of the Inventory Control module.

Unfortunately, when they configured the User Categories in Inventory Control Setup (Inventory ¯ Setup ¯ Inventory Control) 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.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Change Rate Calculation Method To Divide

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPThis 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 (Administration ¯ Setup ¯ System ¯ Exchange Table)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.

● Categories: Dynamics, GP, Microsoft, Multicurrency ● Tags: , , , , ,  ● Permalink ● Shortlink ●