-- =====================================================================
|
-- 新建合格仓库 + 仓库类型 + 质检入合格库改造
|
--
|
-- 1) mes_wm_warehouse 增加 type 字段（10 暂存库 / 20 合格库）
|
-- 2) 现有仓库分类：虚拟线边仓库=10（暂存库），产品库/物料库=20（合格库）
|
-- 3) 新建"成品合格库"(type=20) + 一个库区 + 一个库位
|
-- 4) 虚拟仓库中 stock_state=20 的合格库存迁移到新合格库
|
--
|
-- 说明：本脚本幂等，可重复执行；无租户字段。
|
-- =====================================================================
|
|
-- 1) 增加仓库类型字段（MySQL 不支持 ALTER ADD COLUMN IF NOT EXISTS，先查存在性）
|
SET @type_exists = (
|
SELECT COUNT(*)
|
FROM information_schema.columns
|
WHERE table_schema = DATABASE()
|
AND table_name = 'mes_wm_warehouse'
|
AND column_name = 'type'
|
);
|
SET @type_sql = IF(
|
@type_exists = 0,
|
'ALTER TABLE mes_wm_warehouse ADD COLUMN type tinyint NOT NULL DEFAULT 10 COMMENT ''仓库类型：10暂存库，20合格库'' AFTER frozen',
|
'SELECT 1'
|
);
|
PREPARE type_stmt FROM @type_sql;
|
EXECUTE type_stmt;
|
DEALLOCATE PREPARE type_stmt;
|
|
-- 2) 现有仓库分类
|
UPDATE mes_wm_warehouse SET type = 10 WHERE code = 'WIP_VIRTUAL_WAREHOUSE' AND deleted = 0;
|
UPDATE mes_wm_warehouse SET type = 20 WHERE code IN ('1212', 'WHS000001') AND deleted = 0;
|
|
-- 3) 新建"成品合格库"（type=20）
|
SET @wh_exists = (
|
SELECT COUNT(*) FROM mes_wm_warehouse WHERE code = 'FG_QUALIFIED_WH' AND deleted = 0
|
);
|
INSERT INTO mes_wm_warehouse (code, name, frozen, type, remark)
|
SELECT 'FG_QUALIFIED_WH', '成品合格库', 0, 20, '质检合格直接入库的目标仓库（type=20 合格库）'
|
WHERE @wh_exists = 0;
|
SET @wh_id = (SELECT id FROM mes_wm_warehouse WHERE code = 'FG_QUALIFIED_WH' AND deleted = 0 LIMIT 1);
|
|
-- 新建"成品合格库区"
|
SET @loc_exists = (
|
SELECT COUNT(*) FROM mes_wm_warehouse_location WHERE code = 'FG_QUALIFIED_WH_LOC' AND deleted = 0
|
);
|
INSERT INTO mes_wm_warehouse_location (code, name, warehouse_id, frozen, remark)
|
SELECT 'FG_QUALIFIED_WH_LOC', '成品合格库区', @wh_id, 0, '质检合格入库默认库区'
|
WHERE @loc_exists = 0;
|
SET @loc_id = (SELECT id FROM mes_wm_warehouse_location WHERE code = 'FG_QUALIFIED_WH_LOC' AND deleted = 0 LIMIT 1);
|
|
-- 新建"成品合格库位"
|
SET @area_exists = (
|
SELECT COUNT(*) FROM mes_wm_warehouse_area WHERE code = 'FG_QUALIFIED_WH_AREA' AND deleted = 0
|
);
|
INSERT INTO mes_wm_warehouse_area (code, name, location_id, frozen, remark)
|
SELECT 'FG_QUALIFIED_WH_AREA', '成品合格库位', @loc_id, 0, '质检合格入库默认库位'
|
WHERE @area_exists = 0;
|
SET @area_id = (SELECT id FROM mes_wm_warehouse_area WHERE code = 'FG_QUALIFIED_WH_AREA' AND deleted = 0 LIMIT 1);
|
|
-- 4) 迁移虚拟仓库合格库存到新合格库
|
-- 4.1 若新合格库已存在同 (item_id, batch_id, vendor_id) 的 state=20 记录，先数量累加合并
|
UPDATE mes_wm_material_stock t
|
JOIN mes_wm_material_stock s
|
ON s.warehouse_id = 3 AND s.stock_state = 20 AND s.deleted = 0
|
AND t.warehouse_id = @wh_id AND t.stock_state = 20 AND t.deleted = 0
|
AND t.item_id = s.item_id
|
AND IFNULL(t.batch_id, 0) = IFNULL(s.batch_id, 0)
|
AND IFNULL(t.vendor_id, 0) = IFNULL(s.vendor_id, 0)
|
SET t.quantity = t.quantity + s.quantity,
|
t.reserved_quantity = t.reserved_quantity + s.reserved_quantity,
|
t.frozen_quantity = t.frozen_quantity + s.frozen_quantity;
|
|
-- 4.2 删除已合并到新合格库的源记录（虚拟仓库中）
|
DELETE s FROM mes_wm_material_stock s
|
JOIN mes_wm_material_stock t
|
ON s.warehouse_id = 3 AND s.stock_state = 20 AND s.deleted = 0
|
AND t.warehouse_id = @wh_id AND t.stock_state = 20 AND t.deleted = 0
|
AND t.item_id = s.item_id
|
AND IFNULL(t.batch_id, 0) = IFNULL(s.batch_id, 0)
|
AND IFNULL(t.vendor_id, 0) = IFNULL(s.vendor_id, 0);
|
|
-- 4.3 其余合格库存直接改仓库/库区/库位
|
UPDATE mes_wm_material_stock
|
SET warehouse_id = @wh_id, location_id = @loc_id, area_id = @area_id
|
WHERE warehouse_id = 3 AND stock_state = 20 AND deleted = 0;
|
