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
