JobMtl CTE

We have multi level bills of material and all Parts are Make to Stock throughout all the levels.
I am trying to produce a method of finding all Jobs required to fulfill all shortages based on the top level Job - eg a User has the ability to enter a JobNum and all other Jobs required to fulfill all shortages down all the levels are returned - has anyone achieved this within E10 or within SQL.

My preference would be to produce a SQL View so that I can produce outputs in varying formats for the Team.

Thank you in advance.

this link walks you trough making a CTE for a BOM. you could take this one, then join in job information to it to get a dashboard that looks at jobs related.

You can accomplish it in a BAQ within 1 hour, if you know how to use a CTE and a few Calculated Fields you can come up with something like this:

I can put in the Parent JobNum in the search and it will find it including all it’s children with some Customization Layer and using the LIKE %…% to search on the Hierarchy field.

Point being its possible with some effort. I used the following for inspiration:

I have produced a CTE Query but it takes a long time to execute.

I want to be able to output all Jobs required for a particular Customer within a working week for example.

You need to optimize your Query - It took me 12 Sub-Queries, JobProd, Recursion, Eliminate UNFIRMED Jobs and dozen other things.

mine executes within 6 seconds and I even go as far as to the 0 level job and find the Customer who is Demanding it and associating all the child jobs with it.

I can share the SerialRecursive BAQ with you which starts of with a similar Explosion/Recursion as my JobProd.
SerialRecursive.baq (9.1 KB)

Can this be achieved in SQL - I will demonstrate the output I am trying to achieve.

Yes, I did mine first in SQL #prototype #poc atleast following that article above

Once I got the gist of it I converted it into a BAQ, wasy easier than I thought.

Hopefully this image posts.

@hkeric.wci are your jobs going to stock? Or job to job?

All make to stock.

Mine are both. We have jobs going to stock and then we have where jobs are going to jobs. I use the Child Job functionality introduced in 10.1 - when it doesnt make sense to do a Assembly.

Use SQL subqueries with CTE.

Roberto, use the article(s) above and my entry point was the JobProd table, it has the Source and the Target in seperate columns, Including the Targeted Asm, Mtl


The point is that you do not cause exhaustive recursion, make sure you add this:

from Erp.JobProd as JobProd
 **where (JobProd.TargetJobNum = '')**

You want to START at the Job that is not a child-job, and has demand for WHSE or SO but not Another Job, then you from there you will recurse into the children.

Mine was slow too when I didn’t have:
where (JobProd.TargetJobNum = ‘’)

This runs quickly if you only ask it to take one ParentJob but if you don’t restrict the anchor it takes too long.

I don’t think JobProd hold details when Parts are make to stock?

It does

(CASE WHEN jpe.JobProd_OrderNum > 0 THEN
 	'SO: ' + CAST(jpe.JobProd_OrderNum as nvarchar(10)) + '-' + CAST(jpe.JobProd_OrderLine as nvarchar(5)) + '-' + CAST(jpe.JobProd_OrderRelNum as nvarchar(5))
 WHEN jpe.JobProd_TargetJobNum <> '' THEN
 WHEN jpe.JobProd_WarehouseCode <> '' THEN
 	'WH: ' + jpe.JobProd_WarehouseCode
 END) as [Calculated_Demand]

If its to stock it will have a Warehse, if its to SO it’ll have a SO and if its to another Job then it has the TargetJobNum

It uses the Demand Tabs on the Job Entry.

Go to SQL and Query the JobProd table and focus on 1 or 2 jobs and study it.

There is nothing in JobProd as I am not making Job to Job - all Jobs are make to stock.

So you have no Warehouse Demand? You just make the jobs, without any demand including the warehouse. Interesting, haven’t done it that way before. We always have a demand, including Warehse which we call Demand to Stock. Maybe someone else like @ckrusen knows a bit more about that type of job and best entry point or your attempted goal :slight_smile: