web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Inventory Items with n...
Microsoft Dynamics GP (Archived)

Inventory Items with no transactions

(0) ShareShare
ReportReport
Posted on by babubaskaran@outloo... 12

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

I have the same question (0)
  • L Vail Profile Picture
    L Vail 65,271 on at

    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

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    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 :-)  

  • Alvaro Rodríguez Ochoa Profile Picture
    Alvaro Rodríguez Ochoa 2,395 on at

    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

  • Maria Carrero Profile Picture
    Maria Carrero 150 on at

    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

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Thanks Alvaro for your kind reply.

  • babubaskaran@outlook.com Profile Picture
    babubaskaran@outloo... 12 on at

    Thanks Mary for your kind reply on this.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans