Inventory tracking

[Former Member]

Hi all.

I would appreciate some help with making a query for the following scenario:

Is it possible to show all items where on hand is larger than 0 and has no inventory transactions for a given period of time.

I'm really looking for all items that are in-active - that only lies still in the wearhouse.

Please tell me if any clarifications is needed.

Thanks and regards.

Runar Wigestrand

[JesperB1 · Accepted solution]

Hi Runar,

Please find attached a suggestion. I am sure it can be written better but I hope it gives you an idea.

Hope it helps,

Jesper

[Former Member · Accepted solution]

Hi Jesper.

The query seems to work just fine, but I was wondering if it is possible to bring in item description, the value of items and the quantity of stock?

I'll paste the query for others to view as well:

SELECT distinct ItemCode
FROM OINM 
WHERE ItemCode not in 
	(SELECT distinct itemcode FROM 
	OINM WHERE DocDate >= '2008-01-01' and  
	DocDate  <= '2008-10-31')

Kind regards, Runar.

Edited by: Runar Wigestrand on Jan 27, 2009 9:10 AM

[former_member204969 · Accepted solution]

It looks like Jesper is not listening . . .

Try this one:

SELECT distinct n.ItemCode,i.ItemName
FROM OINM n inner join OITM i on i.ItemCode=n.ItemCode
WHERE n.ItemCode not in 
	(SELECT distinct itemcode FROM 
	OINM WHERE DocDate >= '2008-01-01' and  
	DocDate  <= '2008-10-31')

[Former Member · Accepted solution]

Try this Query, let you put a date cut off:

/* The Date Input (Hasn't Sold Since dd/mm/yyyy */

DECLARE @TODAY TABLE(RepDate datetime)

DECLARE @DAYLIMIT AS datetime

INSERT INTO @TODAY

SELECT DISTINCT T0.DocDate FROM OINV T0 WHERE T0.DocDate = [%0]

SET @DAYLIMIT = (SELECT MAX(RepDate) FROM @TODAY)

SELECT T0.ItemCode AS 'Item no',

T0.ItemName AS 'Description',

T0.CreateDate AS 'Date created',

T1.WhsCode AS 'Warehouse',

T1.OnHand AS 'On hand',

T1.AvgPrice AS 'Avg. price',

(T1.OnHand * T1.AvgPrice) as 'Extended',

(SELECT MAX(TA.DocDate) FROM OINV TA INNER JOIN INV1 TB ON TA.DocEntry = TB.DocEntry

WHERE TA.DocDate < @DAYLIMIT AND TB.ItemCode = T0.ItemCode) AS 'Last Inv. Date',

(SELECT MAX(TA.RlsDate) FROM OWOR TA INNER JOIN WOR1 TB ON TA.DocEntry = TB.DocEntry

WHERE TA.RlsDate < @DAYLIMIT AND TB.ItemCode = T0.ItemCode) AS 'Last W/O Date'

FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.WhsCode NOT IN ('98 Ret','DropShip','Transit')

AND T1.OnHand > 0

AND T1.IsCommited <= 0

AND T0.ItemCode

NOT IN (SELECT TB.ItemCode FROM OINV TA INNER JOIN INV1 TB ON TA.DocEntry = TB.DocEntry

WHERE TB.ItemCode = T0.ItemCode AND TA.DocDate >= @DAYLIMIT)

AND T0.ItemCode

NOT IN (SELECT TB.ItemCode FROM OWOR TA INNER JOIN WOR1 TB ON TA.DocEntry = TB.DocEntry

WHERE TB.ItemCode = T0.ItemCode AND TA.RlsDate >= @DAYLIMIT)

FOR BROWSE

[Former Member · Accepted solution]

Try this Query, let you put a date cut off:

/* The Date Input (Hasn't Sold Since dd/mm/yyyy */

DECLARE @TODAY TABLE(RepDate datetime)

DECLARE @DAYLIMIT AS datetime

INSERT INTO @TODAY

SELECT DISTINCT T0.DocDate FROM OINV T0 WHERE T0.DocDate = [%0]

SET @DAYLIMIT = (SELECT MAX(RepDate) FROM @TODAY)

SELECT T0.ItemCode AS 'Item no',

T0.ItemName AS 'Description',

T0.CreateDate AS 'Date created',

T1.WhsCode AS 'Warehouse',

T1.OnHand AS 'On hand',

T1.AvgPrice AS 'Avg. price',

(T1.OnHand * T1.AvgPrice) as 'Extended',

(SELECT MAX(TA.DocDate) FROM OINV TA INNER JOIN INV1 TB ON TA.DocEntry = TB.DocEntry

WHERE TA.DocDate < @DAYLIMIT AND TB.ItemCode = T0.ItemCode) AS 'Last Inv. Date',

(SELECT MAX(TA.RlsDate) FROM OWOR TA INNER JOIN WOR1 TB ON TA.DocEntry = TB.DocEntry

WHERE TA.RlsDate < @DAYLIMIT AND TB.ItemCode = T0.ItemCode) AS 'Last W/O Date'

FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode

WHERE T1.WhsCode NOT IN ('98 Ret','DropShip','Transit')

AND T1.OnHand > 0

AND T1.IsCommited <= 0

AND T0.ItemCode

NOT IN (SELECT TB.ItemCode FROM OINV TA INNER JOIN INV1 TB ON TA.DocEntry = TB.DocEntry

WHERE TB.ItemCode = T0.ItemCode AND TA.DocDate >= @DAYLIMIT)

AND T0.ItemCode

NOT IN (SELECT TB.ItemCode FROM OWOR TA INNER JOIN WOR1 TB ON TA.DocEntry = TB.DocEntry

WHERE TB.ItemCode = T0.ItemCode AND TA.RlsDate >= @DAYLIMIT)

FOR BROWSE

[JesperB1 · Accepted solution]

Hi All,

Some very elaborate queries there. I will still post this one.

 SELECT 
ItemCode, Dscription, Warehouse, max(DocDate) as 'Last Transaction Date', 
sum(InQty-OutQty) as 'Qty in WareHouse', sum(transvalue) as 'Total Value'
FROM OINM 
WHERE ItemCode not in 
	(SELECT distinct itemcode FROM 
	OINM WHERE DocDate >= '2008-01-01' and  
	DocDate  <= '2008-10-31')
Group By ItemCode, Dscription, Warehouse 

This should give you the data you were looking for. However, unlike the query above here the dates are still written in the query itself but can of course be easily modified.

Don't know if you are using cost price per warehouse and of it is relevant top have the warehouse in there.

Hope it helps,

Jesper

[Former Member · Accepted solution]

Hi Inc. Cowper.

I think this query is very close to what I need, and I thank you for that. I was just wondering about the date that is given. Could you please explain what the date does in the query? I can't seem to work it out on my own by reading through the query.

Thanks and regards, Runar.