You’re offline. This is a read only version of the page.
Would anyone know if there is an Inventory Aging Report available in GP2015? Something that lets you quickly see how long your inventory has been sitting in stock a long time etc
If not, can anyone recommend an existing 3rd party solution? (I imagine this is not terribly difficult to code).
Thank you,
Maria.
*This post is locked for comments
Try this query, just add a value to the date variable @dte which represents 'as of date'
declare @dte date
select rtrim(actNuMst) as "Account Number", Item, Location, sum(Quantity) as Quantity, sum([Extended Cost]) as [Extended Cost],
sum([0-30]) as [0-30], sum([31-60]) as [31-60], sum([61-90]) as [61-90],
sum([91-120]) as [91-120], sum([121-180]) as [121-180], sum([Over 180]) as [Over 180]
from
(select it.IVIVINDX, rtrim (i.ITEMNMBR) as [Item], i.TRXLOCTN as Location, sum (QTYRECVD-isNull(ii.qtysold,0)) as Quantity,
sum ((QTYRECVD-isNull(ii.qtysold,0)) * i.unitcost) as [Extended Cost],
case when DATEDIFF(day, DATERECD, @dte) between 0 and 30 then sum (QTYRECVD-isNull(ii.qtysold,0)) else 0 end as [0-30],
case when DATEDIFF(day, DATERECD, @dte) between 31 and 60 then sum (QTYRECVD-isNull(ii.qtysold,0)) else 0 end as [31-60],
case when DATEDIFF(day, DATERECD, @dte) between 61 and 90 then sum (QTYRECVD-isNull(ii.qtysold,0)) else 0 end as [61-90],
case when DATEDIFF(day, DATERECD, @dte) between 91 and 120 then sum (QTYRECVD-isNull(ii.qtysold,0)) else 0 end as [91-120],
case when DATEDIFF(day, DATERECD, @dte) between 121 and 180 then sum (QTYRECVD-isNull(ii.qtysold,0)) else 0 end as [121-180],
case when DATEDIFF(day, DATERECD, @dte) > 180 then sum (QTYRECVD-isNull(ii.qtysold,0)) else 0 end as [Over 180]
from iv10200 i
join iv00101 it on i.ITEMNMBR = it.ITEMNMBR
left join iv10201 ii on ii.itemNmbr = i.itemNmbr and i.trxLoctn = ii.trxLoctn and srcRctSeqNm = i.rctSeqNm and docDate <= @dte
where i.daterecd <= @dte
group by i.itemnmbr, i.trxloctn, daterecd, it.IVIVINDX) as t1 join gl00105 on t1.IVIVINDX = gl00105.ACTINDX
group by t1.item, t1.location, actNuMst
order by Item
Would you please send me your SQL code. I have a client who would like to age their inventory by the original PO receipt date. However, they have issues using the Purchase Receipts table because they do a lot of transfer from site to site. Transfers update the receipt date to the date of the transfer in the new record that is created.
Using Victoria Yudin's report gave us all that we needed for non-MFG items.
For the Manufacturing Machine Orders we also included activity from IV30300 for ITEMNMBR where DOCNUMBR is in MOP10213. I think I have achieved an accurate usage report that also accounts for MO consumption as well as PO and SO activity.
Hola Vaidh,
Can you please send me the SQL Script for the inventory aging. I will really appreciate it.
Carlos
Hola Maria,
Can you please send me the SQL Script for the inventory aging. I will really appreciate it.
Carlos
Thank you Vaidy.
I was able to adapt the code in your link number 2 above for our purposes. It works great !!
Maria.
Thank you.
I will send you and e-mail if you don't mind sharing your code.
Maria.
It is not really difficult, but is not easy either. Just that we must be aware of relevant GP table and its fields.
I have a SQL code that you could try. Email me at vaidymohanATmeDOTcom, if you are interested. I will send you the code.
Alternate Options:
1. Check Victoria's blog (http://victoriayudin.com) and see if a post is already available.
2. Refer to this link: community.dynamics.com/.../31121.aspx
Hope that helps.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (