K3 BOM多阶层全部显示,其它ERP软件也可参考此方法

1.如果存在临时表 删除临时表

--drop table #bom 
--drop table #bomchild

2.创建2个临时表 bom主表跟bom明细表 ，字段根据实际情况创建，我这里是按K3来创建

create table #bom--根据实际情况创建字段
(
FId int identity(1,1),--主键
FItemID int,--链接物料表的内码 也可以是物料编码
FNumber nvarchar(200)--物料编码
)

create table #bomchild --根据实际情况创建字段
(
FID int identity(1,1),--主键
FOrgID int,--#bom里的FId
FParentID int,--#bomchild表里上一层的FID
FLevel int,--层级
FSN nvarchar(200),
FItemID int,--物料表里的内码，也可以是物料编码
FQty decimal(28,19),--用量
FBOMInterID int,
FEntryID int
)

3.把需要多阶层查询的父物料加入到bom临时表里,这里我加入了所有物料

--insert into #bom
--(FItemID,FNumber)
--select Fitemid,FNumber from t_ICItem where FErpClsID in (2,3,5)
insert into #bom
(FItemID,FNumber)
select t.FItemID,t.FNumber from t_ICItem as t
inner join t_Item t5 on t5.FItemID=t.FItemID
left join ICBOM as t6 on t6.FItemID=t.FItemID
left join ICBOMGroup t7 on t7.FInterID=t6.FParentID
where FErpClsID in (2,3,5) and t5.FDeleted=0 order by t.FNumber
--------------------------------------------------------------------------------------

4.把所有bom临时表里物料的第一层bom先添加到bom明细临时表里 并设置 FLevel为0  第0阶层  以便后续的while循环插入使用
insert into #bomchild
(FOrgID,FParentID,FSN,FItemID,FQty,FBOMInterID,FEntryID,FLevel)
select
FId,-1 as FParentID,'0',u1.FItemID,1 as Fqty,t1.FInterID as FBOMInterID,-1 as FEntryID,0
from #bom u1
left join ICBOM t1 on u1.FItemID=t1.FItemID and t1.FUseStatus=1072
order by FItemID
------------------------------------------------------------------------------------

5.while 循环 insert 直到 #bomchild没数据跟@level<20 ，@level 这个可以根据实际情况自行设置  我这里bom最多20层

declare @level int
set @level=1

while exists(
select 1 from
#bomchild
where FLevel=@level-1
and FItemID in
(select icbom.Fitemid from icbom inner join ICBOMChild on icbom.FInterID=ICBOMChild.FInterID)
)
and @level<20
begin
insert into #bomchild
(FOrgID,FParentID,FSN,
FItemID,FQty,FBOMInterID,
FEntryID,FLevel)
select
u1.FOrgID,u1.FID,u1.FSN+'.'+right('000'+CONVERT(nvarchar(50),t2.Fentryid),3),
t2.Fitemid,--u1.FQty*(t2.FQty/t1.FQty)/(1-t2.FScrap/100)
u1.FQty*(t2.FQty/t1.FQty)/(1-t2.FScrap/100)--t2.FAuxQty
,t2.FInterID,
t2.FEntryID,@level
from #bomchild u1
inner join icbom t1 on u1.FItemID=t1.FItemID
inner join ICBOMChild t2 on t2.FInterID=t1.FInterID and t1.FUseStatus=1072
where u1.FLevel=@level-1

set @level=@level+1
end

------------------------下面是查询bom多阶层语句--------------------------

select #bom.FNumber,t1.FName,t1.FModel,t2.FNumber,t2.FName,t2.FModel,#bomchild.FQty from #bom left join t_ICItem as t1 on #bom.FItemID=t1.FItemID
left join #bomchild on #bom.FId=#bomchild.FOrgID
left join t_ICItem as t2 on #bomchild.FItemID=t2.FItemID
order by #bom.FNumber