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 selects details of tax linked to the GL; I don’t recall the use case for this, but thought it could be useful to someone.

/*
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
	TX.TAXDTLID
	,TXDTLDSC
	,GL105.ACTNUMST
	,GL100.ACTDESCR
	,GL100.ACTINDX
FROM
	TX00201 AS TX WITH (NOLOCK) --Sales/Purchases Tax Master (TX00201)
INNER JOIN
	GL00105 AS GL105 WITH (NOLOCK) --Account Index Master (GL00105)
		ON GL105.ACTINDX = TX.ACTINDX
INNER JOIN
	GL00100 AS GL100 WITH (NOLOCK) --Breakdown Account Master (GL00100)
		ON GL100.ACTINDX = TX.ACTINDX

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 sets a new vendor to a status of on hold, if there are EFT details already present when the vendor record is inserted.

This was created for a client who had some problems due to a bug in Dynamics GP where EFT details weren’t deleted with the vendor card, which meant next time a vendor was created with the same Vendor ID there bank details for the previous record would still be present.

/*
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 TRIGGER [dbo].[utr_AZRCRV_EFTCreditorHold] 
   ON  [dbo].[PM00200]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @VENDORID CHAR(15)
	SELECT @VENDORID = VENDORID FROM INSERTED

	IF 
		@VENDORID IN
			(SELECT 
				VENDORID 
			FROM 
				SY06000 --Address Electronic Funds Transfer Master (SY06000)
			WHERE 
				VENDORID = @VENDORID)
	
		UPDATE 
			PM00200 --PM Vendor Master File (PM00200)
		SET 
			HOLD = 1 
		WHERE 
			VENDORID = @VENDORID
END

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 report of items including assigned bins and average and highest selling prices in the last 12 months,

/*
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
	['Item Master'].ITEMNMBR
	,['Item Master'].ITEMDESC
	,['Item Site Quantity'].QTYONHND
	,['Item Site Quantity'].QTYBKORD
	,['Item Site Quantity'].ATYALLOC
	,['Item Site Quantity'].QTYONHND - ['Item Site Quantity'].ATYALLOC AS 'Available Quantity'
	,['Item Site Quantity'].ORDRPNTQTY
	,['Item Site Quantity'].ORDRUPTOLVL
	,['Item Site Quantity'].QTYSOLD
	,['Item Master'].CURRCOST
	,['Item Site Quantity'].QTYONHND * ['Item Master'].CURRCOST AS EXTNDCST
	,['Item Site Quantity'].LOCNCODE
	,'Site' AS 'Record Type'
	,['Item Master'].ITMCLSCD
	,['Item Master'].ITMSHNAM
	,['Item Master'].UOMSCHDL AS 'Unit of measurement'
	,['BOM Master'].MODIFDT AS 'Last changed BOM date'
	,(
		STUFF((
			SELECT
				', ' + RTRIM(BIN)
			FROM
				IV00112 AS ['Item Site Bin Master'] --Item Site Bin Master (IV00112)
			WHERE
				['Item Site Bin Master'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
			AND
				['Item Site Bin Master'].LOCNCODE = ['Item Site Quantity'].LOCNCODE
			AND
				['Item Site Bin Master'].QUANTITY > 0
			ORDER BY
				BIN
			FOR XML PATH('')
			), 1, 2, '')
		) AS 'Bin Location Code(s)'
	,(
		SELECT
			SUM(['Sales Transaction Amounts History'].UNITPRCE) / SUM(['Sales Transaction Amounts History'].QUANTITY)
		FROM
			SOP30300 AS ['Sales Transaction Amounts History'] --Sales Transaction Amounts History (SOP30300)
		INNER JOIN
			SOP30200 AS ['Sales Transaction History'] --Sales Transaction History (SOP30200)
				ON
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
				AND
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
		WHERE
			['Sales Transaction History'].SOPTYPE = 3
		AND
			['Sales Transaction History'].DOCDATE >= DATEADD(month, -13, GETDATE())
		AND
			['Sales Transaction Amounts History'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
		AND
			['Sales Transaction Amounts History'].LOCNCODE = ['Item Site Quantity'].LOCNCODE
		AND
			['Sales Transaction History'].VOIDSTTS = 0
		) AS 'Average selling price - last 13 months'
	,(
		SELECT TOP 1
			['Sales Transaction Amounts History'].UNITPRCE
		FROM
			SOP30300 AS ['Sales Transaction Amounts History'] --Sales Transaction Amounts History (SOP30300)
		INNER JOIN
			SOP30200 AS ['Sales Transaction History'] --Sales Transaction History (SOP30200)
				ON
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
				AND
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
		WHERE
			['Sales Transaction History'].SOPTYPE = 3
		AND
			['Sales Transaction History'].DOCDATE >= DATEADD(month, -13, GETDATE())
		AND
			['Sales Transaction Amounts History'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
		AND
			['Sales Transaction Amounts History'].LOCNCODE = ['Item Site Quantity'].LOCNCODE
		AND
			['Sales Transaction History'].VOIDSTTS = 0
		ORDER BY
			['Sales Transaction Amounts History'].UNITCOST DESC
		) AS 'Highest selling price - last 13 months'
		,['GL Account Index Master'].ACTNUMST
		,['GL Account Master'].ACTDESCR
FROM
	IV00101 AS ['Item Master'] --Item Master (IV00101)
LEFT JOIN
	IV00102 AS ['Item Site Quantity'] --Item Quantity Master (IV00102)
		ON
			['Item Master'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
		AND
			['Item Site Quantity'].LOCNCODE <> ''
LEFT JOIN
	GL00105 AS ['GL Account Index Master'] --Account Index Master (GL00105)
		ON
			['Item Master'].IVIVINDX = ['GL Account Index Master'].ACTINDX
LEFT JOIN
	GL00100 AS ['GL Account Master'] --Breakdown Account Master (GL00100)
		ON
			['Item Master'].IVIVINDX = ['GL Account Master'].ACTINDX
LEFT OUTER JOIN
	BM00101 AS ['BOM Master'] --Bill of Materials Header (BM00101)
		ON
			['Item Master'].ITEMNMBR = ['BOM Master'].ITEMNMBR
		AND
			['BOM Master'].Bill_Status = 1
WHERE
	['Item Master'].ITEMTYPE = 1
AND
	['Item Site Quantity'].QTYONHND <> 0

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 simple RM Aged TB (Summary) report with figures calculated for the current, previous five periods and anything a column for anything older than this.

/*
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 @DATE AS DATETIME = '2027-04-12'

SELECT DISTINCT
	['RM Debtor Master'].CUSTNMBR AS 'Customer'
	,['RM Debtor Master'].CUSTNAME AS 'Name'
	,ISNULL(
		(
			SELECT
					SUM(CASE WHEN ['RM Key Master'].RMDTYPAL in (1,3,4,5,6) THEN
						['RM Transaction Open'].CURTRXAM ELSE ['RM Transaction Open'].CURTRXAM * -1 END)
						
			FROM
				RM00401 AS ['RM Key Master'] --RM Key File (RM00401)
			LEFT JOIN
				RM20101 AS ['RM Transaction Open'] --RM Open File Debit Copy (RM20101)
					ON
						['RM Transaction Open'].RMDTYPAL = ['RM Key Master'].RMDTYPAL
					AND
						['RM Transaction Open'].DOCNUMBR = ['RM Key Master'].DOCNUMBR
					AND
						FORMAT(['RM Key Master'].DOCDATE, 'yyyyMM') = FORMAT(@DATE, 'yyyyMM')
			WHERE
				['RM Key Master'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
		)
	,0) AS 'Current'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -1, @DATE), 'yyyyMM')
		)
	,0) AS '-1'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -2, @DATE), 'yyyyMM')
		)
	,0) AS '-2'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -3, @DATE), 'yyyyMM')
		)
	,0) AS '-3'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -4, @DATE), 'yyyyMM')
		)
	,0) AS '-4'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -5, @DATE), 'yyyyMM')
		)
	,0) AS '-5'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') <= FORMAT(DATEADD(m, -6, @DATE), 'yyyyMM')
		)
	,0) AS 'Prior'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
		)
	,0) AS 'Total'
FROM
	RM00101 AS ['RM Debtor Master'] WITH (NOLOCK) --RM Customer MSTR (RM00101)
GO