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

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

Read original post SQL Scripts for Microsoft Dynamics GP: Script to Set Transactions as Included on VAT Daybook Return at azurecurve|Ramblings of an IT Professional