SQL Scripts for Microsoft Dynamics GP: Script to Set Transactions as Included on VAT Daybook Return

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