Recursive BOM Revision Handling

[TrevorLenten]

I’m looking for some assistance, I’m trying to create an indented BOM. I used the cte template in the help section and that seems to be working ok now but I am having an issue with part revisions. I either get every revision known to man or not enough because some parts don’t have any revisions.

I was thinking I’d have to use an outer join but that isn’t supported in a recursive query. Maybe it’s something else, I’m not sure as this is my first foray into SQL recursion.

/*
 * 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],
	[Part].[PartDescription] as [Part_PartDescription],
	[Part].[ClassID] as [Part_ClassID],
	[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],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum]
from Erp.PartMtl as PartMtl
inner join Erp.Part as Part on 
	PartMtl.Company = Part.Company
	and PartMtl.PartNum = Part.PartNum
inner join Erp.PartRev as PartRev on 
	PartMtl.Company = PartRev.Company
	and PartMtl.MtlPartNum = PartRev.PartNum
	and PartMtl.RevisionNum = PartRev.RevisionNum
	and PartMtl.AltMethod = PartRev.AltMethod
	and ( PartRev.Approved = 1  and PartRev.EffectiveDate < @Today  and PartRev.RevisionNum = ANY (select Calculated_maxrev from ((select 
	[PartRev3].[Company] as [PartRev3_Company],
	[PartRev3].[PartNum] as [PartRev3_PartNum],
	(max(PartRev3.RevisionNum)) as [Calculated_maxrev]
from Erp.PartRev as PartRev3
where (PartRev3.Approved = 1)
group by [PartRev3].[Company],
	[PartRev3].[PartNum])) as SubQuery4)   )

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],
	[Part1].[PartDescription] as [Part1_PartDescription],
	[Part1].[ClassID] as [Part1_ClassID],
	[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],
	(SubQuery1.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
	(cast ( substring('........',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
 as nvarchar(25))) as [Calculated_Ind2],
	[PartRev2].[RevisionNum] as [PartRev2_RevisionNum]
from  SubQuery1  as SubQuery1
inner join Erp.PartMtl as PartMtl1 on 
	SubQuery1.PartMtl_MtlPartNum = PartMtl1.PartNum
	and SubQuery1.PartMtl_Company = PartMtl1.Company
inner join Erp.Part as Part1 on 
	PartMtl1.Company = Part1.Company
	and PartMtl1.PartNum = Part1.PartNum
inner join Erp.PartRev as PartRev2 on 
	SubQuery1.PartMtl_Company = PartRev2.Company
	and SubQuery1.PartMtl_MtlPartNum = PartRev2.PartNum
	and ( PartRev2.Approved = 1  ))

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].[Part_PartDescription] as [Part_PartDescription],
	[SubQuery11].[Part_ClassID] as [Part_ClassID],
	[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],
	[SubQuery11].[PartRev_RevisionNum] as [PartRev_RevisionNum]
from  SubQuery1  as SubQuery11

[dr_dan (Dan Ramirez)]

The thing with revisions is… you won’t have a BOM if you don’t have a revision. So for those, you don’t care… as the recursive and anchor are joined solely for the purposes of pulling up the BOM of the material record (if there is one). So in that case, no revision doesn’t matter.

For too many revisions, I found that I had to create a couple additional subqueries in order to pick out the most recent effective date from approved revisions so that I always only end up with 1. I did it in two steps. Someone more proficient may be able to do it in one. I’ll attach my BAQ so you can review that part. Originally I was picking off only approved revisions from the PartRev table… but sometimes we have more than 1 approved… and that was jazzing things up. So I had to take it a step farther and ensure I was only picking the most recently effective approved revision… and that’s how Epicor does it when getting details for a job.

The two subqueries to pay attention to are GetMaxEffectiveDate and then GetCurrentRev. I believe I join the GetCurrentRev to my PartRev table and that’s how I ensure I only get 1 revision.
DMR_ProjectedCostsFast.baq (220.8 KB)

[TrevorLenten]

Thank you for sharing Dan, it is greatly appreciated. There is a lot to unpack in your query.

[dr_dan (Dan Ramirez)]

Most of it is extra stuff. You can strip a lot of it away. At the very beginning, it’s the anchor and the recursive just like in the examples. And then you can add the two subqueries I mentioned and see how I joined it into the anchor/recursive subqueries to get to 1 revision. Everything else after that is really just preference on how we display it and what info we include.

[TrevorLenten]

I stripped a bunch of stuff away and deployed for the user that asked for it, fingers crossed. I owe you a beer sir, thank you.

[TerryR (Terry Raymond)]

Dan thanks for posting this, I’m trying to fix the multiple-rev issue as well.
Issue though - In your query, if you have 2 revisions for the same part (component or parent), with approved on the same day/same effective date, they show up as duplicates.

You have the Rows set to 1, but it apparently has no effect…

Workaround might be to combine the EffectiveDate and SysRevID field together into a number… though that might have performance issues.
Or just go off latest SysRevID, though if someone updates an old revision record, not wanting to change effective date, the BAQ would pull the older rev.

I’m still thinking this over.

Same issue here…

BAQ indented BOM of latest revision only with phantom BOM

I did create one in the past, and it worked on a “small” database (the training db) but when you put it into a real world database, it would timeout. The biggest problem is: “How do you determine the ‘current revision’”… it is not as easy as you might think. it is DATE based (effective date) it is based on the current SITE, and whether there is an alternate method. the “Approved” flag must also be examined. There can only be one approved/effective revision that will be used in your query, b…

[dr_dan (Dan Ramirez)]

Yep that’s a limitation for sure. We don’t typically have multiple approved revs for the same effective date so that wasn’t a show stopper for us.