Here is a new base query we use. It has held up for about a year now. You may need to adjust the query a bit for your environment.
with [BOMReviewParent] as
(select
(Part.ShortChar10) as [Calculated_Customer],
(PartRev.PartNum) as [Calculated_TopPart],
(PartRev.RevisionNum) as [Calculated_TopPartRev],
(part.PartNum) as [Calculated_ParentPartNum],
(PartRev.RevisionNum) as [Calculated_ParentRevNum],
(part.PartNum) as [Calculated_ChildPartNum],
(PartRev.RevisionNum) as [Calculated_ChildRevNum],
(CAST(Part.TypeCode as nvarchar(500))) as [Calculated_TypeCode],
(0) as [Calculated_lv],
(Cast(part.PurchasingFactor as decimal(10,2))) as [Calculated_QtyPer],
(Cast(part.PartDescription as nvarchar(500))) as [Calculated_PartName],
(0) as [Calculated_OpSeq],
(CAST(part.PartNum + '-' + partrev.RevisionNum as nvarchar(500))) as [Calculated_Sort]
from dbo.Part as Part
inner join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
--and ( PartRev.PartNum = '003-718')
and Part.InActive = 0
and Part.ClassID in ( 'FGD', 'FGA')
and PartRev.Approved = 1
--and PartRev.PartNum <> '001-855'
union all
select
[BOMReviewParent].[Calculated_Customer] as [Calculated_Customer],
[BOMReviewParent].[Calculated_TopPart] as [Calculated_TopPart],
[BOMReviewParent].[Calculated_TopPartRev] as [Calculated_TopPartRev],
[BOMChildren].[PartMtl_PartNum] as [PartMtl_PartNum],
[BOMChildren].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[BOMChildren].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[BOMChildren].[Calculated_RevNum] as [Calculated_RevNum],
(CAST(BOMChildren.TypeCode as nvarchar(500))) as PlaceHolder,
(BOMReviewParent.Calculated_lv + 1) as [Calculated_BOMlv],
(cast(BOMChildren.Calculated_ChildQtyPer as decimal(10,2))) as [Calculated_BOMQtyPer],
(CAST(REPLICATE ('| ' , BOMReviewParent.Calculated_lv +1) + BOMChildren.PartBOMChild_PartDescription as nvarchar(500))) as [Calculated_BOMPartName],
(BOMChildren.Calculated_MtlSeq) as [Calculated_BOMMtlSeq],
(CAST(BOMReviewParent.Calculated_Sort + '-' + RIGHT( '0000'+ Convert(varchar, BOMChildren.Calculated_SeqNum), 4)as nvarchar(500))) as [Calculated_BOMSort]
from (select PartBOMChild.TypeCode as TypeCode,
[PartMtl].[PartNum] as [PartMtl_PartNum],
[PartMtl].[RevisionNum] as [PartMtl_RevisionNum],
[PartMtl].[MtlPartNum] as [PartMtl_MtlPartNum],
(((select
[PartRevA].[RevisionNum] as [PartRevA_RevisionNum]
from Erp.PartRev as PartRevA
inner join (select PartRevB.Company,
[PartRevB].[PartNum] as [PartRevB_PartNum],
[PartRevB].[RevisionNum] as [PartRevB_RevisionNum],
(ROW_NUMBER() OVER (PARTITION BY partrevb.PartNum ORDER BY partrevb.ApprovedDate DESC)) as [Calculated_PartRevB_RowNum]
from Erp.PartRev as PartRevB
where (PartRevB.Approved = 1 and PartRevB.EffectiveDate <= getdate() and PartRevB.PartNum = [PartMtl].[MtlPartNum] )) as PartRevB1 on
PartRevA.PartNum = PartRevB1.PartRevB_PartNum
and PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum
and PartRevA.Company = PartRevB1.Company
where PartRevB1.Calculated_PartRevB_RowNum = 1))) as [Calculated_RevNum],
(cast(PartMtl.QtyPer as decimal(10,2))) as [Calculated_ChildQtyPer],
[PartBOMChild].[PartDescription] as [PartBOMChild_PartDescription],
(cast(PartMtl.MtlSeq as nvarchar(254))) as [Calculated_SeqNum],
(CAST(PartMtl.MtlSeq as Int)) as [Calculated_MtlSeq]
from Erp.PartMtl as PartMtl
inner join dbo.Part as PartBOMChild on
PartMtl.Company = PartBOMChild.Company
and PartMtl.MtlPartNum = PartBOMChild.PartNum
) as BOMChildren
inner join BOMReviewParent as BOMReviewParent on
BOMChildren.PartMtl_PartNum = BOMReviewParent.Calculated_ChildPartNum
and BOMChildren.PartMtl_RevisionNum = BOMReviewParent.Calculated_ChildRevNum
)
select BOMReviewParentTOP.[Calculated_Customer],
BOMReviewParentTOP.[Calculated_TopPart],
BOMReviewParentTOP.[Calculated_TopPartRev],
BOMReviewParentTOP.[Calculated_ParentPartNum],
BOMReviewParentTOP.[Calculated_ParentRevNum],
BOMReviewParentTOP.[Calculated_ChildPartNum],
BOMReviewParentTOP.[Calculated_ChildRevNum],
BOMReviewParentTOP.[Calculated_lv],
BOMReviewParentTOP.[Calculated_QtyPer],
BOMReviewParentTOP.[Calculated_PartName],
BOMReviewParentTOP.[Calculated_OpSeq],
BOMReviewParentTOP.[Calculated_Sort]
from BOMReviewParent as BOMReviewParentTOP