Inventory Report (Slow move or Obsolete)

(0) Share

Report

Posted on by Community Member

I'm trying to run a report of our physical inventory. We have many different components that have not been used in the last two years. I want to run a report that shows all part numbers with no movements or transactions in the last two years. Your support is appreciated.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Heather Roggeveen 9,146 on at

Like (0)

Report

I am not sure whether one of the out of the box reports will do it, but Victoria Yudin has some SQL queries that also include last sale and purchase date for inventory items. That may be a good starting point.

Here is a link to her page: victoriayudin.com/.../inventory-sql-views

Cheers

Heather

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I use this sometimes. It's not perfect - it includes stock counts and transfers as activity - but it's a good starting point. Maybe you can tweak it for your needs.

SELECT RTRIM(A.ITEMNMBR) [Item Number]

, RTRIM(B.ITEMDESC) [Item Description]

, RTRIM(A.TRXLOCTN) [Location Code]

, CONVERT(VARCHAR,A.DATERECD,101) [Date Received]

, A.QTYRECVD [Qty Received]

, A.QTYSOLD [Qty Sold]

, A.UNITCOST [Unit Cost]

, (A.QTYRECVD - A.QTYSOLD) * A.UNITCOST [Amount]

, DATEDIFF(DAY, A.DATERECD, GETDATE()) [Days In Stock]

, CONVERT(VARCHAR, GETDATE(), 101) [Report Date]

, ISNULL(CONVERT(VARCHAR, C.LAST_CONSUMED, 101),'01/01/1900') [Last Consumed]

, A.RCPTNMBR [Receipt No]

, A.VENDORID [Vendor ID]

, A.PORDNMBR [PO Number]

FROM IV10200 A

JOIN IV00101 B ON A.ITEMNMBR = B.ITEMNMBR

LEFT JOIN (SELECT ITEMNMBR, TRXLOCTN, MAX(DOCDATE) LAST_CONSUMED FROM IV10201 GROUP BY ITEMNMBR, TRXLOCTN) C ON A.ITEMNMBR = C.ITEMNMBR AND A.TRXLOCTN = C.TRXLOCTN

WHERE A.QTYSOLD < A.QTYRECVD

--AND DATEDIFF(DAY, A.DATERECD, GETDATE()) > 180

ORDER BY A.ITEMNMBR

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am looking for the same. I don't know that consuming an item by an MO will show activity in Victoria's view, as it only shows last receipt and last sale dates. I need to see the INV adjustments related to receiving/posting/closing an MO.

What GL tables does this act on? How can you identify a GL TRX for a specific item from SQL?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Using Victoria Yudin's report and adding activity from IV30300 for ITEMNMBR where DOCNUMBR is in MOP10213 I think I have achieved an accurate usage report that also accounts for MO consumption as well as PO and SO activity.

Was this reply helpful? Yes No
Kelly Marinoff 5 on at

Like (0)

Report

ecujak - would you mind sharing the code for your usage report?

Thanks in advance!

Was this reply helpful? Yes No