Just checked again the JobProd table and is contains all the Jobs but no TargetJobNum for any records.
@hkeric.wci - thanks for the input - been wrestling with this now for quite a while and still not solved it.
Cheers
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?
1 Like
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.
1 Like
Good Morning,
Haso, is there any chance you still have access to the query above and could show me your joins, esp on the first 3 querys and then the top level one? I am beating my head against a wall trying to do something similar but less complex.
Given a top level job number, I need to see all open jobs for all parts on associated open jobs, multiple levels down. I think that the need to tie JobMtl to JobHead (open jobs, unissued jobmtls) seems right, per below. So then I’d get jobs for all of the 1st level job mtls, Then I try and use a CTE to try and get recursion for all jobs for all materials, all levels. The big difference I’m finding with this CTE vs the examples I see on this group that provide more details, is that I need 2 tables to get the data vs bom which needs just partmtl. I think I’m butchering it there. I looked at your serial number one, but I’m still struggling alot.
Thanks for your consideration! 
Nancy
1 Like
Me too!
I can take a look this weekend. I am sure I still have the BAQ.
1 Like
Sweet!
1 Like
I have this identical situation.
Can you share this BAQ, if you still have it?
Thank you.
Actually this would help me out a lot as well. Can anyone share the BAQ for this Job info?
thanks!
I see there is no marked solution - and periodically people keep finding this thread.
Is there already something existing in Epicor to essentially look at a Time Phase but instead of going part by part, I want to use a list of parts (say from a BOM or a job). This would support our planner and production teams as we are always jockeying around jobs to fulfill customer requests and navigate material constraint/labor availability. In other words, if I move job ABC up by 2 weeks because we have material and job 123 gets moved out because shortages, how will that impact the rest of my production line?
If there were already a tool within Epicor to help manage this perhaps it would eliminate the need to write the complicated BAQ in the first place. It would in my case, anyway.
I’m not sure any ERP system could provide an ‘out of the box’ production planning tool that would fit all - the number of variables and differing ways of working makes it unfeasible. Instead ERP systems provide the basics and/or building blocks giving the end user scope to develop more sophisctiated solutions that work in their niche.
I use Epicor in MTO, MTS and Mixed mode environments and find that it provides everything I need in terms of capapcity planning, scheduling and priority planning - but each set of solutions is very tailored. The JobMtl cte is absolutely part of that solution but it is only one piece of the jigsaw and only applicable in some scenarios.
I’d be happy to have some offline discussions on this topic to share experiences and hopefully assist and learn from others.
2 Likes
Hi! I did create an CTE with de MLP data to get the job mtls in multi level for all MTS jobs, but my query seems to stop the CTE after the 2nd level.
We work with sub assembly but they al go thru stock. so my goal is to see job mtl availability from the top job all the way down. But the query doesnt go all the way.
Is there any chance you send me your query as an example?
Here is a post in a thread that goes pretty deeply into examples where CTE is trying to do job bill of material type stuff. See if there are some examples in any of those threads that might help you get something working.
~If you want to recursively traverse the BOM you may need a CTE query?~ - Kinetic 202X - Epicor User Help Forum (epiusers.help)
1 Like