Because the company has many approved Partrevs, if it passes PartMtl, it will have to choose 1 approved Partvev and modify it last.
If taking BOM data, perhaps using ECO tables will be more accurate
with [MAXPartRev] as
(select
[PartRev].[Company] as [PartRev_Company],
[PartRev].[PartNum] as [PartRev_PartNum],
(max(isnull(PartRev.ChangedOn,PartRev.ApprovedDate))) as [Calculated_ChangeOn]
from Erp.PartRev as PartRev
where (PartRev.Approved = 1 and PartRev.PartNum = @PartNum)
group by [PartRev].[Company],
[PartRev].[PartNum])
,[PartRevMax_FullInfo] as
(select
[MAXPartRev].[PartRev_Company] as [PartRev_Company],
[MAXPartRev].[PartRev_PartNum] as [PartRev_PartNum],
[PartRev_MaxInfo].[RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartRev_MaxInfo].[Number01] as [PartRev_MaxInfo_Number01],
[PartRev_MaxInfo].[Number02] as [PartRev_MaxInfo_Number02],
[PartRev_MaxInfo].[Number03] as [PartRev_MaxInfo_Number03],
[PartRev_MaxInfo].[Number04] as [PartRev_MaxInfo_Number04],
[PartRev_MaxInfo].[Number05] as [PartRev_MaxInfo_Number05],
[PartRev_MaxInfo].[Number06] as [PartRev_MaxInfo_Number06],
[PartRev_MaxInfo].[AltMethod] as [PartRev_MaxInfo_AltMethod]
from Erp.PartRev as PartRev_MaxInfo
inner join MAXPartRev as MAXPartRev on
PartRev_MaxInfo.Company = MAXPartRev.PartRev_Company
and PartRev_MaxInfo.PartNum = MAXPartRev.PartRev_PartNum
and PartRev_MaxInfo.ChangedOn = MAXPartRev.Calculated_ChangeOn)
,[PartParent_PartMtl_Root] as
(select
[PartRevMax_FullInfo].[PartRev_Company] as [PartRev_Company],
[PartRevMax_FullInfo].[PartRev_PartNum] as [PartRev_PartNum],
[PartRevMax_FullInfo].[PartRev_MaxInfo_RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[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],
[PartMtl].[PullAsAsm] as [PartMtl_PullAsAsm],
[PartMtl].[ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartMtl].[PlanAsAsm] as [PartMtl_PlanAsAsm],
(1) as [Calculated_LevelBom],
(cast ( substring('........',1 ,(LevelBom + 1) ) + PartMtl.MtlPartNum
as nvarchar(250))) as [Calculated_LevelPartMtl]
from Erp.PartMtl as PartMtl
inner join PartRevMax_FullInfo as PartRevMax_FullInfo on
PartMtl.Company = PartRevMax_FullInfo.PartRev_Company
and PartMtl.PartNum = PartRevMax_FullInfo.PartRev_PartNum
and PartMtl.RevisionNum = PartRevMax_FullInfo.PartRev_MaxInfo_RevisionNum
and PartMtl.AltMethod = PartRevMax_FullInfo.PartRev_MaxInfo_AltMethod
union all
select
[PartParent_PartMtl_Root].[PartRev_Company] as [PartRev_Company],
[PartParent_PartMtl_Root].[PartRev_PartNum] as [PartRev_PartNum],
[PartParent_PartMtl_Root].[PartRev_MaxInfo_RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartMtl_Sub].[PartNum] as [PartMtl_Sub_PartNum],
[PartRev_SubMtl].[RevisionNum] as [PartRev_SubMtl_RevisionNum],
[PartMtl_Sub].[MtlSeq] as [PartMtl_Sub_MtlSeq],
[PartMtl_Sub].[MtlPartNum] as [PartMtl_Sub_MtlPartNum],
[PartMtl_Sub].[QtyPer] as [PartMtl_Sub_QtyPer],
[PartMtl_Sub].[RelatedOperation] as [PartMtl_Sub_RelatedOperation],
[PartMtl_Sub].[PullAsAsm] as [PartMtl_Sub_PullAsAsm],
[PartMtl_Sub].[ViewAsAsm] as [PartMtl_Sub_ViewAsAsm],
[PartMtl_Sub].[PlanAsAsm] as [PartMtl_Sub_PlanAsAsm],
(PartParent_PartMtl_Root.Calculated_LevelBom + 1) as [Calculated_LevelBom],
(cast ( substring('........',1 ,(LevelBom + 1) ) + (PartParent_PartMtl_Root.PartMtl_PartNum+N'\'+PartMtl_Sub.MtlPartNum)
as nvarchar(250))) as [Calculated_LevelPartMtl]
from Erp.PartMtl as PartMtl_Sub
inner join PartParent_PartMtl_Root as PartParent_PartMtl_Root on
PartMtl_Sub.Company = PartParent_PartMtl_Root.PartRev_Company
and PartMtl_Sub.PartNum = PartParent_PartMtl_Root.PartMtl_MtlPartNum
inner join Erp.PartRev as PartRev_SubMtl on
PartRev_SubMtl.PartNum = PartParent_PartMtl_Root.PartMtl_MtlPartNum
and PartRev_SubMtl.RevisionNum = PartParent_PartMtl_Root.PartMtl_RevisionNum)
select distinct
[PartParent_PartMtl_Root_Full].[PartRev_Company] as [PartRev_Company],
[PartParent_PartMtl_Root_Full].[PartRev_PartNum] as [PartRev_PartNum],
[PartParent_PartMtl_Root_Full].[PartRev_MaxInfo_RevisionNum] as [PartRev_MaxInfo_RevisionNum],
[PartParent_PartMtl_Root_Full].[PartMtl_PartNum] as [PartMtl_PartNum],
[PartParent_PartMtl_Root_Full].[PartMtl_RevisionNum] as [PartMtl_RevisionNum],
[PartParent_PartMtl_Root_Full].[PartMtl_MtlSeq] as [PartMtl_MtlSeq],
[PartParent_PartMtl_Root_Full].[PartMtl_MtlPartNum] as [PartMtl_MtlPartNum],
[PartParent_PartMtl_Root_Full].[PartMtl_QtyPer] as [PartMtl_QtyPer],
[PartParent_PartMtl_Root_Full].[PartMtl_RelatedOperation] as [PartMtl_RelatedOperation],
[PartParent_PartMtl_Root_Full].[PartMtl_PullAsAsm] as [PartMtl_PullAsAsm],
[PartParent_PartMtl_Root_Full].[PartMtl_ViewAsAsm] as [PartMtl_ViewAsAsm],
[PartParent_PartMtl_Root_Full].[PartMtl_PlanAsAsm] as [PartMtl_PlanAsAsm],
[PartParent_PartMtl_Root_Full].[Calculated_LevelBom] as [Calculated_LevelBom],
[PartParent_PartMtl_Root_Full].[Calculated_LevelPartMtl] as [Calculated_LevelPartMtl]
from PartParent_PartMtl_Root as PartParent_PartMtl_Root_Full
order by PartParent_PartMtl_Root_Full.PartRev_PartNum, PartParent_PartMtl_Root_Full.Calculated_LevelBom, PartParent_PartMtl_Root_Full.Calculated_LevelPartMtl
DMS_GetBomFull.baq (65.5 KB)
Based on the MethodTracker screen
In production jobs, there is a very precise relationship between asm and mtl
select * from JobAsmbl as asm inner join JobMtl as mtl on asm.Company=mtl.Company and asm.JobNum =mtl.JobNum and asm.AssemblySeq=mtl.AssemblySeq
Thank you very much! Have a good day! 