web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Inventory Aging Report
Microsoft Dynamics GP (Archived)

Inventory Aging Report

(0) ShareShare
ReportReport
Posted on by Hayaty 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

I have the same question (0)
  • Richard Whaley Profile Picture
    Richard Whaley 25,195 on at
    Re: Inventory Aging Report

    That is the kind of thing you would need to develop from scratch.

  • sandipdjadhav Profile Picture
    sandipdjadhav 18,306 on at
    Re: Inventory Aging Report

    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
  • Hayaty Profile Picture
    Hayaty 730 on at
    Re: Re: Inventory Aging Report

    Thank you, Richard. I will try to develop it.

  • Hayaty Profile Picture
    Hayaty 730 on at
    Re: Re: Inventory Aging Report

    Thank you for your reply, Sandip.
    The table information is very helpful for me.
    Thanks!

  • waseem Profile Picture
    waseem 230 on at
    Re: Inventory Aging Report

    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

  • Community Member Profile Picture
    Community Member on at
    RE: Inventory Aging Report

    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

  • Community Member Profile Picture
    Community Member on at
    RE: Inventory Aging Report

    Don't forget to add value to variable @dte

  • Community Member Profile Picture
    Community Member on at
    RE: Inventory Aging Report

    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

  • Daryle Ridley Profile Picture
    Daryle Ridley 18 on at
    RE: Inventory Aging Report

    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.

  • Suggested answer
    waseem Profile Picture
    waseem 230 on at
    RE: Inventory Aging Report

    if your inventory is LOT or Serial Tacking Enabled then it is possible via using IV30300 and IV30400 tables

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans