JobHead and LaborDtl BAQ help

Epicor ERP 10
1

I have a BAQ I am makeing to pull in all firm jobs and find the start date of that job based on when the first labor transaction has taken place. I did this with the top query as Jobhead and a subquery that has Labordtl and a calculated field of Min on labordate. I then have a left join to return all rows from Jobhead, so i should see jobs that have no labor record. This is where i get an issue, it is only returning rows with a labor record.

This is what the sql looks like from the baq.

select 
	[JobHead].[ProjectID] as [JobHead_ProjectID],
	[JobHead].[PhaseID] as [JobHead_PhaseID],
	[JobProd2].[OrderNum] as [JobProd2_OrderNum],
	[JobHead].[ProdTeamID] as [JobHead_ProdTeamID],
	[JobHead].[JobNum] as [JobHead_JobNum],
	[JobHead].[PersonID] as [JobHead_PersonID],
	[JobHead].[ProdCode] as [JobHead_ProdCode],
	[JobHead].[PartNum] as [JobHead_PartNum],
	[JobHead].[PartDescription] as [JobHead_PartDescription],
	[JobHead].[RevisionNum] as [JobHead_RevisionNum],
	[JobHead].[StartDate] as [JobHead_StartDate],
	[SubQuery2].[Calculated_FirstLaborDate] as [Calculated_FirstLaborDate],
	[JobHead].[DueDate] as [JobHead_DueDate],
	[SubQuery4].[Calculated_LastOpDate] as [Calculated_LastOpDate],
	[SubQuery4].[LaborDtl1_Complete] as [LaborDtl1_Complete],
	[SubQuery3].[ShipHead_ShipDate] as [ShipHead_ShipDate],
	[SubQuery6].[PartTran_TranDate] as [PartTran_TranDate],
	[SubQuery6].[PartTran_TranType] as [PartTran_TranType],
	[JobHead].[JobComplete] as [JobHead_JobComplete],
	[JobHead].[JobCompletionDate] as [JobHead_JobCompletionDate],
	[JobHead].[ClosedDate] as [JobHead_ClosedDate]
from Erp.JobHead as JobHead
left outer join  (select 
	[JobHead1].[JobNum] as [JobHead1_JobNum],
	[LaborDtl].[Company] as [LaborDtl_Company],
	(min(LaborDtl.CreateDate)) as [Calculated_FirstLaborDate]
from Erp.LaborDtl as LaborDtl
right outer join Erp.JobHead as JobHead1 on 
	JobHead1.Company = LaborDtl.Company
	and JobHead1.JobNum = LaborDtl.JobNum
group by [JobHead1].[JobNum],
	[LaborDtl].[Company])  as SubQuery2 on 
	JobHead.JobNum = SubQuery2.JobHead1_JobNum
left outer join  (select 
	[JobProd].[JobNum] as [JobProd_JobNum],
	[JobProd].[OrderNum] as [JobProd_OrderNum],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	[JobProd].[Company] as [JobProd_Company]
from Erp.JobProd as JobProd
left outer join Erp.ShipDtl as ShipDtl on 
	JobProd.Company = ShipDtl.Company
	and JobProd.OrderNum = ShipDtl.OrderNum
	and JobProd.OrderLine = ShipDtl.OrderLine
	and JobProd.OrderRelNum = ShipDtl.OrderRelNum
inner join Erp.ShipHead as ShipHead on 
	ShipDtl.Company = ShipHead.Company
	and ShipDtl.PackNum = ShipHead.PackNum
	and ( ShipHead.ShipStatus in ('INVOICED', 'SHIPPED')  )

group by [JobProd].[JobNum],
	[JobProd].[OrderNum],
	[ShipHead].[ShipDate],
	[JobProd].[Company])  as SubQuery3 on 
	SubQuery3.JobProd_JobNum = JobHead.JobNum
	and SubQuery3.JobProd_Company = JobHead.Company
left outer join  (select 
	[LaborDtl1].[Complete] as [LaborDtl1_Complete],
	(Max (LaborDtl1.ClockInDate)) as [Calculated_LastOpDate],
	[LaborDtl1].[JobNum] as [LaborDtl1_JobNum],
	[LaborDtl1].[OprSeq] as [LaborDtl1_OprSeq],
	[LaborDtl1].[AssemblySeq] as [LaborDtl1_AssemblySeq]
from Erp.LaborDtl as LaborDtl1
group by [LaborDtl1].[Complete],
	[LaborDtl1].[JobNum],
	[LaborDtl1].[OprSeq],
	[LaborDtl1].[AssemblySeq])  as SubQuery4 on 
	JobHead.JobNum = SubQuery4.LaborDtl1_JobNum
inner join  (select 
	(Max(JobOper.OprSeq)) as [Calculated_Maxoper],
	[JobOper].[JobNum] as [JobOper_JobNum],
	[JobOper].[AssemblySeq] as [JobOper_AssemblySeq]
from Erp.JobOper as JobOper
where (not JobOper.OprSeq in (9998, 9999))
group by [JobOper].[JobNum],
	[JobOper].[AssemblySeq])  as SubQuery5 on 
	SubQuery4.LaborDtl1_JobNum = SubQuery5.JobOper_JobNum
	and SubQuery4.LaborDtl1_AssemblySeq = SubQuery5.JobOper_AssemblySeq
	and SubQuery4.LaborDtl1_OprSeq = SubQuery5.Calculated_Maxoper
left outer join  (select 
	[JobProd1].[JobNum] as [JobProd1_JobNum],
	[PartTran].[TranType] as [PartTran_TranType],
	[PartTran].[TranDate] as [PartTran_TranDate]
from Erp.JobProd as JobProd1
inner join Erp.PartTran as PartTran on 
	JobProd1.Company = PartTran.Company
	and JobProd1.JobNum = PartTran.JobNum
	and ( PartTran.TranType in ('MFG-CUS', 'MFG-MTL', 'MFG-STK')  )

group by [JobProd1].[JobNum],
	[PartTran].[TranType],
	[PartTran].[TranDate])  as SubQuery6 on 
	JobHead.JobNum = SubQuery6.JobProd1_JobNum
left outer join Erp.JobProd as JobProd2 on 
	JobHead.Company = JobProd2.Company
	and JobHead.JobNum = JobProd2.JobNum
where (JobHead.JobFirm = 1)
group by [JobHead].[ProjectID],
	[JobHead].[PhaseID],
	[JobProd2].[OrderNum],
	[JobHead].[ProdTeamID],
	[JobHead].[JobNum],
	[JobHead].[PersonID],
	[JobHead].[ProdCode],
	[JobHead].[PartNum],
	[JobHead].[PartDescription],
	[JobHead].[RevisionNum],
	[JobHead].[StartDate],
	[SubQuery2].[Calculated_FirstLaborDate],
	[JobHead].[DueDate],
	[SubQuery4].[Calculated_LastOpDate],
	[SubQuery4].[LaborDtl1_Complete],
	[SubQuery3].[ShipHead_ShipDate],
	[SubQuery6].[PartTran_TranDate],
	[SubQuery6].[PartTran_TranType],
	[JobHead].[JobComplete],
	[JobHead].[JobCompletionDate],
	[JobHead].[ClosedDate]
2

This is my Subquery
this is joined on jobhead on Jobnum.
i also have labordtl left outer joined on jobhead in this sub query. ive tried with just LaborDtl in the subquery and i get the same result…

image
image1571×585 35.4 KB

3

Can you upload the BAQ?

4

JobStartVariance.baq (79.5 KB)
.

5

It doesn’t look like Subquery 2 is causing the issue, it looks like it’s coming from Subquery 4/5. Are you hitting the max rows returned?

6

4/5 is to get the final operation and the last labor transaction against that operation as a way to measure “Ready to ship”

7

I think it’s the inner join on 4-5 that’s doing it, but I see that’s how you’re filtering out for the final op and it’s respective ship date(Edit last labor date sorry). You may need to switch up the order of joins, and then do outer joins.

8

I wouldn’t think that would matter because i have the outer join on 4 and Jobhead.

9

The inner join goes after that outer join, and it’s filtering out the jobs with no labor date records (since they have 0 operations with labor records). You should just be able to switch subquery 4 and 5 around, and you’ll be able to outer join (and still filter out for the final op)

image ‘’

I deleted out the other stuff so it wouldn’t time out on me.

10

Thanks. That worked.

1 Like