I am trying to do and understand a BAQ of a multi level BOM on the latest revision only.
Have found the epicor example of the CTE BAQ but this doesn’t include the latest revision.
Created a BAQ to calculate the latest revision but for some reason it seems to stop at phantom BOM’s.
Has anyone got this working that they are willing to share.
Regards
Richard
I did create one in the past, and it worked on a “small” database (the training db) but when you put it into a real world database, it would timeout.
The biggest problem is:
“How do you determine the ‘current revision’”… it is not as easy as you might think.
Dear Tim, @timshuwy
I notice that in your explanation you mention the Plant field of the PartRevision record. Have you ever been able to confirm what effect this has in the software, if any? In a previous life, we assumed that since there is a PartRevision.Plant field, then Epicor would only use that revision’s method in the designated plant but we were quickly proven wrong on that. It’s bugged me for years and if you know the answer I’d be interested.
Best,
…Monty.
@MontyMan, as far as I know, the revision that will be used will always be the “current” revision based on the effectivity date. THEN the system chooses the correct “Alternate Method”. There is ONLY ONE current revision based on the date (if there are more than one approved revisions).
It is (or can be) confusing if you are multi-site (Plant in E9/V8)…
PartRev table has a PartRev.Plant field. but it ALSO has an Alternate Method as part of the index
So…
Part, Rev, Alt, Plant
ABC, A, , Plant1
ABC, A, X, Plant1
ABC, A, Y, Plant2
ABC, B, , Plant1
In the above scenario… if rev A is the current effective/approved rev, then it would use alternate Y in plant 2… but in Plant1, it will either use the “primary” (no alternate id) or Alt X, depending on one factor… did you specify to use the alternate method in the PartPlant.AltMethod? If so… then it will use this alternate method as a default.
try creating a BAQ with the following layout and fields, and it might help you understand. You might add a filter for only APPROVED revs so you don’t see tons of repetition.
Thanks Tim! In the multiplant company where I was working at the time, a trusted contractor had told us that in 10.0 the combination of alternate methods and configurator was an absolute no-go because of bugs in that version, so if we were considering the use of configurator in the future, we should use an approved rev per plant and NO alternate methods. We ended up having to do very expensive customizations to get around Epicor’s native selection of latest approved rev, and to instead automatically select during quote entry and job entry, the rev appropriate to a given plant, to use that plant’s resources. I didn’t realize at the time that the site code is part of the indexing of the alt method within a revision’s context. I have since warned anyone who would listen NOT to use multiple approved revisions, particularly if they were doing so with the assumption that the plant code would be used to discern them.
Hi Tim
Thanks for your reply.
Yes I have got a sub query to calculate the maximum effective date of an approved revision. It then uses this sub query to find the revision for that part. I’m using a CTE and Union query to account for all BOM levels as per the Epicor ICE Tools example but for some reason it seems to stop if the part is a phantom assembly. See BAQ below.
SRE_IndentedBOM.baq (88.3 KB)
It works but for some reason no longer searches below phantom parts.
Would like to use this for a dashboard for the DO to show them the current stock/order situation before a design change they have also always been asking for a where used report that searches the latest revision only.
Regards
Richard
Also the idea to only have one approved revision is ridiculous that’s why we have software.
Richard, one wrinkle: it needs to be the latest effective date that’s today or before. So if you have rev A a month ago, rev B effective today and rev C a month in the future, the rev you want to use is B, if you’re querying today.
Richard,
Were you able to get past this phantom block?
Hi Rick
See below. Think I got round it, but you need to check it.
I only got there because of other users on here.
Regards
Richard Whalebelly
SRE_DesignChangeDB.baq (151.7 KB)
