Month: April 2023

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

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 running against a company database when it had been copied to a test company or test system; it sets the WF_AllowOrigApprover flag on Workflow Master (WF100002) to allow originators to be an approver.

/*
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
	WF100002 --Workflow Master (WF100002)
SET
	WF_AllowOrigApprover = 1
WHERE
	WF_AllowOrigApprover = 0

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 is another variation of the simple audit I created, but this time it audits for changes to the RMA tables.

/*
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 ut_AZRCRV_Audit
	(
		RecordType VARCHAR(100)
		,RecordID VARCHAR(100)
		,UpdateType VARCHAR(10)
		,Username varchar(150)
		,ChangeDateTime DateTime
		,OldData NVARCHAR(MAX)
		,NewData NVARCHAR(MAX)
	)
GO

CREATE TRIGGER utr_AZRCRV_SVC05000_AuditUpdate ON SVC05000 AFTER UPDATE AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05000'
		,d.RETDOCID
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'RATETPID = ' + CAST(RTRIM(d.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(d.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(d.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(d.EXCHDATE) AS VARCHAR(100))
		,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100))
	FROM
		deleted d
	LEFT JOIN
		inserted i
			ON
				i.RETDOCID = d.RETDOCID
GO

CREATE TRIGGER utr_AZRCRV_SVC05000_AuditInsert on SVC05000 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05000'
		,i.RETDOCID
		,'Insert'
		,SYSTEM_USER
		,GETDATE()
		,''
		,'RATETPID = ' + CAST(RTRIM(i.RATETPID) AS VARCHAR(100)) + ' | ' + 'EXGTBLID = ' + CAST(RTRIM(i.EXGTBLID) AS VARCHAR(100)) + ' | ' + 'XCHGRATE = ' + CAST(RTRIM(i.XCHGRATE) AS VARCHAR(100)) + ' | ' + 'EXCHDATE = ' + CAST(RTRIM(i.EXCHDATE) AS VARCHAR(100))
	FROM
		inserted i
GO

CREATE TRIGGER utr_AZRCRV_SVC05200_AuditUpdate ON SVC05200 AFTER UPDATE AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05200'
		,CAST(d.RETDOCID AS VARCHAR(15)) + ' | ' + CAST(d.LNSEQNBR AS VARCHAR(20))
		,'Update'
		,SYSTEM_USER
		,GETDATE()
		,'UNITPRCE = ' + CAST(RTRIM(d.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(d.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(d.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(d.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(d.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(d.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(d.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
		,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
	FROM
		deleted d
	LEFT JOIN
		inserted i
			ON
				i.RETDOCID = d.RETDOCID
			AND
				i.LNSEQNBR = d.LNSEQNBR
GO

CREATE TRIGGER utr_AZRCRV_SVC05200_AuditInsert on SVC05200 AFTER INSERT AS
INSERT INTO
	ut_AZRCRV_Audit
--VALUES
	SELECT
		'SVC05200'
		,CAST(RETDOCID AS VARCHAR(15)) + ' | ' + CAST(LNSEQNBR AS VARCHAR(20))
		,'Insert'
		,SYSTEM_USER
		,GETDATE()
		,''
		,'UNITPRCE = ' + CAST(RTRIM(i.UNITPRCE) AS VARCHAR(100)) + ' | ' + 'ORUNTPRC = ' + CAST(RTRIM(i.ORUNTPRC) AS VARCHAR(100)) + ' | ' + 'XTNDPRCE = ' + CAST(RTRIM(i.XTNDPRCE) AS VARCHAR(100)) + ' | ' + 'OXTNDPRC = ' + CAST(RTRIM(i.OXTNDPRC) AS VARCHAR(100)) + ' | ' + 'Originating_Return_Price = ' + CAST(RTRIM(i.Originating_Return_Price) AS VARCHAR(100)) + ' | ' + 'SVC_Extended_Return_Pric = ' + CAST(RTRIM(i.SVC_Extended_Return_Pric) AS VARCHAR(100)) + ' | ' + 'SVC_Orig_Ext_Return_Pric = ' + CAST(RTRIM(i.SVC_Orig_Ext_Return_Pric) AS VARCHAR(100))
	FROM
		inserted i
GO