Skip to Content
author's profile photo Former Member
Former Member

Inventory Aging Report

Hi experts,

Does someone have experience creating or know where I can find a sample Inventory Aging Report? Relevant information to be displayed would include part number, avg price, total qty on hand, qty per warehouse, and aging based on GRPO date broken down by 30 day intervals. I'm hoping for something in XLR format as several additional calculation fields would also need to be added. Thanks so much in advance.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on May 21, 2008 at 02:38 AM

    Hi,

    Unfortunately, there is no inventory aging report in SAP B1. But one of SAP support has told me that to have it just activating valid field in the item master data.

    Anyway, Davin, a member of this forum has advertised inventory aging query in my mailist forum try to contact him :

    davin_x@yahoo.com

    Rgds,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 21, 2008 at 02:51 AM

    Greetings,

    While its true that there is no real stock aging in SAP B1, you can create a rudimentary aging for your serial number items. This is because for each serial number item, there is a stock in date and when its sold there is a stock out date (per item).

    SELECT T1.ITEMCODE , T2.ITEMNAME,T2.LASTPURPRC,

    CASE WHEN Datediff(day, T1.INDATE,GETDATE())<30 THEN 1 END '<30 Days(Qty)',

    CASE WHEN DateDiff(day, T1.INDATE,GETDATE()) >= 31 AND DateDiff(day, T1.INDATE,GETDATE()) < 60 THEN 1 END '31 to 60 Days(Qty)',

    CASE WHEN DateDiff(day, T1.INDATE,GETDATE()) >= 61 AND DateDiff(day, T1.INDATE,GETDATE()) < 90 THEN 1 END '61 to 90 Days(Qty)',

    CASE WHEN DateDiff(day, T1.INDATE,GETDATE()) >= 91 AND DateDiff(day, T1.INDATE,GETDATE()) < 120 THEN 1 END '91 to 120 Days(Qty)',

    CASE WHEN Datediff(day, T1.INDATE,GETDATE()) >= 120 THEN 1 END '>120 Days(Qty)'

    FROM OSRI T1 INNER JOIN OITM T2 ON T1.ITEMCODE=T2.ITEMCODE

    WHERE T1.Status = 0

    Please note that the above query is only used to test some values and functions in the serial item table, so if you want to use it, you will have to make sure it fits your needs. Or you could just use it as a reference.

    Also note, that an aging report must be carefully designed because there have been cases of end users tweaking the stock age by doing an inventory transfer from one warehouse to another.

    Currently we have developed an aging report using Excel (because our client requested it in Excel) but I am unsure on its cost and support to other partners. Drop me an email and I'll try to review it with you if required.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 21, 2008 at 03:41 AM

    Hi,

    Pl. try to use this query.

    SELECT T0.ITEMCODE , T0.ONHAND as 'Total Qty',

    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND END '<45 Days(Qty)',

    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())<45 THEN T0.ONHAND*T0.AVGPRICE END '<45 Days(Value)',

    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END '45 to 90 Days(Qty)' ,

    CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END '45 to 90 Days(Value)',

    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND END '>90 Days(Qty)',

    CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= 90 THEN T0.ONHAND*T0.AVGPRICE END '>90 Days(Value)'

    FROM OITW T0 INNER JOIN OITM T1 ON T0.ITEMCODE = T1.ITEMCODE

    INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD

    WHERE

    T0.ONHAND>0

    AND T0.WhsCode ='[%0]'

    AND T2.ITMSGRPNAM = [%2]

    Regards,

    Venkatesan G.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Please note that the query provided by Venkatesan above will not be accurate (this issue has been discussed before) because the query calls out the LastPurchaseDate from the OITM table.

      An example:

      1. Run the query today, and you will get 10 units in > 90 days for stock item ABC

      2. Do an AP Invoice for stock item ABC, 5 quantity.

      3. Run the query again, and you will get 15 units in the > 90 days for stock item ABC, instead of 5 units in the < 45 days and 10 units in the > 90 days

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.