Material costs by operation BAQ

Kinetic ERP
1

Hello,

We are looking to build a BAQ that includes the material cost by operation.

The production detail report includes material costs separated by part which is a start. Is it possible to see the what tables/fields are being used to populate the actual material costs? Or is there an easier way to go about getting the material costs by operation?

image
image1594Ă—265 58.1 KB

2

Actual material costs are recorded in the PartTran table. That gives you the job number, assembly seq, and material seq. From there you can join to the JobMtl table and look at the related operation to get the costs by operation.

3

Thanks, this is exactly what I am looking for.

My only issue is I do not have any jobs or assemblies populate when I press analyze.

select
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[JobNum] as [PartTran_JobNum],
[PartTran].[AssemblySeq] as [PartTran_AssemblySeq]
from Erp.PartTran as PartTran

image
image420Ă—858 56.5 KB

4

Part tran has a ton of stuff if it. So filter down to known job number to get your BAQ to return something. If you run it wide open, it’s gonna return a ton of unrelated stuff.

5

The scroll wheel is my friend…

Thanks!

6

Any clue why I’m not getting any values under assembly?

7

Not without seeing some of your data. What does your job look like? 0 is a valid sequence number.

8

Here is a standard MOM:

image
image278Ă—523 59.7 KB

9

Yup. Your assembly seq is 0. So 0 is correct. What are you expecting to see?

image
image321Ă—523 47 KB

10

Ahh, apologies - I’m looking to see the related operation which needs to be pulled from the joined JobMtl table?

11

Correct. Before you get that far though, take a look and make sure your materials are related to operations. That step isn’t strictly required. So you might be hosed by your data entry.

image
image760Ă—422 32.7 KB

12

All of our materials are linked to an operation via “related operation”.

In phrase build there is no automatic link created between PartTran and JobMtl. Is there a middle or shared table I need to bring in?

13

no… Not everything is automatic. Stop and think for a second of you how you would need to join those two tables, should be pretty straight forward.

14

I’ve already manually linked them was just curious if I was missing something…

15

When I create manual links, I try to use an index and choose as many fields from left to right in my BAQ linkage:

image
image870Ă—527 27.2 KB

16

For analyzing job - I always recommend the Job Status Dashboard.
It is available as a right click off of the jobnum field.
The material list shows the related operation.
The material transaction tab does not, but could by copying the dashboard and modifying it.