Skip to Content
0

Stock Query only seeing 1 warehouse

Jun 27, 2017 at 02:04 PM

39

avatar image

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]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Nagarajan K Jun 27, 2017 at 02:12 PM
0

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]

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Nagarajan - this hasn't done anything and tbh, all I can see you have done is removed the

AND (T0.DocDate > getdate()-90) from my query - but I need this to report on last 90 days as I don't want to report on everything sold, ever.

Thanks

0

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]

0