Skip to Content

Stock report from different warehouse

Hi All,

I have three warehouse like A,B & C. Warehouse B get the material from A & C by inventory transfer. After that i'll use the B materials for delivery or Goods issue. Now i need to take the report of Warehouse B current stock with material received From Warehouse(A or C).

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Feb 10, 2017 at 07:07 AM

    Hi,

    Have you tried Inventory Warehouse report under inventory reports?

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Yes i have tried, but it's not met my requirement. This report showing cumulative QTY. But i want to seperate WH B stock received from A alone or B stock received from C. This stock details must be the current available stock in WH B.

      Ex:

      Item Code serial No From WH

      x xxxx A

      y yyyy A

      z zzzz A

      This report should show currently available stock in B. It's shouldn't show all the transaction A to B.

  • Feb 10, 2017 at 08:50 AM

    Hi

    try run this query:

    select T1.itemcode,sum(T1.quantity)

    from OWTR T0 inner join WTR1 T1 on T0.docentry = T1.docentry

    where T0.docdate >='20160101'and T0.docdate <='20160131'

    and T1.fromwhscod ='C' and T1.whscode ='B'

    groupby T1.itemcode

    orderby T1.itemcode

    Change the date and the fiel T1.fromwhscod as you need.

    Kind regards

    Agustín

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 10, 2017 at 09:07 AM

    avatar imageHi Agustin Marcos Cividanes ,

    I am using this query ,

    SELECT T0.[DocNum], T0.[DocDate], T0.[Filler]'From Warehouse', T0.[ToWhsCode], T1.[LineNum], T1.[ItemCode], T1.[Dscription],T4.[DistNumber], T1.[Quantity] FROM OWTR T0 INNER JOIN WTR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left outer join SRI1 I1 on T1.ItemCode=I1.ItemCode and (T1.DocEntry=I1.BaseEntry and T1.ObjType=I1.BaseType) left outer join OSRN T4 on T4.ItemCode=I1.ItemCode and I1.SysSerial=T4.SysNumber WHERE T0.[Filler]='CS01' and T0.[ToWhsCode] ='CS02'

    it's showing all the transaction, But i want only currently available stock list from this Report.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 10, 2017 at 09:29 AM

    How did you get cumulative qty in Inventory Warehouse report? Could you share your screen here?

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Warehouse B getting materials from warehouse A & C. Now i want to take the B stock from A or B stock from C.

      How can we achieve this in warehouse report??????

  • avatar image
    Former Member
    Feb 11, 2017 at 01:55 PM

    Hi,

    Under Inventory in warehouse report you can view the report either by location or by warehouse and display style is normal and detailed.

    Please go by warehouse and display in normal. You will get the desired result.

    stock-in-warehouse.png

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 16, 2017 at 05:14 AM

    Hi Pradnya S,

    Please go through the below Question. I hope you will understand my requirement....

    Warehouse B getting materials from warehouse A & C. Now i want to take the B stock material received from A or B stock material received from C with serial number.

    How can we achieve this in warehouse report??????

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello,

      Please check with Stock Audit Report , it will give you each items stock warehouse wise.

      Thanks

      Engr. Taseeb Saeed

  • Feb 19, 2017 at 03:28 PM

    Hi Prabakaran,

    Your requirement is a bit complex, and I believe is not possible to do it just with a query or using standard reports.

    Keep in mind the following situtation:

    Your warehouse B receive material 'XX' from A.

    Your warehouse B receive material 'XX' from C.

    You input a delivery of material 'XX'.

    In this situation, if your material is handled by batch/serial may can be simple to know if the remaining item in warehouse B is from A or C, but if this item does not have control, is complex to determine from where is the remaining material.

    I thought in this approach to meet your requirement, in this approach I assumes that the materials in warehouse B follow the FIFO (First in, first out) system:

    1. Create a table in SAP Business One for you to control the references in warehouse B, this table should be of type no object, because we will handle directly from SQL:

    2. Create the following procedure in your database (This procedure will handle the input/output in your warehouse B and keep data in your temp table). Look in this procedure and put your B warehouse code in the correct place:

    CREATE PROCEDURE [dbo].[spcUpdateWareHouseReferences]
    			@Item NVARCHAR(30)
    AS
    
    
    BEGIN 
    	--############### Part 1 - Update an temp table ###################################
    	-- The logic below will keep a temp table, where we will handle all input/output stock by FIFO.
    
    
    	DECLARE @TransNum INT, @Ref2 NVARCHAR(30), @TransType INT, @DocDate DATETIME, 
    	@ItemCode NVARCHAR(30), @Dscription NVARCHAR(100), @Warehouse NVARCHAR(30), 
    	@InQty NUMERIC (18,10), @OutQty NUMERIC (18,10), @SerialNum NVARCHAR(36), @SerialId INT
    
    
    	DECLARE _cursorWarehouse CURSOR FOR
    	SELECT 
    		T0.TransNum,
    		T0.Ref2,
    		T0.TransType,
    		T0.DocDate, 
    		T0.ItemCode, 
    		T0.Dscription,
    		T0.Warehouse,
    		CASE WHEN T0.InQty > 0 THEN
    			CASE WHEN T3.Quantity IS NOT NULL THEN T3.Quantity ELSE T0.InQty END
    		ELSE
    			0
    		END AS InQty,
    		CASE WHEN T0.OutQty > 0 THEN
    			CASE WHEN T3.Quantity IS NOT NULL THEN T3.Quantity * (-1) ELSE T0.OutQty END
    		ELSE
    			0
    		END AS OutQty,
    		ISNULL(T4.DistNumber, T5.DistNumber) AS SerialNum, 
    		ISNULL(T4.SysNumber, T5.SysNumber) AS SerialId
    	FROM 
    		B1_OinmWithBinTransfer T0
    		INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
    		LEFT JOIN OITL T2 ON T2.DocType = T0.TransType AND T2.DocEntry = T0.CreatedBy AND T2.DocLine= T0.DocLineNum
    			AND T2.LocCode = T0.Warehouse
    		LEFT JOIN ITL1 T3 ON T2.LogEntry = T3.LogEntry
    		LEFT JOIN OBTN T4 ON T4.ItemCode = T3.ItemCode AND T4.SysNumber = T3.SysNumber AND T1.manbtchnum = 'Y'
    		LEFT JOIN OSRN T5 ON T5.ItemCode = T3.ItemCode AND T5.SysNumber = T3.SysNumber AND T1.mansernum = 'Y'
    	WHERE
    		T0.ItemCode = @Item
    		AND (T0.InQty > 0 OR T0.OutQty > 0)
    		AND T0.Warehouse IN ('put the id of your B warehouse here')
    		AND T0.TransNum > ISNULL((SELECT MAX(U_LastRegRead) FROM [@TMP_WHS_MOVEMENT] WHERE U_ItemCode = @Item), 0)
    	ORDER BY TransNum 
    	OPEN _cursorWarehouse
    
    
    	FETCH NEXT FROM _cursorWarehouse INTO @TransNum, @Ref2, @TransType, @DocDate, @ItemCode, @Dscription, @Warehouse, @InQty, @OutQty, @SerialNum, @SerialId
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    		IF (@InQty > 0) 
    		BEGIN
    			-- We will add a new entry in our temp table for our item
    			INSERT INTO [@TMP_WHS_MOVEMENT] 
    				   (Code, Name, U_Ref2, U_TransType, U_DocDate, U_ItemCode, U_Dscription, U_Warehouse, U_InQty, U_DistNumber, U_SysNumber, U_LastRegRead)
    			Values (CAST(@TransNum AS NVARCHAR(30)), CAST(@TransNum AS NVARCHAR(30)), @Ref2, @TransType, 
    					@DocDate, @ItemCode, @Dscription, @Warehouse, @InQty, @SerialNum, @SerialId, @TransNum)
    		END
    
    
    		IF (@OutQty > 0)
    		BEGIN
    			-- we will update our temp table, to remove the out values
    			DECLARE @QtyRemain INT
    			SET @QtyRemain = @OutQty
    			DECLARE @TransNum1 INT, @Ref21 INT, @TransType1 INT, @InQty1 NUMERIC (18,10),
    					@SerialNum1 NVARCHAR(36), @SerialId1 INT
    
    
    			DECLARE _cursorTemp CURSOR FOR
    			SELECT
    				Code,
    				U_TransType,
    				U_InQty,
    				U_DistNumber,
    				U_SysNumber
    			FROM
    				[@TMP_WHS_MOVEMENT] 
    			WHERE
    				U_Warehouse = @Warehouse
    				AND U_ItemCode = @ItemCode
    				AND U_InQty > 0
    				AND ISNULL(U_DistNumber, '') = ISNULL(@SerialNum, '')
    				AND ISNULL(U_SysNumber, 0) = ISNULL(@SerialId, 0)
    			OPEN _cursorTemp
    			FETCH NEXT FROM _cursorTemp INTO @TransNum1, @TransType1, @InQty1, @SerialNum1, @SerialId1
    			WHILE @@FETCH_STATUS = 0
    			BEGIN
    				UPDATE [@TMP_WHS_MOVEMENT] SET U_InQty = CASE WHEN @InQty1 - @QtyRemain < 0 THEN 0 ELSE @InQty1 - @QtyRemain END 
    				WHERE CAST(Code AS INT) = @TransNum1 AND ISNULL(U_DistNumber, '') = ISNULL(@SerialNum1, '') 
    					  AND ISNULL(U_SysNumber, 0) = ISNULL(@SerialId1, 0) AND U_Warehouse = @Warehouse
    			
    				SET @QtyRemain = @QtyRemain - @InQty1
    				IF @QtyRemain <= 0 
    				BEGIN
    					BREAK;
    				END
    				FETCH NEXT FROM _cursorTemp INTO @TransNum1, @TransType1, @InQty1, @SerialNum1, @SerialId1
    			END;
    			CLOSE _cursorTemp;
    			DEALLOCATE _cursorTemp;
    		END
    		UPDATE [@TMP_WHS_MOVEMENT] SET U_LastRegRead = @TransNum WHERE U_ItemCode = @ItemCode
    		FETCH NEXT FROM _cursorWarehouse INTO @TransNum, @Ref2, @TransType, @DocDate, @ItemCode, @Dscription, @Warehouse, @InQty, @OutQty, @SerialNum, @SerialId
    	END;
    	CLOSE _cursorWarehouse;
    	DEALLOCATE _cursorWarehouse;
    
    
    	--############### Part 1 end ######################################################################
    END
    

    3. All time you want to see your report use this sql or something like this:

    DECLARE	@ItemCode NVARCHAR(30)
    DECLARE @MyWareHouse AS NVARCHAR(30)
    SET @MyWareHouse = 'warehouse code from your B warehouse'
    
    DECLARE _cursor CURSOR FOR 
    --Look for items that have quantities on B warehouse
    SELECT 
    	ItemCode
    FROM
    	OITW
    WHERE
    	OnHand > 0
    	AND WhsCode = @MyWareHouse
    OPEN _cursor
    
    FETCH NEXT FROM _cursor INTO @ItemCode
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	--Call the procedure that have quantities in warehouse B to update your references.
    	EXEC dbo.spcUpdateWareHouseReferences @ItemCode;
    	
    	FETCH NEXT FROM _cursor INTO @ItemCode
    END;
    CLOSE _cursor;
    DEALLOCATE _cursor;
    
    --Now you are able to see from where is your remaining quantities in your warehouse B
    SELECT
    	U_ItemCode,
    	U_Warehouse,
    	U_Ref2 AS WhsFrom,
    	SUM(U_InQty) AS OnHand,
    	U_DistNumber,
    	U_SysNumber
    FROM 
    	[@TMP_WHS_MOVEMENT]
    WHERE
    	U_Warehouse = @MyWareHouse 
    	AND U_InQty > 0 
    	AND U_Ref2 IN ('warehouse code from your A warehouse', 'warehouse code from your C warehouse')
    GROUP BY
    	U_ItemCode, U_Warehouse, U_DistNumber, U_SysNumber, U_Ref2
    

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded

    • Hi DIEGO LOTHER,

      Thank you so much for spending your valuable time to reply my question. Yes i understood that, this is little complicated. But still i'm working on it.Here I want to mention something, Warehouse B received the material only by inventory transfer and all items are serial number items. So i have write the below query to take the report, but it's showing repeat data. Can you help me to solve this????

      SELECT T0.[Filler],T0.ToWhsCode, T6.[Location], T0.[DocDate], T0.[DocNum],
      T0.[DocType], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription],
      T1.[Quantity], T1.[Price], T1.[LineTotal],T8.OnHand, T9.SerialNum, T1.[WhsCode], T4.[Location],
      T2.[Vat], T2.[TransCat], T2.[FormNo], T0.[DocTotal]
      FROM OWTR T0 LEFT OUTER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry
      LEFT OUTER JOIN WTR12 T2 ON T0.DocEntry = T2.DocEntry
      LEFT OUTER JOIN OWHS T3 ON T3.WhsCode = T1.WhsCode
      LEFT OUTER JOIN OLCT T4 ON T4.Code = T3.Location

      LEFT OUTER JOIN OWHS T5 ON T5.WhsCode = T0.Filler
      LEFT OUTER JOIN OLCT T6 ON T6.Code = T5.Location
      Inner join OITM T8 on T8.itemcode = T1.itemcode
      Inner Join OINM T9 on T9.ItemCode = T8.ItemCode

      WHERE T0.[DocDate] >='[%1]' AND T0.[DocDate] <='[%2]'and T8.OnHand >= '1'

      and (T0.Filler Like '%PSG%' or T0.ToWhsCode like '%PSG%')

  • Feb 22, 2017 at 11:59 AM

    Hi prabakran,

    Following the scenario that you described, I believe that this query should met your requirement.

    DECLARE @DtIni AS DATETIME
    DECLARE @DtEnd AS DATETIME
    
    
    /* SELECT TOP 1 1 FROM OWTR T0 WHERE */ SET @DtIni /* T0.DocDate >= */ = '[%0]' /* AND */ SET @DtEnd /* T0.DocDate <= */ = '[%1]'
    
    
    SELECT
    	*
    FROM
    	(SELECT
    		T0.ItemCode,
    		T0.Dscription,
    		T0.Warehouse,
    		T0.Ref2,
    		SUM(T0.Qty) AS Qty,
    		T0.SerialNum,
    		T0.SerialId
    	FROM
    		(SELECT 
    			T0.TransNum,
    			CASE WHEN T0.TransType = 67 AND T0.InQty > 0
    			THEN T0.Ref2 
    			ELSE
    				ISNULL((SELECT TOP 1
    					FromWhsCod 
    				FROM 
    					OWTR T00
    					INNER JOIN WTR1 T01 ON T00.DocEntry = T01.DocEntry
    					INNER JOIN OITM T02 ON T01.ItemCode = T02.ItemCode
    					LEFT JOIN OITL T03 ON T03.DocType = T00.ObjType AND T03.DocEntry = T00.DocEntry AND T03.DocLine= T01.LineNum
    						AND T03.LocCode = T01.WhsCode AND T03.StockEff = 1
    					LEFT JOIN ITL1 T04 ON T03.LogEntry = T04.LogEntry
    					LEFT JOIN OBTN T05 ON T05.ItemCode = T04.ItemCode AND T05.SysNumber = T04.SysNumber AND T02.manbtchnum = 'Y'
    					LEFT JOIN OSRN T06 ON T06.ItemCode = T04.ItemCode AND T06.SysNumber = T04.SysNumber AND T02.mansernum = 'Y'
    				WHERE
    					T01.ItemCode = T0.ItemCode
    					AND ISNULL(T05.SysNumber, T06.SysNumber) = ISNULL(T4.SysNumber, T5.SysNumber)
    					AND T01.WhsCode = T0.Warehouse), '')
    			END AS Ref2,
    			T0.TransType,
    			T0.DocDate, 
    			T0.ItemCode, 
    			T0.Dscription,
    			T0.Warehouse,
    			CASE WHEN T0.InQty > 0 THEN
    				CASE WHEN T3.Quantity IS NOT NULL THEN T3.Quantity ELSE T0.InQty END
    			ELSE
    				CASE WHEN T0.OutQty > 0 THEN
    					CASE WHEN T3.Quantity IS NOT NULL THEN T3.Quantity ELSE T0.OutQty * (-1) END
    				END 
    			END AS Qty,
    			ISNULL(T4.DistNumber, T5.DistNumber) AS SerialNum, 
    			ISNULL(T4.SysNumber, T5.SysNumber) AS SerialId,
    			T1.manbtchnum,
    			T1.mansernum
    		FROM 
    			B1_OinmWithBinTransfer T0
    			INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
    			LEFT JOIN OITL T2 ON T2.DocType = T0.TransType AND T2.DocEntry = T0.CreatedBy AND T2.DocLine= T0.DocLineNum
    				AND T2.LocCode = T0.Warehouse AND StockEff = 1
    			LEFT JOIN ITL1 T3 ON T2.LogEntry = T3.LogEntry
    			LEFT JOIN OBTN T4 ON T4.ItemCode = T3.ItemCode AND T4.SysNumber = T3.SysNumber AND T1.manbtchnum = 'Y'
    			LEFT JOIN OSRN T5 ON T5.ItemCode = T3.ItemCode AND T5.SysNumber = T3.SysNumber AND T1.mansernum = 'Y'
    		WHERE
    			(T1.manbtchnum = 'Y' OR T1.mansernum = 'Y')
    			AND (T0.InQty > 0 OR T0.OutQty > 0)
    			AND T0.Warehouse IN ('set your B warehouse code')
    			AND T0.DocDate BETWEEN @DtIni AND @DtEnd
    			) T0
    	GROUP BY 
    		T0.ItemCode, T0.Dscription, T0.SerialId, T0.SerialNum, T0.Warehouse, T0.Ref2
    		) T0
    WHERE
    	T0.Qty > 0

    Hope it helps.

    Kind Regards,

    Diego Lother

    Add comment
    10|10000 characters needed characters exceeded