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