I could use some help with a sql query for BOMs I’m trying to get in SSMS. I’m bascially using the ParMtl table and linking it to itself via a PartMtl.MtlPart = PartMtl_1.PartNum type linking (like alias tables in version 9). However the Mtl Part could have multiple revisions, so how do I know, or what are the links I need to make to get the right revision of the mtl part? Is this possible, i’ve looked over the schema and I’m a little stumped as I don’t see anything indicating a Mtl Revision number. If i look at the mtl list inside of method tracker it does not have the revision associated to the mtl, but if i use the View Cost from method tracker it shows the materials revision as well as indention level, so it looks possible but I’m unable to recognize how. Any input is appreciated.
I really wish Epicor wouldn’t show that rev in the tree information of Method Tracker. A PartMtl record does not hold the revision; instead, Epicor assumes based on when the job is to be made, what revision of the material will be in effect at time of manufacture. So what I think you want is Part -> PartRev -> PartMtl -> Part_1.
In a previous life unfortunately we had multiple approved revisions, and one of the customizations I had to make was at the time of Job creation, to show an error message if there would not be any approved rev of any material (including subassemblies) for the plant in question.
Thanks for the reply Monty. Yeah ultimately I just grabbed the revision for each material part based on the latest effective date. It wasn’t the most elegant solution but it got me what I wanted.
SELECT pr1.PartNum, p1.PartDescription, pr1.RevisionNum, pr1.RevDescription, pr1.ECO, pr1.EffectiveDate, pr1.ApprovedDate, pm1.MtlSeq, pm1.MtlPartNum as lvl1_Part, pm2.RevisionNum as lvl1_Rev, pm1.QtyPer as lvl1_QtyPer, pm2.MtlPartNum as lvl2_Part, pm3.RevisionNum as lvl2_Rev, pm2.QtyPer as lvl2_QtyPer, pm3.MtlPartNum as lvl3_Part, pm4.RevisionNum as lvl3_Rev, pm3.QtyPer as lvl3_QtyPer, pm4.MtlPartNum as lvl4_Part, pm5.RevisionNum as lvl4_Rev, pm4.QtyPer as lvl4_QtyPer, pm5.MtlPartNum as lvl5_Part, pm6.RevisionNum as lvl5_Rev, pm5.QtyPer as lvl5_QtyPer, pm6.MtlPartNum as lvl6_Part, pm7.RevisionNum as lvl6_Rev, pm6.QtyPer as lvl6_QtyPer, pm7.MtlPartNum as lvl7_Part, pm8.RevisionNum as lvl7_Rev, pm7.QtyPer as lvl7_QtyPer
FROM MfgTrain803.dbo.PartRev pr1
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm1 ON pm1.Company = pr1.Company AND pm1.PartNum = pr1.PartNum and pm1.RevisionNum = pr1.RevisionNum
INNER JOIN MfgTrain803.dbo.Part p1 ON p1.Company = pr1.Company AND p1.PartNum = pm1.PartNum
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm2 ON pm2.Company = pm1.Company AND pm1.MtlPartNum = pm2.PartNum and pm2.RevisionNum = (SELECT Top 1 rc1.RevisionNum FROM MfgTrain803.dbo.PartRev rc1 WHERE rc1.PartNum = pm2.PartNum AND rc1.Approved = '1' ORDER BY rc1.EffectiveDate DESC)
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm3 ON pm3.Company = pm2.Company AND pm2.MtlPartNum = pm3.PartNum and pm3.RevisionNum = (SELECT Top 1 rc3.RevisionNum FROM MfgTrain803.dbo.PartRev rc3 WHERE rc3.PartNum = pm3.PartNum AND rc3.Approved = '1' ORDER BY rc3.EffectiveDate DESC)
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm4 ON pm4.Company = pm3.Company AND pm3.MtlPartNum = pm4.PartNum and pm4.RevisionNum = (SELECT Top 1 rc4.RevisionNum FROM MfgTrain803.dbo.PartRev rc4 WHERE rc4.PartNum = pm4.PartNum AND rc4.Approved = '1' ORDER BY rc4.EffectiveDate DESC)
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm5 ON pm5.Company = pm5.Company AND pm4.MtlPartNum = pm5.PartNum and pm5.RevisionNum = (SELECT Top 1 rc5.RevisionNum FROM MfgTrain803.dbo.PartRev rc5 WHERE rc5.PartNum = pm5.PartNum AND rc5.Approved = '1' ORDER BY rc5.EffectiveDate DESC)
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm6 ON pm6.Company = pm6.Company AND pm5.MtlPartNum = pm6.PartNum and pm6.RevisionNum = (SELECT Top 1 rc6.RevisionNum FROM MfgTrain803.dbo.PartRev rc6 WHERE rc6.PartNum = pm6.PartNum AND rc6.Approved = '1' ORDER BY rc6.EffectiveDate DESC)
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm7 ON pm7.Company = pm7.Company AND pm6.MtlPartNum = pm7.PartNum and pm7.RevisionNum = (SELECT Top 1 rc7.RevisionNum FROM MfgTrain803.dbo.PartRev rc7 WHERE rc7.PartNum = pm7.PartNum AND rc7.Approved = '1' ORDER BY rc7.EffectiveDate DESC)
LEFT OUTER JOIN MfgTrain803.dbo.PartMtl pm8 ON pm8.Company = pm8.Company AND pm7.MtlPartNum = pm8.PartNum and pm8.RevisionNum = (SELECT Top 1 rc8.RevisionNum FROM MfgTrain803.dbo.PartRev rc8 WHERE rc8.PartNum = pm8.PartNum AND rc8.Approved = '1' ORDER BY rc8.EffectiveDate DESC)
WHERE pr1.Approved = '1' AND pr1.EffectiveDate = (SELECT max(pr2.EffectiveDate) FROM MfgTrain803.dbo.PartRev pr2 WHERE pr2.PartNum = pr1.PartNum) AND pr1.Company = 'CompanyID'
ORDER BY pr1.PartNum , pm1.MtlSeq, pm2.MtlPartNum, pm3.MtlPartNum
Yes, and even more difficult if you allow for the possibility that someone accidentally leaves a rev unapproved after an Engineering Workbench session. It may be possible to smart-filter for that case, using a left-join and some calculated fields. Other possibilities include a dedicated dashboard to find track-revision parts without a current approved rev, and/or parts where the latest rev approved date is not approved. This can be valuable information for that day when MRP just stops processing demand for an item.