BOM hierarchy Query

Epicor ERP 10
1

Im working on a BOM Query using a recursive CTE, and im close to getting what I want. only issue I have is I cannot do a outer join to get the query to look exactly how I would like it. What we would like to see, is in the parent part column, Id like to see all of the material parts below the parent part by level. this is working as long as the part has a material underneath it, but if the part has no material, it wont give me a blank material or null material slot. it just skips the part altogether.

image
image.png446×880 111 KB

Typically id fix this issue with a outer join, but apparently i cannot use a outer join in a CTE. so how can i get this null value that im looking for in the material column?

with [MaterialCTE] as

(select 
	[BOM].[PartMtl_PartNum] as [PartMtl_PartNum],
	[BOM].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[BOM].[Part_PartDescription] as [Part_PartDescription],
	[BOM].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[BOM].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[BOM].[Calculated_Level] as [Calculated_Level],
	[BOM].[Calculated_Path] as [Calculated_Path],
	[BOM].[Calculated_LSeq] as [Calculated_LSeq],
	[BOM].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	[BOM].[Calculated_TLQty] as [Calculated_TLQty],
	[BOM].[Part_ClassID] as [Part_ClassID]
from  (select 
	[PartMtl].[PartNum] as [PartMtl_PartNum],
	[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[PartMtl].[MtlSeq] as [PartMtl_MtlSeq],
	[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
	(0) as [Calculated_Level],
	(cast('' as nvarchar(255))) as [Calculated_Path],
	(cast( convert(varchar,PartMtl.PartNum) + '\' + convert(varchar,(RIGHT('' + CAST(PartMtl.MtlSeq AS NVARCHAR(5)), 5 ))) as nvarchar(255))) as [Calculated_LSeq],
	[PartMtl].[QtyPer] as [PartMtl_QtyPer],
	(cast(1 as decimal(10,1))) as [Calculated_TLQty],
	[Part].[ClassID] as [Part_ClassID]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on 
	PartMtl.Company = Part.Company
	and PartMtl.PartNum = Part.PartNum
	and ( Part.PartNum = '1001174277P029'  ))  as BOM
left outer join  (select 
	[Part2].[Company] as [Part2_Company],
	[Part2].[PartNum] as [Part2_PartNum]
from Erp.Part as Part2
inner join  (select 
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	(max(PartRev.EffectiveDate)) as [Calculated_LastEffDate]
from Erp.PartRev as PartRev
inner join  (select 
	[PartRevLAST].[PartNum] as [PartRevLAST_PartNum],
	(max(PartRevLAST.EffectiveDate)) as [Calculated_MaxEffDate]
from Erp.PartRev as PartRevLAST
where (PartRevLAST.Approved = True)
group by [PartRevLAST].[PartNum])  as LastEffDate on 
	PartRev.PartNum = LastEffDate.PartRevLAST_PartNum
group by [PartRev].[PartNum],
	[PartRev].[RevisionNum])  as MtlRevision1 on 
	Part2.PartNum = MtlRevision1.PartRev_PartNum)  as Parts on 
	BOM.PartMtl_MtlPartNum = Parts.Part2_PartNum
union all
select 
	[ChildPartMtl].[PartNum] as [ChildPartMtl_PartNum],
	[ChildPartMtl].[RevisionNum] as [ChildPartMtl_RevisionNum],
	[MaterialCTE1].[Part_PartDescription] as [Part_PartDescription],
	[ChildPartMtl].[MtlPartNum] as [ChildPartMtl_MtlPartNum],
	[ChildPartMtl].[MtlSeq] as [ChildPartMtl_MtlSeq],
	(MaterialCTE1.Calculated_Level  + 1) as [Calculated_Level2],
	(Cast(MaterialCTE1.Calculated_Path + '.' as nvarchar(255))) as [Calculated_Path2],
	(cast(MaterialCTE1.Calculated_LSeq + '\' + convert(varchar,ChildPartMtl.PartNum) + '\' + (RIGHT('' + CAST(ChildPartMtl.MtlSeq AS NVARCHAR(5)), 5 )) as nvarchar(255))) as [Calculated_LevSeq2],
	[MaterialCTE1].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	(cast(ChildPartMtl.QtyPer as decimal(10,1))) as [Calculated_MLQty],
	[MaterialCTE1].[Part_ClassID] as [Part_ClassID]
from Erp.PartMtl as ChildPartMtl
inner join  MaterialCTE  as MaterialCTE1 on 
	ChildPartMtl.PartNum = MaterialCTE1.PartMtl_MtlPartNum
where (ChildPartMtl.PartNum is not null))

select 
	[MaterialCTE].[PartMtl_PartNum] as [PartMtl_PartNum],
	[MaterialCTE].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
	[MaterialCTE].[Part_PartDescription] as [Part_PartDescription],
	[MaterialCTE].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
	[PartMain].[PartDescription] as [PartMain_PartDescription],
	[MaterialCTE].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
	[MaterialCTE].[Calculated_Level] as [Calculated_Level],
	[MaterialCTE].[Calculated_Path] as [Calculated_Path],
	[MaterialCTE].[Calculated_LSeq] as [Calculated_LSeq],
	[MaterialCTE].[Calculated_TLQty] as [Calculated_TLQty],
	[MaterialCTE].[PartMtl_QtyPer] as [PartMtl_QtyPer],
	(MaterialCTE.Calculated_TLQty * MaterialCTE.PartMtl_QtyPer) as [Calculated_ReqQty],
	[MaterialCTE].[Part_ClassID] as [Part_ClassID]
from  MaterialCTE  as MaterialCTE
inner join Erp.Part as PartMain on 
	MaterialCTE.PartMtl_MtlPartNum = PartMain.PartNum
group by [MaterialCTE].[PartMtl_PartNum],
	[MaterialCTE].[PartMtl_RevisionNum],
	[MaterialCTE].[Part_PartDescription],
	[MaterialCTE].[PartMtl_MtlPartNum],
	[PartMain].[PartDescription],
	[MaterialCTE].[PartMtl_MtlSeq],
	[MaterialCTE].[Calculated_Level],
	[MaterialCTE].[Calculated_Path],
	[MaterialCTE].[Calculated_LSeq],
	[MaterialCTE].[Calculated_TLQty],
	[MaterialCTE].[PartMtl_QtyPer],
	(MaterialCTE.Calculated_TLQty * MaterialCTE.PartMtl_QtyPer),
	[MaterialCTE].[Part_ClassID]
order by MaterialCTE.Calculated_LSeq, MaterialCTE.Calculated_Level

This is how i would like it to look in the end. Is this at all possible?
image

2

Add in a third column that is always the 0 level. So you would have 0 Level, Parent, and Child.

3

Any way to could break out how to do that? Im exactly sure how to set it up that way.

4

Could you post some screen shots of your CTE, union, and top level. Or can you export your BAQ and post. I’m trying to recreate your query.

5

But at a high level, I’m not sure you can put an outer join on the CTE sub-query. Can you try to put the outer join on the top level?

6

I misread your post the first time. Missed the want for the empty field.

I would create the tables you need in the CTE part and then do an outer join in the main query. You can create more than 1 CTE query, so maybe have MaterialCTE1 and MaterialCTE2.

7

ya, i tried putting the outer join on the higher level with no luck. didn’t seem to change the result. here’s the BAQRM-IndentedBOMRev.baq (75.3 KB)

8

This doesn’t seem to work. The CTE appears to be the Starting parameters and the Union All Query won’t allow an outer join. Being the recursion ends in the Union All query without the Outer join, it doesn’t effect the end result.

9

Do something like:

with CTE 
as
(
    Select
    From
    Union All
    Select
    From
),
CTE2
(
    Select
    From
)
Select
From CTE2 outer join CTE1
10

If you don’t find any simple solution, You could add a UNION just after the your Select clause (the final Select, the one that calls your CTE). You could retrieve from CTE all parts with no material and add the result to your first query.