BAQ-Recursive BAQ for a Costed BOM report

Jack,

We have recently built something like that.

Brief desciption of what this BAQ does:

BOMReviewParent - this sets the anchor for the recursive BOM blow down. Fields inside this query will be made availble for use elsewhere
BOMChildren - this uses PartMtl to display the Parent to Child relationships which can be looped through by the recursive code
BOMReviewChild - this holds the recursive code to blow down through the BOM layers to build the indented BOM. Besides building the visual structure it also extends the Qty/Per down through the BOM layers
PartRevB - this finds the correct current revision, to do this we must ensure the Rev is approved, that we are only looking at the default (blank) AltMethod, and that we are choosing the EffectiveDate which is the most recent (but not in the future)
PartRevA - this feeds the recursive code with information obtained in PartRevB
JobDemand - this is used to obtain a sum of the jobmatl quantities
OrderDemand - this is used to obtain a sum of the orderrel quantities
RcvDetailMain - this is used to determine from whom we recieved the last PO
RecDetailsList - this is used to determine when we received the last PO, the qty that was recieved (as there may be price breaks), and the cost of each piece received
SumPartWhse - this is used to get a summary of the on hand blance
ESH - this is used to get a summary of the ESH for each BOM level for manufactured goods
BOMReviewTop - this is the output of the recursive BOM blowdown. It is also where all other tables (except costs) are joined
LastPostedCostRoll - this tells us when each part had its last cost capture/roll (it can be different for each part as rolls sometimes only occur if there are parts on hand, and can be sectionalized by the person performing the roll)
LastCostRoll - using the partnum and date of the posted roll we can then determine the cost per unit of measure
BOMFinal - This takes the aggregated inputs inside BOMReviewTop and appends costing data. It allow applies the sort order

Note: I only pull the default method (I do this by constraining all tables with AltMethod = (blank))

