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

Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight
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

News and Announcements

🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro
Introducing our New Video Series: Community Spotlight

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Dan Liebl Profile Picture

Dan Liebl 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans