金蝶云星空7.2-后期通过后台启用物料批号方法讨论

步骤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'