I recently had cause to knock together a fairly simple SQL script to determine the quantity to order based on Sales transactions in the system taking into account the Order Point Quantity and Quantity To Order for the Item/Site combination in the Inventory module:
SELECT SOP10200.LOCNCODE AS 'Site' ,SOP10200.ITEMNMBR AS 'Item Number' ,SUM(SOP10200.QUANTITY) AS 'Quantity Required' ,IV00102.ORDRUPTOLVL - SUM(SOP10200.QTYTORDR) AS 'Quantity To Order' FROM SOP10200 (NOLOCK) LEFT OUTER JOIN IV00102 (NOLOCK) ON IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND IV00102.LOCNCODE = SOP10200.LOCNCODE WHERE SOP10200.QTYTORDR <= ORDRPNTQTY AND SOP10200.QTYTORDR <> 0 GROUP BY SOP10200.ITEMNMBR, SOP10200.LOCNCODE, IV00102.ORDRUPTOLVL
Can ISC Software help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.
3 thoughts on “SQL Query To Determine Quantity To Order”