Skip to Content
0

Stock report from different warehouse

Feb 10, 2017 at 05:49 AM

297

avatar image

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).

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Nagarajan K Feb 10, 2017 at 07:07 AM
0

Hi,

Have you tried Inventory Warehouse report under inventory reports?

Regards,

Nagarajan

Show 1 Share
10 |10000 characters needed characters left 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.

0
Agustin Marcos Cividanes Feb 10, 2017 at 08:50 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
prabakaran R Feb 10, 2017 at 09:07 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 10, 2017 at 09:29 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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??????

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

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


Share
10 |10000 characters needed characters left characters exceeded
prabakaran R Feb 16, 2017 at 05:14 AM
0

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??????

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hello,

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

Thanks

Engr. Taseeb Saeed

0
DIEGO LOTHER Feb 19, 2017 at 03:28 PM
0

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


img.png (42.6 kB)
Show 1 Share
10 |10000 characters needed characters left 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%')

0
DIEGO LOTHER Feb 22, 2017 at 11:59 AM
0

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

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Diego Lother,

Thank you so much for your query. It's working. I have small doubt on this, This report showing onhand stock details or All transaction details?

0

Hi Prabakaran,

The query shows the available quantity in stock on the dates that you provide. But if the items have more than one batch numbers in stock the quantities available are showed separeted by serial number/batch number.

If the query solved your problem, don't forget to click on the accept button below my answer.

Hope it helps.

Kind Regards,

Diego Lother

0
Hi DIEGO LOTHER, Yeah i understood, Thank you so much :)
0