K3 BOM卷积查询

--建立BOM查询表表头
create table HWbom
(
FId int identity(1,1),
FItemID int
)


--建立BOM查询表表体
create table HWbomchild
(
 FID int identity(1,1),
 FOrgID int,
 FParentID int,
 FLevel int,
 FSN nvarchar(200),
 FItemID int,
 FQty decimal(28,19),
 FBOMInterID int,
 FEntryID int
)


--将需要的产品代码插入到BOM查询表头中，此表在此处需求根据公司财务要求：本次查询为所有的产品代码的BOM，根据公司物料要求的设计为01.08.为产品代码，但不包含01.08.00
insert into HWbom
(FItemID)
select t.Fitemid from t_ICItem t
inner join t_item t5 ON t5.FItemID = t.Fitemid
left join icbom t6 on t6.fitemid=t.Fitemid
left join ICBOMGROUP t7 on t7.finterid=t6.fparentid
where 
t.FErpClsID in (2,3,5)--2代表自制件，3代表委外件，5代表虚拟件
and t5.fdeleted=0 
--此处根据公司要求筛选物料代码即可
and t5.fnumber like '01.08.%' and t5.fnumber not like '01.08.00%' --可根据需要限定BOM范围
order by t.fnumber


--此处插入表体的内容分别为：表头自增长内码、父类标志码、编号、物料内码、数量、BOM内码、行号、级别
insert into HWbomchild
(FOrgID,FParentID,FSN,FItemID,FQty,FBOMInterID,FEntryID,FLevel)
select 
FId,-1 as FParentID,'001',u1.FItemID,1 as Fqty,t1.FInterID as FBOMInterID,-1  as FEntryID,0
from HWbom u1
left join ICBOM t1 on u1.FItemID=t1.FItemID


declare @level int
set @level=1
 
--循环进行BOM填充
--判定BOM表中的物料内码在BOM中是否存在，在继续进行插入，插入BOM表中的数据信息
while exists
(
 select 1 from 
 HWbomchild
 where FLevel=@level-1
 and FItemID in 
 (select Fitemid from icbom)
)
and @level<20
begin
 
 insert into HWbomchild
 (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),
 t2.FInterID,t2.FEntryID,@level
 from HWbomchild u1
 inner join icbom t1 on u1.FItemID=t1.FItemID
 inner join ICBOMChild t2 on t2.FInterID=t1.FInterID
 where u1.FLevel=@level-1
 
 set @level=@level+1
 
end


select
row_number() over(order by t2.FNumber ) number, t2.FNumber 产品代码,t2.FName 产品名称,t2.FModel 产品规格,
t1.FSN 序号,
t3.FNumber 材料代码,t3.FName 材料名称,t3.FModel 材料规格,t1.FQty 产品用量,yy.fname 材料属性,
t5.FQty 单位用量,
t5.FScrap 损耗率,
t4.FBOMNumber BOM编号,
t6.FName as 是否跳层,
t5.FNote 备注,
t5.FPositionNo 位置号
into #caicai
 from HWbom u1
inner join HWbomchild t1 on u1.FId=t1.FOrgID
inner join t_icitem t2 on t2.FItemID=u1.FItemID
inner join t_ICItem t3 on t3.FItemID=t1.FItemID
left join ICBOM t4 on t4.FInterID=t1.FBOMInterID
left join ICBOMChild t5 on t5.FInterID=t1.FBOMInterID and t5.FEntryID=t1.FEntryID
left join t_SubMessage t6 on t6.FInterID=t4.FBOMSkip
inner join t_SubMessage yy on yy.FInterID=t3.FErpClsID
--where fbomnumber='物料编码'
order by u1.FId,t1.FSN



TRUNCATE TABLE HWbom
--清空附表数据
TRUNCATE TABLE HWbomchild
--清空子表数据


drop table HWbom
--删除父表
drop table HWbomchild
--删除子表