Inventory Aging Report

[Former Member]

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.

[Former Member]

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.

[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

[Former Member]

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.

[former_member186095]

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,