cancel
Showing results for 
Search instead for 
Did you mean: 

SAP B1 Warehouse with Lagerort Query

0 Kudos

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]

Accepted Solutions (1)

Accepted Solutions (1)

dinesh-pn
Active Participant
0 Kudos

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

Answers (3)

Answers (3)

dinesh-pn
Active Participant
0 Kudos

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]

0 Kudos

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.

dinesh-pn
Active Participant
0 Kudos

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

0 Kudos

hi

I changed my Warehouse and I have Products but without Lagerplatz

dinesh-pn
Active Participant
0 Kudos

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]

0 Kudos

Hi Dinesh

Thank you for your answer

all for dates it is writing no data found.