Help with BAQ and subquery?

Epicor ERP 10
1

Hi all,
I have been at this for a bit now and just cannot get this subquery deal down for the life of me. What began as what I thought would be a simple request turned into complete brain fry. The request was to get all MTJ actual prod hours for a parent job. So, JobHead → JobMtl → JobProb (related by TargetJobNum, TargetAssySeq, and TargetMtlSeq) → JobOper with a calculated field as sum(JobOper.ActualProdHours). Works great (using the legacy IsSummary on JobMtl, JobProd and JobOper) until I run into an MTJ that is making parts for multiple materials meaning there’s more than 1 job demand link in JobProd. When this happens, I get ActualProdHours * total job demand links in JobProd.
I am sure this can be done using a subquery and not using IsSummary but I cannot figure it out for the life of me. Any help would be greatly appreciated,

Thanks in advance,
Chris

2

@SimpsonGranco A couple of questions. Is this 9 or 10? Can you use the LLA values in JobAsmbl that Epicor builds and divide by the quantity for this top line?

3

10.2.400
I could but I would still get double or more labor hours due to JobProd filling demands for multiple job material demands.

4

So either way you will need to use Sum(JobOper.ActualProdHours) * (JobHead.ProdQty / JobProd.ProdQty)). In a sub query you would group JobProd by JobNum,TargetJobNum, TargetAssySeq, and TargetMtlSeq and then join back to the main query on the same fields.

5

Thank you. I am getting group by errors. Here is what I have so far.
TopLevel
image

Subquery
image

Subquery Display Fields and Group By
image

Top Level Display Fields

image
image700×259 4.96 KB

Error when analyzing: Column ‘Erp.JobOper.ActProdHours’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

6

Everything in the sub query except the group bys has to be a calculated field. Even dates and strings have to be something like MIN() or MAX()

1 Like
7

My calculated field is located in the TopLevel query:
sum(MTJHours.JobOper_ActProdHours) * (MTJHours.JobHeadMTJ_ProdQty / MTJHours.JobProd_ProdQty)

8

Like @gpayne mentioned your subquery is the one with the error.

You can make the subquery the top query. Get the result you want and then work on the main query.

I am guessing you want the JobOper_ActProdHours, JobHeadMTJ_ProdQty, and JobProd_ProdQty to be SUM. You will need to create a calculated field for those.

9

Maybe this visual will help? In the below example, MTJ 35577-11-100 is making a total of 2 but 1 for 35577-11/4/1220 and another for 35577-11/5/860 on a single operation that has .98 actual production hours recorded. However, if I sum on ActualProdHours, it will double to 1.96 actual hours. If I can just get it to return 1 JobProd record, I would be perfectly fine.

MTJ Parent Job Asm Mtl Act Prod Hrs JobHead Prod. Qty JobProd Prod Qty
35577-11-100 35577-11 4 1220 0.98 2 1
35577-11-100 35577-11 5 860 0.98 2 1
10

ahh.
still not sure what you are looking for.

If you click the group by on the fields ( JobOper_ActProdHours, JobHeadMTJ_ProdQty, and JobProd_ProdQty) will that work?

If not and there might be multiples, but you only want one of the multiples, then AVG would work.

11

I’d like to just return only one record (or first record found) from JobProd is all I want.

12

@SimpsonGranco move your calculation to the sub query and then sum that field in the top level.
You may also need another set of parens