Just checked again the JobProd table and is contains all the Jobs but no TargetJobNum for any records.
@hasokeric - thanks for the input - been wrestling with this now for quite a while and still not solved it.
Threw me off when you said Top Level Job, I assumed you had Multi-Level Jobs. So based on JobProd you really have no multi-level job’s everything is a Top-Level Job, there is only Level 0 for you.
I get it; so you have a Shortage of -500pcs and you want to know, what are the priority jobs that you must complete to be back at 0pcs shortage.
Well your Query is a good starting point, use the SQL Studio debugger to figure out why it’s slow… in my case I was doing too much work and Also Exclude any UNFIRMED Jobs, usually MRP will create like 2000 in our case, no need to traverse or limit it to look at a limited amount of 100 unfirmed ones.
One thing I learned with SQL. It LOVES Sub-Queries! If I run a big query it’s slow, when I start grouping things into Sub-Queries where it makes sense, SQL Execution Plan is on fire! Shaves off 20-30 seconds.
Are you looking for a way to tie a Job for a material that is built to stock but will be used on a higher level job together? Since everything for you is built to stock Epicor has no way to know what material will be used on what parent job other than the due date and quantity.
Am I understanding your fundamental problem correctly?
Is there a solution to this problem?
@John_Mitchell - yes correct.
I have written the SQL code to accomplish this.
Basically - when a resource goes into bottleneck - I need to provide a ‘chase’ data set that will allow the team to visualize the status of sales orders by drilling down in to the top level job - cascading through all the levels and returning the shortages and Jobs required to satisfy those shortages.
The data set will provide all work orders required to compete the parent part as well as a live operation status.
As I say I have accomplished this in SQL but it was taking a little to long to execute but I’ve got it outputting the data now in 1-2 seconds.
I can use this data either as an external BAQ or data set for reporting purposes.
Do you have a similar problem?
@rppmorris - How did you make the connection between the jobs since they are all make to stock jobs? What tables did you use? I am on the same boat
I would take a look at the Multi Level Pegging, which functions like Time Phase but runs across all parts on a schedule.
They added a Projected Sales Order Shortage dashboard in 10.1 that uses MLP (multi level pegging) and probably gets really close to what you are trying to do (BAQ’s: zSalesOrderShortage, zSalesOrderShortagePegging,zSalesOrderShortagePegSupply).
Keep in mind that MLP runs as a process specifically because of the recursion problem of BOM’s. This means that if you have MLP scheduled to run once a day your suggestions can be old and inaccurate. You could add a feature enhancement request to run MLP for a specific part, similar to how MRP functions now. In theory, you could then have a BPM that runs when a Sales Order is updated to rerun MLP for those parts and your demand side would always be up to date.