Skip to Content

SAP B1 Warehouse with Lagerort Query

Hello,

I have a Problem. If Lagerplatz (T3.[U_appBinLocation]) is null i want to take Lagerplatz from 01 Warehouse. I couldn't find it. Can you help me?

Thank you.

SELECT T0.[TaxDate], T0.[DocNum], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[U_appBinLocation], T3.[OnHand], T1.[WhsCode]
FROM [dbo].[ORDR]  T0 
INNER JOIN [dbo].[RDR1]  T1 ON T0.[DocEntry] = T1.[DocEntry] 
INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode] 
WHERE T0.[TaxDate] >= [%0] AND T0.[TaxDate] <= [%1] AND T3.WhsCode = '02' AND T3.WhsCode NOT LIKE '%%RMA%%' AND T1.WhsCode NOT LIKE '%%01%%' AND T0.[GroupNum] = 4 
GROUP BY T0.[TaxDate], T0.[DocNum], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[U_appBinLocation], T3.[OnHand], T1.[WhsCode]
example.png (92.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Best Answer
    Jan 07 at 05:29 AM

    Dear Onurcan,

    You mean to say if the Largerplatz for the item is empty in whse 2 then it should take from whse 1 right ?

    if it is so then try this query :

    SELECT T0."TaxDate", T0."DocNum", T0."NumAtCard", T1."ItemCode", T1."Dscription", T1."Quantity",

    CASE WHEN T3."U_appBinLocation" IS NULL then (SELECT A."U_appBinLocation" FROM OITW A WHERE A."WhsCode" = 01 AND A."ItemCode" = T1."ItemCode") ELSE T3."U_appBinLocation" END as Lagerplatz, T3."OnHand", T1."WhsCode"

    FROM ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"

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

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

    WHERE T0."TaxDate" >=[%0] AND T0."TaxDate" <=[%1] AND T3."WhsCode" = 02 AND T0."GroupNum" = 4

    Regrads

    Dinesh

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06 at 04:40 AM

    Dear Onurcan,

    Try this

    SELECT T0.[TaxDate], T0.[DocNum], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[U_appBinLocation], T3.[OnHand], T1.[WhsCode]

    FROM [dbo].[ORDR] T0 INNER JOIN [dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]

    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

    INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]

    WHERE T0.[TaxDate] >= [%0] AND T0.[TaxDate] <= [%1] AND T3.WhsCode = '01' AND T3.WhsCode NOT LIKE '%%RMA%%' AND T1.WhsCode NOT LIKE '%%02%%' AND T0.[GroupNum] = 4 AND T3.[U_appBinLocation] is null

    GROUP BY T0.[TaxDate], T0.[DocNum], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[U_appBinLocation], T3.[OnHand], T1.[WhsCode]

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06 at 11:53 AM

    Dear Onurcan,

    I Execute the query in my system it was working, Check if there is data available or not. Or change the Warehouse or date selection.

    Thanks

    Dinesh

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 06 at 12:33 PM

    SELECT T0.[TaxDate], T0.[DocNum], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[U_appBinLocation], T3.[OnHand], T1.[WhsCode]

    FROM [dbo].[ORDR] T0 INNER JOIN [dbo].[RDR1] T1 ON T0.[DocEntry] = T1.[DocEntry]

    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]

    INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode]

    WHERE T0.[TaxDate] >= [%0] AND T0.[TaxDate] <= [%1] AND T3.WhsCode = '01' AND T3.WhsCode NOT LIKE '%%RMA%%' AND T1.WhsCode NOT LIKE '%%02%%' AND T0.[GroupNum] = 4 AND T3.[U_appBinLocation] IS NOT NULL

    GROUP BY T0.[TaxDate], T0.[DocNum], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.[U_appBinLocation], T3.[OnHand], T1.[WhsCode]

    Add comment
    10|10000 characters needed characters exceeded

    • Thank you again for your answer

      I think, i couldn't explain good my Problem. I want to see these informations if I write T3.[U_appBinLocation] IS NOT NULL, I can't see any more if Lagerplatz (T3.[U_appBinLocation]) is null i want to take Lagerplatz from 01 Warehouse.

      bild2.png (310.3 kB)