Query for unused items

[steven_delombaerde2]

Can anyone help me to build a simple query to list items that have not been used in any transactions? I do not want to go through all the document lines in the various tables ... The purpose is to do a manual cleanup of items that are not needed anymore.

SBO greys out the itemcode when you've used it in transactions (stock or non-stock), but I have no idea what determines this. Same thing for Business Partners, the code is greyed out once you start using it in transactions.

Thanks

Steven D

[former_member201110 · Accepted solution]

Hi Steven,

I think you can use the OINM table for this. The OINM holds details of all stock movements for all transaction types that affect stock.

This query will show item codes that have not been used in a transaction:

select 
	* 
from 
	OITM 
where 
	ItemCode not in (select ItemCode from OINM)

Kind Regards,

Owen

[former_member583013]

Just an addition to what Owen has mentioned since you quoted SBO greys out the Item you might want to also check QUT1, RDR1 and POR1 tables. This is mainly because even if an Item is in a Quote or Sales Order SBO would not allow the item to be deleted.

Query for the same

SELECT ITEMCODE, ITEMNAME, CREATEDATE

FROM OITM

WHERE ITEMCODE NOT IN

(SELECT DISTINCT ITEMCODE FROM OINM)

UNION

SELECT ITEMCODE, ITEMNAME, CREATEDATE

FROM OITM

WHERE ITEMCODE NOT IN

(SELECT DISTINCT ITEMCODE FROM QUT1)

UNION

SELECT ITEMCODE, ITEMNAME, CREATEDATE

FROM OITM

WHERE ITEMCODE NOT IN

(SELECT DISTINCT ITEMCODE FROM RDR1)

UNION

SELECT ITEMCODE, ITEMNAME, CREATEDATE

FROM OITM

WHERE ITEMCODE NOT IN

(SELECT DISTINCT ITEMCODE FROM POR1)

Best Wishes

Suda

[Former Member]

Have you tried Inventory> Inventory Reports> Inactive Items report?