Skip to Content
Sep 19 at 11:41 AM

Batchwise Inventory with Warehouse & Bin


Hi Experts,

I was trying to get inventory by batch with warehouse and Bin code. Still, after getting BIN code the inventory quantity got mismatch from before adding last 2 tables in my query as given below. Pls help.

Select  T1."ItemCode" "PartCode"
		,T3."ItemName" "PartName"
		,T5."ItmsGrpNam" "Item GroupName"
		,Sum(T1."Quantity")as "Remaining Stock"
		,T2."DistNumber" "BATCH NUMBER"
		,T7."Location" "Location"
		,T6."WhsCode" "WareHouse Code"
		,T3."U_EAN" "EAN"
		,T2."U_MRP" "MRP"
		,T2."ExpDate" "ExpiryDate"
		,T2."MnfDate" "Manufacturing Date",T9."BinCode"
,(Case When T2."Status"=0 Then 'Released' When T2."Status"=1 Then 'Not Accessible' 
	  When T2."Status"=2 Then 'Locked' Else '' End) "Status"

From OITL T0 
Inner Join ITL1 T1 on T0."LogEntry"=T1."LogEntry"
Left Join OITM T3 on T1."ItemCode" =T3."ItemCode"
left Join OITB T5 on T3."ItmsGrpCod"=T5."ItmsGrpCod"
Left Join OWHS T6 on T0."LocCode"=T6."WhsCode" 
left Join OLCT T7 on T6."Location"=T7."Code"
Left Join OBTN T2 on T1."ItemCode"=T2."ItemCode" AND T1."MdAbsEntry"=T2."AbsEntry" AND T0."ManagedBy"=T2."ObjType"
Left join OBTL T8 on T1."LogEntry" = T8."ITLEntry" AND T1.MdAbsEntry=T8.SnBMDAbs --inventory mismatch after adding this & after this table
Left join OBIN T9 on T8."BinAbs" = T9."AbsEntry"

Group By  T1."ItemCode",T3."ItemName",T5."ItmsGrpNam",T0."ManagedBy",T2."DistNumber",