I realized the other day that I’ve only posted this code in a reply, never as its own post, so I’m adding this now.
Slow-moving and obsolete inventory is traditionally a measure of inventory destined for markdown and ultimately write-off if not addressed. The nature of what is slow-moving and obsolete varies dramatically. Fresh bread is slow-moving if it hasn’t sold in a couple of days, heavy equipment could be months. Each organization determines what is slow-moving or obsolete.
This code compares inventory items to sales to determine items with quantities on hand that have not sold with the timeframe set in the date area.
SELECT IV00101.* FROM
dbo.IV00101 INNER JOIN
dbo.IV00102 ON dbo.IV00101.ITEMNMBR = dbo.IV00102.ITEMNMBR
WHERE
(dbo.IV00101.ITEMNMBR NOT IN
(SELECT
dbo.SOP30300.ITEMNMBR
FROM dbo.SOP30300
INNER JOIN dbo.SOP30200
ON dbo.SOP30300.SOPNUMBE = dbo.SOP30200.SOPNUMBE
AND dbo.SOP30300.SOPTYPE = dbo.SOP30200.SOPTYPE
WHERE (dbo.SOP30200.DOCDATE BETWEEN
CONVERT(DATETIME, ‘2019-03-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2021-02-28 00:00:00’, 102))
))
AND (dbo.IV00102.QTYONHND > 0)
Note: I had this in my set of scripts with no notations, that usually means I wrote it, however, I see that I posted a link to a version of this written by Mohammed Doud. The link is now gone so I’m not sure if this is his version or mine and I don’t want to take the credit if it’s his. Either way, it’s useful.
One Reply to “Dynamics GP: Slow-Moving Inventory”
Comments are closed.