Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Don McCready]

Hi All,

Does anyone have a suggestion for identifying inventory items that are obsolete (i.e., not being stocked or sold)?  Brad Smith proposed an interesting SQL script and I’m familiar with the inventory turnover report.  Any other suggestions?

Thanks!

——————————
Don McCready
Controller and IT Director
The Schaefer Group, Inc.
Beavercreek OH
——————————

[Windi Epperson]

Hey Don,
I haven’t followed this all the way through, but this might be a starting point for you.   If you go to InventoryCardsHistory you’ll get a really interesting window that shows you info about item sales.  From there I clicked Table Finder and the 2 main history tables are the IV30101 and IV30102 – Inv Sales Summary Hist and Inv Sales Summary Per Hist.

This window, or maybe those tables (say in SmartList Builder) might get you a good starting point.

Thanks
Windi

——————————
Windi Epperson
President/GP Senior Consultant
Advanced Integrators, Inc.
Norman OK
405-946-1774 Ext 102
——————————
——————————————-

[Andrew Collins]

Don,

I would also look at the IV00101, IV00102, IV10200, and IV10201 tables to see some relevant dates associated with each Item Number.  That should give you a good idea of what Item Numbers haven’t had any activity against them, or which ones haven’t had any activity in a while.

——————————
Andrew Collins
Senior Consultant
Socius
Atlanta, GA
——————————
——————————————-

[Bob DiPasquale]

Hi Don,

I don’t recall if GP gives you a lot of help or not when it comes to component parts if you happen to be assembling. A last order date doesn’t necessarily tell you the last time you reduced your inventory amount (QTYONHND) , which is really the key to answering whether or not a component part should be obsoleted IMHO. 

I would be curious to see the SQL that you mentioned.

——————————
Bob DiPasquale
——————————
——————————————-

[Leslie Vail]

?Hi,
Victoria Yudin has some great views on inventory. I think the Inventory with dates view is a good place to start.
Leslie

[Jeff Dalton]

?Hi Don,
Depending on the number of items you have, and how familiar you are with them, a simple smartlist can provide a great deal of insight.

Use run a smartlist on Item Transactions, for all items with an On Hand quantity greater than 0, then sort by document date. I bring in the document type so I can determine if the most recent transaction was a sale, PO receipt, or manufacturing activity.

This can help discover obsolete inventory, customers that may be due for a cold call, and possibly obsolete vendors.

Jeff

——————————
Jeff Dalton
Corporate Operations Manager
The Spiratex Company
Romulus MI
——————————
——————————————-

[Leslie Vail]

?Yikes!
Not sure what happened with my post, but here is Victoria’s view for Inventory Items with Dates:


CREATE VIEW view_Inventory_with_Dates
AS
/********************************************************************
view_Inventory_with_Dates
Created on Dec 4, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
All inventory items with quantity on hand and last sale
     and receipt dates Functional amounts only
Tables used:
I - IV00101 - Item Master
S - IV30300 - Transaction Amounts History with DOCTYPE = 6 (sales)
Q - IV00102 - Item Quantity Master
U - IV40201 - U of M Schedule Header
Updated Dec 22, 2009 to add WHERE clause at end
Updated Jan 29, 2011 to change join type for IV30300 ********************************************************************/

SELECT I.ITEMNMBR Item_Number,
       I.ITEMDESC Item_Description,
       Q.QTYONHND Quantity_on_Hand,
       U.BASEUOFM U_of_M,
       CASE I.ITEMTYPE
          WHEN 1 THEN 'Sales Inventory'
          WHEN 2 THEN 'Discontinued'
          WHEN 3 THEN 'Kit'
          WHEN 4 THEN 'Misc Charges'
          WHEN 5 THEN 'Services'
          WHEN 6 THEN 'Flat Fee'
          END Item_Type,
       I.CURRCOST Current_Cost,
       I.ITMCLSCD Item_Class,
       coalesce(S.LastSale,'1/1/1900') Last_Sale_Date,
       coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,
       coalesce(Q.LSORDVND,'') Last_Vendor

FROM IV00101 I

LEFT OUTER JOIN
     (SELECT ITEMNMBR, MAX(DOCDATE) LastSale
      FROM IV30300
      WHERE DOCTYPE = 6
      GROUP BY ITEMNMBR) S
     ON I.ITEMNMBR = S.ITEMNMBR

INNER JOIN
     IV00102 Q
     ON I.ITEMNMBR = Q.ITEMNMBR
     AND RCRDTYPE = 1

INNER JOIN
     IV40201 U
     ON U.UOMSCHDL = I.UOMSCHDL

WHERE Q.QTYONHND <> 0

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Inventory_with_Dates TO DYNGRP

——————————
Leslie Vail
Accounting Systems Consulting, Inc.
Dallas TX
leslievail@earthlink.net 972-814-8550
——————————
——————————————-