摘要由AI智能服务提供本文详细描述了开启物料批号控制及为特定物料统一设置批号的步骤。首先，通过SQL查询启用物料批号控制，并更新相关表。接着，为查询到的物料定义一个统一的批号，并在批号主档表中连续插入多条记录以对应不同物料。然后，将库存中的物料与批号主档表中的记录关联，更新库存物料的批号。整个流程包括数据库表的更新、临时表的操作以及数据关联等步骤，最终完成物料批号的设置。
–启用批号
— 更新物料 库存页签 中的 启用批号 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′
–更新临时表#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′ –删除掉之前这一行
–把#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'
以上已完成批号开启动作。
赞 26
打赏