72 lines
5.1 KiB
Transact-SQL
72 lines
5.1 KiB
Transact-SQL
USE ASRS_WXZD_JYMH_CPK
|
||
go
|
||
SET ANSI_NULLS ON
|
||
GO
|
||
SET QUOTED_IDENTIFIER ON
|
||
GO
|
||
ALTER TABLE kwjbxx ALTER COLUMN 双伸位都有货 TINYINT NULL
|
||
go
|
||
ALTER TRIGGER [dbo].[t_kwjbxx_U] ON [dbo].[kwjbxx]
|
||
FOR UPDATE
|
||
AS
|
||
/**
|
||
* 在总数量从其它值变为0时，清空托盘号、库存峰值、实际物资类型、备料单号、实放物资种类
|
||
* 如果修改后的总数量大于修改前的总数量，则更新库存峰值为修改后的总数量
|
||
* Created by 蒋智湘 ON 05-07-08
|
||
* Modified by 蒋智湘 ON 05-08-23 如果修改总数量前得库存数量可能为NULL，此时也需要修改库存峰值
|
||
* Modified by jzx on 2009-8-22 库存总数量清零时，新增字段kzjrks（可追加入库数）清空。
|
||
* Modified by jzx on 2014-12-9 增加对新字段“双伸位都有货”赋值；关闭对kcfz字段的赋值
|
||
* Modified by jzx on 2015-6-24 当库位库存总数更改为0时，不再清空托盘号字段值。是为了处理整盘出库指令审核后，取消审核时托盘号无法复原问题
|
||
* Modified by jzx on 2015-11-24 当更改“托盘个数”字段值时，如果当前库位存放物资是空托盘，则直接修改zsl字段值;
|
||
当空库位设定托盘个数大于0后，sfwzzl字段值自动更新为'T'
|
||
* Modified by jzx on 2016-8-27 jzx 当修改托盘个数字段值从0到1时，不再更改sfwzzl字段值
|
||
* Modified by jzx on 2017-11-27 优化对kwjbxx表的“双伸位都有货”字段的赋值方法。由原来的4条语句更改为1条语句
|
||
*/
|
||
IF UPDATE(zsl)
|
||
BEGIN
|
||
|
||
SET NOCOUNT ON;
|
||
|
||
IF EXISTS(SELECT 2 FROM inserted WHERE zsl = 0)
|
||
UPDATE kwjbxx SET kcfz = NULL, sjwzlx = NULL, sfwzzl = NULL, kzjrks = NULL
|
||
FROM inserted I
|
||
WHERE I.kwbh = kwjbxx.kwbh
|
||
AND I.zsl = 0;
|
||
|
||
IF EXISTS(SELECT 2 FROM inserted WHERE xnkw = 0 AND (nckw IS NOT NULL OR wckw IS NOT NULL))
|
||
BEGIN
|
||
UPDATE kwjbxx SET 双伸位都有货=
|
||
CASE WHEN (r.zsl > 0 AND wc.浅货位库存数>0)
|
||
OR (r.zsl > 0 AND nc.深货位库存数>0) THEN 3
|
||
-- 深货位有货，则更新为1
|
||
WHEN nc.深货位库存数> 0 THEN 1
|
||
-- 浅货位有货，则更新为2
|
||
WHEN wc.浅货位库存数> 0 THEN 2
|
||
ELSE 0 END
|
||
FROM kwjbxx r LEFT JOIN (SELECT zsl 浅货位库存数, kwbh FROM kwjbxx) wc ON r.wckw=wc.kwbh
|
||
LEFT JOIN (SELECT zsl 深货位库存数, kwbh FROM kwjbxx) nc ON r.nckw=nc.kwbh
|
||
, inserted i
|
||
WHERE (i.nckw IS NOT NULL OR i.wckw IS NOT NULL)
|
||
AND (r.kwbh IN(i.nckw,i.wckw,i.kwbh))
|
||
END;
|
||
END
|
||
go
|
||
UPDATE kwjbxx SET 双伸位都有货=r.x
|
||
FROM ( select r.kwbh,r.zsl,r.wckw,浅货位库存数,r.nckw,深货位库存数
|
||
, CASE WHEN (r.zsl > 0 AND wc.浅货位库存数>0) OR (r.zsl > 0 AND nc.深货位库存数>0) THEN 3
|
||
WHEN nc.深货位库存数> 0 THEN 1
|
||
WHEN wc.浅货位库存数> 0 THEN 2
|
||
ELSE 0 END x
|
||
from
|
||
kwjbxx r LEFT JOIN (SELECT zsl 浅货位库存数, kwbh FROM kwjbxx) wc ON r.wckw=wc.kwbh
|
||
LEFT JOIN (SELECT zsl 深货位库存数, kwbh FROM kwjbxx) nc ON r.nckw=nc.kwbh
|
||
WHERE (nckw IS NOT NULL OR wckw IS NOT NULL)
|
||
) r
|
||
WHERE kwjbxx.kwbh=r.kwbh
|
||
go
|
||
|
||
-- kws字段从java代码移入数据库；双伸库位都没有托盘的优先安排，深货位有的浅货位没有的，则次优安排
|
||
update sys_SQL_statements set SQL_string='kws,双伸位都有货,storeDist'
|
||
where ID='orderClauseForDistance';
|
||
go
|