Category: GP (Great Plains)

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of sales in the last five years summarised by salesperson:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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	
	ISNULL(['RM Salesperson Master'].SLPRSNID, '* No Salesperson *') AS CustomerSalesperson
	,RTRIM(LTRIM(RTRIM(ISNULL(['RM Salesperson Master'].SLPRSNFN, '')) + ' ' + ISNULL(['RM Salesperson Master'].SPRSNSLN, ''))) AS CustomerSalesPersonName
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) THEN SLSAMNT ELSE 0 END) AS SalesYear0
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -1 THEN SLSAMNT ELSE 0 END) AS [SalesYear-1]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -2 THEN SLSAMNT ELSE 0 END) AS [SalesYear-2]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -3 THEN SLSAMNT ELSE 0 END) AS [SalesYear-3]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -4 THEN SLSAMNT ELSE 0 END) AS [SalesYear-4]
FROM
	(
		SELECT
			CUSTNMBR
			,DOCNUMBR
			,RMDTYPAL
			,DOCDATE
			,GLPOSTDT
			,CASE WHEN RMDTYPAL < 7 THEN
				SLSAMNT
			ELSE
				SLSAMNT * -1
			END AS SLSAMNT
			,SLPRSNID
		FROM
			RM20101 [sqlgreen]--[gptl=RM20101][/sqlgreen]
		WHERE
			RMDTYPAL <> 9
		AND
			VOIDSTTS = 0
		UNION ALL
			SELECT
				CUSTNMBR
				, DOCNUMBR
				, RMDTYPAL
				, DOCDATE
				, GLPOSTDT
				,CASE WHEN RMDTYPAL < 7 THEN
					SLSAMNT
				ELSE
					SLSAMNT * -1
				END AS SLSAMNT
				,SLPRSNID
			FROM
				RM30101 [sqlgreen]--[gptl=RM30101][/sqlgreen]
			WHERE
				RMDTYPAL <> 9
			AND
				VOIDSTTS = 0
	) AS ['Sales Data']
	LEFT OUTER JOIN
		RM00301 AS ['RM Salesperson Master'] --RM Salesperson Master (RM00301)
			ON
				['Sales Data'].SLPRSNID = ['RM Salesperson Master'].SLPRSNID
GROUP BY
	['RM Salesperson Master'].SLPRSNID
	,['RM Salesperson Master'].SLPRSNFN
	,['RM Salesperson Master'].SPRSNSLN
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of sales in the last five years summarised by customer:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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	
	['RM Customer MSTR'].CUSTNMBR AS CustomerNumber
	,['RM Customer MSTR'].CUSTNAME AS CustomerName
	,['RM Customer MSTR'].CUSTCLAS AS CustomerClass
	,['RM_Class_MSTR'].CLASDSCR AS CustomerClassDescription
	,ISNULL(['RM Salesperson Master'].SLPRSNID, '* No Salesperson *') AS CustomerSalesperson
	,RTRIM(LTRIM(RTRIM(ISNULL(['RM Salesperson Master'].SLPRSNFN, '')) + ' ' + ISNULL(['RM Salesperson Master'].SPRSNSLN, ''))) AS CustomerSalesPersonName
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) THEN SLSAMNT ELSE 0 END) AS SalesYear0
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -1 THEN SLSAMNT ELSE 0 END) AS [SalesYear-1]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -2 THEN SLSAMNT ELSE 0 END) AS [SalesYear-2]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -3 THEN SLSAMNT ELSE 0 END) AS [SalesYear-3]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -4 THEN SLSAMNT ELSE 0 END) AS [SalesYear-4]
FROM
	(
		SELECT
			CUSTNMBR
			,DOCNUMBR
			,RMDTYPAL
			,DOCDATE
			,GLPOSTDT
			,CASE WHEN RMDTYPAL < 7 THEN
				SLSAMNT
			ELSE
				SLSAMNT * -1
			END AS SLSAMNT
		FROM
			RM20101 [sqlgreen]--[gptl=RM20101][/sqlgreen]
		WHERE
			RMDTYPAL <> 9
		AND
			VOIDSTTS = 0
		UNION ALL
			SELECT
				CUSTNMBR
				, DOCNUMBR
				, RMDTYPAL
				, DOCDATE
				, GLPOSTDT
				,CASE WHEN RMDTYPAL < 7 THEN
					SLSAMNT
				ELSE
					SLSAMNT * -1
				END AS SLSAMNT
			FROM
				RM30101 [sqlgreen]--[gptl=RM30101][/sqlgreen]
			WHERE
				RMDTYPAL <> 9
			AND
				VOIDSTTS = 0
	) AS ['Sales Data']
	INNER JOIN
		RM00101 ['RM Customer MSTR'] --RM Customer MSTR (RM00101)
			ON
				['Sales Data'].CUSTNMBR = ['RM Customer MSTR'].CUSTNMBR
	LEFT OUTER JOIN
		RM00301 AS ['RM Salesperson Master'] --RM Salesperson Master (RM00301)
			ON
				['RM Customer MSTR'].SLPRSNID = ['RM Salesperson Master'].SLPRSNID
	LEFT OUTER JOIN
		RM00201 AS ['RM_Class_MSTR'] --RM_Class_MSTR (RM00201)
			ON
				['RM Customer MSTR'].CUSTCLAS = ['RM_Class_MSTR'].CLASSID
