web

You’re offline. This is a read only version of the page.

News and Announcements icon

Dynamics 365 CommunityForums Microsoft Dynamics GP (Archived) Last Used Inventory It...

Microsoft Dynamics GP (Archived)

Last Used Inventory Item Report

(0) Share

Report

Report

Posted on by Bill Campbell

12

We are looking for a reporting method that will allow us to determine the date that an item was last USED.

Used may be Sold or Purchased

Used may be Built or Adjusted ( in or out via Inventory Count )

Client is using Horizon Light Manufacturing and core GP 2013 Inventory. There are no other 3rd party and no other customizations in place.

We would like to use an Excel link to SQL or if possible stay in SmartList and build the necessary reports.

I believe I have the Last Order, Last Purchased from the Item Quantities table. So my big ask is for the when last used - manufacturing or sales.

*This post is locked for comments

I have the same question (0)

All responses Img

All responses (3)

Answers Img

Answers (0)

c4c4571f00c7433e841... 1,110 on at

Like (0)

Report

hey bill, me also trying to get same data...but not succeeded yet, have u came to any solution...??

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

Use this one (this was created by Victoria) but you can modify to your needs

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I am not familiar with Horizon Manufacturing as we use the GP manufacturing module. Are you using MO's? If so then to see true last used date on items you need to tap into the table(s) holding data of the material issued to MO's as well as sales transactions of the same item.

Was this reply helpful? Yes No

Helpful resources

Quick Links

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans