BAQ for linked and unlinked jobs

[NateS (Nate Schoonover)]

Good morning,
I am setting up a relatively simple BAQ. I want to see:

• All the open sales order releases
• All the open jobs linked to releases
• All the open jobs not linked to releases

I got the first two working in this BAQ. However, I want to find a way to add that last part to see if there are jobs out there that are make to stock, or otherwise not linked to a sales order. Here is the simple BAQ I have:

/*
 * 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.
 */
 
select 
	[Orders].[OrderDtl_PartNum] as [OrderDtl_PartNum],
	[Orders].[OrderRel_OrderNum] as [OrderRel_OrderNum],
	[Orders].[OrderRel_OrderLine] as [OrderRel_OrderLine],
	[Orders].[OrderRel_OrderRelNum] as [OrderRel_OrderRelNum],
	[Orders].[OrderRel_ReqDate] as [OrderRel_ReqDate],
	[Orders].[OrderRel_OurReqQty] as [OrderRel_OurReqQty],
	[Orders].[Calculated_Shipped] as [Calculated_Shipped],
	[Jobs].[JobHead_JobNum] as [JobHead_JobNum],
	[Jobs].[JobHead_PartNum] as [JobHead_PartNum],
	[Jobs].[JobHead_StartDate] as [JobHead_StartDate],
	[Jobs].[JobHead_DueDate] as [JobHead_DueDate],
	[Jobs].[JobAsmbl_AssemblySeq] as [JobAsmbl_AssemblySeq],
	[Jobs].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
	[Jobs].[JobOper_OprSeq] as [JobOper_OprSeq],
	[Jobs].[JobOper_StartDate] as [JobOper_StartDate],
	[Jobs].[JobOper_DueDate] as [JobOper_DueDate],
	[Jobs].[JobOper_EstSetHours] as [JobOper_EstSetHours],
	[Jobs].[JobOper_EstProdHours] as [JobOper_EstProdHours],
	[Jobs].[JobOper_RunQty] as [JobOper_RunQty],
	[Labor].[Calculated_SetHours] as [Calculated_SetHours],
	[Labor].[Calculated_ProdHours] as [Calculated_ProdHours]
from  (select 
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	(OrderRel.OurJobShippedQty+ OrderRel.OurStockShippedQty) as [Calculated_Shipped],
	[OrderDtl].[PartNum] as [OrderDtl_PartNum]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = true  )

where (OrderHed.OpenOrder = true))  as Orders
left outer join Erp.JobProd as JobProd on 
	Orders.OrderRel_OrderNum = JobProd.OrderNum
	and Orders.OrderRel_OrderLine = JobProd.OrderLine
	and Orders.OrderRel_OrderRelNum = JobProd.OrderRelNum
left outer join  (select 
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[StartDate] as [JobHead_StartDate],
	[JobHead].[DueDate] as [JobHead_DueDate],
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[JobOper].[OprSeq] as [JobOper_OprSeq],
	[JobOper].[EstSetHours] as [JobOper_EstSetHours],
	[JobOper].[EstProdHours] as [JobOper_EstProdHours],
	[JobOper].[RunQty] as [JobOper_RunQty],
	[JobOper].[StartDate] as [JobOper_StartDate],
	[JobOper].[DueDate] as [JobOper_DueDate]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on 
	JobHead.Company = JobAsmbl.Company
	and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on 
	JobAsmbl.Company = JobOper.Company
	and JobAsmbl.JobNum = JobOper.JobNum
	and JobAsmbl.AssemblySeq = JobOper.AssemblySeq
inner join Erp.JobOpDtl as JobOpDtl on 
	JobOper.Company = JobOpDtl.Company
	and JobOper.JobNum = JobOpDtl.JobNum
	and JobOper.AssemblySeq = JobOpDtl.AssemblySeq
	and JobOper.OprSeq = JobOpDtl.OprSeq
	and ( JobOpDtl.OpDtlSeq = 10  )

where (JobHead.JobClosed = false))  as Jobs on 
	JobProd.JobNum = Jobs.JobHead_JobNum
left outer join  (select 
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[LaborDtl].[AssemblySeq] as [LaborDtl_AssemblySeq],
	[LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
	(sum(iif(LaborDtl.LaborType = 'S', LaborDtl.LaborHrs,0) )) as [Calculated_SetHours],
	(sum(iif(LaborDtl.LaborType = 'P', LaborDtl.LaborHrs,0) )) as [Calculated_ProdHours]
from Erp.LaborDtl as LaborDtl
group by [LaborDtl].[JobNum],
	[LaborDtl].[AssemblySeq],
	[LaborDtl].[OprSeq])  as Labor on 
	Jobs.JobHead_JobNum = Labor.LaborDtl_JobNum
	and Jobs.JobAsmbl_AssemblySeq = Labor.LaborDtl_AssemblySeq
	and Jobs.JobOper_OprSeq = Labor.LaborDtl_OprSeq

I think I have to use union on a subquery or something. What do you think?
Thank you!
Nate

[jkane (John Kane)]

Yes, you will have to use a Union. You should be able to get away with a Union and not a Union All, but I would test both.

Also, you will need a CTE.

[NateS (Nate Schoonover)]

@jkane

Also, you will need a CTE.

Noooo!!!

[jkane (John Kane)]

It is a simple one. Turn the query you created into a CTE, create a Union query and make sure you have all of the same fields. On the unlinked one, you will have to default the fields that don’t exist to ‘0’ by making them calculated fields.

[NateS (Nate Schoonover)]

Thats a lot of empty fields. hmmmm… I can see why it would be beneficial to minimize the number of empty columns. I’ll have to look closer at this. Thank you!

[jkane (John Kane)]

If you don’t want to do a Union, hold on and let me review your query.

[jkane (John Kane)]

Table 1 should be JobHead
Table 2 should be JobProd
Do a Left Outer Join from 1 to 2.
Filter on JobHead.JobClosed to only get open jobs.

This will give you ALL open jobs and their demand link or lack thereof. You can then add in more tables and do left outer joins to them from JobProd to get any additional data you may want.

[NateS (Nate Schoonover)]

I may end up needing the union and CTE after all. I have to start with orders as my base. Then I want the linked and unlinked jobs.

[jkane (John Kane)]

Why do you have to start with orders?

[NateS (Nate Schoonover)]

The underlying demand shows us where we will need work to be performed. Once we make a job out of the demand, we can use the setup and production estimates for each operation to see the length of the load. As long as we have our demand tied to a job, then we can estimate the future load for a given resource. However, if they haven’t linked the demand to a job (and engineered it) then we can’t see any details about the need for capacity. I want to make sure I can see all the open sales order releases, regardless of if they are linked to a job. That way I can either point to the issue being the sales order is not linked to a job, or that a job simply hasn’t been created for a sales order yet.

I understand I won’t be able to estimate load by resource group, but at least I could see that I have an order for 100 parts that doesn’t have a job created yet.

[jkane (John Kane)]

If you need to know if a Release is linked to a Job, there is a field for that in the OrderRel table.

[Hally (Simon Hall)]

@jkane

Why do you have to start with orders?

I was thinking the same thing… Then I saw “All Open Sales Order releases” and of course all open jobs not linked to release…

One other way I can think of doing that would be to have multiple sub queries all with the same number of columns then a top level that allowed had the same columns also and load everything into that… so you could have a row for Open Sales order releases and a row for open jobs… then populate the linked columns where appropriate… Hope I am explaining myself clearly.

[NateS (Nate Schoonover)]

@jkane

if a Release is linked to a Job, there is a field for that in the OrderRel table

OrderRel.Linked is only for intercompany POs as far as I know. I have to link to JobProd to tell if it linked to a job, right?

[jkane (John Kane)]

My bad. I must have been thinking of in screen dataset. I remember I did something with the related Job starting from the OrderRel, but it must have been using the dataset.

[kananga (John K)]

@NateS

• All the open sales order releases
• All the open jobs linked to releases
• All the open jobs not linked to releases

Been there done this… It’s possible to do this without a union. Future you will appreciate if you use unions.

Without unions you’d use full outer joins if you want to return all the jobs and all the order-line-releases regardless of if they’re linked to one another or not. Then a bunch of your select fields compound into case statements and null handling riddles for future you to figure out.

With unions, you can partition the madness into bites that can be reasoned about. Select all of JobProd where there’s a stock demand link and match up related stock detail. Same for job demand and order demand. Don’t forget the orphan jobs with no demand link at all. Union in any order you like. Add some calculated field placeholders to get your union’d fields lined up, because attemptimg to make one field do different things at multiple levels of a union may devour your soul.

[edit] PS: consider how jobs with multiple demand links will be received by the query’s audience if that’s not normal and expected. It can happen, therefore it will happen.

[LarsonSolutions (Bruce Larson)]

This isn’t the whole query - but an open join would give you all open jobs linked to orders or others.

[NateS (Nate Schoonover)]

@kananga

With unions, you can partition the madness into bites that can be reasoned about.

I have created three subqueries with the same number of fields each. The OpenOrder subquery shows the open releases, and has some null fields as placeholders to match the fields in the other two subqueries.

The OpenJobLink and OpenJobUnlinked subqueries show the jobs that are (and are not) linked to releases. Im trying to union them but I don’t use union ever anymore so I am missing something.

/*
 * 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.
 */
 
union all
(select  * 
from  (select 
	[OrderDtl].[PartNum] as [OrderDtl_PartNum],
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate],
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	(OrderRel.OurJobShippedQty+ OrderRel.OurStockShippedQty) as [Calculated_Shipped],
	(null) as [Calculated_JobNum],
	(null) as [Calculated_PartNum],
	(null) as [Calculated_JobStartDate],
	(null) as [Calculated_JobDueDate],
	(null) as [Calculated_AsmSeq],
	(null) as [Calculated_AsmPart],
	(null) as [Calculated_OprSeq],
	(null) as [Calculated_EstSetHours],
	(null) as [Calculated_EstProdHours],
	(null) as [Calculated_RunQty],
	(null) as [Calculated_OpStartDate],
	(null) as [Calculated_OpDueDate]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
	OrderHed.Company = OrderDtl.Company
	and OrderHed.OrderNum = OrderDtl.OrderNum
	and ( OrderDtl.OpenLine = true  )

inner join Erp.OrderRel as OrderRel on 
	OrderDtl.Company = OrderRel.Company
	and OrderDtl.OrderNum = OrderRel.OrderNum
	and OrderDtl.OrderLine = OrderRel.OrderLine
	and ( OrderRel.OpenRelease = true  )

where (OrderHed.OpenOrder = true))  as OpenOrders
cross join  (select 
	(null) as [Calculated_PartNum],
	(null) as [Calculated_OrderNum],
	(null) as [Calculated_OrderLine],
	(null) as [Calculated_OrderRel],
	(null) as [Calculated_ReqDate],
	(null) as [Calculated_OurReqQty],
	(null) as [Calculated_Shipped],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[StartDate] as [JobHead_StartDate],
	[JobHead].[DueDate] as [JobHead_DueDate],
	[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
	[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
	[JobOper].[OprSeq] as [JobOper_OprSeq],
	[JobOper].[EstSetHours] as [JobOper_EstSetHours],
	[JobOper].[EstProdHours] as [JobOper_EstProdHours],
	[JobOper].[RunQty] as [JobOper_RunQty],
	[JobOper].[StartDate] as [JobOper_StartDate],
	[JobOper].[DueDate] as [JobOper_DueDate]
from Erp.JobProd as JobProd
inner join Erp.JobHead as JobHead on 
	JobProd.Company = JobHead.Company
	and JobProd.JobNum = JobHead.JobNum
	and ( JobHead.JobClosed = false  )

inner join Erp.JobAsmbl as JobAsmbl on 
	JobHead.Company = JobAsmbl.Company
	and JobHead.JobNum = JobAsmbl.JobNum
inner join Erp.JobOper as JobOper on 
	JobAsmbl.Company = JobOper.Company
	and JobAsmbl.JobNum = JobOper.JobNum
	and JobAsmbl.AssemblySeq = JobOper.AssemblySeq)  as OpenJobsLinked
cross join  (select 
	(null) as [Calculated_PartNum],
	(null) as [Calculated_OrderNum],
	(null) as [Calculated_OrderLine],
	(null) as [Calculated_OrderRel],
	(null) as [Calculated_ReqDate],
	(null) as [Calculated_OurReqQty],
	(null) as [Calculated_Shipped],
	[JobHead1].[JobNum] as [JobHead1_JobNum],
	[JobHead1].[PartNum] as [JobHead1_PartNum],
	[JobHead1].[StartDate] as [JobHead1_StartDate],
	[JobHead1].[DueDate] as [JobHead1_DueDate],
	[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
	[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
	[JobOper1].[OprSeq] as [JobOper1_OprSeq],
	[JobOper1].[EstSetHours] as [JobOper1_EstSetHours],
	[JobOper1].[EstProdHours] as [JobOper1_EstProdHours],
	[JobOper1].[RunQty] as [JobOper1_RunQty],
	[JobOper1].[StartDate] as [JobOper1_StartDate],
	[JobOper1].[DueDate] as [JobOper1_DueDate]
from Erp.JobProd as JobProd1
inner join Erp.JobHead as JobHead1 on 
	JobProd1.Company = JobHead1.Company
	and JobProd1.JobNum = JobHead1.JobNum
	and ( JobHead1.JobClosed = false  )

inner join Erp.JobAsmbl as JobAsmbl1 on 
	JobHead1.Company = JobAsmbl1.Company
	and JobHead1.JobNum = JobAsmbl1.JobNum
inner join Erp.JobOper as JobOper1 on 
	JobAsmbl1.Company = JobOper1.Company
	and JobAsmbl1.JobNum = JobOper1.JobNum
	and JobAsmbl1.AssemblySeq = JobOper1.AssemblySeq
where (not JobHead1.JobNum in (select JobProd2_JobNum from (select 
	[JobProd2].[JobNum] as [JobProd2_JobNum]
from Erp.JobProd as JobProd2) as JobFilter)))  as OpenJobsUnlinked)

I know I don’t have the top or CTE in place yet. I am not sure how those will work just yet.
I appreciate your input!

[Banderson (Brandon Anderson)]

Go to the subquery list tab. You can change the order of the queries there.

Also, I don’t know why @jkane says you need a CTE. Most case, a subquery ,or a CTE functions essentially the same.

[NateS (Nate Schoonover)]

@Banderson

You can change the order of the queries there.

Yes, but to what end? What order should they be in?

[Banderson (Brandon Anderson)]

Unions first. Then CTE (if you are using them) then the top level.

The “union” type will bring the results of that query into the next previous subqquery (whether it’s another subquery, CTE or top level).

Don’t forget about parenthesis in that list if you are grouping subs together.

Edited: wasn’t correct.