SQL Query To Determine Quantity To Order

June 13th, 2014 by

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

Source: azurecurve

Leave a Reply