Dashboard Creation for Open Jobs w no activity

Epicor ERP 10
, ,
1

I have been racking my brain on trying to figure out how to get this to work. Ive tried many different methods using subqueries and different types (except and innersubquery).
I am wanting to create a dashboard that will display all Jobs where they are Open and Released and have no material transactions, labor transactions, or subcontract activity. Then I want to pull the The first operation and display the op code. I am doing this to show jobs that have been released but have not been started.

All help is appreciated!

I know this is chaotic but this was as close as I could get and it’s not very quick with returns:
/*

  • 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
[JobHead].[JobNum] as [JobHead_JobNum],
[JobOps].[Calculated_FirstOpSeq] as [Calculated_FirstOpSeq]
from Erp.JobHead as JobHead
inner join (select
[JobOper].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
(min(JobOper.OprSeq)) as [Calculated_FirstOpSeq]
from Erp.JobOper as JobOper
inner join Erp.JobOper as JobOper1 on
JobOper.Company = JobOper1.Company
and JobOper.JobNum = JobOper1.JobNum
and JobOper.OprSeq = JobOper1.OprSeq
group by [JobOper].[Company],
[JobOper].[JobNum]) as JobOps on
JobHead.Company = JobOps.JobOper_Company
and JobHead.JobNum = JobOps.JobOper_JobNum
where (JobHead.JobClosed = FALSE and JobHead.JobReleased = TRUE)
and (not JobHead.JobNum = ANY (select LaborDtl_JobNum from ((select
[LaborDtl].[JobNum] as [LaborDtl_JobNum]
from Erp.LaborDtl as LaborDtl)) as LaborTrans) and not JobHead.JobNum = ANY (select PartTran_JobNum from ((select
[PartTran].[JobNum] as [PartTran_JobNum]
from Erp.PartTran as PartTran)) as PartTrans))

2

Welcome to the forum @caseyasue!

If you can export your BAQ and attach it in here, I’ll take a look at what you have going on. I think what you’re trying to do is possible and should be relatively straight forward so seeing your BAQ will help me to see ways to optimize it.

3

I’ve rewrote it and it is much faster.
CASSandbox.baq (33.4 KB)

Before I was using multiple subqueries. Now I am using only one. The subquery is using subquery criteria where jobnum is null in the connected tables.

Appreciate your help and thanks for the welcome!

The biggest issue I am having now is getting the OpCode associated with the OperSeq that is equal to the calculated field of min(JobOper.OpSeq). No matter how I’ve tried connecting this it returns duplicate or erroneous information.

4

I finally figured it out. Subassemblies were the culprit. I thought it was giving me incorrect information as some rows were duplicated and when I looked at the Job Tracker I was completely missing the fact that those jobs that were duplicates had subassemblies and weren’t really duplicates. Since I rewrote the BAQ the results are returning in around 1.5 seconds instead of 20ish previously. Finally banged my head on the brick wall long enough I was able to see the light on the other side :slight_smile:

CASSandbox.baq (38.2 KB)

2 Likes
5

@caseyasue Good that you got your way solved. Another way would be to use JobAsmbl which is where Epicor stores estimated and actual costs. If any of the TLA and possibly the LLA fields had values then material, labor or subcontract had been applied.

1 Like