Hi all,
We are working on a model which creates a make direct order and all the relevant materials that go into the job that fulfills the order are also non stock. Our BoMs are 6 levels deep so naturally making everything non-stock in the assembly area makes it all linked to each other. We want to do it this way so that we can find what sales order any job is allocated to.
I now want to create an updatable BAQ OR a quick search that is going to help us in firming the entire job tree once we have ensured that the materials and load vs capacity is ok.
I tried creating an updatable BAQ, which works fine when I run it in SSMS but times out when I’m running it in the BAQ as a test.
I also tried creating a quick search on job status maintenance so that I can pull all the jobs related to a sales order and firm them in one go through the job status maintenance, but the quick search requires a filter on the anchor to find the sales order that the job is linked to (which is the level 0 job) and then find all the relevant job links via recursion.
However, the quick search can only prompt to filter based on the entire query and not pass the parameter inside the anchor (before the union all section of the script), which then returns just one row which has sales order populated instead of the entire job tree.
Any ideas?
Expected output
Current Output (with BAQ)
Current Output (With QS)
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
with [Anchor] as
(select
[JobProd].[JobNum] as [JobProd_JobNum],
[JobHead].[StartDate] as [JobHead_StartDate],
[JobHead].[JobReleased] as [JobHead_JobReleased],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
[JobProd].[PartNum] as [JobProd_PartNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[JobProd].[ProdQty] as [JobProd_ProdQty],
[JobHead].[JobCompletionDate] as [JobHead_JobCompletionDate],
[JobProd].[OrderNum] as [JobProd_OrderNum],
[JobProd].[OrderLine] as [JobProd_OrderLine],
[JobProd].[OrderRelNum] as [JobProd_OrderRelNum],
[JobHead3].[StartDate] as [JobHead3_StartDate],
[JobProd].[TargetJobNum] as [JobProd_TargetJobNum],
[JobProd].[TargetAssemblySeq] as [JobProd_TargetAssemblySeq],
[JobProd].[TargetMtlSeq] as [JobProd_TargetMtlSeq],
(0) as [Calculated_lvl],
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[RevisionNum] as [JobHead_RevisionNum],
[JobHead].[JobCode] as [JobHead_JobCode],
[JobHead].[JobFirm] as [JobHead_JobFirm]
from Erp.JobProd as JobProd
inner join Erp.JobHead as JobHead on
JobProd.Company = JobHead.Company
and JobProd.JobNum = JobHead.JobNum
left outer join Erp.JobHead as JobHead3 on
JobProd.Company = JobHead3.Company
and JobProd.TargetJobNum = JobHead3.JobNum
where (JobProd.OrderNum = @OrderNum and JobProd.OrderLine = @OrderLine and JobProd.OrderRelNum = @OrderRelNum)
union all
select
[JobProd1].[JobNum] as [JobProd1_JobNum],
[JobHead1].[StartDate] as [JobHead1_StartDate],
[JobHead1].[JobReleased] as [JobHead1_JobReleased],
[JobHead1].[DueDate] as [JobHead1_DueDate],
[JobHead1].[ReqDueDate] as [JobHead1_ReqDueDate],
[JobProd1].[PartNum] as [JobProd1_PartNum],
[JobHead1].[PartDescription] as [JobHead1_PartDescription],
[JobProd1].[ProdQty] as [JobProd1_ProdQty],
[JobHead1].[JobCompletionDate] as [JobHead1_JobCompletionDate],
[JobProd1].[OrderNum] as [JobProd1_OrderNum],
[JobProd1].[OrderLine] as [JobProd1_OrderLine],
[JobProd1].[OrderRelNum] as [JobProd1_OrderRelNum],
[JobHead4].[StartDate] as [JobHead4_StartDate],
[JobProd1].[TargetJobNum] as [JobProd1_TargetJobNum],
[JobProd1].[TargetAssemblySeq] as [JobProd1_TargetAssemblySeq],
[JobProd1].[TargetMtlSeq] as [JobProd1_TargetMtlSeq],
(Anchor.Calculated_lvl+1) as [Calculated_lvl01],
[JobHead1].[JobNum] as [JobHead1_JobNum],
[JobHead1].[PartNum] as [JobHead1_PartNum],
[JobHead1].[RevisionNum] as [JobHead1_RevisionNum],
[JobHead1].[JobCode] as [JobHead1_JobCode],
[JobHead1].[JobFirm] as [JobHead1_JobFirm]
from Anchor as Anchor
inner join Erp.JobProd as JobProd1 on
Anchor.JobProd_JobNum = JobProd1.TargetJobNum
inner join Erp.JobHead as JobHead1 on
JobProd1.Company = JobHead1.Company
and JobProd1.JobNum = JobHead1.JobNum
inner join Erp.JobHead as JobHead4 on
JobProd1.Company = JobHead4.Company
and JobProd1.TargetJobNum = JobHead4.JobNum
where (JobProd1.TargetJobNum is not null))
select
[Anchor1].[Calculated_lvl] as [Calculated_lvl],
[Anchor1].[JobProd_JobNum] as [JobProd_JobNum],
[Anchor1].[JobHead_StartDate] as [JobHead_StartDate],
[Anchor1].[JobHead_JobReleased] as [JobHead_JobReleased],
[Anchor1].[JobHead_DueDate] as [JobHead_DueDate],
[Anchor1].[JobHead_ReqDueDate] as [JobHead_ReqDueDate],
[Anchor1].[JobHead_JobCompletionDate] as [JobHead_JobCompletionDate],
[Anchor1].[JobHead_PartNum] as [JobHead_PartNum],
[Anchor1].[JobHead_RevisionNum] as [JobHead_RevisionNum],
[Anchor1].[JobHead_PartDescription] as [JobHead_PartDescription],
[Anchor1].[JobProd_OrderNum] as [JobProd_OrderNum],
[Anchor1].[JobProd_OrderLine] as [JobProd_OrderLine],
[Anchor1].[JobProd_OrderRelNum] as [JobProd_OrderRelNum],
[Anchor1].[JobProd_PartNum] as [JobProd_PartNum],
[Anchor1].[JobProd_ProdQty] as [JobProd_ProdQty],
[Anchor1].[JobHead3_StartDate] as [JobHead3_StartDate],
[Anchor1].[JobProd_TargetJobNum] as [JobProd_TargetJobNum],
[Anchor1].[JobProd_TargetAssemblySeq] as [JobProd_TargetAssemblySeq],
[Anchor1].[JobProd_TargetMtlSeq] as [JobProd_TargetMtlSeq],
[JobHead2].[JobNum] as [JobHead2_JobNum],
[JobHead2].[PartNum] as [JobHead2_PartNum],
[JobHead2].[RevisionNum] as [JobHead2_RevisionNum],
[JobHead2].[JobCode] as [JobHead2_JobCode],
[Anchor1].[JobHead_JobCode] as [JobHead_JobCode],
[JobHead2].[JobFirm] as [JobHead2_JobFirm]
from Anchor as Anchor1
inner join Erp.JobHead as JobHead2 on
Anchor1.JobProd_JobNum = JobHead2.JobNum