I have a BAQ to find parts marked as spare parts (Part.IsGiftCard) inside an indented bill of material for all bills of material on a sales order. Below is my query. The query works great and I have turned it into a report but I have noticed one problem I can’t seem to figure out. If I have a part on the order with a BOM the query finds all the spare parts but if I just add a single part to the order that itself is marked a spare part it won’t show in the query. Have I missed a level somewhere in the query?
Heres the query:
with [PartMtl] as
(select
[PartMtl].[Company] as [PartMtl_Company],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[Customer].[Name] as [Customer_Name],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[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],
[SubQuery5].[Part_IUM] as [Part_IUM],
[PartMtl].[RelatedOperation] as [PartMtl_RelatedOperation],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(0) as [Calculated_Hierarchy],
(cast (substring('........',1 ,(Hierarchy + 1) ) + PartMtl.MtlPartNum as nvarchar(25))) as [Calculated_Ind1],
[SubQuery5].[Part_PartDescription] as [Part_PartDescription],
[SubQuery5].[Part_TypeCode] as [Part_TypeCode],
[SubQuery5].[Part_ClassID] as [Part_ClassID],
[SubQuery5].[Part_CostMethod] as [Part_CostMethod],
[SubQuery5].[Part_IsGiftCard] as [Part_IsGiftCard],
[SubQuery5].[Calculated_PartCost] as [Calculated_PartCost],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
(substring(OrderDtl.LineDesc,1 ,100 )) as [Calculated_ParentAssyDesc]
from Erp.PartMtl as PartMtl
inner join Erp.OrderDtl as OrderDtl on
PartMtl.Company = OrderDtl.Company
and PartMtl.PartNum = OrderDtl.PartNum
and PartMtl.RevisionNum = OrderDtl.RevisionNum
inner join Erp.Customer as Customer on
OrderDtl.Company = Customer.Company
and OrderDtl.CustNum = Customer.CustNum
inner join (select
[Part].[Company] as [Part_Company],
[Part].[IUM] as [Part_IUM],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[TypeCode] as [Part_TypeCode],
[Part].[ClassID] as [Part_ClassID],
[Part].[CostMethod] as [Part_CostMethod],
[Part].[IsGiftCard] as [Part_IsGiftCard],
(case
when Part.CostMethod = 'A' then PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost
when Part.CostMethod = 'L' then PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost
else PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost
end) as [Calculated_PartCost],
[Part].[PartNum] as [Part_PartNum]
from Erp.Part as Part
inner join Erp.PartCost as PartCost on
PartCost.Company = Part.Company
and PartCost.PartNum = Part.PartNum) as SubQuery5 on
SubQuery5.Part_Company = PartMtl.Company
and SubQuery5.Part_PartNum = PartMtl.MtlPartNum
union all
select
[PartMtl1].[Company] as [PartMtl1_Company],
[PartMtl2].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
[PartMtl2].[Customer_Name] as [Customer_Name],
[PartMtl2].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
[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].[UOMCode] as [PartMtl1_UOMCode],
[PartMtl1].[RelatedOperation] as [PartMtl1_RelatedOperation],
[PartMtl1].[PullAsAsm] as [PartMtl1_PullAsAsm],
[PartMtl1].[ViewAsAsm] as [PartMtl1_ViewAsAsm],
[PartMtl1].[PlanAsAsm] as [PartMtl1_PlanAsAsm],
(Calculated_Hierarchy + 1) as [Calculated_Hierarchy2],
(cast ( substring('........',1 ,(Hierarchy2 + 1) ) + PartMtl1.MtlPartNum
as nvarchar(25))) as [Calculated_Ind2],
[SubQuery51].[Part_PartDescription] as [Part_PartDescription],
[SubQuery51].[Part_TypeCode] as [Part_TypeCode],
[SubQuery51].[Part_ClassID] as [Part_ClassID],
[SubQuery51].[Part_CostMethod] as [Part_CostMethod],
[SubQuery51].[Part_IsGiftCard] as [Part_IsGiftCard],
[SubQuery51].[Calculated_PartCost] as [Calculated_PartCost],
[PartMtl2].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[PartMtl2].[Calculated_ParentAssyDesc] as [Calculated_ParentAssyDesc]
from Erp.PartMtl as PartMtl1
inner join PartMtl as PartMtl2 on
PartMtl2.PartMtl_Company = PartMtl1.Company
and PartMtl2.PartMtl_MtlPartNum = PartMtl1.PartNum
inner join (select
[Part].[Company] as [Part_Company],
[Part].[IUM] as [Part_IUM],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[TypeCode] as [Part_TypeCode],
[Part].[ClassID] as [Part_ClassID],
[Part].[CostMethod] as [Part_CostMethod],
[Part].[IsGiftCard] as [Part_IsGiftCard],
(case
when Part.CostMethod = 'A' then PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost
when Part.CostMethod = 'L' then PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost
else PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMaterialCost + PartCost.StdSubContCost
end) as [Calculated_PartCost],
[Part].[PartNum] as [Part_PartNum]
from Erp.Part as Part
inner join Erp.PartCost as PartCost on
PartCost.Company = Part.Company
and PartCost.PartNum = Part.PartNum) as SubQuery51 on
SubQuery51.Part_Company = PartMtl1.Company
and SubQuery51.Part_PartNum = PartMtl1.MtlPartNum)
select
[PartMtl3].[PartMtl_Company] as [PartMtl_Company],
[PartMtl3].[OrderDtl_OrderNum] as [OrderDtl_OrderNum],
[PartMtl3].[Customer_Name] as [Customer_Name],
[PartMtl3].[OrderDtl_OrderLine] as [OrderDtl_OrderLine],
[PartMtl3].[OrderDtl_PartNum] as [OrderDtl_PartNum],
[PartMtl3].[Calculated_ParentAssyDesc] as [Calculated_ParentAssyDesc],
[PartMtl3].[PartMtl_PartNum] as [PartMtl_PartNum],
[PartMtl3].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[PartMtl3].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[PartMtl3].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[PartMtl3].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[PartMtl3].[Part_IUM] as [Part_IUM],
[PartMtl3].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[PartMtl3].[Calculated_Hierarchy] as [Calculated_Hierarchy],
[PartMtl3].[Calculated_Ind1] as [Calculated_Ind1],
[PartMtl3].[Part_PartDescription] as [Part_PartDescription],
[PartMtl3].[Part_TypeCode] as [Part_TypeCode],
[PartMtl3].[Part_ClassID] as [Part_ClassID],
[PartMtl3].[Part_CostMethod] as [Part_CostMethod],
[PartMtl3].[Part_IsGiftCard] as [Part_IsGiftCard],
[PartMtl3].[Calculated_PartCost] as [Calculated_PartCost],
[TranDate].[PartTran_TranDate] as [PartTran_TranDate]
from PartMtl as PartMtl3
left outer join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[TranDate] as [PartTran_TranDate],
[PartTran].[TranNum] as [PartTran_TranNum],
(Rank() over (partition by PartTran.PartNum order by PartTran.TranDate desc, PartTran.TranNum desc)) as [Calculated_TranDate]
from Erp.PartTran as PartTran
where (PartTran.TranType = 'MFG-STK' or PartTran.TranType = 'PUR-STK')) as TranDate on
TranDate.PartTran_Company = PartMtl3.PartMtl_Company
and TranDate.PartTran_PartNum = PartMtl3.PartMtl_MtlPartNum
and ( TranDate.Calculated_TranDate = 1 )
where (PartMtl3.Part_IsGiftCard = true)
order by PartMtl3.OrderDtl_OrderLine