Removing Analytical Accounting from Microsoft Dynamics GP

● Ian Grieve ●  ● 5 Comments   ● 

Microsoft Dynamics GPI recently posted a script to remove the Fixed Asset Management tables from Microsoft Dynamics GP. I wrote that script for a client who wanted to start using Fixed Asset Management, but it turned out the module had been installed at some point in the past and then removed and the client wanted to reintroduce it.

We’ve just had a similar instance come up with a client upgrading from Microsoft Dynamics GP 2013 SP2 to 2016 R2 where Analytical Accountijng has been installed at some stage and removed and was stopping the upgrade from going through.

In this case though, there was (operative word was) a Knowledge Base article from Microsoft on how to remove Analytical Accounting which is no longer available (hopefully it will be back as I understand Microsoft are moving to a new KB system).

The script was available when we had the issue, but a couple of weeks later, the question was asked by one of the other MVPs. By the time I saw the question, they’d been emailed the scripts by one of the others.

However, when my colleague downloaded the scripts they had also saved a copy of the web page, which meant I could grab the download links for the scripts.

  1. System Database Script
  2. Company Database Script

After running the Microsoft scripts, you’ve downloaded using the above links, run the below script on the system database to remove the Analytical Accounting entries from the upgrade tables:

DECLARE @PRODID INT = 3180

DELETE DB_Upgrade WHERE PRODID = @PRODID
DELETE DU000020 WHERE PRODID = @PRODID

As always before running scripts, make sure you have a good backup of your databases.

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

SQL Script To Log Into Each Company

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPI have a few of clients who have a large number of companies. This generally isn’t a problem as Microsoft Dynamics GP supports this very well.

However, after doing an upgrade of a system which has the VAT Daybook module installed, you need to log into each and every database using the sa account.

Doesn’t sound so bad on the surface, but when you have two hundred companies, this soon adds up to a substantial length of time.

I did a little looking around a while ago for ways to automate this process and found a post by Aaron Berquist from 2011 where he had done exactly this.

However, when we tried his script we got a few errors, so I made a few changes and improvements; my script automatically adds OK button clicks for Test, Historic and Fabrikam sample company databases.

The Test and Historic databases are identified by having or at the end of their names; this is what Dynamics GP uses to display the message.

I removed the temporary table from Aaron’s script as well; when my version of the script is run, output the results to text (make sure you have increased the query results length).

You can copy and paste the output into a Notepad document which can be saved with a .mac extension.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
Based on idea and code from http://www.aaronberquist.com/2011/07/use-dynamic-sql-to-generate-a-dynamics-gp-login-macro/
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	'# DEXVERSION=16.00.0033.000 2 2
	  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company'' 
		NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company'' 
		  ClickHit field ''(L) Company Names'' item ' + CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY CMPANYID)) + '  # ' + LTRIM(RTRIM(CMPNYNAM)) + ' 
		  MoveTo field ''OK Button'' 
		  ClickHit field ''OK Button''
		NewActiveWin dictionary ''default''  form sheLL window sheLL 
		NewActiveWin dictionary ''default''  form sheLL window sheLL ' +
		CASE WHEN RTRIM(CMPNYNAM) LIKE '%' THEN
			'
			  # This company is set up for testing only. Do not use this company when processing live data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN RTRIM>(CMPNYNAM) LIKE '%' THEN
			'
			  # This company is used for storing historical data only. Do not use this company when processing current-year data.
			NewActiveWin dictionary ''default''  form ''SY_Error_Message'' window ''SY_Error_Message'' 
			  ClickHit field ''OK Button'' 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		WHEN CMPANYID = -1 THEN
			'
			  # You have chosen to use the sample company, which provides data that you can use to practice procedures or learn more about the product. When you use this sample company, the date is automatically set to April 12, 2017.
			NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
			  ClickHit field OK 
			NewActiveWin dictionary ''default''  form sheLL window sheLL '
		ELSE
			''
		END
FROM
	DYNAMICS..SY01500
ORDER BY
	CMPNYNAM
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Remove Fixed Asset Management Tables

● Ian Grieve ●  ● 5 Comments   ● 

Microsoft Dynamics GPEvery so often when doing an upgrade, or implementing a module, for a client, we encounter errors when doing the GP Utilities database upgrade. This time round we encountered the error when implementing Fixed Asset Management (FAM) in Microsoft Dynamics GP 2015 R2.

