K3没有批量BOM单级展开并导出功能,如果需要对特定物料的BOM进行批量单级展开,可以使用如下sql代码来实现。
1、创建BOM父项表
create table HWbom
(
FId int identity(1,1),
FItemID int
)
2、创建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
)
3、将需要批量导出的BOM编号引入PORTAL数据库中的一个表TTGI28E中;
4、将父项BOM对应的物料内码插入父项表(可带条件),并与portal的TTGI28E表数据进行匹配:
insert into HWbom
(FItemID)
select t.Fitemid from t_ICItem t
inner join t_item t5 ON t5.FItemID = t.Fitemid
inner join [portal].[dbo].TTGI28E t1 on t1.F3J00XC=t.fnumber--将数据与portal数据库中的TTGI28E进行BOM编号匹配,如果需要对所有BOM进行单级展开,可将本条注释掉
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.fnumber in('物料编码1','物料编码2')--可根据需要限定BOM范围
and t5.fdeleted=0
order by t.fnumber
5、根据父项表数据,将数据插入子项表,为后续卷算做准备
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
6、BOM单层卷算
declare @level int
set @level=1
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
7、最终BOM展开数据检索,执行结果可复制到Excel
select
t2.FNumber 产品代码,t2.FName 产品名称,t2.FModel 产品规格,
t1.FSN 序号,
t3.FNumber 材料代码,t3.FName 材料名称,t3.FModel 材料规格,t1.FQty 产品用量,yy.fname 材料属性,
t7.fname 计量单位,
t5.FQty 单位用量,
t5.FScrap 损耗率,
--t4.FBOMNumber BOM编号,
t6.FName as 是否跳层,
t5.FNote 备注,
t5.FPositionNo 位置号
--t1.FBOMInterID,t1.FEntryID
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
inner join t_MeasureUnit t7 on t7.fmeasureunitid=t2.FProductUnitID
order by u1.FId,t1.FSN
8、最后清空父项表和子项表数据,以便下次运算使用(不用重新建表了)
TRUNCATE TABLE HWbom
--清空附表数据
TRUNCATE TABLE HWbomchild
--清空子表数据
9、如果不再使用,可以将这两个表删除
drop table HWbom
--删除父表
drop table HWbomchild
--删除子表