Best way to pull latest Revision (letter or number) into BAQ

Epicor ERP 10
,
1

I have a BAQ im trying to create that pulls all of the operations associated with that part; im creating a bill of operations for one of my engineers. Problem is, we dont need to see the duplicates created by the multiple revisions that exists on some parts. Easiest way for me to do this was to trust my coworkers only Approved one revision and sort by that. BUT when i set the criteria for Approved = TRUE, im still getting unapproved revisions in my list. I checked the part master and the rev is 100% unapproved and yet its still showing as approved when i pull it in.

Am i using the wrong fields to pull the rev?

select 
	[ECOOpDtl].[PartNum] as [ECOOpDtl_PartNum],
	[ECOOpDtl].[OprSeq] as [ECOOpDtl_OprSeq],
	[Part].[ClassID] as [Part_ClassID]
from Erp.ECOOpDtl as ECOOpDtl
inner join Erp.Part as Part on 
	ECOOpDtl.Company = Part.Company
	and ECOOpDtl.PartNum = Part.PartNum
	and ( Part.ClassID = 'SMMS'  )

left outer join Erp.PartRev as PartRev on 
	Part.Company = PartRev.Company
	and Part.PartNum = PartRev.PartNum
	and ( PartRev.Approved = TRUE  )

group by [ECOOpDtl].[PartNum],
	[ECOOpDtl].[OprSeq],
	[Part].[ClassID]
order by ECOOpDtl.PartNum, ECOOpDtl.OprSeq

image

2

What are the Table Relations between Part and PartRev?

edit

Never mind. I see in your query expression.
Make that relations ship be an inner join

3

This comes up a lot, I think if you search for “indented BOM” you’ll find some posts with samples

Here is an example of what criteria I use

PartRev where PartRev.Approved = True
and where PartRev.EffectiveDate <= Constants.Today
and where Effective date = max(PartRev.EffectiveDate)
and where SysRevID = max(PartRev.SysRevID)
----- this last one, just in case there are multiple approved revs with the same Effective dates (it happens)
and here is a very simple example BAQ export if you want to try it…
in a test system… works for my needs but your mileage may vary
.CUS-CurrentPartRev.baq (38.1 KB)

1 Like
4

Just a thought, if 1 approved Revision per Part is your company’s policy then you might consider a BPM to force that to be true… that’s a common BPM request I’ve seen.

1 Like
5

I tried this but it doesn’t do a group by the PartNum to bring in the latest (max) effective date correctly. How would we do a group by PartNum and bring in the max effective date?

image

6

Yeah, that version still listed all revisions - approved, effective.

You can try this one instead
Same basic query except if you look at the joins, you should notice fewer key fields specified.
This way it can pick a single rev per part.
CUS-LatestPartRev.baq (37.7 KB)

7