This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted 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 items with serial numbers which were purchased and the linked sales transaction.

CREATE VIEW uv_AZRCRV_AllocatedSerialNumbers AS
/*
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
	['Purchasing Receipt Line Quantities'].PONUMBER
	,['Purchasing Receipt Line Quantities'].VENDORID
	,['Purchasing Receipt Line Quantities'].POPRCTNM
	,['Sales Serial/Lot Work and History'].ITEMNMBR
	,[Purchasing Serial Lot History'].SERLTNUM
	,['Sales Serial/Lot Work and History'].SOPTYPE
	,CASE ['Sales Serial/Lot Work and History'].SOPTYPE
		WHEN 1 THEN	'Quote'
		WHEN 2 THEN	'Order'
		WHEN 3 THEN	'Invoice'
		WHEN 5 THEN	'Back Order'
		ELSE ''
	END AS 'Type'
	,['Sales Serial/Lot Work and History'].SOPNUMBE
	,['Sales Transactions'].DOCDATE
	,['Sales Transactions'].DOCID
FROM
	POP30330 AS [Purchasing Serial Lot History']
INNER JOIN
	POP10500 AS ['Purchasing Receipt Line Quantities']
		ON
			['Purchasing Receipt Line Quantities'].POPRCTNM = [Purchasing Serial Lot History'].POPRCTNM
		AND
			['Purchasing Receipt Line Quantities'].RCPTLNNM = [Purchasing Serial Lot History'].RCPTLNNM
LEFT JOIN
	SOP10201 AS ['Sales Serial/Lot Work and History']
		ON
			['Sales Serial/Lot Work and History'].SERLTNUM = [Purchasing Serial Lot History'].SERLTNUM
LEFT JOIN
	(
		SELECT
			SOPNUMBE
			,SOPTYPE
			,DOCID
			,DOCDATE
			,CUSTNMBR
			,CUSTNAME
		FROM
			SOP10100 AS ['Sales Transaction Work']
		UNION ALL
			SELECT
				SOPNUMBE
				,SOPTYPE
				,DOCID
				,DOCDATE
				,CUSTNMBR
				,CUSTNAME
			FROM
				SOP30200 AS ['Sales Transaction History']
	) AS ['Sales Transactions']
		ON
			['Sales Transactions'].SOPNUMBE = ['Sales Serial/Lot Work and History'].SOPNUMBE
		AND
			['Sales Transactions'].SOPTYPE = ['Sales Serial/Lot Work and History'].SOPTYPE
GO

GRANT SELECT ON uv_AZRCRV_AllocatedSerialNumbers TO DYNGRP
GO

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

This script was created for a client to run to rename the company database with a prefix and add when they copied the databases from production to either the dev or test servers; to allow for a single script to be used for both systems, the script includes an IF statement.

/*
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).
*/
DECLARE @CompanyNamePrefix AS VARCHAR(4)

IF @@SERVERNAME = 'SQL-DEV-01\GP' -- dev
	SET @CompanyNamePrefix = 'DEV'
IF @@SERVERNAME = 'SQL-TEST-01\GP' -- test
	SET @CompanyNamePrefix = 'TEST: '
	
UPDATE
	['Company Master']
SET
	CMPNYNAM = LEFT(@CompanyNamePrefix + ': ' + CAST(RTRIM(['Company Master'].CMPNYNAM) + ' ' AS CHAR(150)), 150)
FROM
	DYNAMICS..SY01500 AS ['Company Master'] [[Company Master (SY01500)
WHERE
	INTERID = DB_NAME()
AND
	LEFT(['Company Master'].CMPNYNAM, 3) <> @CompanyNamePrefix
AND
	LEFT(['Company Master'].CMPNYNAM, 4) <> @CompanyNamePrefix

While I no longer deal with Microsoft Dynamics GP myself, since my move to working with Microsoft Dynamics 365 Business Central, but I do still know a lot of people who work with Dynamics GP.

I received a call from one of them recently to see if I had a way of flagging items for inclusion on a specific VAT Daybook return. The script for setting transactions as already included on a VAT Daybook return was originally rejected, when we discussed requirements it turned out that it was the solution, but a little more work was required.

The issue was that a company was having the VAT Daybook enabled in a company which had never had VAT installed or configured before which meant transactions hadn’t previously been included in the VAT tables.

When the VAT Daybook module was installed transactions started being included. However, the first return which needed to be submitted was Q4, but with the VAT calendar created, there were all four quarters created.

To make sure transactions were only included in the Q$ return, the solution was multi-step:

1. Use the script to flag all transactions as included on a VAT return.
2. Run the Q1, Q2 and Q3 VAT returns.
3. Run the script to remove the flag against transactions saying they had been included.
4. Run the Q4 VAT return to pick up all of the required transactions.

Once the above was done, all future VAT returns can be processed as normal and they will include the correct transactions.

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

This script can be used when implementing the VAT Daybook; if the implementation is on an existing system then you need to flag already procesed transactions as included on a return.

This can be done through the system by running VAT returns, but if Dynamics GP has been in use for a while then this could mean a large number of transactions.

The below script can be used to select transactions and stamp them as included on a VAT return so they are not picked up and submitted to HMRC again.

/*
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).
*/
DECLARE @Tax_Return_ID VARCHAR(20) = 'Y2022Q4'
DECLARE @YEAR VARCHAR(4) = 2022
DECLARE @START_MONTH VARCHAR(2) = 10
DECLARE @END_MONTH VARCHAR(2) = 12

UPDATE
	TX30000 --Tax History (TX30000)
SET
	Tax_Return_ID = @Tax_Return_ID
	,Included_On_Return = 1
WHERE
	YEAR(DOCDATE) = @YEAR 
AND
	MONTH(docdate) BETWEEN @START_MONTH AND @END_MONTH

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

This script was created for a client to run to change the web service location when they copied the DYNAMICS database from production to either the dev or test servers; to allow for a single script to be used for both systems, the script includes an IF statement.

/*
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).
*/
-- set parameter
DECLARE @Web_Service_Server AS VARCHAR(100)

IF @@SERVERNAME = 'DEVSQL-01\GP' -- dev
	SET @Web_Service_Server = 'devweb-01'
IF @@SERVERNAME = 'TESTSQL-01\GP' -- test
	SET @Web_Service_Server = 'testweb-01'

-- use parameter to update the WF00100 table with the new URL
UPDATE WF00100 --Workflow Setup (WF00100)
	SET Web_Service_Server = @Web_Service_Server
WHERE
	SETUPKEY = 0