-- 工艺路线产品增加报工产出仓库绑定（可重复执行）
|
SET @ddl := IF(
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_pro_route_product' AND COLUMN_NAME = 'warehouse_id') = 0,
|
'ALTER TABLE `mes_pro_route_product` ADD COLUMN `warehouse_id` bigint NULL COMMENT ''报工产出仓库ID'' AFTER `item_id`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @ddl := IF(
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_pro_route_product' AND COLUMN_NAME = 'location_id') = 0,
|
'ALTER TABLE `mes_pro_route_product` ADD COLUMN `location_id` bigint NULL COMMENT ''报工产出库区ID'' AFTER `warehouse_id`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @ddl := IF(
|
(SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mes_pro_route_product' AND COLUMN_NAME = 'area_id') = 0,
|
'ALTER TABLE `mes_pro_route_product` ADD COLUMN `area_id` bigint NULL COMMENT ''报工产出库位ID'' AFTER `location_id`',
|
'SELECT 1'
|
);
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 历史路线产品使用虚拟线边库兜底，新建/编辑时由接口强制选择具体仓库、库区、库位。
|
UPDATE `mes_pro_route_product` p
|
JOIN `mes_wm_warehouse` w ON w.`code` = 'WIP_VIRTUAL_WAREHOUSE' AND w.`deleted` = b'0'
|
JOIN `mes_wm_warehouse_location` l ON l.`code` = 'WIP_VIRTUAL_LOCATION' AND l.`deleted` = b'0'
|
JOIN `mes_wm_warehouse_area` a ON a.`code` = 'WIP_VIRTUAL_AREA' AND a.`deleted` = b'0'
|
SET p.`warehouse_id` = COALESCE(p.`warehouse_id`, w.`id`),
|
p.`location_id` = COALESCE(p.`location_id`, l.`id`),
|
p.`area_id` = COALESCE(p.`area_id`, a.`id`),
|
p.`updater` = 'system', p.`update_time` = NOW()
|
WHERE p.`deleted` = b'0'
|
AND (p.`warehouse_id` IS NULL OR p.`location_id` IS NULL OR p.`area_id` IS NULL);
|
