Need to narrow BOM by MOM report to single instance of ASM 0 Part

Epicor ERP 10
1

I have a BAQ I have worked on for over one month. So much, I am missing the obvious next step.

The requirement was to create a BOM Explosion with true ASM information for ALL parts Active and Mfg in past 720 days. Dont ask why, it is crazy. This BAQ draws from JobAsmbl and Job Material to give all levels of the break down the data.

I have it delivering this information but now need to narrow it down to a single explosion instance of each ASM 0 Part. I cannot do this by MAX(Completion Date) or Max(JOB) as the plant floor often BATCHES production runs so the complete on the same date.

How can I create a calculated field that simply returns one instance of each ASM 0 Part? Sorry, I am absolutely beyond FRIED on this one.

Attached is a copy for review and ANY help is appreciated. I have to get this one off my plate after SO much time.

Here is a copy of the BAQ Currently

bks-JobBOM2.baq (98.7 KB)

2

I (think I ) have your BAQ running on a test system OK but…
I am not sure what your final view to look like… can you expand?
e.g. just simple sums of the qtys per each MtlPart, to a single row per each Asm Zero PartNum… or something more involved?

image
image977×425 183 KB

3

Thank you for your response. No, what I need is only one Instance of PARENT Part Number (calculated field) by the HIGHEST Job number for that Parent Part. I am getting multiple jobs for each parent part currently. I thought about using MAX(JobNum) but I lose most of my parts. I also cant do Max(Completiondate) as the production floor is batching multiple jobs manually and completing them all on the same date.

???

4

Further down, you would see our products can have as many as 17 ASM levels. Our new Supply Chain Manager wants this query to contain the parent part, each ASM part and any Materials associated with that ASM level (some ASM are OPs only so they will skip) and he wants the BOM level associated with each also.

He wants them flat , not indented as he will manipulate this in Excel. Each time he does a pivot on my query he loses data (user).

I just want to limit EA parent part by the max(Jobnum) for that part really. One set of data per Parent part.

5

Do you model your assemblies/products in a modeling software. We export a flattened BOM out of ours that I can relate to a job or parent part.

6

We do but all data must come from Epicor as the bible as senior management has dictated.