Stock report with batch numbers query

[former_member239716]

Hi guys - I am trying to create a query for a stock report that shows volume in stock (excluding zero stock), associated batch number to in stock items and the bin location.

The below attempt byt me, returns the correct stock level but applies that number to any batch that the item has ever had booked in against it - i cant seem to get it to provide in stock items with its unique batch, only. Also I cant seem to figure out how to get it to show bin locations next to it also - any help would be greatfully received:

SELECT T0.[ItemCode] AS 'Item No.', T0.[itemName] AS 'Item Description', T1.[OnHand] AS 'In Stock', T0.[DistNumber] AS 'Batch Number' FROM  [dbo].[OBTN] T0  INNER  JOIN [dbo].[OITM] T1  ON  T1.[ItemCode] = T0.[ItemCode]

[former_member239716 · Accepted solution]

Thanks for the assistance, Nagarajan - finally managed to sort it - working code below:

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]

[kothandaraman_nagarajan · Accepted solution]

Thanks for your feedback and query!

[kothandaraman_nagarajan]

Hi,

Please check this thread Stock Report with Batch Number | SCN

Thanks

[former_member239716]

Hi Nagarajan - I have seen this thread from my searches and, whilst it is a good report, this is showing an audit trail - I just need to see what is in stock, at this time, only - not a trail of goods in and out - also, the location code on that query only goes as far as to show the warehouse and not the bin location.

[kothandaraman_nagarajan]

Please try this query,

SELECT T0.ItemCode, T2.[ItemName],  T0.WhsCode, T1.BatchNum,T1.Quantity,T0.Onhand

FROM OITW T0

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 T0.[WhsCode] between [%0] and [%1] and T1.Quantity >0

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

[former_member239716]

Hi Nagarajan - this is brilliant - but would there be a way for the bin locations to be displayed also within the report as our warehouses are quite large?

[kothandaraman_nagarajan]

Please try this query,

SELECT

T1.[BinCode] as 'Bin Location', T2.[WhsCode],sum(T0.[OnHandQty]) as OnhandQty, 

T0.[ItemCode],

  T4.Itemname ,

T3.[DistNumber]

FROM OIBQ T0  LEFT JOIN OBIN T1 ON T0.[BinAbs] = T1.[AbsEntry] and T0.[WhsCode] = T1.[WhsCode] LEFT JOIN OBBQ T2 ON T1.[AbsEntry] = T2.[BinAbs] and T0.[ItemCode] = T2.[ItemCode] LEFT JOIN OBTN T3 ON T2.[SnBMDAbs] = T3.[AbsEntry] and T0.[ItemCode] = T3.[ItemCode] left join OITm T4 on T4.itemcode = t0.itemcode

WHERE T0.[WhsCode]  between '[%0]' and '[%1]'

group by T0.[ItemCode], T1.[BinCode], T0.[OnHandQty], T3.[DistNumber] ,T2.[WhsCode],t4.itemname

Having sum(T0.[OnHandQty]) <>0

order by T1.[BinCode]

[former_member239716]

This shows the bin codes great but it has done the same thing as my original problem by showing all of the batches that have been against the item and returning the correct stock in at this time, against all of those - please see picture - in the example the highlighted is the only batch in stock with the correct volume:

[kothandaraman_nagarajan]

Please try to change/modify above queries to get desired result. I need time to check!