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
ecujak - would you mind sharing the code for your usage report?
Thanks in advance!
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.
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?
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
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
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
