Where Shortage = SUM(Demand+Minimum Qty) - SUM(On Hand Qty+On Order)
I have been asked to provide a date the shortage occurs based on the above and if i’m honest this is going to be a tough nut to crack. Has anybody written anything similar?
I am currently building this in excel…
I first thought of the PartTran table, this could get me back to the on hand qty and provides the detail such as date, running total etc…but the table is enormous
Any thoughts / feedback would be much appreciated. Many thanks in advance.
A long, long time ago in a galaxy far, far away (V8.03) I also had to build this in Excel. Those were the days when I would get up at 5:30 to RDP into my server and start the ODBC update (we were on Progress), and by the time I got to work (two hours later) it would be finished.
This is what subqueries in E10 were built for. If you’re on SQL, you could probably do it in a view. Most of the data is in the PartDtl table IF MRP has been run… it’s the MRP Process that coagulates all the demands and supplies. If you aren’t running MRP, you’ll need to pull data from all the tables in your view.
If you don’t have access to that, then I would suggest checking out the “Multi-Level Pegging” and “Run the Multi-Level Pegging Process” sections of the MRP Technical Reference.
Finally, after you’ve run multi-level pegging, create a BAQ starting with the PegDmdMst table, then joining PegLink, and then joining PegSupMst to PegLink. That will give you a matching of supply to demand for all of your parts.
I’ve decided to go with your method on this project…so in a way, kinda mirroring the Time Phase screen as this is a good method for capturing shortages in a detailed manor.
Your right, PartDetail seems to get a lot of the information…but im not sure it is everything.
It would be great to try and get back to the “Balance” column.
I’ve hidden some columns on the excel sheet to squeeze the data in but you can see that for the most part this example works ok…but for things like unfirm jobs it falls over.
I’ve started by brining in table partbin (for the OnHandQty)…AND partplant (for the safety stock qty).
I’ve noticed that PartDtl.Quantity is always positive so i’ve done an IF statement to say partdtl.sourcefile = PO give me positive, otherwise give me negative…
Do you know which other tables / formulas i need to bring in to consider? Thanks.
The “Balance” column in Time Phase is calculated on-the-fly when the program is opened… which is why the first row is “On-hand quantity”. Each succeeding row adds or subtracts from there.
If you have manufactured parts that are received to stock, their quantities would also want to be positive.
And yes, Min/Max/Safety/Lead/DaysOfSupply values are all on PartPlant.
The PartDtl table itself is a product of MRP and/or Generate PO Suggestions… so one or both of those programs need to be run regularly to keep it updated. Make sure you do at least one Regenerate run each week or so, otherwise a lot of deadwood tends to build up.
I see that you’ve chosen a path with this, but I’m passing along one more thing for your consideration. I am just investigating and learning multi-level pegging myself, so I am certainly not a subject expert. I just recently learned more about the “Projected Sales Order Shortage” dashboard. You may want to investigate this feature, which is provided out-of-the-box by Epicor to utilize the data generated by the multi-level pegging process. As Ernie commented, you basically want a Time Phase analysis for all parts. That is essentially what the dashboard provides.
