Indented BOM SQL Query

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?


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.

I just gave it a quick go, seemed to work OK

1 Like

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 
	[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],
 ,[PartRevMax_FullInfo] as 
	[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 
	[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
	[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

:thinking: :thinking:
Thank you very much! Have a good day! :smiling_face_with_three_hearts: :smiling_face_with_three_hearts: