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