cancel
Showing results for 
Search instead for 
Did you mean: 

SAP B1 from different Warehouse Stock

0 Kudos

Hello,

I have different Warehouse. I want to find T0.[OnHand] >=2 for Warehouse 01 and T0.[OnHand] = 0 for Warehouse 02. I want to see only this Products. I tried but it is not working. How can I do that?

Thank you.

SELECT T1.[WhsCode], T0.[ItemCode], T0.[OnHand], T0.[ItemName],  , T1.[U_appBinLocation] as Lagerplatz
FROM OITM T0  
INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] 
WHERE (T0.[OnHand] >= 2 AND T1.[WhsCode] = '01') AND (T0.[OnHand] = 0 AND T1.[WhsCode] = '02')  AND T0.[frozenFor] = 'N'



Accepted Solutions (1)

Accepted Solutions (1)

zal_parchem2
Active Contributor
0 Kudos

Greeting Onurcan...I gave you various ways you can test this out for your own usage. Always great to have alternatives!!! Just remove or comment out pieces of the SQL depending upon if you want the Available to show or the OnHand to show - or maybe both. Also please note under the WHERE area - I also gave you the ability to determine how you want the results to be restricted by OnHand or by Availability. I had to use 100K in the TEST Company I have so I could make you a screen print, but changed it back to your value of "2" for Warehouse 01.

Please let me know if this works..

Here are the results with 100K using the Availability Criteria in the WHERE Area :

Here are the results with 100K using the OnHand Criteria in the WHERE Area :

And here is the SQL used. Please modify as you wish:

--NOTE:  I used 100K in Available to draft SQL in Test Company for SQL Results and Scrn Print
--Onurcan needs the OnHand or Available to be 2 for WH 1

SELECT DISTINCT 
T1.WhsCode, 
T0.ItemCode, 
--T0.ItemName,
T1.OnHand AS 'On Hand in WH1',
T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder] AS 'AVAILABLE in WH 01',
T2.OnHand AS 'On Hand in WH2',
T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder] AS 'AVAILABLE in WH 02'
--,T1.U_AppBinLocation AS 'Lagerplatz'

FROM OITM T0  

INNER JOIN OITW T1 
ON T0.ItemCode = T1.ItemCode 
AND T1.WhsCode = '01'

INNER JOIN OITW T2 
ON T0.ItemCode = T2.ItemCode 
AND T2.WhsCode = '02'

WHERE 
T0.frozenFor = 'N'

--OnHand Criteria
AND (T1.OnHand >= 2 AND T1.WhsCode = '01')
AND (T2.OnHand = 0 AND T2.WhsCode = '02')
AND (T1.OnHand > T2.OnHand)

--Availability Criteria
--AND (T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder] >= 2 AND T1.WhsCode = '01')
--AND (T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder] = 0 AND T2.WhsCode = '02')
--AND (T1.[OnHand] - T1.[IsCommited] + T1.[OnOrder]) > (T2.[OnHand] - T2.[IsCommited] + T2.[OnOrder])

ORDER BY
T0.ItemCode,
T1.WhsCode
zal_parchem2
Active Contributor
0 Kudos

PS - make sure you order Gordon Du's book - will be a big help.

0 Kudos

Hello Zal,

Thank you for your big help and for your time.

(PS - make sure you order Gordon Du's book - will be a big help.) I will order today.

zal_parchem2
Active Contributor
0 Kudos

More than welcome Onurcan...Regards, Zal

Answers (4)

Answers (4)

zal_parchem2
Active Contributor
0 Kudos

Great Onurcan - now we have it!!! Okay - completely different then - thanks for the screen prints. I know what to do now...am in a meeting and will get back shortly...

Regards, Zal

0 Kudos

(PS - Do you know about the book by Gordon Du titled "Mastering SQL Queries for SAP Business One"?) I didn't know so far.

I have to buy one.

I tried with 'OR' but i have different results

zal_parchem2
Active Contributor
0 Kudos

Also, dann auf Deutsch:

Verfugbar = Available

Auf Lager = On Hand

Und noch etwas - Willst Du den Wert des Produkts (was Du jetzt hast - 7 - OITM T0) oder den Wert des Lagers ( 3 fur 01 and 4 fur 02 - OITW T1)??? Willst Du Verfugbar oder Auf Lager???

Now SQL terms. There are three different sceanrios:

Show me all the products which have a value greater than 2 in warehouse 01 AND the product has a value of zero in warehouse 2.

Show me all the products which have a value greater than 2 in warehouse 01 OR the product has a value of zero in warehouse 2.

Maybe you mean show me all products which have a value greater than 2 in warehouse 01 on one line and also show me seperately all products which have a value equal to 0 in warehouse 02 on a seperate line. So you could have only one line or several lines on each product.

Which one is the correct one above??? Or do you have a different requirement???

0 Kudos

An Example:

I have a Product like this one for Warehouse 01 available 2 and for Warehouse 02 available 4. I don't want to see this product because I need only if a product in Warehouse 01 more than 2 AND in Warehouse 02 empty(zero)

zal_parchem2
Active Contributor
0 Kudos

Hello Onurcan - doing great - heading to Walldorf tommorow!!! Hope to hear things are going great for you.

Then change the AND to OR. Like the code below.

Regards, Zal

PS - Do you know about the book by Gordon Du titled "Mastering SQL Queries for SAP Business One"?

AND ((T0.OnHand >= 2 AND T1.WhsCode = '01') OR (T0.OnHand = 0 AND T1.WhsCode = '02'))
zal_parchem2
Active Contributor
0 Kudos

Onurcran - Quick Question - are you looking for the quantity of the Item (OITM T0) or quantity of the Item's Warehouse (OITW T1)???

zal_parchem2
Active Contributor
0 Kudos

Hello Onucran...just a small change in the WHERE area in which you make the requirement for 2 OnHand and 0 OnHand one individual requirement of the SQL. Like this with parenthesis:

((T0.OnHand >= 2 AND T1.WhsCode = '01') AND (T0.OnHand = 0 AND T1.WhsCode = '02'))

Go ahead and run this code and let us know if it works. Best Regards, Zal

PS - if it does not work, please attach a screen print - it was great help when you have done that in the past.

SELECT DISTINCT 
T1.WhsCode, 
T0.ItemCode, 
T0.OnHand, 
T0.ItemName,
T1.U_AppBinLocation AS 'Lagerplatz'

FROM OITM T0  
INNER JOIN OITW T1 
ON T0.ItemCode = T1.ItemCode 

WHERE 
T0.[frozenFor] = 'N'
AND ((T0.OnHand >= 2 AND T1.WhsCode = '01') AND (T0.OnHand = 0 AND T1.WhsCode = '02'))

ORDER BY
T0.ItemCode,
T1.WhsCode
0 Kudos

Hello Zal. How are you? Thank you for your answer but I have a massage 'No data found'. Not any Error.