Indented BOM - CTE Expression

Epicor ERP 10
1

Hello All,

I’m trying to build a query for an indented BOM listing. I followed the tutorial in the help section, but when I analyze, I am getting every part from the BOM on every BOM level. It’s basically just duplicating itself on every BOM level.

Our BOMs have subassemblies within subassemblies, within subassemblies, within a parent assembly. All of those subs have materials, and rather than returning the part material, the query is returning the subassembly part number within the parent part.

Images below for reference. Any help appreciated.

image

image
image984×230 5.53 KB

*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [SubQuery1] as 
(select 
	[PartMtl].[Company] as [PartMtl_Company],
	[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],
	(0) as [Calculated_Hierarchy],
	(cast ( substring('........',1 ,(Hierarchy + 1) ) + PartMtl.MtlPartNum
 as nvarchar(25))) as [Calculated_Ind1]
from Erp.PartMtl as PartMtl
where (PartMtl.PartNum = @PartNum)

union all
select 
	[PartMtl1].[Company] as [PartMtl1_Company],
	[PartMtl1].[PartNum] as [PartMtl1_PartNum],
	[PartMtl1].[RevisionNum] as [PartMtl1_RevisionNum],
	[PartMtl1].[MtlSeq] as [PartMtl1_MtlSeq],
	[PartMtl1].[MtlPartNum] as [PartMtl1_MtlPartNum],
	[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
	[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
	[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
	[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
	[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
	(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(cast ( substring('........',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
 as nvarchar(25))) as [Calculated_Ind2]
from Erp.PartMtl as PartMtl1
inner join  SubQuery1  as SubQuery1 on 
	PartMtl1.Company = SubQuery1.PartMtl_Company
	and PartMtl1.PartNum = SubQuery1.PartMtl_PartNum)

select 
	[SubQuery11].[PartMtl_Company] as [PartMtl_Company],
	[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],
	[SubQuery11].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[SubQuery11].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
	[SubQuery11].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
	[SubQuery11].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
	[SubQuery11].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
	[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
	[SubQuery11].[Calculated_Ind1] as [Calculated_Ind1]
from  SubQuery1  as SubQuery11
1 Like
2

Double check your joins between the recursive subquery and the PartMtl table. Looks like you have PartNum joined to PartNum. You want MtlPartNum joined to PartNum.

1 Like
3

Thank you.

4

I’m in the process of needing a recursive indented BOM query. Any chance you could share this?

5

There’s a really good step by step guide in the Epicor help section. All I did was follow those instructions step by step, and I got the results I was looking for. Just search CTE in the Epicor help application and follow the guide on creating a CTE query.

image
image1034×898 179 KB

6

Ok, I’ll give that a shot. Thanks.

7

Hi Dan,

Any idea why the example in epicor help pulls in duplicates? Is there a way to get one row per BOM item?

image
image969×747 36.9 KB

8

In their example it appears multiple lines appear because they show up in different revisions.

9

Even the different revisions have duplicates within them though. For example, the first part under BOM level 1, IND1 has two of the same material parts for the same mtl sequence, and same revision.

10

@dr_dan

11

You need to consider effective date in your query as well. If you do not use effective date, you will end up with duplicates because you have different revisions.

12

Sounds like I need to go back to the drawing board…

13

Try putting a MAX() on the effective date

14

As in create an innersub with calculated Max(effectivedate), and link back to the top level?

15

I would join PartRev to PartMtl and group on everything and put a calculated field that ignores effectivedate > today and returns the Max(effectivedate).

16

That’s exactly what I ended up with.

Thank you for the help!

~WRD000.jpg