Skip to Content

SAP B1 from different Warehouse Stock

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'



Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 09 at 10:11 PM

    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
    

    available.png (80.8 kB)
    onhand.png (73.6 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 09 at 01:52 PM

    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
    
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 09 at 02:31 PM

    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'))
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 09 at 03:10 PM

    (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


    example.png (77.1 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • Jan 09 at 06:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded