Query - Stock Take with bin locations

[former_member239716]

Hi guys - I have written a query below that, for 99% of it works perfectly. BUT! One issue still remains.

The report will only return the default bin location of the products and not where they are actually booked in. Most times, this is not an issue as the default will always be the bin location the products are in however, we have one or two situations where a product is booked into two separate locations but the below query will only show all product booked into its default and not the seperate locations as, for whatever reason I am only pointing to the default bin location.

Any help will, as always be greatfully appreciated:

SELECT T0.ItemCode, T2.[ItemName], T0.WhsCode, T1.BatchNum,T1.[Quantity] as 'Batch Quantity',T0.[Onhand] as 'Total Stock', T3.[BinCode]

FROM OITW T0
INNER JOIN OBIN T3 ON T0.[DftBinAbs] = T3.[AbsEntry]
INNER JOIN OIBT T1 ON T0.WhsCode = T1.WhsCode and T0.ItemCode = T1.ItemCode Inner join OITM T2 on T0.itemcode = T2.itemcode

WHERE T0.OnHand > 0 AND T3.[BinCode] between [%0] and [%1] and T1.Quantity >0

Group by T3.[BinCode], T0.ItemCode, T2.[ItemName], T0.WhsCode, T1.BatchNum, T1.Quantity,T0.Onhand,T1.[ExpDate]

Regards

Roy

[former_member185682 · Accepted solution]

Hi Roy,

If I understood the situation that you mentioned, this happened because you did a inner join with OIBT that is a table that keeps the transactions for the batch numbers.

Try this query:

SELECT 
	T0.ItemCode, 
	T5.ItemName, 
	T0.WhsCode, 
	T4.DistNumber,
	SUM(T3.OnHandQty) as 'Batch Quantity',
	SUM(T2.OnHandQty) as 'Total Stock Bin',
	SUM(T0.OnHand) AS 'Total Stock Warehouse',
	T1.BinCode
FROM 
	OITW T0
	INNER JOIN OBIN T1 ON T0.WhsCode = T1.WhsCode
	INNER JOIN OIBQ T2 ON T2.WhsCode = T0.WhsCode AND T1.AbsEntry = T2.BinAbs AND T0.ItemCode = T2.ItemCode
	INNER JOIN OBBQ T3 ON T3.ItemCode = T0.ItemCode AND T3.BinAbs = T1.AbsEntry AND T3.WhsCode = T2.WhsCode
	INNER JOIN OBTN T4 ON T4.AbsEntry = T3.SnBMDAbs
	INNER JOIN OITM T5 ON T5.ItemCode = T0.ItemCode
WHERE 
	T2.OnHandQty > 0 
	AND T1.BinCode between [%0] and [%1] 
	AND T3.OnHandQty > 0
	AND T2.OnHandQty > 0
GROUP BY 
	T0.ItemCode, T5.ItemName, T0.WhsCode, 
	T0.WhsCode, T1.BinCode, T4.DistNumber
ORDER BY T0.ItemCode, T0.WhsCode, T1.BinCode

I believe that this query will bring what you need.

Hope it helps.

Kind Regards,

Diego Lother