SQL Scripts for Microsoft Dynamics GP: Return Items with Incorrect Quantities

This script returns details of items where the quantity on hand does not match the sum of the goods booked in and despatched.

/*
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

	RTRIM(['Inventory Transaction Amounts History'].ITEMNMBR) AS 'Item Number'

	,['Item Master'].ITEMDESC AS 'Item Description'

	,['Inventory Transaction Amounts History'].TRXLOCTN AS 'Location/Site'

	,SUM(['Inventory Transaction Amounts History'].TRXQTY) AS 'Transaction Quantity'

	,['Item Quantity Master'].QTYONHND AS 'Quantity On Hand'

	,['Item Quantity Master'].ATYALLOC AS 'Quantity Allocated'

	,CASE WHEN ['Item Quantity Master'].QTYONHND <> 0 THEN

		['Item Quantity Master'].QTYONHND

	ELSE

		0

	END AS 'Adjustment Quantity'

FROM

	IV30300 AS ['Inventory Transaction Amounts History']
INNER JOIN

	IV00101 AS ['Item Master']
		ON

			['Item Master'].ITEMNMBR = ['Inventory Transaction Amounts History'].ITEMNMBR

INNER JOIN

	IV00102 AS ['Item Quantity Master']
		ON

			['Item Quantity Master'].ITEMNMBR = ['Inventory Transaction Amounts History'].ITEMNMBR

		AND

			['Item Quantity Master'].LOCNCODE = ['Inventory Transaction Amounts History'].TRXLOCTN

GROUP BY

	['Inventory Transaction Amounts History'].ITEMNMBR

	,['Item Master'].ITEMDESC

	,['Inventory Transaction Amounts History'].TRXLOCTN

	,['Item Quantity Master'].QTYONHND

	,['Item Quantity Master'].ATYALLOC

HAVING

	SUM(['Inventory Transaction Amounts History'].TRXQTY) <> ['Item Quantity Master'].QTYONHND

GO

Read original post SQL Scripts for Microsoft Dynamics GP: Return Items with Incorrect Quantities at azurecurve|Ramblings of an IT Professional