Skip to Content

Stock Query only seeing 1 warehouse

hey guys - wondered if anyone can help - the below query works well for what I need BUT it only sees items either in stock or on order from the default warehouse - it does not, for instance, see WH16 which is our contract packing warehouse, so consequently when we order material from USA that will be delivered to the contract pack warehouse, the query doesn't know that volumes of material are in that WH code - any help that anyone can provide will be greatfully received:

SELECT
T2.[ItemCode]
, T2.[ItemName]
, SUM (T1.[Quantity]) AS 'Total Qty Sold'
, T3.[OnHand] AS 'Currently In Stock'
, T3.[OnOrder] AS 'Currently On Order'
, T2.MinLevel
, T2.MaxLevel
, CASE WHEN CEILING(((SUM (T1.[Quantity]) / 3*4 - (T3.[OnHand]) - (T3.[OnOrder])))) < 0 THEN 0 ELSE CEILING(((SUM (T1.[Quantity]) / 3*4 - (T3.[OnHand]) - (T3.[OnOrder])))) END AS 'Recommended to Order'

FROM
ODLN T0
INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode AND T3.WhsCode = T1.WhsCode
WHERE
T2.[CardCode] = 'JES002'
AND T1.[ItemCode] NOT Like '%%Freight%%'
AND (T0.DocDate > getdate()-90)

GROUP BY
T2.[ItemCode],
T2.[ItemName],
T2.MinLevel,
T2.MaxLevel,
T3.[OnOrder],
T3.[OnHand]
ORDER BY
T2.[ItemCode]

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jun 27, 2017 at 02:12 PM

    Hi,

    Try this query,

    SELECT
    T2.[ItemCode]
    , T2.[ItemName]
    , SUM (T1.[Quantity]) AS 'Total Qty Sold'
    , T3.[OnHand] AS 'Currently In Stock'
    , T3.[OnOrder] AS 'Currently On Order'
    , T2.MinLevel
    , T2.MaxLevel
    , CASE WHEN CEILING(((SUM (T1.[Quantity]) / 3*4 - (T3.[OnHand]) - (T3.[OnOrder])))) < 0 THEN 0 ELSE CEILING(((SUM (T1.[Quantity]) / 3*4 - (T3.[OnHand]) - (T3.[OnOrder])))) END AS 'Recommended to Order'

    FROM
    ODLN T0
    INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
    INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
    INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode AND T3.WhsCode = T1.WhsCode
    WHERE
    T2.[CardCode] = 'JES002'
    AND T1.[ItemCode] NOT Like '%%Freight%%'

    GROUP BY
    T2.[ItemCode],
    T2.[ItemName],
    T2.MinLevel,
    T2.MaxLevel,
    T3.[OnOrder],
    T3.[OnHand]
    ORDER BY
    T2.[ItemCode]

    Add comment
    10|10000 characters needed characters exceeded

    • Yes, removed from query to check whether any transactions list in the query for warehouse code WH16.

      Try this query,

      SELECT T2.[ItemCode] , T2.[ItemName] , SUM (T1.[Quantity]) AS 'Total Qty Sold' ,T3.WhsCode as WH , T3.[OnHand] AS 'Currently In Stock' , T3.[OnOrder] AS 'Currently On Order' , T2.MinLevel , T2.MaxLevel , CASE WHEN CEILING(((SUM (T1.[Quantity]) / 3*4 - (T3.[OnHand]) - (T3.[OnOrder])))) < 0 THEN 0 ELSE CEILING(((SUM (T1.[Quantity]) / 3*4 - (T3.[OnHand]) - (T3.[OnOrder])))) END AS 'Recommended to Order'

      FROM

      ODLN T0 INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T3.ItemCode = T2.ItemCode

      WHERE T1.[ItemCode] NOT Like '%%Freight%%' AND (T0.DocDate > getdate()-90)

      GROUP BY T2.[ItemCode], T2.[ItemName], T2.MinLevel, T2.MaxLevel, T3.[OnOrder], T3.[OnHand], T3.WhsCode

      ORDER BY T2.[ItemCode]