SQL Scripts for Microsoft Dynamics GP: Get Items Showing Quantities By Type

Microsoft Dynamics GPThis 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

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.

Your Name

Your Email

Suggested Topic

Suggestion Details

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.

Leave a Reply

Your email address will not be published. Required fields are marked *