/*
 * 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 [BOMReviewParent] as 
(select 
    (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],
    (0) as [Calculated_lv],
    (Cast(part.PurchasingFactor as decimal(10,6))) as [Calculated_QtyPer],
    (Cast(part.PartDescription as nvarchar(500))) as [Calculated_PartName],
    (case      when Part.TypeCode = 'P' then PartPlant1.LeadTime      else PartPlant1.LvlMfgLeadTimeSys / 4 *7 --this is to get them both back to calendar days  end) as [Calculated_ChildLeadTime],
    (0) as [Calculated_OpSeq],
    (CAST(part.PartNum + '-' + partrev.RevisionNum as nvarchar(500))) as [Calculated_Sort]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on 
    Part.Company = PartRev.Company
    and Part.PartNum = PartRev.PartNum
    and ( PartRev.AltMethod = ''  )

inner join Erp.PartPlant as PartPlant1 on 
    Part.Company = PartPlant1.Company
    and Part.PartNum = PartPlant1.PartNum
    and ( PartPlant1.Plant = 'MfgSys'  )

where (PartRev.PartNum = @PartNum  and PartRev.RevisionNum = @RevNum)

union all
select 
    [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_PartRevNum] as [Calculated_PartRevNum],
    (BOMReviewParent.Calculated_lv + 1) as [Calculated_BOMlv],
    (cast(BOMChildren.Calculated_ChildQtyPer * BOMReviewParent.Calculated_QtyPer as decimal(10,6))) as [Calculated_BOMQtyPer],
    (CAST(REPLICATE ('|     ' , BOMReviewParent.Calculated_lv +1) + BOMChildren.PartBOMChild_PartDescription as nvarchar(500))) as [Calculated_BOMPartName],
    (BOMChildren.Calculated_ChildLeadTime + BOMReviewParent.Calculated_ChildLeadTime) as [Calculated_BOMLeadTime],
    (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 
    [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].[PartNum] as [PartRevB_PartNum],
    [PartRevB].[RevisionNum] as [PartRevB_RevisionNum],
    (ROW_NUMBER() OVER (PARTITION BY partrevb.PartNum ORDER BY  partrevb.EffectiveDate DESC)) as [Calculated_PartRevB_RowNum]
from Erp.PartRev as PartRevB
where (PartRevB.Approved = 1  and PartRevB.EffectiveDate <= getdate()  and PartRevB.PartNum = [PartMtl].[MtlPartNum]  and PartRevB.AltMethod = ''))  as PartRevB1 on 
    PartRevA.PartNum = PartRevB1.PartRevB_PartNum
    and PartRevA.RevisionNum = PartRevB1.PartRevB_RevisionNum
    and ( PartRevB1.Calculated_PartRevB_RowNum = 1  )

where (PartRevA.AltMethod = '')
 and (PartRevB1.Calculated_PartRevB_RowNum = 1)))) as [Calculated_PartRevNum],
    (cast(PartMtl.QtyPer as decimal(10,6))) as [Calculated_ChildQtyPer],
    [PartBOMChild].[PartDescription] as [PartBOMChild_PartDescription],
    (case      when PartBOMChild.TypeCode = 'P' then PartPlant2.LeadTime      else PartPlant2.LvlMfgLeadTimeSys / 4 *7 --this is to get them both back to calendar days  end) as [Calculated_ChildLeadTime],
    (cast(PartMtl.MtlSeq as nvarchar(254))) as [Calculated_SeqNum],
    (CAST(PartMtl.MtlSeq as Int)) as [Calculated_MtlSeq]
from Erp.PartMtl as PartMtl
inner join Erp.Part as PartBOMChild on 
    PartMtl.Company = PartBOMChild.Company
    and PartMtl.MtlPartNum = PartBOMChild.PartNum
inner join Erp.PartPlant as PartPlant2 on 
    PartPlant2.Company = PartMtl.Company
    and PartPlant2.PartNum = PartMtl.MtlPartNum
    and ( PartPlant2.Plant = 'MfgSys'  )

where (PartMtl.AltMethod = ''))  as BOMChildren
inner join  BOMReviewParent  as BOMReviewParent on 
    BOMChildren.PartMtl_PartNum = BOMReviewParent.Calculated_ChildPartNum
    and BOMChildren.PartMtl_RevisionNum = BOMReviewParent.Calculated_ChildRevNum)
 ,[BOMReviewTop] as 
(select 
    [BOMReviewParentTOP].[Calculated_Sort] as [Calculated_Sort],
    [BOMReviewParentTOP].[Calculated_TopPart] as [Calculated_TopPart],
    [BOMReviewParentTOP].[Calculated_TopPartRev] as [Calculated_TopPartRev],
    [BOMReviewParentTOP].[Calculated_ParentPartNum] as [Calculated_ParentPartNum],
    [BOMReviewParentTOP].[Calculated_ParentRevNum] as [Calculated_ParentRevNum],
    [BOMReviewParentTOP].[Calculated_OpSeq] as [Calculated_OpSeq],
    [BOMReviewParentTOP].[Calculated_ChildPartNum] as [Calculated_ChildPartNum],
    [BOMReviewParentTOP].[Calculated_ChildRevNum] as [Calculated_ChildRevNum],
    (case when (BOMReviewParentTOP.Calculated_ChildRevNum) is null then 'null'       when BOMReviewParentTOP.Calculated_ChildRevNum = '' then 'null'       else BOMReviewParentTOP.Calculated_ChildRevNum  end) as [Calculated_JoinRev],
    [BOMReviewParentTOP].[Calculated_PartName] as [Calculated_PartName],
    [Part2].[TypeCode] as [Part2_TypeCode],
    [BOMReviewParentTOP].[Calculated_QtyPer] as [Calculated_QtyPer],
    [Part2].[IUM] as [Part2_IUM],
    [PartPlant].[MinOrderQty] as [PartPlant_MinOrderQty],
    [SumPartWhse].[Calculated_SumQOH] as [Calculated_SumQOH],
    [SumPartWhse].[Calculated_SumNonNetQty] as [Calculated_SumNonNetQty],
    [SumPartWhse].[Calculated_SumRsvdQty] as [Calculated_SumRsvdQty],
    (ISNULL(SumPartWhse.Calculated_SumQOH,0)-ISNULL(SumPartWhse.Calculated_SumRsvdQty,0)) as [Calculated_AvailableQTYOH],
    ((ISNULL(JobDemand.Calculated_JMRequiredQTY, 0) - ISNULL(JobDemand.Calculated_JMIssuedQTY, 0)) + ISNULL(OrderDemand.Calculated_ODOrderQty, 0)  --could just use partdtl table sums instead) as [Calculated_PartDemand],
    (BOMReviewParentTOP.Calculated_QtyPer * ESH.Calculated_SumESH) as [Calculated_ESH],
    (case      when Part2.TypeCode = 'P' then PartPlant.LeadTime      else PartPlant.LvlMfgLeadTimeSys / 4 *7 --this is to get them both back to calendar days  end) as [Calculated_LeadTime],
    [BOMReviewParentTOP].[Calculated_ChildLeadTime] as [Calculated_ChildLeadTime],
    [Part2].[ClassID] as [Part2_ClassID],
    [PartClass].[BuyerID] as [PartClass_BuyerID],
    [Vendor].[VendorID] as [Vendor_VendorID],
    [RcvDetailMain].[RcvDtlMain_ReceiptDate] as [RcvDtlMain_ReceiptDate],
    [RcvDetailMain].[RcvDtlMain_OurQty] as [RcvDtlMain_OurQty],
    [RcvDetailMain].[RcvDtlMain_OurUnitCost] as [RcvDtlMain_OurUnitCost],
    [Part2].[InActive] as [Part2_InActive],
    [BOMReviewParentTOP].[Calculated_lv] as [Calculated_lv]
from  BOMReviewParent  as BOMReviewParentTOP
left outer join Erp.Part as Part2 on 
    BOMReviewParentTOP.Calculated_ChildPartNum = Part2.PartNum
left outer join Erp.PartPlant as PartPlant on 
    Part2.Company = PartPlant.Company
    and Part2.PartNum = PartPlant.PartNum
left outer join Erp.Vendor as Vendor on 
    PartPlant.Company = Vendor.Company
    and PartPlant.VendorNum = Vendor.VendorNum
left outer join  (select 
    [JobMtl].[Company] as [JobMtl_Company],
    [JobMtl].[PartNum] as [JobMtl_PartNum],
    (SUM(JobMtl.RequiredQty)) as [Calculated_JMRequiredQTY],
    (SUM(JobMtl.IssuedQty)) as [Calculated_JMIssuedQTY]
from Erp.JobMtl as JobMtl
where (JobMtl.JobComplete = 0)
group by [JobMtl].[Company],
    [JobMtl].[PartNum])  as JobDemand on 
    Part2.Company = JobDemand.JobMtl_Company
    and Part2.PartNum = JobDemand.JobMtl_PartNum
left outer join  (select 
    [OrderDtl].[Company] as [OrderDtl_Company],
    [OrderDtl].[PartNum] as [OrderDtl_PartNum],
    (SUM(OrderRel.OurReqQty - OrderRel.OurJobShippedQty - OrderRel.OurStockShippedQty)) as [Calculated_ODOrderQty]
from Erp.OrderDtl as OrderDtl
inner join Erp.OrderRel as OrderRel on 
    OrderDtl.Company = OrderRel.Company
    and OrderDtl.OrderNum = OrderRel.OrderNum
    and OrderDtl.OrderLine = OrderRel.OrderLine
    and ( OrderRel.OpenRelease = 1  )

where (OrderDtl.OpenLine = 1)
group by [OrderDtl].[Company],
    [OrderDtl].[PartNum])  as OrderDemand on 
    Part2.Company = OrderDemand.OrderDtl_Company
    and Part2.PartNum = OrderDemand.OrderDtl_PartNum
left outer join  (select 
    [RcvDtlMain].[PartNum] as [RcvDtlMain_PartNum],
    [RcvDtlMain].[ReceiptDate] as [RcvDtlMain_ReceiptDate],
    [RcvDtlMain].[OurQty] as [RcvDtlMain_OurQty],
    [RcvDtlMain].[OurUnitCost] as [RcvDtlMain_OurUnitCost]
from Erp.RcvDtl as RcvDtlMain
inner join  (select 
    [RcvDtl].[PartNum] as [RcvDtl_PartNum],
    (MAX(rcvdtl.SysRevID)) as [Calculated_RDMaxSysRevID]
from Erp.RcvDtl as RcvDtl
group by [RcvDtl].[PartNum])  as RecDetailsList on 
    RcvDtlMain.SysRevID = RecDetailsList.Calculated_RDMaxSysRevID
    and RcvDtlMain.PartNum = RecDetailsList.RcvDtl_PartNum)  as RcvDetailMain on 
    Part2.PartNum = RcvDetailMain.RcvDtlMain_PartNum
left outer join  (select 
    [PartWhse1].[Company] as [PartWhse1_Company],
    [PartWhse1].[PartNum] as [PartWhse1_PartNum],
    (SUM(PartWhse1.OnHandQty)) as [Calculated_SumQOH],
    (SUM(PartWhse1.DemandQty)) as [Calculated_SumDemandQty],
    (SUM(PartWhse1.AllocatedQty)) as [Calculated_SumAllocQty],
    (SUM(PartWhse1.ReservedQty)) as [Calculated_SumRsvdQty],
    (SUM(PartWhse1.NonNettableQty)) as [Calculated_SumNonNetQty]
from Erp.PartWhse as PartWhse1
group by [PartWhse1].[Company],
    [PartWhse1].[PartNum])  as SumPartWhse on 
    SumPartWhse.PartWhse1_Company = Part2.Company
    and SumPartWhse.PartWhse1_PartNum = Part2.PartNum
left outer join Erp.PartClass as PartClass on 
    PartClass.Company = Part2.Company
    and PartClass.ClassID = Part2.ClassID
inner join Erp.Part as PartParentPart on 
    BOMReviewParentTOP.Calculated_TopPart = PartParentPart.PartNum
left outer join  (select 
    [PartOpr].[PartNum] as [PartOpr_PartNum],
    [PartOpr].[RevisionNum] as [PartOpr_RevisionNum],
    (SUM(PartOpr.QtyPer * PartOpr.ProdCrewSize * PartOpr.ProdStandard)) as [Calculated_SumESH]
from Erp.PartOpr as PartOpr
inner join Erp.Part as Part4 on 
    PartOpr.Company = Part4.Company
    and PartOpr.PartNum = Part4.PartNum
    and ( Part4.TypeCode <> 'P'  )

where (PartOpr.AltMethod = '')
group by [PartOpr].[PartNum],
    [PartOpr].[RevisionNum])  as ESH on 
    ESH.PartOpr_PartNum = BOMReviewParentTOP.Calculated_ChildPartNum
    and ESH.PartOpr_RevisionNum = BOMReviewParentTOP.Calculated_ChildRevNum)
 ,[LastCostRoll] as 
(select 
    [CostPart1].[Company] as [CostPart1_Company],
    [CostPart1].[PartNum] as [CostPart1_PartNum],
    [CostPart1].[RevisionNum] as [CostPart1_RevisionNum],
    (case when CostPart1.RevisionNum = '' then 'null'       when (CostPart1.RevisionNum) is null then 'null'       else CostPart1.RevisionNum  end) as [Calculated_JoinRev],
    [LastPostedCostRoll].[Calculated_LastPostedDt] as [Calculated_LastPostedDt],
    [CostPart1].[TypeCode] as [CostPart1_TypeCode],
    (case when CostPart1.TypeCode = 'P' then CostPart1.StdMaterialCost else 0 end) as [Calculated_UnitMatlCost],
    (CostPart1.TLRLaborCost + CostPart1.TLRSetupLaborCost) as [Calculated_UnitLabCost],
    (CostPart1.TLRBurdenCost + CostPart1.TLRSetupBurdenCost) as [Calculated_UnitBurCost],
    (CostPart1.TLRSubcontractCost) as [Calculated_UnitSubCost]
from Erp.CostGrp as CostGrp1
inner join Erp.CostPart as CostPart1 on 
    CostGrp1.Company = CostPart1.Company
    and CostGrp1.GroupID = CostPart1.GroupID
    and ( CostPart1.AltMethod = ''  )

inner join  (select 
    [CostPart].[Company] as [CostPart_Company],
    [CostPart].[PartNum] as [CostPart_PartNum],
    [CostPart].[RevisionNum] as [CostPart_RevisionNum],
    (MAX(CostGrp.PostedDate)) as [Calculated_LastPostedDt]
from Erp.CostPart as CostPart
inner join Erp.CostGrp as CostGrp on 
    CostPart.Company = CostGrp.Company
    and CostPart.GroupID = CostGrp.GroupID
    and ( CostGrp.Posted = 1  )

where (CostPart.AltMethod = '')
group by [CostPart].[Company],
    [CostPart].[PartNum],
    [CostPart].[RevisionNum])  as LastPostedCostRoll on 
    CostGrp1.Company = LastPostedCostRoll.CostPart_Company
    and CostGrp1.PostedDate = LastPostedCostRoll.Calculated_LastPostedDt
inner join  (select 
    [CostPart].[Company] as [CostPart_Company],
    [CostPart].[PartNum] as [CostPart_PartNum],
    [CostPart].[RevisionNum] as [CostPart_RevisionNum],
    (MAX(CostGrp.PostedDate)) as [Calculated_LastPostedDt]
from Erp.CostPart as CostPart
inner join Erp.CostGrp as CostGrp on 
    CostPart.Company = CostGrp.Company
    and CostPart.GroupID = CostGrp.GroupID
    and ( CostGrp.Posted = 1  )

where (CostPart.AltMethod = '')
group by [CostPart].[Company],
    [CostPart].[PartNum],
    [CostPart].[RevisionNum])  as LastPostedCostRoll
    and 
    CostPart1.Company = LastPostedCostRoll.CostPart_Company
    and CostPart1.PartNum = LastPostedCostRoll.CostPart_PartNum
    and CostPart1.RevisionNum = LastPostedCostRoll.CostPart_RevisionNum)

select 
    [BOMReviewTop].[Calculated_Sort] as [Calculated_Sort],
    [BOMReviewTop].[Calculated_TopPart] as [Calculated_TopPart],
    [BOMReviewTop].[Calculated_TopPartRev] as [Calculated_TopPartRev],
    [BOMReviewTop].[Calculated_ParentPartNum] as [Calculated_ParentPartNum],
    [BOMReviewTop].[Calculated_ParentRevNum] as [Calculated_ParentRevNum],
    [BOMReviewTop].[Calculated_OpSeq] as [Calculated_OpSeq],
    [BOMReviewTop].[Calculated_lv] as [Calculated_lv],
    [BOMReviewTop].[Calculated_ChildPartNum] as [Calculated_ChildPartNum],
    [BOMReviewTop].[Calculated_ChildRevNum] as [Calculated_ChildRevNum],
    [BOMReviewTop].[Calculated_PartName] as [Calculated_PartName],
    [BOMReviewTop].[Part2_TypeCode] as [Part2_TypeCode],
    [BOMReviewTop].[Calculated_QtyPer] as [Calculated_QtyPer],
    [BOMReviewTop].[Part2_IUM] as [Part2_IUM],
    (ExtMatlCost + ExtLabCost + ExtBurCost + ExtSubCost) as [Calculated_ExtTOTALCost],
    (BOMReviewTop.Calculated_QtyPer * LastCostRoll.Calculated_UnitMatlCost) as [Calculated_ExtMatlCost],
    (BOMReviewTop.Calculated_QtyPer  * LastCostRoll.Calculated_UnitLabCost) as [Calculated_ExtLabCost],
    (BOMReviewTop.Calculated_QtyPer  * LastCostRoll.Calculated_UnitBurCost) as [Calculated_ExtBurCost],
    (BOMReviewTop.Calculated_QtyPer  * LastCostRoll.Calculated_UnitSubCost) as [Calculated_ExtSubCost],
    [BOMReviewTop].[PartPlant_MinOrderQty] as [PartPlant_MinOrderQty],
    [BOMReviewTop].[Calculated_SumQOH] as [Calculated_SumQOH],
    [BOMReviewTop].[Calculated_SumNonNetQty] as [Calculated_SumNonNetQty],
    [BOMReviewTop].[Calculated_SumRsvdQty] as [Calculated_SumRsvdQty],
    [BOMReviewTop].[Calculated_AvailableQTYOH] as [Calculated_AvailableQTYOH],
    [BOMReviewTop].[Calculated_PartDemand] as [Calculated_PartDemand],
    [BOMReviewTop].[Calculated_ESH] as [Calculated_ESH],
    [BOMReviewTop].[Calculated_LeadTime] as [Calculated_LeadTime],
    [BOMReviewTop].[Calculated_ChildLeadTime] as [Calculated_ChildLeadTime],
    [BOMReviewTop].[Part2_ClassID] as [Part2_ClassID],
    [BOMReviewTop].[PartClass_BuyerID] as [PartClass_BuyerID],
    [BOMReviewTop].[Vendor_VendorID] as [Vendor_VendorID],
    [BOMReviewTop].[RcvDtlMain_ReceiptDate] as [RcvDtlMain_ReceiptDate],
    [BOMReviewTop].[RcvDtlMain_OurQty] as [RcvDtlMain_OurQty],
    [BOMReviewTop].[RcvDtlMain_OurUnitCost] as [RcvDtlMain_OurUnitCost],
    [BOMReviewTop].[Part2_InActive] as [Part2_InActive]
from  BOMReviewTop  as BOMReviewTop
left outer join  LastCostRoll  as LastCostRoll on 
    LastCostRoll.CostPart1_PartNum = BOMReviewTop.Calculated_ChildPartNum
    and LastCostRoll.Calculated_JoinRev = BOMReviewTop.Calculated_JoinRev
order by BOMReviewTop.Calculated_Sort