Does anyone know how to come up with an Aged Inventory Report? SSRS, BAQ, or straight SQL?
I’m not looking for a last used, but rather the date the part was put into inventory, grouped into 30/60/90/120 day buckets. For instance, in the attached screenshot, the highlighted part has 259,200 on hand. 108,000 of those were put into inventory 61-90 days ago and 7,200 of those were put into inventory 91-120 days ago, and 144,000 where put into inventory over 120 days ago. 108,000 + 7,200 + 144,000 = 259,200 that are currently in stock. Those numbers are from Oracle. Any idea how to replicate this in Epicor?
I’m at a total loss at this point. No, the slow moving and stock status reports did not work for me.
Off the top of my head…
Qty more than 120 days old would be the SSR calc for 121 days ago. (call this Bucket_120_)
Qty in the 91 to 120 days would be the SSR calc for 91 days ago - Bucket_120_ (call this Bucket_91_120)
Qty in the 61 to 90 days would be the SSR calc for 61 days ago - Bucket_91_120 (call this Bucket_61_90)
and so on…
Does that work?
EDIT
No, that’s not right…
The Bucket_91_120 calc would first need to sum up the issues (anything that removes QOH), then subtract that from the Bucket_120_, and then add in the receipts between days 91 to 120.
So–you’re using the PartTran table, yeah? Going off of the transactions since that’s the only place they’re associated with a date, you would summarize all +/- stock transactions in a 0-30 day period for the first bucket, then summarize all of the +/- transactions in the 31-60 period and subtract the sum from the 0-30 period, repeat for each subsequent bucket? Am I understanding that correctly?
I’m not even understanding it myself. 
I assumed no lot or serial control, and FIFO (Any issue after in the last 120 says draws from any QOH that was 120+ days old. If none exists then draw from the 91-102 bucket).
For example, your highlighted row shows that zero receipts must have happened in the last 60 days.
That correct?
No lot or serial and we can assume FIFO if that makes it easier though, in practice, I’m not 100% sure they are following that.
I assume so, yes.
As far as FIFO - I’m just talking about qty’s. As in you use up the oldest QOH first.
In your example, the next 144,000 issues from stock would deplete the that inventory that’s older than +120 days, before any of the 7,200 or 108,000 would be touched.
There is a Stock Aging report in the system and you can select the type of transactions you want to use for the report.
Vinay Kamboj
1 Like
That doesn’t age by the date the quantity was transacted into stock.
That provisions the cost of the current on hand based on the last transaction date.
So, it’s close, but not really, lol. The sum of each of the buckets does, indeed, equal the on hand total, but it’s not really showing what bucket each of the 19 pieces is coming from. The problem seems to be when there are more “outs” than “ins” in a bucket. In the screenshot, it is showing “-1 of the 19 on hand” was done in the 0-30 days bucket, which makes sense mathematically because I’m summarizing the “ins” and “outs” in that period, but it’s not quite what they want.
Well, I got something “close enough” to what they were asking. It’s basically Aged Inventory using the LIFO method (Last In First Out) which isn’t what we do in practice, but works for a visualization.
The query works by getting the total on hand (including non-nettable), summarizes the transactions that increase inventory in each date bucket, then subtracts those amounts from the on hand until nothing is left.
Here’s the query if anyone wants it in the future–designed in 10.2.200.11: InventoryAging_LIFO.041520.baq (23.6 KB)
1 Like
You sure you got the correct post, Chris? lol
Invoice totals != inventory.
I was showing the formula to split out by dates can be done done by days or months. Table is secondary
Regards,
Chris Wineinger
The table is 100% of the problem, though; it was never about getting it into buckets. The issue is trying to figure out, of the 100 pieces on hand, what bucket to place it in since the stock in PartWhse and PartBin don’t have a date stamp of when they were put into inventory. The difficulty comes in when you try and write some rules around the PartTran table which is a mess of ins, outs, lefts, and rights.
1 Like
Epicor has a Stock Aging report. Actually there are 3 different reports. Slow Moving, Excess Stock and Aging. The first two give the value of the stocks for the purpose of calculating provisions based on buckets. These buckets are defined based on the transaction types. The third one does aging of the quantity on hand based on date of manufacture. I tested the aging report once. I gives the data but the report needs to be modified to give neat summary.
Either of these reports can be modified to your requirements, since they already have buckets calculated.
Vinay
What would that report be called because if I searched for “Aged” or “Aging” in the menus, nothing shows up.
I don’t think I have the module.
I found all of the pieces, but when I put them all in place and try to run it, I get this:
What module does this fall under?
Inventory Management->Setup->Stock Provision Report Format ---- Report format setup here
IM->Reports->Excess Stock Provision/Slow Moving Stock/Stock Aging Report
We are on 10.2.400
Vinay Kamboj
That report is not available in version 10.2.200.11 which brings me back to the query.
Was able to restore our latest data to our 10.2.500 Sandbox server and get what we needed. Thanks @Vinaykamboj