SQL Scripts for Microsoft Dynamics GP: SQL View to Return Quantity Available

This script was created for a client to use in SmartList Builder, and other, reports; it returns the quantity available for items on a location basis.

/*
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 VIEW uv_AZRCRV_GetAvailableQuantity AS

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

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)
		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

WHERE

	[Item Quantity Master'].RCRDTYPE = 2

GROUP BY

	[Item Quantity Master'].ITEMNMBR

	,[Item Quantity Master'].LOCNCODE

GO
GRANT SELECT ON uv_AZRCRV_GetAvailableQuantity TO DYNGRP

GO

Read original post SQL Scripts for Microsoft Dynamics GP: SQL View to Return Quantity Available at azurecurve|Ramblings of an IT Professional