Day: January 4, 2023

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 items at each location and the different quantities on each quantity type (such as on hold, allocation and on different types of open transactions).

/*
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 Quantity Master'].ITEMNMBR
	,['Item Quantity Master'].LOCNCODE
	,SUM(['Item Quantity Master'].QTYONHND - ['Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0)) AS 'QTYAVAIL'
	,SUM(['Item Quantity Master'].QTYONHND) AS QTYONHND
	,SUM(['Item Quantity Master'].ATYALLOC)  AS 'QTYALLOC'
	,SUM(['Sales Transaction Amounts Work'].ORDQTYALLOC) AS 'OPENORDERS'
	,SUM(['Item Quantity Master'].QTYBKORD) AS 'OPENBACKORDERS'
	,SUM(['Sales Transaction Amounts Work'].RTNQTYALLOC) AS 'OPENRETURNS'
	,SUM(['Sales Transaction Amounts Work'].INVQTYALLOC) AS 'OPENINVOICES'
	,SUM(['Sales Transaction Amounts Work'].QTYONPO) AS 'QTYONORD'
	,SUM(['Purchase Order Line'].QTYONPO) AS QTYONPO
	,SUM(['Item Quantity Master'].QTYDMGED) AS QTYDAMAGED
	,['Item Quantity Master'].RCRDTYPE AS RCRDTYPE
FROM
	IV00102 AS ['Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM(CASE WHEN SOPTYPE NOT IN (1,4,5) THEN (QTYREMAI - ATYALLOC) * QTYBSUOM ELSE 0 END) AS QTYALLOC
			,SUM(CASE WHEN SOPTYPE = 2 THEN (QTYREMAI - ATYALLOC) * QTYBSUOM ELSE 0 END) AS ORDQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 4 THEN (ATYALLOC) * QTYBSUOM ELSE 0 END) AS RTNQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 3 THEN (ATYALLOC) * QTYBSUOM ELSE 0 END) AS INVQTYALLOC
			,SUM(CASE WHEN SOPTYPE = 5 THEN QTYONPO ELSE 0 END) AS QTYONPO
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		GROUP BY
			ITEMNMBR
			,LOCNCODE
	) 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
			(['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) - ISNULL(['Purchasing Receipt Line Quantities'].QTYSHPPD, 0) AS QTYONPO
			,['Purchase Order Line'].ITEMNMBR
			,['Purchase Order Line'].LOCNCODE
		FROM
			POP10110 AS ['Purchase Order Line'] --Purchase Order Line (POP10110)
		LEFT JOIN
			POP10500 AS ['Purchasing Receipt Line Quantities'] --Purchasing Receipt Line Quantities (POP10500)
				ON
					['Purchasing Receipt Line Quantities'].PONUMBER = ['Purchase Order Line'].PONUMBER
				AND
					['Purchasing Receipt Line Quantities'].POLNENUM = ['Purchase Order Line'].ORD
	) AS ['Purchase Order Line']
		ON
			['Purchase Order Line'].ITEMNMBR = ['Item Quantity Master'].ITEMNMBR
		AND
			['Purchase Order Line'].LOCNCODE = ['Item Quantity Master'].LOCNCODE
WHERE
	['Item Quantity Master'].RCRDTYPE = 2
GROUP BY
	['Item Quantity Master'].ITEMNMBR
	,['Item Quantity Master'].LOCNCODE
	,['Item Quantity Master'].RCRDTYPE