I am trying to build a BAQ that calculates how many of each top level part (finished good) can be built based on a multilevel BOM and current inventory.

Attached is a BAQ that uses recursion to explode the BOM and calculate how much of each material is needed for the top level part. The excel file is sample data showing the current BAQ output and how it needs to collapse each level into the level above because assemblies could be stocked or created.

Where I am stuck is rolling up the on hand quantity of the current level plus the quantity that can be made based on the lower level’s on hand quantity all the way to the parent part, taking the minimum on each level. I tried using window functions to get the minimum at each level but it seems to fail a few levels deep. The deepest BOM is about 6 levels deep. This report needs to calculate the “can be built” quantity for about a thousand top level parts at a time.

Is this solvable using just a BAQ? If not, what is the best method to do this calculation?

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],

(0) as [Calculated_Hierarchy],

(cast ( substring(’ + + + + + + + + +‘,1 ,(Hierarchy + 1) ) + PartMtl.MtlPartNum

as nvarchar(25))) as [Calculated_Ind1],

(cast(Part.PartNum +’- ‘+ PartMtl.MtlPartNum as nvarchar(1000))) as [Calculated_SortOrder],

[Part].[PartNum] as [Part_PartNum],

(convert(int, PartMtl.QtyPer)) as [Calculated_ExtQtyPer],

(cast(Part.PartNum +’|‘+ PartMtl.MtlPartNum as nvarchar(1000))) as [Calculated_SplitByDelim]

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.PartNum = PartRev.PartNum

and PartMtl.RevisionNum = PartRev.RevisionNum

and PartMtl.AltMethod = PartRev.AltMethod

and ( PartRev.Approved = 1 )

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],

(SubQuery1.Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],

(cast ( substring(’+ + + + + + + + + + + ',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum

as nvarchar(25))) as [Calculated_Ind2],

(cast(SubQuery1.Calculated_SortOrder + '- ‘+ right(‘0000’ + cast(PartMtl1.MtlSeq as nvarchar(4)),4)as nvarchar(1000))) as [Calculated_SortOrder2],

[SubQuery1].[Part_PartNum] as [Part_PartNum],

(convert(int, SubQuery1.PartMtl_QtyPer* PartMtl1.QtyPer)) as [Calculated_ExtQtyPer],

(cast(SubQuery1.Calculated_SplitByDelim + ‘|’+ PartMtl1.MtlPartNum as nvarchar(1000))) as [Calculated_SplitByDelim2]

from Erp.PartMtl as PartMtl1

inner join SubQuery1 as SubQuery1 on

SubQuery1.PartMtl_Company = PartMtl1.Company

and SubQuery1.PartMtl_MtlPartNum = PartMtl1.PartNum

inner join Erp.PartRev as PartRev1 on

PartMtl1.Company = PartRev1.Company

and PartMtl1.PartNum = PartRev1.PartNum

and PartMtl1.RevisionNum = PartRev1.RevisionNum

and PartMtl1.AltMethod = PartRev1.AltMethod

and ( PartRev1.Approved = 1 ))

select

[SubQuery11].[Part_PartNum] as [Part_PartNum],

[SubQuery11].[PartMtl_PartNum] as [PartMtl_PartNum],

[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],

[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],

[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],

(replicate(’ ‘, SubQuery11.Calculated_Hierarchy)+’+ '+ SubQuery11.PartMtl_MtlPartNum) as [Calculated_IndentedPartNum],

[SubQuery11].[Calculated_SortOrder] as [Calculated_SortOrder],

[Part1].[ClassID] as [Part1_ClassID],

[Part1].[NonStock] as [Part1_NonStock],

[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],

[SubQuery11].[Calculated_ExtQtyPer] as [Calculated_ExtQtyPer],

[OHQ].[Calculated_OHQty] as [Calculated_OHQty],

(case when SubQuery11.Calculated_ExtQtyPer>0 then OHQ.Calculated_OHQty/ SubQuery11.Calculated_ExtQtyPer else 0 end) as [Calculated_OHQtyDivExtQtyPer],

[SubQuery11].[Calculated_SplitByDelim] as [Calculated_SplitByDelim]

from SubQuery1 as SubQuery11

left outer join Erp.Part as Part1 on

SubQuery11.PartMtl_MtlPartNum = Part1.PartNum

left outer join (select

[PartWhse].[PartNum] as [PartWhse_PartNum],

(sum(case when Part2.NonStock=1 then null else

PartWhse.OnHandQty end)) as [Calculated_OHQty]

from Erp.PartWhse as PartWhse

inner join Erp.Part as Part2 on

PartWhse.Company = Part2.Company

and PartWhse.PartNum = Part2.PartNum

group by [PartWhse].[PartNum]) as OHQ on

SubQuery11.PartMtl_MtlPartNum = OHQ.PartWhse_PartNum

order by SubQuery11.Calculated_SortOrder

BOMExample.baq (191.0 KB)

BOM Example.xlsx (10.8 KB)