SQL View To Allow Customer/Item Link In SmartList Builder

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPOn this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.

As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):


CREATE VIEW uv_AZRCRV_CustomerItemLink 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
		SOP102.SOPNUMBE
		,SOP102.SOPTYPE
		,SOP102.LNITMSEQ
		,SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
	FROM
		SOP10200 AS SOP102 WITH (NOLOCK)
	INNER JOIN
		SOP10100 AS SOP101 WITH (NOLOCK)
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
	INNER JOIN
		SOP60300 AS SOP603 WITH (NOLOCK)
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
GO
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Error: “The query uses non-ANSI outer join operators”

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPI’ve recently been working on a project upgrading a client from Microsoft Dynamics GP 9 SP3 to a later version and also from Microsoft SQL Server 2000 to SQL Server 2008 R2. Much of the upgrade has gone through without problems, but we’ve encountered one or two issues with customisations and custom reports.

The following error message was produced during testing when generating one of the old Crystal Reports:

Crystal Report Viewer - Failed to open rowset. Detsails: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...

Crystal Report Viewer

Failed to open rowset.
Details: 42000:[Microsoft][SQL Server Native Client 10.0][SQL Server]The query uses non-ANSI outer join operators("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backwa...

The compatibility level of a database for Microsoft Dynamics GP should NOT be changed back to 80 when this error is encountered. The solution to use is the recommended one from the error message: to rewrite the query.

In this case the client had a set of Crystal Reports written against GP 9 which called a variety of stored procedures. I spent a few hours reviewing and rewriting stored procedures to remove the non-ANSI outer joins and replacing them with ANSI ones.

● Categories: Dynamics, GP, Microsoft, SQL Server, SQL Server 2000, SQL Server 2008 ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Copy Tax Setup To All Companies Using Cursor

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPFollowing on from the creation of the script to copy tax schedules between companies, I was on a fairly long train journey and spent a small part of the time wrapping a cursor round the original script to allow tax schedules to be configured in one company and rolled through the others.

There is one setting at the top of the script to set the SourceCompany.

One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

/*
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).
*/
DECLARE @SQLStatement AS VARCHAR(2000)
DECLARE @SourceCompany VARCHAR(5)
DECLARE @DestinationCompany VARCHAR(5)

SET @SourceCompany = 'TWO'

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
		@DestinationCompany
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)

			-- Sales/Purchases Tax Schedule Header Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
				(TAXSCHID,TXSCHDSC)
			--VALUES
				(SELECT 
					TAXSCHID,TXSCHDSC
				FROM
					' + @SourceCompany + '..TX00101 AS TX
				WHERE
					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
			EXEC (@SQLStatement)

			-- Sales/Purchases Tax Schedule Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
				(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
			--VALUES
				(SELECT
					TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
				FROM
					' + @SourceCompany + '..TX00102 AS TX
				WHERE
					(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			-- Sales Purchases Tax Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
				(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
				TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
				NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
				TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
			--VALUES
				(SELECT
					TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
					TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
					NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
					TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
				FROM
					' + @SourceCompany + '..TX00201 AS TX 
				WHERE
					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			-- Sales/Purchases Tax Summary Master
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
				(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
			--VALUES
				(SELECT
					TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
				FROM
					' + @SourceCompany + '..TX00202 AS TX
				WHERE
					(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
			EXEC (@SQLStatement)

			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

If you use this script, then please make sure you have a good backup before running it and also test afterwards.

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

SQL Script To Copy Tax Setup To A New Company

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPWhile helping a client configure some new companies recently, we needed to create the tax schedules in each of the companies; unfortunately, there were about 30 tax schedules and 4 companies which needed them. To do manually, or even with a macro, was going to take a while, so I took a few minutes out to see if it was going to be possible to run the update using a SQL script.

The below is what I came up with and appeared to work fine for the companies we tested it in. One item to note, is that the Account Index is set to 0 so all companies do need the tax nominal account entering once they have been copied.

There are two variables at the top which will need to be configured before running the script: @SourceCompany and @DestinationCompany.

/*
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).
*/
DECLARE @Statement AS VARCHAR(2000)
DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'PI'

-- Sales/Purchases Tax Schedule Header Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00101
	(TAXSCHID,TXSCHDSC)
--VALUES
	(SELECT 
		TAXSCHID,TXSCHDSC
	FROM
		' + @SourceCompany + '..TX00101 AS TX
	WHERE
		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00101 WHERE TAXSCHID = TX.TAXSCHID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Schedule Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00102
	(TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate)
--VALUES
	(SELECT
		TAXSCHID,TAXDTLID,TXDTLBSE,TDTAXTAX,Auto_Calculate
	FROM
		' + @SourceCompany + '..TX00102 AS TX
	WHERE
		(SELECT COUNT(TAXSCHID) FROM ' + @DestinationCompany + '..TX00102 WHERE TAXSCHID = TX.TAXSCHID AND TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00201
	(TAXDTLID,TXDTLDSC,TXDTLTYP,ACTINDX,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
	TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,NOTEINDX,
	NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
	TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT)
--VALUES
	(SELECT
		TAXDTLID,TXDTLDSC,TXDTLTYP,0,TXIDNMBR,TXDTLBSE,TXDTLPCT,TXDTLAMT,TDTLRNDG,TXDBODTL,TDTABMIN,
		TDTABMAX,TDTAXMIN,TDTAXMAX,TDRNGTYP,TXDTQUAL,TDTAXTAX,TXDTLPDC,TXDTLPCH,TXDXDISC,CMNYTXID,0,
		NAME,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHONE1,PHONE2,PHONE3,FAX,TXUSRDF1,
		TXUSRDF2,VATREGTX,TaxInvReqd,TaxPostToAcct,TaxBoxes,IGNRGRSSAMNT,TDTABPCT
	FROM
		' + @SourceCompany + '..TX00201 AS TX 
	WHERE
		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00201 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

-- Sales/Purchases Tax Summary Master
SET @Statement = 'INSERT INTO ' + @DestinationCompany + '..TX00202
	(TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST)
--VALUES
	(SELECT
		TAXDTLID,TDTSYTD,TDSLLYTD,TXDTSYTD,TDTSLYTD,TXDSTYTD,TDSTLYTD,KPCALHST,KPERHIST
	FROM
		' + @SourceCompany + '..TX00202 AS TX
	WHERE
		(SELECT COUNT(TAXDTLID) FROM ' + @DestinationCompany + '..TX00202 WHERE TAXDTLID = TX.TAXDTLID) = 0)'
EXEC (@Statement)

If you do use this script, then please make sure you have a good backup before running it and also test afterwards.

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

SQL Script To Fix A Corrupt VAT 100 Return

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPThe process of creating a Tax Return in Microsoft Dynamics GP can sometimes take a while when there are a large number of transactions to process. While this usually isn’t a problem, on occasion a network connectivity issue (or a Citrix server cutting the user off after a certain length of time) can cut GP off from the database resulting in corrupt information in the Tax Return tables.

The following script will remove the corrupt data from the Tax tables and reset the flag on the transactions which shows which tax Return they’re on.

Change the highlighted section to the Tax Return ID to be reset:

/*
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).
*/
DECLARE @TAX_RETURN_ID AS VARCHAR(100) = 'taxreturnid'

DELETE FROM
	TX00301
WHERE
	TAX_RETURN_ID = @TAX_RETURN_ID
GO

DELETE FROM
	TX00303
WHERE
	TAX_RETURN_ID = @TAX_RETURN_ID
GO

DELETE FROM
	TX00304
WHERE
	TAX_RETURN_ID = @TAX_RETURN_ID
GO

UPDATE
	TX30000
SET
	Included_On_Return = 0
	,Tax_Return_ID = ''
WHERE
	TAX_RETURN_ID = @TAX_RETURN_ID
GO

Before running the script make sure you have a good backup of the database and test afterwards to make sure everything is OK.

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

SQL Trigger To Automatically Enable Email Documents In Purchasing – All

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPThis post includes a script which amalgamates the previous two posted triggers to enable email documents (purchase orders and check remittances) into one.

The below script creates a trigger on the SY04905 table to automatically flag the remittance and purchase orders to be emailed out in PDF format and using Message IDs of REMITTANCE and 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_All 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
	-- 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 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 ●