I am trying to create a “Traveler” for a part using the MOM tables PartMtl and PartOpr.
I created the recursive CTE and Union All example Epicor offers and that is working well. However, I need to add the PartOpr table and I am struggling to see how that is done.
I see that the PartOpr has Company, PartNum, Revision, OprSeq - however I don’t see how the joins are made. I can join PartMtl using the same fields (except OprSeq is RelatedOperation) but the MOM could have all operations and NO material and NO Subassemblies?
Yeah, this can get tricky… I’d normally try an open join but…varies depending on what you’ve done already, where you need to join, want to show in your BAQ.
Off the top of my head I’d think you’d use a subquery but again… will depends on what your BAQ structure looks like so far.
If you search the site you might find a few examples… this one is for the Job tables but basically the same for Part tables except you won’t have to worry about the Assembly.
Personally, I really dislike (and therefore dont believe in it) any materials that are not “related” to an operation. All materials SHOULD be related to the first operation, or the operation where they are consumed. This means that they will not schedule to be needed at the correct time. I typically push my customers to relate ALL materials…
By the way, if you edit a MOM in the Engineering Workbench, and you add the operations first, then the system automatically assigns an operation to the material and doesn’t like you to clear it (last I checked).
But… to answer your question, the join is as follows… note that these are all “One-to-many” joins… so you can have more than one record join at each level.
JobHead -> JobAssy - Company/JobNum
JobAssy -> JobOper - Company/JobNum/AssyNum
JobOper -> JobMtl - Company/JobNum/AssyNum/RelatedOperation
Mr. Tim: Thank you for the insight. I would agree that all materials (and assemblies) should be related to an operation. However, Epicor allows it.
For the joins - I cannot use the Job tables since I am trying to create a Job traveler for MOM parts that may or may not have a job.
So, the tables i have to work with are: Part - PartRev - PartMtl - PartOpr.
I was able to build a recursive CTE to display the PartMtl records and that is working great. I now would like to add the PartOpr table to my PartMtl records.
They are joined very similar to JobMtl and JobOpr, except you will need to add the Revision and AltMethod fields into the join…
Part to PartRev to PartOpr to PartMtl… everything is basically the same… the most detailed is
PartOpr -> PartMtl = Company, Part, Revision, AltMethod, Related operation
Mr. Tim: Thank you again - we are getting so close.
If I have the same part number used twice in the MOM (both times as an assembly) but the first instance uses 3 operations and the second instance uses 4 operations - how do i get the PartOpr table to connect to the right Part?
Hi @DaveOlender,
to link between JobOper and JobMtl you need to add the AssemblySeq to your criteria to determine which level of assembly you are targeting, and this is how you can solve your stated problem
The PartMtl table does not store assemblies, it only stores the manufacturing details for THAT particular part number. If one of the values in the PartMtl table is a manufactured part, you would need to pull the PartMtl table of THAT material part as a sub-table to create the entire MOM from that point “downward”. When Epicor creates a Job, it does all that dynamically so the JobMtl table contains everything for all levels of the manufactured part… but each individual PartRev/PartMtl table entry does NOT contain all lower level information.
unfortunately, we do not use the assembly functionality in our environment, - i can try and create one if i get chance- but could you print screen some test data from your SQL, however i can see a field called ParentOprSeq and ParentMtlSeq could you try to see what values been stored in them in your multi assembly level parts?
There is no need to have the “Assembly” level in the BOM… I always teach that a BOM is BUILT as a single level BOM… Part A has a list of materials (X, Y, Z). if part X also has a BOM, then it is a lower level BOM, but it is edited on its own in the Engineering workbench as a separate edit.
When you create part A’s BOM, you have the choice to mark part X as a “PULL AS ASSEMBLY” (PAA)… this means: “Pull this part as a Sub-Assembly into the job instead of making and stocking it”. By flipping this PAA checkbox, you will have a JOB with a sub-assembly. So, your job will look different for Part A then it will look in the engineering workbench.
FURTHER, you can also flip the PHANTOM flag on part X. If you do this, the Job’s BOM will not even list part X as a component, nor will it have a sub-assembly. Simply all the materials AND OPERATIONS that were inside part X will be transferred into the BOM/BOO of Part A as the job is created.
This is why there is a separate set of JobMtl, JobOpr, JobAssy tables vs the ones in PartMtl, PartOpr… the “Job” versions represent “As built” but the Part versions represent how future builds will happen.
I think I’m catching on - I need to think of the PartMtl and PartOpr as instructions on how to create the job. So there would never be an subassembly x listed twice on a Parent part with two different operation lists. Each Subassembly is drawn from PartMtl so both instances of subassembly x will have the same operations list.
Once the MOM is brought into the Job - Then the operations could be modified separately for the subassembly x instances.
I hope I am seeing that right? And thank you for your patience and help.
DaveO