GROUP BY
	['RM Customer MSTR'].CUSTNMBR
	,['RM Customer MSTR'].CUSTNAME
	,['RM Customer MSTR'].CUSTCLAS
	,['RM Salesperson Master'].SLPRSNID
	,['RM Salesperson Master'].SLPRSNFN
	,['RM Salesperson Master'].SPRSNSLN
	,['RM_Class_MSTR'].CLASDSCR
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was put together a while ago for a client who was migrating from Dynamics GP to another ERP system.

There are three sections to the script.

Firstly, a table is created in the DYNAMICS database into which data from all company databases will be populated:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 GLPeriodBalances
	(
		INTERID VARCHAR(100),
		ACTINDX VARCHAR(100),
		ACTNUMST VARCHAR(100),
		YEAR1 VARCHAR(100),
		PERIODID VARCHAR(100),
		LEDGERID VARCHAR(100),
		PERDBLNC VARCHAR(100),
		ACTNUMBR_1 VARCHAR(100),
		ACTNUMBR_2 VARCHAR(100),
		ACTNUMBR_3 VARCHAR(100),
		ACCATNUM VARCHAR(100),
		DEBITAMT VARCHAR(100),
		CRDTAMNT VARCHAR(100)
	)
GO

Secondly, a script which can be run in each company database to populate the extract table in the DYNAMICS database:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/INSERT INTO DYNAMICS..GLPeriodBalances
	(
		INTERID
		,ACTINDX
		,ACTNUMST
		,YEAR1
		,PERIODID
		,LEDGERID
		,PERDBLNC
		,ACTNUMBR_1
		,ACTNUMBR_2
		,ACTNUMBR_3
		,ACCATNUM
		,DEBITAMT
		,CRDTAMNT
	)
	
	SELECT
		DB_NAME(),
		['GL Period Balance'].ACTINDX
		,['Account Index Master'].ACTNUMST
		,['GL Period Balance'].YEAR1
		,['GL Period Balance'].PERIODID
		,['GL Period Balance'].LEDGER_ID
		,['GL Period Balance'].PERDBLNC
		,['Account Index Master'].ACTNUMBR_1
		,['Account Index Master'].ACTNUMBR_2
		,['Account Index Master'].ACTNUMBR_3
		,['GL Period Balance'].ACCATNUM
		,['GL Period Balance'].DEBITAMT
		,['GL Period Balance'].CRDTAMNT
	FROM
		GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
	INNER JOIN
		GL10110 AS ['GL Period Balance'] --Account Current Summary Master (GL10110)
			ON
				['Account Index Master'].ACTINDX = ['GL Period Balance'].ACTINDX
GO

Thirdly, the script which is used to select data from the extract table in the DYNAMICS database:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 
      *
FROM
	DYNAMICS..GLPeriodBalances
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script creates a SQL view which returns a list of payments and the invoices linked to them.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 VIEW uv_AZRCRV_InvoicesLinkedToPayments AS

SELECT
	['Payment Batch'].BACHNUMB AS 'Batch ID'
	,['Payment Batch'].DOCDATE AS 'Payment Date'
	,['Payment Batch'].DOCNUMBR As 'Cheque Number'
	,['PM Transactions'].VCHRNMBR
	,['PM Transactions'].VENDORID
	,['PM Creditor Master'].VENDNAME
	,CASE WHEN ['PM Transactions'].DOCTYPE = 1 THEN
		'Invoice'
	WHEN ['PM Transactions'].DOCTYPE = 5 THEN
		'Credit Note'
	WHEN ['PM Transactions'].DOCTYPE = 6 THEN
		'Payment'
	END AS DOCTYPE
	,['PM Transactions'].DOCDATE
	,['PM Transactions'].VALUE
	,['PM Transactions'].MODIFDT
	,['PM Transactions'].PSTGDATE
	,['PM Transactions'].POSTEDDT
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	PM30200 AS ['Payment Batch'] WITH (NOLOCK) --PM Paid Transaction History File (PM30200)
INNER JOIN
	PM10201 AS ['Remittance'] WITH (NOLOCK) --PM Payment Apply To Work File (PM10201)
		ON
			['Payment Batch'].VCHRNMBR = ['Remittance'].PMNTNMBR
INNER JOIN
	(SELECT
		['PM Transaction Open'].VCHRNMBR
		,['PM Transaction Open'].VENDORID
		,['PM Distribution Work Open'].DSTINDX
		,['PM Transaction Open'].DOCTYPE
		,['PM Transaction Open'].DOCDATE
		,CASE WHEN ['PM Distribution Work Open'].CRDTAMNT > 0 THEN
			['PM Distribution Work Open'].CRDTAMNT * -1
		ELSE
			['PM Distribution Work Open'].DEBITAMT
		END AS 'Value'
		,['PM Transaction Open'].MODIFDT
		,['PM Transaction Open'].PSTGDATE
		,['PM Transaction Open'].POSTEDDT
	FROM
		PM20000 AS ['PM Transaction Open'] WITH (NOLOCK) --PM Transaction OPEN File (PM20000)
	INNER JOIN
		PM10100 AS ['PM Distribution Work Open'] WITH (NOLOCK) --PM Distribution WORK OPEN (PM10100)
			ON
				['PM Distribution Work Open'].VCHRNMBR = ['PM Transaction Open'].VCHRNMBR
			AND
				['PM Distribution Work Open'].CNTRLTYP = ['PM Transaction Open'].CNTRLTYP
	UNION ALL
		SELECT
			['PM Transaction History'].VCHRNMBR
			,['PM Transaction History'].VENDORID
			,['PM Distribution Hist'].DSTINDX
			,['PM Transaction History'].DOCTYPE
			,['PM Transaction History'].DOCDATE
			,CASE WHEN ['PM Distribution Hist'].CRDTAMNT > 0 THEN
				['PM Distribution Hist'].CRDTAMNT * -1
			ELSE
				['PM Distribution Hist'].DEBITAMT
			END AS 'Value'
			,['PM Transaction History'].MODIFDT
			,['PM Transaction History'].PSTGDATE
			,['PM Transaction History'].POSTEDDT
		FROM
			PM30200 AS ['PM Transaction History'] WITH (NOLOCK)
		INNER JOIN
			PM30600 AS ['PM Distribution Hist'] WITH (NOLOCK) --PM Distribution History File (PM30600)
				ON
					['PM Distribution Hist'].VCHRNMBR = ['PM Transaction History'].VCHRNMBR
				AND
					['PM Distribution Hist'].DOCTYPE = ['PM Transaction History'].DOCTYPE
	) AS ['PM Transactions']
		ON
			['PM Transactions'].VCHRNMBR = ['Remittance'].VCHRNMBR
		AND
			['PM Transactions'].DOCTYPE = ['Remittance'].DOCTYPE
INNER JOIN
	GL00105 AS ['Account Index Master'] WITH (NOLOCK) --Account Index Master (GL00105)
		ON ['Account Index Master'].ACTINDX = ['PM Transactions'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master'] WITH (NOLOCK) --Breakdown Account Master (GL00100)
		ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX
INNER JOIN
	PM00200 AS ['PM Creditor Master'] WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON ['PM Creditor Master'].VENDORID = ['PM Transactions'].VENDORID
GO

GRANT SELECT ON uv_AZRCRV_InvoicesLinkedToPayments TO DYNGRP
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will delete the orphaned vendor EFT details left over after a vendor record has been deleted, which was due to a but in Dynamics GP (which may be fixed by now).

I’ve previously posted about a SQL trigger to delete the EFT when the vendor card is deleted, but that wouldn’t remove existing orphaned records which this script will do.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DELETE
	['Address Electronic Transfer Funds Master']
FROM
	SY06000 AS ['Address Electronic Transfer Funds Master'] --Address Electronic Funds Transfer Master (SY06000)
LEFT JOIN
	PM00300 AS ['PM Creditor Address Master'] --PM Address MSTR (PM00300)
		ON
			['PM Creditor Address Master'].VENDORID = ['Address Electronic Transfer Funds Master'].VENDORID
		AND
			['PM Creditor Address Master'].ADRSCODE = ['Address Electronic Transfer Funds Master'].ADRSCODE
WHERE
	['PM Creditor Address Master'].VENDORID IS NULL

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created to allow a client to quickly export the entire chart of accounts.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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
	['Account Index Master'].ACTNUMST AS 'Account'
	,['Account Master'].ACTDESCR AS 'Account Description'
	,['Account Category Master'].ACCATDSC AS 'Account Category'
	,CASE ['Account Master'].PSTNGTYP
		WHEN 0 THEN
			'Balance Sheet'
		ELSE
			'Profit and Loss'
	END AS 'Posting Type'
	,CASE ['Account Master'].TPCLBLNC
		WHEN 0 THEN
			'Debit'
		ELSE
			'Credit'
	END AS 'Typical Balance'
	,USERDEF1 AS 'User-Defined 1'
	,USERDEF2 AS 'User-Defined 1'
	,USRDEFS1 AS 'User-Defined 1'
	,USRDEFS2 AS 'User-Defined 4'
FROM
	GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100)
INNER JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
INNER JOIN
	GL00102 AS ['Account Category Master'] --Account Category Master (GL00102)
		ON
			['Account Category Master'].ACCATNUM = ['Account Master'].ACCATNUM

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will copy the email messages from a source database to a destination one. It was created for a client who was implementing a number of new companies in Dynamics GP and wanted to save some time by migrating the email messages from an existing company.

The highlighed database is the source database which you will need to amend and run the script in the destination.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
--Email Messages
INSERT INTO SY04901 --Email Messages (SY04901)
	SELECT
		EmailMessageID
		,EmailDictionaryID
		,EmailSeriesID
		,EmailDocumentID
		,Workflow_Type_Name
		,EmailMessageDesc
		,EmailMessageSubject
		,EmailMessageFrom
		,Email_Message_Type
		,EmailReplyToAddress
		,EmailMessageBody
	FROM
		TWO..SY04901 AS ['Source Email Messages']
	WHERE
		EmailMessageID IN ('MESSAGEID')
	AND
		(SELECT
			COUNT(*)
		FROM
			SY04901 AS ['Destination Email Messages']
		WHERE
			['Destination Email Messages'].EmailMessageID = ['Source Email Messages'].EmailMessageID
		AND
			['Destination Email Messages'].EmailDictionaryID = ['Source Email Messages'].EmailDictionaryID
		AND
			['Destination Email Messages'].	EmailSeriesID = ['Source Email Messages'].	EmailSeriesID) = 0
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will copy the workflow calendar from a source database to a destination one. It was created for a client who did testing of workflow in one database and then wanted to deploy the changes across a couple of dozen more.

The highlighed databases are the source database which you will need to amend and run the script in the destination.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
--Workflow Calendar Down Days
INSERT INTO WF40310 --Workflow Calendar Down Days (WF40310)
	SELECT
		YEAR1
		,DATE1
		,DateDescription
	FROM
		TWO..WF40310 AS ['Workflow Calendar Down Days']
GO
--Workflow Calendar
UPDATE
	['Destination Workflow Calendar']
SET
	IsWorkDay = ['Source Workflow Calendar'].IsWorkDay
	,STRTTIME = ['Source Workflow Calendar'].STRTTIME
	,ENDTIME = ['Source Workflow Calendar'].ENDTIME
FROM
	WF40300 AS ['Destination Workflow Calendar']
INNER JOIN
	TWO..WF40300 AS ['Source Workflow Calendar']
		ON
			['Source Workflow Calendar'].DayOfWeek = ['Destination Workflow Calendar'].DayOfWeek
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will copy the workflow setup from a source database to a destination one. It was created for a client who did testing of workflow in one database and then wanted to deploy the changes across a couple of dozen more.

The highlighed databases are the source database which you will need to amend and run the script in the destination.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
--UPDATE SETUP IF EXISTS
UPDATE
	DESTWF
SET
	EnableWFNotifService = SRCWF.EnableWFNotifService
	,Web_Service_Server = SRCWF.Web_Service_Server
	,Web_Service_Server_Port = SRCWF.Web_Service_Server_Port
	,Web_Service_Server_SSL = SRCWF.Web_Service_Server_SSL
	,Enable_Workflow_Email = SRCWF.Enable_Workflow_Email
	,EMail = SRCWF.EMail
	,DSPLNAME = SRCWF.DSPLNAME
	,SERVERID = SRCWF.SERVERID
	,Server_Port = SRCWF.Server_Port
	,IsSSL = SRCWF.IsSSL
	,SMTP_Authentication = SRCWF.SMTP_Authentication
	,SMTP_User = SRCWF.SMTP_User
	,SMTP_Password = SRCWF.SMTP_Password
FROM
	WF00100 AS DESTWF --Workflow Setup (WF00100)
INNER JOIN
	TWO..WF00100 AS SRCWF
		ON
			SRCWF.SETUPKEY = DESTWF.SETUPKEY
WHERE
	DESTWF.SETUPKEY = 0
GO
--INSERT SETUP IF NOT EXISTS
INSERT WF00100
	SELECT
		SETUPKEY
		,EnableWFNotifService
		,Web_Service_Server
		,Web_Service_Server_Port
		,Web_Service_Server_SSL
		,Enable_Workflow_Email
		,EMail
		,DSPLNAME
		,SERVERID
		,Server_Port
		,IsSSL
		,SMTP_Authentication
		,SMTP_User
		,SMTP_Password
	FROM
		TWO..WF00100 AS ['Workflow Setup']
	WHERE
		(SELECT COUNT(*) FROM WF00100 WHERE SETUPKEY = 0) = 0
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script created for a client who wanted to verify tax details on vendors in Dynamics GP against a spreadsheet. It uploads a tab delimited text file and outputs lines where the tax detail on the file is different to the one in the database.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 #Vendor
	(
	VENDORID VARCHAR(30)
	,VENDNAME VARCHAR(100)
	,ADDRESS1 VARCHAR(100)
	,ADDRESS2 VARCHAR(100)
	,CITY	VARCHAR(100)
	,STATE VARCHAR(100)
	,ZIPCODE VARCHAR(100)
	,PHONE1 VARCHAR(100)
	,OLDTAXSCHID VARCHAR(30)
	,BLANK VARCHAR(30)
	,TAXSCHID VARCHAR(30)
	,COMMENT VARCHAR(500)
	)
GO

BULK INSERT
	#Vendor
FROM
	'c:\Temp\Vendors.txt'
WITH
	(FIELDTERMINATOR = '\t'
	,ROWTERMINATOR = '\n')
GO

SELECT
	['PM Vendor Master'].VENDORID,*
FROM
	#Vendor AS VendorUpdate
LEFT JOIN
	PM00200 AS ['PM Vendor Master'] --PM Vendor Master File (PM00200)
		ON
			VendorUpdate.VENDORID = ['PM Vendor Master'].VENDORID
WHERE
	['PM Vendor Master'].VENDORID IS NULL
OR
	VendorUpdate.TAXSCHID <> ['PM Vendor Master'].TAXSCHID
GO

DROP TABLE #Vendor
GO