-- =============================================
|
-- M4：SN 单品库存轻量闭环
|
-- 1. mes_wm_sn 增加在库库位维度列（status/batch_id/remark 表中已存在）
|
-- 2. mes_wm_transaction 增加 sn_code 列
|
-- 目标库：mom-xgdl（local profile）
|
-- =============================================
|
|
-- 1. mes_wm_sn.warehouse_id（在库仓库 ID）
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_sn' AND column_name = 'warehouse_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_sn ADD COLUMN warehouse_id BIGINT NULL COMMENT ''在库仓库 ID'' AFTER batch_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 2. mes_wm_sn.location_id（在库库区 ID）
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_sn' AND column_name = 'location_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_sn ADD COLUMN location_id BIGINT NULL COMMENT ''在库库区 ID'' AFTER warehouse_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 3. mes_wm_sn.area_id（在库库位 ID）
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_sn' AND column_name = 'area_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_sn ADD COLUMN area_id BIGINT NULL COMMENT ''在库库位 ID'' AFTER location_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- 4. mes_wm_transaction.sn_code（SN 码）
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_transaction' AND column_name = 'sn_code');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_transaction ADD COLUMN sn_code VARCHAR(100) NULL COMMENT ''SN 码'' AFTER batch_code',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
