步骤1: 开启基础资料物料批号 功能
–启用批号
— 更新物料 库存页签 中的 启用批号 T_BD_MATERIALSTOCK 启用批号控制 FISBATCHMANAGE
--no1查找需要添加批号的物料 select * from T_BD_MATERIAL w1 left join T_BD_MATERIAL_L w2 on w2.FMATERIALID = w1.FMATERIALID left join T_BD_MATERIALSTOCK w3 on w3.FMATERIALID = w1.FMATERIALID where w1.FNUMBER like '30101%' and w1.FNUMBER not like '%-%'
--no2启用批号控制 更新FISBATCHMANAGE值由0变成1 update w3 set w3.FISBATCHMANAGE='1' from T_BD_MATERIAL w1 left join T_BD_MATERIAL_L w2 on w2.FMATERIALID = w1.FMATERIALID left join T_BD_MATERIALSTOCK w3 on w3.FMATERIALID = w1.FMATERIALID where w1.FNUMBER like '30101%' and w1.FNUMBER not like '%-%'
–注: 后台更新完库存页签的启用批号控制后, 物料维度处的 批号 为显示为已经启用,不用另外再处理。
–no4定义一个统一 的批号,将上面查询到的库存物料,全部都使用一个默认批号,例如: 20210425 T_BD_LOTMASTER/批号主档表
select * from T_BD_LOTMASTER where FLOTID=’409668′
–将批号主档最后一行数据,存入临时表 drop table #temp_ph SELECT * FROM #temp_ph
select * into #temp_ph from T_BD_LOTMASTER where FLOTID=’409668′
–更新临时表#temp_ph中的 FLOTID, FMASTERID,FMATERIALID,FNUMBER四个字段, 前三个字段数据递增1, FNUMBER为你要定义的批号.
–update #temp_ph set FLOTID=’409668′,FMASTERID=’409701′,FMATERIALID=’107124′ ,FNUMBER=’test20210425′,FCUSTID=”
–以上即时库存中有20行物料要添加批号,以下在批号主档中连续插入20号批号数据
declare @a int, @sql int select @a=409668 while @a<=409687 begin set @a=@a+1 insert into #temp_ph ([FLOTID] ,[FMASTERID] ,[FMATERIALID] ,[FAUXPROPERTYID] ,[FNUMBER] ,[FLOTSTATUS] ,[FDOCUMENTSTATUS] ,[FSUPPLYID] ,[FSUPPLYLOT] ,[FPRODUCEDEPTID] ,[FCREATEORGID] ,[FUSEORGID] ,[FCREATORID] ,[FCREATEDATE] ,[FMODIFIERID] ,[FMODIFYDATE] ,[FPRODUCEDATE] ,[FEXPIRYDATE] ,[FFORBIDSTATUS] ,[FBIZTYPE] ,[FCUSTID] ,[FCANCELSTATUS] ,[FINSTOCKDATE]) VALUES ( @a, @a, N'1111111', 0, N'test20210425', '1', 'C', NULL, NULL, NULL, 1, 1, 100073, N'2021-04-25T15:30:54.183', 100073, N'2021-04-25T15:30:54.183', NULL, NULL, 'A', '1', 0, 'A', N'2021-04-25T00:00:00' ) end -- SELECT * FROM #temp_ph
--取即时库存中的20行物料数据, 并插入临时表 #temp_chuliwl select identity(int,409669,1) as fid, --注意这里的409669,与上面批号主档的fid相同 RK1.FSTOCKID, --仓库内码 CK1.FNUMBER, ck2.FNAME as CKFNAME, RK1.FMATERIALID, --物料内码 W01.FNUMBER as WLFNUMBER, W02.FNAME as WLFNAME, W01.F_NET_WLCGBZ, --常规标志 W02.FSPECIFICATION, RK1.FBASEQTY, --库存量(基本单位) RK1.FLOT, --批号ID PH1.FNUMBER as PHFNUMBER --批号代码 into #temp_chuliwl from T_STK_INVENTORY RK1 left join T_BD_STOCK CK1 on CK1.FSTOCKID = RK1.FSTOCKID --关联仓库表 left join T_BD_STOCK_L ck2 on ck2.FSTOCKID = CK1.FSTOCKID left join dbo.T_BD_MATERIAL W01 on W01.FMATERIALID =RK1.FMATERIALID --连接物料表 left join dbo.T_BD_MATERIAL_L W02 on W02.FMATERIALID=W01.FMATERIALID --连接物料多语言表 left join dbo.T_BD_MATERIALBASE W03 on W03.FMATERIALID=W02.FMATERIALID --关联物料基本表 left join T_BD_LOTMASTER PH1 on PH1.FLOTID=RK1.FLOT --and ph1.FMATERIALID=rk1.FMATERIALID --关联批号主档表 where W01.FNUMBER like '30101%' and RK1.FSTOCKID='100090' order by CK1.FNUMBER
— select * FROM #temp_chuliwl
–通过要处理物料表的fid和上面插入到批号主档的flotid关联,把物料ID写入到批号主档中
delete FROM #temp_ph where FLOTID=’409668′ –删除掉之前这一行
SELECT * FROM #temp_ph t1 left join #temp_chuliwl t2 on t1.FLOTID=t2.fid update t1 set t1.FMATERIALID=t2.FMATERIALID from #temp_ph t1 left join #temp_chuliwl t2 on t1.FLOTID=t2.fid SELECT * FROM #temp_ph
–把#temp_ph 处理好的数据 插入到批号主档表中
insert into T_BD_LOTMASTER ([FLOTID] ,[FMASTERID] ,[FMATERIALID] ,[FAUXPROPERTYID] ,[FNUMBER] ,[FLOTSTATUS] ,[FDOCUMENTSTATUS] ,[FSUPPLYID] ,[FSUPPLYLOT] ,[FPRODUCEDEPTID] ,[FCREATEORGID] ,[FUSEORGID] ,[FCREATORID] ,[FCREATEDATE] ,[FMODIFIERID] ,[FMODIFYDATE] ,[FPRODUCEDATE] ,[FEXPIRYDATE] ,[FFORBIDSTATUS] ,[FBIZTYPE] ,[FCUSTID] ,[FCANCELSTATUS] ,[FINSTOCKDATE]) SELECT * FROM #temp_ph
–把批号主档ID FLOTID写入即时库存物料中
update rk1 set rk1.FLOT= ph.FLOTID from T_STK_INVENTORY RK1 left join T_BD_STOCK CK1 on CK1.FSTOCKID = RK1.FSTOCKID --关联仓库表 left join T_BD_STOCK_L ck2 on ck2.FSTOCKID = CK1.FSTOCKID left join dbo.T_BD_MATERIAL W01 on W01.FMATERIALID =RK1.FMATERIALID --连接物料表 left join dbo.T_BD_MATERIAL_L W02 on W02.FMATERIALID=W01.FMATERIALID --连接物料多语言表 left join dbo.T_BD_MATERIALBASE W03 on W03.FMATERIALID=W02.FMATERIALID --关联物料基本表 left join T_BD_LOTMASTER ph on ph.FMATERIALID=rk1.FMATERIALID where W01.FNUMBER like '30101%' and RK1.FSTOCKID='100090' and w01.FMATERIALID<>'107124' and w01.FMATERIALID<>'107120'