Skip to Content
0

SAP B1 query not including all warehouses for ONHAND element of query

May 03, 2017 at 02:08 PM

11

avatar image

Hi guys - could any shed any light on why the below query is not including all stock from all warehouses when it returns the ONHAND (in stock) portion of the 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 - (T3.[OnHand]) - (T3.[OnOrder])))) < 0 THEN 0 ELSE CEILING(((SUM (T1.[Quantity]) *3 - (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] = [%0]
AND T0.[DocDate] BETWEEN [%1] AND [%2]
AND T1.[ItemCode] NOT Like '%%Freight%%'

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

What it appears to be doing, is only including the 1st warehouse it sees in the stock list (see picture) and I can't work out why - any help would be greatly appreciated thanks.

stock-example.jpg (187.5 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers