Good Day All!
Not sure what I changed in my BAQ, but for some reason it will no longer return any of my parts that are nested further in than BOM level 2. It returns BOM level 1 and 2, but no parts within the sub-assemblies on the second level. In fact, it doesn’t even display those 2nd level sub-assemblies. I’ll post the query, and I can give any other needed information. I believe this worked before, so not sure what changed now.
/*
* 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],
[PartMtl].[QtyPer] as [PartMtl_QtyPer],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
(1) as [Calculated_Hierarchy],
(case
when PartMtl.MtlSeq < 10 then cast(concat('000', PartMtl.MtlSeq) as nvarchar(25))
when PartMtl.MtlSeq < 100 then cast(concat('00', PartMtl.MtlSeq) as nvarchar (25))
when PartMtl.MtlSeq < 1000 then cast(concat('0', PartMtl.MtlSeq) as nvarchar (25))
end) as [Calculated_Ind1],
(PartMtl.PartNum) as [Calculated_TopLevel],
(case
when Hierarchy = 1 AND MinMtlSeq1 = 1 THEN PartMtl.PartNum
else ''
end) as [Calculated_DisplayedTopLevel],
[PartXRefVend].[VendPartNum] as [PartXRefVend_VendPartNum],
[PartXRefVend].[MfgPartNum] as [PartXRefVend_MfgPartNum],
[PartWhse].[OnHandQty] as [PartWhse_OnHandQty],
[PartWhse].[DemandQty] as [PartWhse_DemandQty],
[PlantWhse].[PrimBin] as [PlantWhse_PrimBin],
[Part].[UnitPrice] as [Part_UnitPrice],
(PartCost.StdLaborCost+ PartCost.StdBurdenCost+ PartCost.StdMaterialCost+ PartCost.StdSubContCost+ PartCost.StdMtlBurCost) as [Calculated_StdTotalCost],
[Part].[PartDescription] as [Part_PartDescription],
(Row_Number() over (partition by Part.Company, TopLevel order by PartMtl.MtlSeq)) as [Calculated_MinMtlSeq1]
from Erp.PartMtl as PartMtl
left outer join Erp.PartXRefVend as PartXRefVend on
PartMtl.MtlPartNum = PartXRefVend.PartNum
left outer join Erp.PartWhse as PartWhse on
PartWhse.PartNum = PartMtl.MtlPartNum
left outer join Erp.PlantWhse as PlantWhse on
PartMtl.MtlPartNum = PlantWhse.PartNum
inner join Erp.Part as Part on
PlantWhse.Company = Part.Company
and PlantWhse.PartNum = Part.PartNum
left outer join Erp.PartCost as PartCost on
PartCost.PartNum = PartMtl.MtlPartNum
where PartMtl.PartNum like @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],
[PartMtl1].[QtyPer] as [PartMtl1_QtyPer],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(case
when PartMtl1.MtlSeq < 10 then cast(concat(Calculated_Ind1, '-', '000', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 100 then cast(concat(Calculated_Ind1, '-', '00', PartMtl1.MtlSeq) as nvarchar(25))
when PartMtl1.MtlSeq < 1000 then cast(concat(Calculated_Ind1, '-', '0', PartMtl1.MtlSeq) as nvarchar(25))
end) as [Calculated_Ind2],
(Calculated_TopLevel) as [Calculated_TopLevel1],
(case
when Hierarchy2 = 1 AND MinMtlSeq2 = 1 THEN PartMtl1.PartNum
else ''
end) as [Calculated_DisplayTopLevel2],
[PartXRefVend1].[VendPartNum] as [PartXRefVend1_VendPartNum],
[PartXRefVend1].[MfgPartNum] as [PartXRefVend1_MfgPartNum],
[PartWhse1].[OnHandQty] as [PartWhse1_OnHandQty],
[PartWhse1].[DemandQty] as [PartWhse1_DemandQty],
[PlantWhse1].[PrimBin] as [PlantWhse1_PrimBin],
[Part1].[UnitPrice] as [Part1_UnitPrice],
(PartCost1.StdLaborCost+ PartCost1.StdBurdenCost+ PartCost1.StdMaterialCost+ PartCost1.StdSubContCost+ PartCost1.StdMtlBurCost) as [Calculated_StdTotalCost1],
[Part1].[PartDescription] as [Part1_PartDescription],
(Row_Number() over (partition by Part1.Company, TopLevel1 order by PartMtl1.MtlSeq)) as [Calculated_MinMtlSeq2]
from Erp.PartMtl as PartMtl1
inner join SubQuery1 as SubQuery1 on
PartMtl1.PartNum = SubQuery1.PartMtl_MtlPartNum
and PartMtl1.Company = SubQuery1.PartMtl_Company
inner join Erp.PartXRefVend as PartXRefVend1 on
PartXRefVend1.PartNum = PartMtl1.MtlPartNum
inner join Erp.PartWhse as PartWhse1 on
PartWhse1.PartNum = PartMtl1.MtlPartNum
inner join Erp.PlantWhse as PlantWhse1 on
PartMtl1.MtlPartNum = PlantWhse1.PartNum
inner join Erp.Part as Part1 on
PlantWhse1.Company = Part1.Company
and PlantWhse1.PartNum = Part1.PartNum
inner join Erp.PartCost as PartCost1 on
PartCost1.PartNum = PartMtl1.MtlPartNum)
select
[SubQuery11].[Calculated_DisplayedTopLevel] as [Calculated_DisplayedTopLevel],
[SubQuery11].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[SubQuery11].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[SubQuery11].[Part_PartDescription] as [Part_PartDescription],
[SubQuery11].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[SubQuery11].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[SubQuery11].[PartXRefVend_VendPartNum] as [PartXRefVend_VendPartNum],
[SubQuery11].[PartXRefVend_MfgPartNum] as [PartXRefVend_MfgPartNum],
[SubQuery11].[PartWhse_OnHandQty] as [PartWhse_OnHandQty],
[SubQuery11].[PartWhse_DemandQty] as [PartWhse_DemandQty],
[SubQuery11].[Part_UnitPrice] as [Part_UnitPrice],
[SubQuery11].[Calculated_StdTotalCost] as [Calculated_StdTotalCost],
[SubQuery11].[PlantWhse_PrimBin] as [PlantWhse_PrimBin]
from SubQuery1 as SubQuery11
order by SubQuery11.Calculated_TopLevel, SubQuery11.Calculated_Ind1
