SQL Query for "Master" BOM for manufactured parts

In other ERP systems, I was able to easily find the “Master” BOM for a manufactured part. A field would be set to “E” for Engineering Master and “W” for work order. What is the table / field that designates a manufactured part BOM as the “Master” from which “Jobs” are then created (copied) for quantities to be built.

My goal is to generate a query for “Masters” so I can see if specific parts are used and where. This will help me gauge the effect of deactivating parts.

Are there reports that already show this information? I also like to know how it works on the backend (SQL).

Thanks
Tony

In Epicor, you can create Jobs from Part Revs that are “Approved”. You can find this via something like

SELECT * FROM Erp.PartRev WHERE PartRev.Approved = 1

You should be careful with that, though, as parts can have multiple “Approved” revisions, AND a Job can exist on the floor that was created from a different version of a Rev, that may or may not be still approved.

Inside of Epicor, there is a “Where Used” report that can tell you where any given PartNum is used, including BOMs.

When guaging whether to deactivate a specific part, you can use the “Part Tracker” menu item in Epicor, which will give you information on whether there are Revisions, NonConformances, or On Hand inventory for that part.

Hi Jeff,

Great information. I also see tables that begin with ECO. Are those the ones for the material and operations (BOM, BOO) for what I call the “Master”? I see an ECOrev as well as PartRev. What is the difference? PartRev appears to have cost. ECOrev looks like it controls check in / check out for changes to the “Master”. We have multiple revisions because there are multiple sites making the product.

Queries:
select * from erp.partrev where partnum = ‘100’;

select * from erp.ecorev where partnum = ‘100’;

select * from erp.JobAsmbl where partnum = ‘100’;

select * from erp.ecomtl where partnum = ‘100’;

select * from erp.ECOOpr where partnum = ‘100’;

Thanks
Tony

ECO is Engineering Change Order.

ECO tables track the changes made through the Engineering Workbench. For most cases, we use PartRev as these records are controlled and have clear indication whether they are active or not.

I think PartRev would be closest to your “Master”. This is what directly influences BOMs on created jobs.

When we opened a new site, we tested different ways to have active shared parts between different sites, and in our case, the best solution was to use “Alternate Methods” instead of different Revisions per site. This allowed both sites to have their own distinct Resource Groups, and in some cases different material partnums.

I’ve attached a document that was created for Epicor 9 (by an Epicor Partner), but it is still relevant today and may give you a better view of Parts/Revs/ECO.
Part.pdf (277.5 KB)

ERP.PartMtl Contains what you would call the master bom. when jobs are created this is the BOM that it uses.
It will then create a BOM for the job (JobMtl) this is what you see when you look in job entry.

Everything @Craig and @jeffj have said is correct… but if you have multi-level BOMs there is a whole 'nother can of worms. Epicor does not store multiple levels as such in the PartMtl table, but there are flags in that table that indicate if the material component is pulled from inventory OR manufactured as a subassembly. This is where the excitement really begins.

2 Likes

very wormy

So I took what @craig and @jeff said, excluding the wormy warning from @utah at this time, to come up with the following as a guide where AL-1820B is the Finished Good and 0102007 is a JOB for it:

– PARTREV contains REV (by Site) info from Part Maintenance with FG Costs
select * from erp.partrev where partnum = ‘AL-1820B’;

– PARTMTL contains the Master BOM Materials by Site (REV) and related operations
select * from erp.partmtl where partnum = ‘AL-1820B’;

– JOBHEAD contains header info on JOBS to create finished goods
select * from erp.jobhead where partnum = ‘AL-1820B’;

– JOBASMBL contains info for JOBS such as costs and scheduling
select * from erp.JobAsmbl where partnum = ‘AL-1820B’;

– JOBMTL contains BOM Material for JOBS
select * from erp.JobMtl where jobNum = ‘0102007’;

–JOBOPER contains Operations / Resource estimated & actual Costs for Setup & Prod, Labor & Burden
select * from erp.JobOper where jobNum = ‘0102007’ order by OprSeq;

– JOBOPDTL contains Operations / Resource estimated & actual Costs for Setup & Prod, Labor & Burden for OpDtlSeq#
select * from erp.JobOpDtl where jobNum = ‘0102007’ order by OpDtlSeq, OprSeq;

select * from erp.Resource;
select * from erp.ResourceGroup;

What is the purpose for JobAsmbl? Does every finished good have a JobAsmbl record? Is it costing and scheduling as I indicate? Some Jobs / Masters can have SubAssemblies (Legs). Thus, I guess there would be a 1-Many relationship between JobHead and JobAsmbl?

Thanks
Tony

Are you looking for the “Master”? The job tables are what’s on the individual jobs. You have have 100s of jobs with the same finished good number, and every job has it’s own records for the job. And if the “Master” changes, the job does not.

Job assembly is the things that get put together to create the finished good. There is always at least one (Asm 0) which is the final finished good. That can be made up of materials (JobMtl) and/or other assemblies. The other assemblies that also be made up of materials and other assemblies. You have a parent assembly in each assembly record that relates the hierarchy.

The JobAsmbl answer was what I had suspected. Thanks for confirming, Brandon.