730
Hello,
Does anybody know whether or not GP has an Inventory Aging Report by lot number?
I would like to have 'Site ID', 'Item Number', 'Received Date', 'Lot Number', 'QTY', 'Amount', '1-30days', '31-60days', '61-90days' in this Inventory Aging Report.
Should we develop it from scratch?
Thank you for your advice!
*This post is locked for comments
That is a very common problem and not easy to solve. You then have to work out using your valuation method, for example FIFO what items moved where based on the inventory movement time line.
Its something I need to write myself but keep putting off.
Hopefully someone will reply here with a pre-built SQL system to do this complex analysis....
With tracking by serial it becomes much easier as you know what items moved where...
Tim.
Waseem, I have used your query for sometime now, but we started moving items into more sites and this report no longer ages correctly. The DATERECD gets set to current date when moving items into a new site. Is there a way to see the original received and not the transfer qty into the site? thanks.
HI, Sandip,
Iam looking to get an Inventory Aging for as at 13.12.2017 in GP, your suggestion is highly appreciated
thank you,
Shifan
Don't forget to add value to variable @dte
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
yes ofcorse. i have a view. try it
CREATE VIEW vw_IVStockAgeing
AS SELECT IV00101.ITEMNMBR AS ITEMNUMBER,
IV00101.ITEMDESC AS ITEMNAME,
IV10200.DATERECD AS DATERECEIVED,
IV10200.QTYRECVD AS QTYRECEIVED,
IV10200.QTYSOLD AS QTYSOLD,
IV00101.ITMCLSCD AS ITEMCLASS,
IV10200.UNITCOST AS UNITCOST,
IV10200.PCHSRCTY AS RECEIPTTYPE,
IV10200.RCPTNMBR AS RECEIPTNO,
IV10200.TRXLOCTN AS LOCATION,
( IV10200.QTYRECVD – IV10200.QTYSOLD ) AS QTYAVAILABLE,
( ( IV10200.QTYRECVD – IV10200.QTYSOLD ) * IV10200.UNITCOST ) AS CURRENTVALUE,
DATEDIFF(day, IV10200.DATERECD, GETDATE()) AS AGEDDAYS,
CASE WHEN DATEDIFF(day, IV10200.DATERECD, GETDATE()) < 0
THEN 'Current'
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 0
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 30
) THEN '0-30 Days' -- 1 month
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 30
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 60
) THEN '31-60 Days' -- 2 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 60
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 90
) THEN '61-90 Days' -- 3 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 90
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 180
) THEN '91-180 Days' -- 6 months
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 180
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 360
) THEN '181-360 Days' -- 1 year
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 360
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 720
) THEN '> 1 year’ — 2 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 720
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 1440
) THEN '> 2 years’ — 3 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 1440
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 2880
) THEN '> 3 years’– 4 years
WHEN ( DATEDIFF(day, IV10200.DATERECD, GETDATE()) > 2880
AND DATEDIFF(day, IV10200.DATERECD, GETDATE()) <= 5760
) THEN '> 4 years’– 5 years
ELSE ‘> 5 years’ — Above 5 years
END AS BUCKET
FROM ( IV00101 IV00101
INNER JOIN IV10200 IV10200 ON IV00101.ITEMNMBR = IV10200.ITEMNMBR
)
INNER JOIN IV00102 IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE IV00102.RCRDTYPE = 1
AND IV10200.PCHSRCTY <> 0
Hayato,
Inventory aging report is not available in Dynamics GP , I still remember when I was working in Bahrain I have developed Inventory Aging report for one of customer.
IV00300 table store lotwise Qty information and date of Received.
Thanks
Sandip
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,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (