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.
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.
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.
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.
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.
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 = ‘’)
(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
jpe.JobProd_TargetJobNum
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.
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