Thank you for pointing me to it. I just noticed it’s in help files.
1 Like
Hi James - would appreciate it if you could post something - would be great. Thanks Rod
1 Like
This was an awesome find for me. Tried for years to join the boms together but never could master it.
1 Like
This is great, I have applied it in the Query and it comes out almost all the information of the BOM that has several levels, but the amount that appears is that of the child, the amount of the parent does not appear and I require it to get the cost of the entire BOM .
Anyone can share the Intended BOM Listing baq?
I have Epicor 9 and I don’t see sub-queries. Seems this would be impossible without using sub-queries - right?
Yeah
Hi there, I’d like a copy of your detailed BOM. I have a project that needs all of this. Thanks in advance.
I know this is an older post but hoping you could maybe point me in the right direction. I am trying to add the operation description instead of just the related operation number. When I add partopr along with the operation description it duplicates the lines multiple times instead of just showing the operation description based on the partmtl related operation.
Something is wrong with your join then. It’s getting multiple matches. Maybe the revision isn’t matching?
I would make a test query and pick an example that’s giving you problems and see which table is getting you extra rows.
A quick and dirty way to see is to add the sysrow ID from each table and you can see which ones are duplicate rows, and which are unique.
You’ll need to add a revision to that join, if you have multiple revisions, you’re going to get duplicates.
1 Like
So I got it to work. Part of the problem was related to all revisions showing instead of just the approved Rev and the second less annoying part is that it will duplicate the material based on how many operations are typed to the parent part but that is manageable, Thanks for pointing me in a good direction.
1 Like
If you want to list all of the operations in a single cell, you can use an aggregate function.
Group all of the other field, then add this as a calculated field
string_agg(OppField, ',')
Replace the OppField with the field that you actual field you want to see, and the second parameter is the delimiter. I just put a , but you can have that be whatever you want.
1 Like
Because the company has many approved Partrevs, if it passes PartMtl, it will have to choose 1 approved Partvev and modify it last.
If taking BOM data, perhaps using ECO tables will be more accurate
with [MAXPartRev] as
(select
[PartRev].[Company] as [PartRev_Company],
[PartRev].[PartNum] as [PartRev_PartNum],
(max(isnull(PartRev.ChangedOn,PartRev.ApprovedDate))) as [Calculated_ChangeOn]
from Erp.PartRev as PartRev
where (PartRev.Approved = 1 and PartRev.PartNum = @PartNum)
group by [PartRev].[Company],
[PartRev].[PartNum])
,[PartRevMax_FullInfo] as
(select
[MAXPartRev].[PartRev_Company] as [PartRev_Company],
[MAXPartRev].[PartRev_PartNum] as [PartRev_PartNum],
[PartRev_MaxInfo].[RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartRev_MaxInfo].[Number01] as [PartRev_MaxInfo_Number01],
[PartRev_MaxInfo].[Number02] as [PartRev_MaxInfo_Number02],
[PartRev_MaxInfo].[Number03] as [PartRev_MaxInfo_Number03],
[PartRev_MaxInfo].[Number04] as [PartRev_MaxInfo_Number04],
[PartRev_MaxInfo].[Number05] as [PartRev_MaxInfo_Number05],
[PartRev_MaxInfo].[Number06] as [PartRev_MaxInfo_Number06],
[PartRev_MaxInfo].[AltMethod] as [PartRev_MaxInfo_AltMethod]
from Erp.PartRev as PartRev_MaxInfo
inner join MAXPartRev as MAXPartRev on
PartRev_MaxInfo.Company = MAXPartRev.PartRev_Company
and PartRev_MaxInfo.PartNum = MAXPartRev.PartRev_PartNum
and PartRev_MaxInfo.ChangedOn = MAXPartRev.Calculated_ChangeOn)
,[PartParent_PartMtl_Root] as
(select
[PartRevMax_FullInfo].[PartRev_Company] as [PartRev_Company],
[PartRevMax_FullInfo].[PartRev_PartNum] as [PartRev_PartNum],
[PartRevMax_FullInfo].[PartRev_MaxInfo_RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(1) as [Calculated_LevelBom],
(cast ( substring('........',1 ,(LevelBom + 1) ) + PartMtl.MtlPartNum
as nvarchar(250))) as [Calculated_LevelPartMtl]
from Erp.PartMtl as PartMtl
inner join PartRevMax_FullInfo as PartRevMax_FullInfo on
PartMtl.Company = PartRevMax_FullInfo.PartRev_Company
and PartMtl.PartNum = PartRevMax_FullInfo.PartRev_PartNum
and PartMtl.RevisionNum = PartRevMax_FullInfo.PartRev_MaxInfo_RevisionNum
and PartMtl.AltMethod = PartRevMax_FullInfo.PartRev_MaxInfo_AltMethod
union all
select
[PartParent_PartMtl_Root].[PartRev_Company] as [PartRev_Company],
[PartParent_PartMtl_Root].[PartRev_PartNum] as [PartRev_PartNum],
[PartParent_PartMtl_Root].[PartRev_MaxInfo_RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartMtl_Sub].[PartNum] as [PartMtl_Sub_PartNum],
[PartRev_SubMtl].[RevisionNum] as [PartRev_SubMtl_RevisionNum],
[PartMtl_Sub].[MtlSeq] as [PartMtl_Sub_MtlSeq],
[PartMtl_Sub].[MtlPartNum] as [PartMtl_Sub_MtlPartNum],
[PartMtl_Sub].[QtyPer] as [PartMtl_Sub_QtyPer],
[PartMtl_Sub].[RelatedOperation] as [PartMtl_Sub_RelatedOperation],
[PartMtl_Sub].[PullAsAsm] as [PartMtl_Sub_PullAsAsm],
[PartMtl_Sub].[ViewAsAsm] as [PartMtl_Sub_ViewAsAsm],
[PartMtl_Sub].[PlanAsAsm] as [PartMtl_Sub_PlanAsAsm],
(PartParent_PartMtl_Root.Calculated_LevelBom + 1) as [Calculated_LevelBom],
(cast ( substring('........',1 ,(LevelBom + 1) ) + (PartParent_PartMtl_Root.PartMtl_PartNum+N'\'+PartMtl_Sub.MtlPartNum)
as nvarchar(250))) as [Calculated_LevelPartMtl]
from Erp.PartMtl as PartMtl_Sub
inner join PartParent_PartMtl_Root as PartParent_PartMtl_Root on
PartMtl_Sub.Company = PartParent_PartMtl_Root.PartRev_Company
and PartMtl_Sub.PartNum = PartParent_PartMtl_Root.PartMtl_MtlPartNum
inner join Erp.PartRev as PartRev_SubMtl on
PartRev_SubMtl.PartNum = PartParent_PartMtl_Root.PartMtl_MtlPartNum
and PartRev_SubMtl.RevisionNum = PartParent_PartMtl_Root.PartMtl_RevisionNum)
select distinct
[PartParent_PartMtl_Root_Full].[PartRev_Company] as [PartRev_Company],
[PartParent_PartMtl_Root_Full].[PartRev_PartNum] as [PartRev_PartNum],
[PartParent_PartMtl_Root_Full].[PartRev_MaxInfo_RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartParent_PartMtl_Root_Full].[PartMtl_PartNum] as [PartMtl_PartNum],
[PartParent_PartMtl_Root_Full].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[PartParent_PartMtl_Root_Full].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[PartParent_PartMtl_Root_Full].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[PartParent_PartMtl_Root_Full].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[PartParent_PartMtl_Root_Full].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[PartParent_PartMtl_Root_Full].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[PartParent_PartMtl_Root_Full].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartParent_PartMtl_Root_Full].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
[PartParent_PartMtl_Root_Full].[Calculated_LevelBom] as [Calculated_LevelBom],
[PartParent_PartMtl_Root_Full].[Calculated_LevelPartMtl] as [Calculated_LevelPartMtl]
from PartParent_PartMtl_Root as PartParent_PartMtl_Root_Full
order by PartParent_PartMtl_Root_Full.PartRev_PartNum, PartParent_PartMtl_Root_Full.Calculated_LevelBom, PartParent_PartMtl_Root_Full.Calculated_LevelPartMtl
DMS_GetBomFull.baq (65.5 KB)
Based on the MethodTracker screen
In production jobs, there is a very precise relationship between asm and mtl
select * from JobAsmbl as asm inner join JobMtl as mtl on asm.Company=mtl.Company and asm.JobNum =mtl.JobNum and asm.AssemblySeq=mtl.AssemblySeq
Thank you very much! Have a good day! 
I don’t recommend relying on the ECO tables. Those tables are meant to hold the data as you are working with them in the ECO, but info left in there can be messed up depending on how eco groups are used and is not a reliable source of information. I wish they would clear the data out of those tables after you are done with engineering workbench because the data there can be misleading.
5 Likes