I’m pretty sure I’ve exhausted any possibility of doing this (through forum scrolling and research) but thought I should check before throwing in the towel. Is there any way to get historical on-hand bin quantities into an SSRS Stock Status report? I know Epicor doesn’t hold historical data for bins but was thinking I could come up with some kind of calculation.
I’ve been told one way to accomplish this is if I create a nightly snapshot and store that data in a UD table. I imagine with that the UD table would grow astronomically. Is there any other way?
I don’t think it will grow astronomically, how big is your part bin table right now?
Also, how long do you wish to hold the data for?
I think the only way to see historical snapshots is to take historical snapshots. My mind is racing now though thinking you could try and do the calculations that part transaction history tracker does for running total, but dang that would be a lot.
To get a historical snapshot of an inventory at any point in time you would need to start at the beginning of time for each given part should the part tran table be used, right?
Instead of going from the beginning of time, you can take transactions since the cutoff and use those. I would have a subquery that filters PartTran by TranDate >= @CutOffDate then groups PartTran by Part, Warehouse, Lot, and Bin and sums up the net transaction quantity, taking into account the transaction types as needed. Then outer join the PartBin table and use that on-hand quantity (or a default of zero) as your starting point and subtract the net movements. You’d have to make sure you get the joins right and possibly join to Part or PartPlant to make sure you get zero values to display if needed.
For example, say I have 500 EA of Part A in Bin B right now. I want to know what I had in that bin as of 12/31/2021. PartTran shows a net increase between 12/31/2021 and now of +200 EA (a few bin movements, receipts to inventory, shipments, etc.) Therefore my on-hand quantity as of 12/31 would have been 500 - 200 = 300 EA.
Awesome suggestion! This definitely gets me where I need to be.Thank you so much for your help! Doing this also gave me a better understanding of the whole process. Trying to add this functionality to an existing SSRS report was just making me bang my head against the wall.Thank you again!