How would you write this query?

Nice, glad to hear it! 

Yeah, something is screwy with the group today.
Here's the problem that I am trying to solve.

I just mass added a bunch of production standards for the laser cutting operation. Don't ask me how I figured out what the standard is, it's barely logical but I had to use what I have. But , basically I wrote a formula in a query that spits out a production estimate, and then DMT'd the results in. (into a duplicated test environment in case you're wondering)

Now I need to compare some actuals and this estimate on jobs that have already been completed to see how I need to adjust my formula. I can do this using the job status dashboard and the view cost part of method tracker, but it's very tedious to get the information out, and after I adjust my formula I will have to redo it all over.

Because of the nature of my estimates, we are shooting for a aggregate average to be close to actual to start with. We are using labor=burden for all of the cost in the facility. Because of the nature of the laser cutting operation, the labor that is on a specific part does not correlate to how long the part actually takes to cut. Basically it takes the same amount of time to nest and pull a small part that takes 30 seconds to cut as it does for a part that takes 10 mins to cut, and they work on multiple parts at a time, and sometimes the clock into a dummy operation on the top level instead of the individual parts. So I can't use individual parts to compare. However the larger total for a job (we have large jobs that go through with all of the pieces together) should be roughly the total labor for all of the parts in the job. So if I add up the actual labor time for all Laser operations, and then compare that to all of the labor estimates for laser operations and add them up by job number/top level assembly number. I should be able to test whether or not my estimates are on par with actuals on a macro level.

What I would like to do is created a query/dashboard that does this comparison based on the job number and top level part number. To get the actual by job, it's pretty easy using the labor detail table. The problem comes when I need to get the estimate (which isn't on the job remember because I just changed all of the standards) from the PartOpr table based on the BOM from the top level part.

The first solution that comes to mind is a CTE query. The issue with that, is that so far, I have always written them with a parameter. I would like to have all the query grab the BOM for all of the part numbers that are on the job header and then give the total for the laser operation standard based on that. Will a CTE work with a join and the next level up instead of a parameter?

If you have any other good ideas of structure this query let me know.

Brandon


I would think just a normal inner sub query would work.

Top level:
JobHead inner join LaborDtl
SUM(LaborHrs)
Group

Sub:
PartOpr
SUM(ProdStrandard)
Group

join the two by company/part/rev?
I had to think about it for a second, but yeah that works! Much simpler than what I was thinking. Thanks. I didn't think of just matching part numbers to create the BOM on the PartOpr side.

Thanks
So I'm having a couple of issues with my query. I had to add some tables for cross referencing since the labor detail only has sequences with no part numbers and no operation codes. So on my bottom level I have JobHead, JobOper, JobAsmbl and LaborDTL all tied together to get the total actual labor and quantities on a job that has lasers as the operation code. That works fine. I get a line per part number with the laser operation and a labor record on the part.

On the next level up I have the bottom level tied to the PartOp table with a left join since for some jobs they are clocking into a laser operation that is added on the top level assembly simply to collect time when they are working on a job, but not necessarily a specific part. I need to add up that time with the job for actual, but there won't be a corresponding estimate to get. That seems to work and I get the lines for a job and if there is labor on the top level, it has nulls for the estimate columns, which I would expect.

Now on the top level, I want to sum up the actual labor, and the estimated labor lines from the second inner sub. For some reason, for the jobs with a null line in the estimates, the sum comes back blank. I'm grouping by job number and top level part, which should work since they are the same in the middle level query. I tried using the isnull command to get it to ignore the blank line. I also tried to create a calculated field that would add the zero if the line was null for the estimate, but when I sum the lines, it gives me a zero, even though if I look at the returns from the mid level it has multiple lines with values in them.

I'm sure that I am missing something simple. Any help would be appreciated.


Here's the code for the query if that would be helpful. I have some criteria in place to try to make debugging easier, so just ignore those.

select
    [job_and_estimated].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
    (sum( isnull( job_and_estimated.Calculated_Total_labor_hrs,0 ))) as [Calculated_Actual_labor],
    ((case when Actual_labor <> 0 then (estimated - Actual_labor)/Actual_labor else 0 end)) as [Calculated_percent_off],
    (sum(job_and_estimated.Calculated_multiplied_op_hours)) as [Calculated_estimated]
from  (select
    [Job_Labor].[JobAsmbl_JobNum] as [JobAsmbl_JobNum],
    [Job_Labor].[JobHead_PartNum] as [JobHead_PartNum],
    [Job_Labor].[JobAsmbl_PartNum] as [JobAsmbl_PartNum],
    [Job_Labor].[LaborDtl_OprSeq] as [LaborDtl_OprSeq],
    [Job_Labor].[JobOper_OpCode] as [JobOper_OpCode],
    [Job_Labor].[Calculated_total_qty] as [Calculated_total_qty],
    [Job_Labor].[Calculated_Total_labor_hrs] as [Calculated_Total_labor_hrs],
    [PartOpr].[PartNum] as [PartOpr_PartNum],
    [PartOpr].[OpCode] as [PartOpr_OpCode],
    [PartOpr].[EstProdHours] as [PartOpr_EstProdHours],
    (isnull((PartOpr.EstProdHours * isnull(Job_Labor.Calculated_total_qty,0) ) , 0)) as [Calculated_multiplied_op_hours]
from  (select
    [JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
    [JobHead].[PartNum] as [JobHead_PartNum],
    [JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
    [LaborDtl].[OprSeq] as [LaborDtl_OprSeq],
    [JobOper].[OpCode] as [JobOper_OpCode],
    (sum( LaborDtl.LaborQty )) as [Calculated_total_qty],
    (sum( LaborDtl.LaborHrs )) as [Calculated_Total_labor_hrs]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.LaborDtl as LaborDtl on
    JobAsmbl.Company = LaborDtl.Company
And
    JobAsmbl.JobNum = LaborDtl.JobNum
And
    JobAsmbl.AssemblySeq = LaborDtl.AssemblySeq

inner join Erp.JobOper as JobOper on
    JobAsmbl.Company = JobOper.Company
And
    JobAsmbl.JobNum = JobOper.JobNum
And
    JobAsmbl.AssemblySeq = JobOper.AssemblySeq
 and ( JobOper.OpCode = 'lasers'  and JobOper.JobComplete = true  )

inner join Erp.JobOper as JobOper and
Your query cuts off.

I don't have any data similar to your setup but all I can really suggest is simplifying the query as much as possible until it works and then adding slowly until you find the issue.  

but just to give it a shot 

 [PartOpr].[EstProdHours] as [PartOpr_EstProdHours],
    (isnull((PartOpr.EstProdHours * isnull(Job_Labor.Calculated_total_qty,0) ) , 0)) as [Calculated_multiplied_op_hours]

maybe try is null on estprodhours on the first line and I dont think this would make a difference but try

 (isnull((PartOpr.EstProdHours, 0) * isnull(Job_Labor.Calculated_total_qty,0))) 

I am wondering if it’s possible to have conditional operations routing in Epicor E10.

We have a requirement from our customer for each serially tracked board got thru every operation without skipping an operation. So far it’s straight forward, it gets complicated when one of the operations is Inspection and if the parts fail they need to go forward one operation: Rework, but the ones which did not fail they will jump over the Rework operation and go to the next manufacturing operation.

For example:

 

Pass GoTo

Fail GoTo

100

Board Prep

110

110

Inspection

130

120

120

Rework

110

130

Paste 1

140

140

Inspection

160

150

150

Rework

140

160

Soldier 2

170

 

 

 

MIKE TONOYAN | IT Director

MTonoyan@...

Tel +1.818.734.6511  |  Cell +1.818.397.8515

 

_________________________________________________________________________________ Notice: This email transmission may contain confidential information and is intended only for the person(s) named. Any use, copying or disclosure to/by any other person(s) is strictly prohibited. By accepting this information, recipients confirm that they understand and will comply with all applicable ITAR and EAR requirements. _________________________________________________________________________________
Sorry, I meant to reply but it didn't seem to take. I had an issue with a criteria on the query which is why it wasn't working. I fixed that and now it's working correctly.