We had previously upgraded the client from Dynamics GP 2010 R2 where they had not been using Fixed Asset Management and never had. However, despite the feature not being installed in Dynamics GP 2010 R2 and the client never knowing having used it, there were tables for Fixed Asset Management in their of the 20+ company databases; this looks like one of their previous partners had done something odd when creating these companies.

The solution in this case was to remove all of the Fixed Asset Management tables from the database. When I have done this type of thing before I have manually written scripts to do this, but have tired of doing so (the previous time I had to do this it was the HR modules.

So I wrote a simple script using a cursor which is run against the system database and which loops through all of the company databases and generates delete scripts for all of the tables for the designated module.

It also generates scripts to delete the rows from the DU tables in the system database.

There are three parameters at the top which need to be set:

  1. The database at the top which should be a system database
  2. @PRODID which is the numeric product id; for FAM this 309
  3. @TablePrefix which is the alpha prefix to the table names, which for FAM is FA

The parameters are not authenticated or verified in any so oyu need to make sure the product id and table prefix are correct before proceeding.

When the script is run, output it to Text which will give you a series of DROP TABLE commands you can then verify you are happy with the scripts before running them. I would strongly recommend checking the scripts and running them on a test system containing a copy of live first to ensure the result is what you require.

These fields have been highlighted in the, below, script:

/*
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).
*/
USE D16R1
GO

DECLARE @PRODID INT = 309
DECLARE @TablePrefix VARCHAR(5) = 'FA'

DECLARE @SQL_Statement VARCHAR(1000)

CREATE TABLE #Scripts(
	COMMAND VARCHAR(200)
)

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		RTRIM(INTERID)
	FROM
		SY01500
	UNION
		SELECT DB_NAME()
	
	OPEN cursor_InterID

	DECLARE @INTERID VARCHAR(100)

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@INTERID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
			IF (@@FETCH_STATUS <> -2)
				BEGIN
					SET @SQL_Statement = 'INSERT INTO #Scripts (COMMAND) (SELECT ''DROP TABLE ' + @INTERID + '..'' + name FROM ' + RTRIM(@INTERID) + '.sys.tables WHERE name LIKE ''' + @TablePrefix + '%'')'
					EXEC (@SQL_Statement)
				END
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@INTERID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DB_Upgrade WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000010 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000020 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
INSERT INTO #Scripts (COMMAND) (SELECT 'DELETE FROM DU000030 WHERE PRODID = ' + CAST(@PRODID AS VARCHAR(5)))
GO

SELECT COMMAND + CHAR(10) + 'GO' FROM #Scripts
GO

DROP TABLE #Scripts
GO
● Categories: Dynamics, Fixed Asset Management, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Fixing Typo in Fabrikam’s Tax Schedule Description

● Ian Grieve ●  ● 3 Comments   ● 

Microsoft Dynamics GPThe script in this post fixes a typo, in the Fabrikam sample database, in the Tax Schedule Description whereby the h in Purchases was missing. Not a big thing, but I was working on a report which was configured to return the Description as well as the Tax Schedule ID and it was bugging me.

Hence a script to fix it, so I can easily fix it again in future.

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/UPDATE
	['Tax Schedule Header Master']
SET
	TXSCHDSC = REPLACE(TXSCHDSC, 'Purcase', 'Purchase')
FROM
	TX00101 AS ['Tax Schedule Header Master']
WHERE
	TXSCHDSC LIKE '%Purcase%'
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Create macro To Activate BOMs

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPWhile implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).

This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.

I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.

The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.

