Skip to Content

Query - Stock Take with bin locations

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Dec 15, 2016 at 05:22 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 15, 2016 at 12:39 PM

    Hi Roy,

    To get all bin locations you need do this in your inner join

    INNER JOIN OBIN T3 ON T0.[WhsCode] = T3.[WhsCode]

    In your query you force to join just the default bin location of the warehouse.

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 15, 2016 at 02:55 PM

    Hi Diego - cheers for the reply :)
    I have tried that join before but unfortunately, what it does is list all items in the inventory, within the range that's selected....any of the ranges so, for example it lists all products in the WH1-AA-1 to WH1-AA-6 range if that is what I selected, but of course that's not the case.

    Regards

    Roy

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 16, 2016 at 09:53 AM

    That is brilliant - thank you, Diego - genius mate :)

    Regards

    Roy

    Add comment
    10|10000 characters needed characters exceeded