SAP B1 - SQL Query - On Hand in Warehouses

[tinplate]

Hi, I have written a SQL for a function button on the BOM; but I'm not sure how to get the report to show me the stock I have in separate warehouse locations as well as overall stock!

Example screen grab below of what I'm trying to achieve from the SQL:

I can get the report to work without the Q and W warehouse columns, as it looks at the overall stock "OnHand"; but how do make the report work to replicate the image attached??

The SQL below works on the BOM without the W and Q warehouse locations, so it's nearly there but I can't figure out how to finalise it!! Any help would be very much appreciated 🙂

SELECT

T0."Father", T0."Code", T0."ItemName", T0."Currency", T0."Price", T1."OnHand", T1."IsCommited", T1."OnOrder", SUM(T1."OnHand" + T1."OnOrder" - T1."IsCommited"), T0."Currency", SUM(T0."Price" * T1."OnHand")

FROM ITT1 T0

INNER JOIN OITM T1 ON T0."Code" = T1."ItemCode"

INNER JOIN OITW T2 ON T1."ItemCode" = T2."ItemCode"

WHERE

T0."Father" = $[$4.0.0]

GROUP BY

T0."Father", T0."Code", T0."ItemName", T0."Currency", T0."Price", T1."OnHand", T1."IsCommited", T1."OnOrder", T0."Currency"

[tinplate]

Thanks Johan,

I'd like to show 2 of the available warehouses we have allocated; we have 5 warehouses setup on SAP in total but I only need to see stock in 2 of them (W and Q).

Ideally I'd like to see them in the same row, next to each other, rather than on separate lines per warehouse, is this possible? The row would show total in stock (OnHand) then total in Q warehouse, then total in W warehouse along the same row item on the report; is this possible in SQL on SAP?

[Johan_H]

Hi Mark,

OITM.OnHand will show the total stock. You pretty much get that already with the 'Available' column.

The problem is that there can be multiple warehouses determined per item. That means that you need to decide which warehouse's stock you want to show in either column.

So do you want to show all possible warehouses, or just certain ones?

Regards,

Johan

[Johan_H]

Hi tinplate,

With only 2 specific warehouses, it will not be a problem to get all the data into one row per item. For this we best use two subqueries. Something like this:

SELECT T0."Father", T0."Code", T0."ItemName", T0."Currency", T0."Price"
, T1."OnHand" AS "Total stock"
,(select w.OnHand from OITW w where T1."ItemCode" = w."ItemCode" and w."WhsCode" = 'Q') AS "Q stock"
,(select w.OnHand from OITW w where T1."ItemCode" = w."ItemCode" and w."WhsCode" = 'W') AS "W stock"
, T1."IsCommited", T1."OnOrder", SUM(T1."OnHand" + T1."OnOrder" - T1."IsCommited"), T0."Currency", SUM(T0."Price" * T1."OnHand")
FROM ITT1 T0
INNER JOIN OITM T1 ON T0."Code" = T1."ItemCode"
WHERE T0."Father" = $[$4.0.0]
GROUP BY T0."Father", T0."Code", T0."ItemName", T0."Currency", T0."Price", T1."OnHand", T1."IsCommited", T1."OnOrder", T0."Currency"

Regards,

Johan

[tinplate]

Thanks Johan, I will have a play and see if I can get it to work.

I have tried a simple copy and paste of the SQL supplied but I get errors, I can see what it is trying to achieve though so I'll attempt to rewrite it to work for us.

Thanks for your help so far 🙂

[tinplate]

Just to close this off, the SQL is working now as:

SELECT

T0."Father"

, T0."Code"

, T0."ItemName"

, T0."Currency"

, T0."Price"

, (SELECT T2."OnHand" FROM OITW T2 WHERE T2."ItemCode" = T1."ItemCode" AND T2."WhsCode" = 'W') AS "W Stock"

, (SELECT T2."OnHand" FROM OITW T2 WHERE T2."ItemCode" = T1."ItemCode" AND T2."WhsCode" = 'Q') AS "Q Stock"

, T1."OnHand"

, T1."OnOrder"

, T1."IsCommited"

, SUM(T2."OnHand" + T2."OnOrder" - T2."IsCommited")

FROM ITT1 T0

INNER JOIN OITM T1 ON T0."Code" = T1."ItemCode"

INNER JOIN OITW T2 ON T1."ItemCode" = T2."ItemCode"

WHERE

T0."Father" = $[$4.0.0]

GROUP BY

T0."Father"

, T0."Code"

, T0."ItemName"

, T0."Currency"

, T0."Price"

, T1."ItemCode"

, T1."OnHand"

, T1."OnOrder"

, T1."IsCommited"

ORDER BY T0."Code"