However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT DISTINCT
	'# DEXVERSION=11.00.0364.000 2 2
	CheckActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + '''
	  MoveTo field ''Bill Status'' item 0 
	  ClickHit field ''Bill Status'' item 2  # ''Pending'' 
	  MoveTo field ''Expansion Button 1'' 
	  ClickHit field ''Expansion Button 1'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window ChangeStatus 
	  ClickHit field ''Bill Status'' item 1  # ''Active'' 
	  MoveTo field ''Process Button P'' 
	  ClickHit field ''Process Button P'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  MoveTo field ''Save Button'' 
	  ClickHit field ''Save Button'' 
	'
FROM
	BM00101 AS BMH
INNER JOIN
	BM00111 AS BMC
		ON BMC.ITEMNMBR = BMH.ITEMNMBR
WHERE
	BMH.Bill_Status = 2

I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.

Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.

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

Find SQL Trigger In All Databases

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPIn the last post, I posted a SQL script which could be used to find a column in the database. This post has a SQL script which can be used to locate triggers in all databases.

Change the highlighted section to the name of the trigger you’re looking for and run the script.

This is not something I am going to do manually, so I wrote a script which would find all tables containing the specified column (change 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @command nvarchar(max)
DECLARE @Trigger VARCHAR(50) = 'utr_'

SELECT @command = 'IF EXISTS ( SELECT 1 FROM sys.databases WHERE name = ''?'')   
					BEGIN
						USE [?];
						DECLARE @stmt nvarchar(max) DECLARE @n char(1) SET @n = char(10)
						SELECT DB_NAME() AS ''DB_NAME'',t.name,t.is_disabled FROM sys.triggers AS t 
						INNER JOIN sys.objects AS o ON o.object_id = t.object_id WHERE o.name LIKE ''' + @Trigger + '%''
					END'

EXEC sp_MSforeachdb @command
● Categories: Microsoft, SQL Server ● Tags: , , , ,  ● Permalink ● Shortlink ●

Find Column In SQL

● Ian Grieve ●  ● 8 Comments   ● 

Microsoft Dynamics GPDuring a recent upgrade we encountered an error which resulted in me poking around in the database attempting to locate column called PAYRCORD.

This is not something I am going to do manually, so I wrote a script which would find all tables containing the specified column (change 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
DECLARE @ColumnToFind VARCHAR(20) = 'PAYRCORD'
SELECT
	SCHEMA_NAME(t.schema_id) AS 'Schema'
	,t.name AS 'Table'
FROM
	sys.tables AS t
INNER JOIN
	sys.columns AS c
		ON
			t.OBJECT_ID = c.OBJECT_ID
WHERE
	c.name = @ColumnToFind
ORDER BY
	'Schema'
	,'Table'
● Categories: Microsoft, SQL Server ● Tags: , , ,  ● Permalink ● Shortlink ●

List View Action Pane Error During Upgrade

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPWe have been doing a number of upgrades for clients recently, the majority of which went fine. However, we did have one, being done by one of my team, where there was an error produced on the table List View Action Pane when upgrading to Microsoft Dynamics GP 2015 R2:

Company Detail

Continue reading → List View Action Pane Error During Upgrade

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

SQL View Joining GL Transactions To MDA

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPWe have a couple of clients using MDA (Multi-dimensional Analysis; the precursor to Analytical Accounting), which I don’t know terribly well. So, when one of them asked for a new SmartList Object to be created which extracts information about General Journals and the related MDA information, I needed to do some exploring of the database to work out the links.

Unfortunately, the links between the GL transactions and MDA are not especially obvious. To verify what I had created I did a search and came across a post from 2011 by Mark Polino which was posting code created by a Jeremy Lowell.

I ended up combining some of the code I had with Jeremy’s code (when I tried just his I was getting duplicate lines) to create the below SQL View. Since writing and giving the view to the client, I’ve spotted a few places where the SQL could be tightened up, but this view has been tested in its current state.

CREATE VIEW uv_AZRCRV_LinkGLtoMDA AS
	SELECT DISTINCT
		GLT.JRNENTRY
		,GLT.YEAR
		,GLT.TRXDATE
		,GLT.REFRENCE
		,GLT.SOURCDOC
		,GLT.DEBITAMT
		,GLT.CRDTAMNT
		,GLT.ACTINDX
		,DTA10100.DTASERIES
		,DTA10100.DTAREF
		,DTA10100.GROUPID
		,DTA10100.DTA_GL_Reference
		,DTA10100.GROUPAMT
		,DTA10200.CODEID
		,DTA10200.POSTDESC
		,DTA10200.CODEAMT
	FROM
		(SELECT GLT.JRNENTRY
			,GLT.YEAR
			,GLT.TRXDATE
			,GLT.REFRENCE
			,GLT.SOURCDOC
			,GLT.DEBITAMT
			,GLT.CRDTAMNT
			,GLT.ACTINDX
		FROM
			GL20000 WITH (NOLOCK)
		UNION ALL 
			SELECT GLT.JRNENTRY
				,GLT.YEAR
				,GLT.TRXDATE
				,GLT.REFRENCE
				,GLT.SOURCDOC
				,GLT.DEBITAMT
				,GLT.CRDTAMNT
				,GLT.ACTINDX
			FROM
				GL30000 WITH (NOLOCK)
		) AS GLT
	LEFT OUTER JOIN
		DTA10100 WITH (NOLOCK)
			ON
				DTA10100.JRNENTRY = GLT.JRNENTRY
			AND
				DTA10100.ACTINDX = GLT.ACTINDX 
			AND
				(DTA10100.SEQNUMBR = GLT.SEQNUMBR OR DTA10100.SEQNUMBR <> GLT.SEQNUMBR)
			AND
				GLT.ORCTRNUM = DTA10100.DOCNUMBR 
	LEFT OUTER JOIN
		DTA10200 WITH (NOLOCK)
			ON
				(DTA10200.DTAREF = DTA10100.DTAREF
					AND
				GLT.SEQNUMBR = GLT.OrigSeqNum)
			OR
				(DTA10200.DTAREF = DTA10100.DTAREF
					AND
				GLT.SEQNUMBR <> GLT.OrigSeqNum)
GO
GRANT SELECT ON uv_AZRCRV_LinkGLtoMDA TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

View To Return Sales Orders (Work Status) Requiring An Assembly

● Ian Grieve ●  ● 5 Comments   ● 

Microsoft Dynamics GPI have some clients who use the Bill of Materials and assemblies within the Inventory series rather than the Manufacturing series and full MRP; their processes are not so complex that they need this level of MRP functionality. To make it easy to link an assembly to a sales order, the assemblies are created with the same ID as the order (one of the clients has a high level of automation added via customisations to automatically create the assembly from the order).

I’ve created a script to return this information on more than one occasion, so finally decided to post it here so I can easily find it.


CREATE VIEW uv_AZRCRV_SalesOrdersToBeAssembled AS
/*
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).
*/
SELECT
	CASE WHEN LEFT(RTRIM(SOP102.SOPNUMBE),3) = 'ORD' THEN
		SUBSTRING(RTRIM(SOP102.SOPNUMBE),4,LEN(RTRIM(SOP102.SOPNUMBE))-3)
	ELSE
		RTRIM(SOP102.SOPNUMBE)
	END +
	CASE WHEN (SELECT COUNT(SOPNUMBE) FROM SOP10200 SOP102I WHERE SOP102I.SOPNUMBE = SOP102.SOPNUMBE) > 1 THEN
		'_' + CAST(SOP102.LNITMSEQ/13684 AS VARCHAR(2))
	ELSE
		''
	END AS 'Assembly'
	,FORMAT(GETDATE(), 'yyyyMMdd') AS 'Batch Number'
	,RTRIM(SOP102.ITEMNMBR) AS 'Item Number'
	,CASE WHEN SOP102.UOFM = 'EACH' THEN
		CAST(CAST(SOP102.QUANTITY AS DECIMAL(10,0)) AS VARCHAR(10))
	ELSE
		CAST(CAST(SOP102.QUANTITY*10000 AS DECIMAL(10,0)) AS VARCHAR(10))
	END AS 'Quantity'
	,SOP102.UOFM AS 'UofM'
	,SOP101.BACHNUMB AS 'Sales Batch'
FROM
	SOP10200 SOP102 WITH (NOLOCK)
INNER JOIN
	SOP10100 SOP101 WITH (NOLOCK)
		ON
			SOP101.SOPNUMBE = SOP102.SOPNUMBE
		AND
			SOP101.SOPTYPE = SOP102.SOPTYPE
INNER JOIN
	BM00101 BM101 WITH (NOLOCK)
		ON
			BM101.ITEMNMBR = SOP102.ITEMNMBR
		AND
			BM101.Bill_Status = 1
LEFT JOIN
	BM10200 BM102 WITH (NOLOCK)
		ON BM102.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
LEFT JOIN
	BM30200 BM302 WITH (NOLOCK)
		ON BM302.TRX_ID = RTRIM(SUBSTRING(SOP102.SOPNUMBE,4,LEN(SOP102.SOPNUMBE)-3))
WHERE
	SOP102.SOPTYPE = 2
AND
	BM102.TRX_ID IS NULL
AND
	BM302.TRX_ID IS NULL
GO

GRANT SELECT ON uv_AZRCRV_SalesOrdersToBeAssembled TO DYNGRP
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●