--建立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
--删除子表