Support on a query

[MekhalaP]

Greetings of the day.

Following is the query.

SELECT T0.[DocNum], T0.DocDate,T1.[ShipDate], T0.CardName, T1.[ItemCode], T1.[Dscription], T1.[Quantity], T2.[Code], (T2.[Quantity]*T1.[OpenQty]) 'Requirement',(Select T3.OnHand from OITW T3 where T3.ItemCode=T2.Code and T3.whscode='WH-WIP') 'In Stock',
(Select T3.IsCommited from OITM T3 where T3.ItemCode=T2.Code) 'Committed',
(Select T3.OnOrder from OITM T3 where T3.ItemCode=T2.Code)'PO',' ' as DataFROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner join ITT1 T2 on T2.Father =T1.ItemCode WHERE T1.[OpenQty] >0 and T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

Need to pickup the value from the Default warehouse of the Item Code as it is picking up the total from all warehouse.

Thanks in advance.

[Johan_Hakkesteegt]

Hi,

How about this version where you get the stock from the warehouse that was set on the row level of the order:

SELECT T0.[DocNum], T0.DocDate,T1.[ShipDate], T0.CardName
     , T1.[ItemCode], T1.[Dscription], T1.[Quantity], T2.[Code]
     , (T2.[Quantity]*T1.[OpenQty]) as [Requirement]
     , w.OnHand as [In Stock]
     , i.IsCommited as [Committed]
     , i.OnOrder as [PO]
     ,' ' as [Data]
FROM ORDR T0 
     INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
     inner join ITT1 T2 on T2.Father =T1.ItemCode
     INNER JOIN OITM i ON T1.ItemCode = i.ItemCode
     INNER JOIN OITW w ON T1.ItemCode = w.ItemCode AND T1.WhsCode = w.WhsCode
WHERE T1.[OpenQty] >0 and T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1]

Regards,

Johan

[MekhalaP]

Greetings of the day, Johan,

We did try the query shared by you, but the quantity fetched from the Default warehouse "w.OnHand" is having an error.  Quantity available in the default warehouse is 176 but the query is fetching 100.

Please review.

Regards.