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
Click to show/hide the Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.