Day: December 23, 2022

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 work status sales transactions including the different quantity types.

/*
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 @ITEMNMBR VARCHAR(30) = '100XLG'

SELECT
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE
	,SUM([Item Quantity Master'].QTYONHND - [Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0) - ISNULL(['Sales Transaction Amounts Work Backorder'].QTYALLOC,0)) AS 'QTYAVAIL'
	,SUM([Item Quantity Master'].QTYONHND) AS QTYONHND
	,SUM([Item Quantity Master'].ATYALLOC)  AS 'QTYALLOC'
	,SUM(['Sales Transaction Amounts Work'].QTYALLOC) AS 'OPENORDERS'
	,SUM([Item Quantity Master'].QTYBKORD) AS 'OPENBACKORDERS'
	,SUM(['Sales Transaction Amounts Work Backorder'].QTYONPO) AS 'QTYONORD'
	,2 AS RCRDTYPE
FROM
	IV00102 AS [Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = 2
		GROUP BY
			ITEMNMBR
			,LOCNCODE
			,SOPTYPE
	) AS ['Sales Transaction Amounts Work']
		ON
			['Sales Transaction Amounts Work'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work'].LOCNCODE = [Item Quantity Master'].LOCNCODE
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
			,SUM(QTYONPO) AS QTYONPO
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = 5
		GROUP BY
			ITEMNMBR
			,LOCNCODE
			,SOPTYPE
	) AS ['Sales Transaction Amounts Work Backorder']
		ON
			['Sales Transaction Amounts Work Backorder'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work Backorder'].LOCNCODE = [Item Quantity Master'].LOCNCODE
WHERE
	[Item Quantity Master'].RCRDTYPE = 2
AND
	[Item Quantity Master'].ITEMNMBR = @ITEMNMBR
GROUP BY
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE