10
Dear All,
I wanted to get a list of items which was created on Dynamics GP but doesn't have any type of transactions including purchase/ sales / inventory adjustment. How to find this items is there any report I can use or it has to come from by writing SQL view.
*This post is locked for comments
Thanks Mary for your kind reply on this.
Thanks Alvaro for your kind reply.
Hi,
You need to run Reconciliation on Inventory for update the table IV00102, then you can run this script:
SELECT * FROM IV00101 WHERE ITEMNMBR NOT IN (SELECT DISTINCT ITEMNMBR FROM IV30300
UNION SELECT DISTINCT ITEMNMBR FROM IV00102 WHERE ATYALLOC <> 0)
Mary
Hi we have this view wich display item rotation, I think you could modify it to display item with no transactions.
SELECT TOP (100) PERCENT ITEMNMBR, TRXLOCTN, SUM(CASE WHEN datediff(month, docdate, getdate()) = 0 THEN TRXQTY ELSE 0 END) AS period1, SUM(CASE WHEN datediff(month, docdate, getdate())
= 1 THEN TRXQTY ELSE 0 END) AS period2, SUM(CASE WHEN datediff(month, docdate, getdate()) = 2 THEN TRXQTY ELSE 0 END) AS period3, SUM(CASE WHEN datediff(month, docdate, getdate())
= 3 THEN TRXQTY ELSE 0 END) AS period4, SUM(CASE WHEN datediff(month, docdate, getdate()) = 4 THEN TRXQTY ELSE 0 END) AS period5, SUM(CASE WHEN datediff(month, docdate, getdate())
= 5 THEN TRXQTY ELSE 0 END) AS period6, SUM(CASE WHEN datediff(month, docdate, getdate()) IN (0, 1, 2, 3, 4, 5) THEN TRXQTY ELSE 0 END) / 6 * - 1 AS total
FROM dbo.IV30300
WHERE (TRXQTY < 0)
GROUP BY ITEMNMBR, TRXLOCTN
Hi Leslie,
Thanks for your kind reply. Yes we have created number of items which never used so wanted to delete them. Even if I get a list of items which used at least once I can compare that with full list to identify the items not used. I will try it out SQL :-)
Hi Babu,
I think you may need to use your outstanding SQL skills to get what you need. I cannot think of a canned report that will list inventory items that have never been used. Even in SmartList. You could build your own smartlist in a jiffy. Are you looking for a list of items you can delete?
Kind regards,
Leslie
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
