--BOM表头数据库导入注意事项

--首先建立一个数据库表icBOM_CYY，字段为fbomnumber,存储未导入BOM的BOM编号信息。

--1、finterid 需要与ICMaxNum表里面的ftablename=icbom匹对，取fmaxnum的值

--2、fbomnumber取自物料表的物料代码，首先检测t_icitem表是否存在此物料，如果不存在这个物料，则把物料代码信息写入到ICBOM_CYY

--3、

--取需要导入另一账套的BOM内码,使用状态的BOM需导入

select identity(int,1,1) as fint,icbom.finterid into #cyy1 from icbomchild

join t_icitem on t_icitem.fitemid=icbomchild.fitemid

join icbom on icbom.finterid=icbomchild.finterid

join t_icitem v1 on v1.fitemid=icbom.fitemid

--where  icbom.fusestatus=1072  --and v1.fname not like '%禁%' and v1.fname not like '%停用%' and v1.FDeleted=0 and icbom.fbomnumber='BOM016940'

order by icbom.fbomnumber,fentryid

select * into #cyy from #cyy1 where fint in (select max(fint) from #cyy1 group by finterid)

declare @finterid int --要导入的BOM内码

delete icbom_cyy

--循环取数（游标开始）

DECLARE my_Cursor

CURSOR FOR (select finterid from #cyy)

OPEN My_Cursor;  

FETCH NEXT FROM My_Cursor into @finterid;

WHILE @@FETCH_STATUS = 0

BEGIN  

--提取物料内码是否存在

declare @fitemid int --物料内码

declare @fparentid int --组别内码

declare @fbomnumber varchar(50) --BOM编码

select @fitemid=fitemid,@fparentid=fparentid,@fbomnumber=fbomnumber from icbom where finterid=@finterid

if not exists(select * from [AIS20180930093441].[dbo].icbom where fbomnumber=@fbomnumber)

begin

--提取新账套组别内码

declare @fparentid1 int --新账套BOM组别ID

select @fparentid1=finterid from  [AIS20180930093441].[dbo].icbomgroup where fnumber in (select fnumber from icbomgroup where finterid=@fparentid)

declare @finterid1 int --取新账套的ICBOM内码

select @finterid1=fmaxnum from [AIS20180930093441].[dbo].ICMaxNum where ftablename='icbom'

--提取新账套物料名称，内码

declare @funitid int

declare @fitemid1 int

select @funitid=funitid,@fitemid1=fitemid from [AIS20180930093441].[dbo].t_icitem where fnumber in (select fnumber from t_icitem where fitemid=@fitemid)

if @fitemid1  is not null and not exists(select fitemid from [AIS20180930093441].[dbo].icbom where fitemid=@fitemid1)

and not exists(select fnumber from icbomchild join t_icitem on t_icitem.fitemid=icbomchild.fitemid

where t_icitem.fnumber not in (select fnumber from [AIS20180930093441].[dbo].t_icitem) and finterid=@finterid)

begin

--插入BOM表头

insert into [AIS20180930093441].[dbo].icbom

select 0,@finterid1,fbomnumber,0,fusestatus,fversion,@fparentid1,@fitemid1,fqty,fyield,16394,getdate(),16394,getdate(),fstatus,0,50,0,fbomtype,0,0,0,fnote,@funitid,

fauxqty,16394,getdate(),0,fbeenchecked,fforbid,fauxpropid,fpdmimportdate,fbomskip,fclasstypeid,16394,getdate(),fprintcount,fisplm from icbom where finterid=@finterid

--插入BOM表体

insert into [AIS20180930093441].[dbo].icbomchild

select icbomchild.fbrno,fentryid,@finterid1,case when v1.fitemid is null then 9904 else v1.fitemid end,fauxqty,fqty,fscrap,fopersn,foperid,fmachinepos,icbomchild.fnote,fmaterieltype,fmarshaltype,fpercent,fbeginday,fendday,foffsetday,fbackflush,0,icbomchild.fspid,fsupply,v1.funitid,

fauxpropid,fpdmimportdate,fpositionno,fitemsize,fitemsuite,fnote1,fnote2,fnote3,fhaschar,fdetailid,fcostpercentage

from icbomchild

join t_icitem on t_icitem.fitemid=icbomchild.fitemid

join [AIS20180930093441].[dbo].t_icitem v1 on v1.fnumber=t_icitem.fnumber

where finterid=@finterid

--新账套ICBOM内码加1，在最后满足条件后执行。

update [AIS20180930093441].[dbo].ICMaxNum

set fmaxnum=fmaxnum+1

from [AIS20180930093441].[dbo].ICMaxNum

where ftablename='icbom'

END

else

begin

insert into icbom_cyy

select fbomnumber from icbom where finterid=@finterid

end

end

--游标结束

FETCH NEXT FROM My_Cursor into @finterid ;  

End

CLOSE My_Cursor;  

DEALLOCATE My_Cursor;

select * from icbom_cyy

DROP TABLE #CYY,#CYY1

「该帖子被 cyy275 在 2018/10/6 22:11:33 编辑过」