-- 批次追溯数据模型补强
|
-- 说明：字段先允许为空，历史数据回填完成后再评估是否收紧约束。
|
-- 注意：项目不使用 tenant_id；执行前请确认字段和索引不存在。
|
|
ALTER TABLE mes_wm_batch
|
ADD COLUMN pro_batch_id BIGINT NULL COMMENT '对应生产批次ID' AFTER id;
|
ALTER TABLE mes_wm_batch
|
ADD INDEX idx_mes_wm_batch_pro_batch_id (pro_batch_id);
|
|
ALTER TABLE mes_wm_sn
|
ADD COLUMN pro_batch_id BIGINT NULL COMMENT '生产批次ID' AFTER id;
|
ALTER TABLE mes_wm_sn
|
ADD COLUMN bag_code_id BIGINT NULL COMMENT '袋码ID' AFTER pro_batch_id;
|
ALTER TABLE mes_wm_sn
|
ADD COLUMN pallet_id BIGINT NULL COMMENT '托盘ID' AFTER bag_code_id;
|
ALTER TABLE mes_wm_sn
|
ADD COLUMN sales_id BIGINT NULL COMMENT '销售出库单ID' AFTER pallet_id;
|
ALTER TABLE mes_wm_sn
|
ADD INDEX idx_mes_wm_sn_pro_batch_id (pro_batch_id);
|
ALTER TABLE mes_wm_sn
|
ADD INDEX idx_mes_wm_sn_bag_code_id (bag_code_id);
|
|
ALTER TABLE mes_qc_seed_quality
|
ADD COLUMN batch_id BIGINT NULL COMMENT '生产批次ID' AFTER item_id;
|
ALTER TABLE mes_qc_seed_quality
|
ADD INDEX idx_mes_qc_seed_quality_batch_id (batch_id);
|
|
CREATE TABLE mes_trace_event (
|
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
event_no VARCHAR(64) NOT NULL COMMENT '事件编号',
|
event_type VARCHAR(32) NOT NULL COMMENT '事件类型',
|
event_category VARCHAR(32) NULL COMMENT '事件分类',
|
event_time DATETIME NOT NULL COMMENT '事件时间',
|
pro_batch_id BIGINT NULL COMMENT '生产批次ID',
|
wm_batch_id BIGINT NULL COMMENT '仓储批次ID',
|
bag_code_id BIGINT NULL COMMENT '袋码ID',
|
pallet_id BIGINT NULL COMMENT '托盘ID',
|
sn_id BIGINT NULL COMMENT 'SN ID',
|
item_id BIGINT NULL COMMENT '物料ID',
|
biz_type VARCHAR(32) NULL COMMENT '业务类型',
|
biz_id BIGINT NULL COMMENT '业务ID',
|
biz_code VARCHAR(100) NULL COMMENT '业务编码',
|
biz_line_id BIGINT NULL COMMENT '业务明细ID',
|
from_node_type VARCHAR(32) NULL COMMENT '来源节点类型',
|
from_node_id BIGINT NULL COMMENT '来源节点ID',
|
from_node_code VARCHAR(100) NULL COMMENT '来源节点编码',
|
to_node_type VARCHAR(32) NULL COMMENT '目标节点类型',
|
to_node_id BIGINT NULL COMMENT '目标节点ID',
|
to_node_code VARCHAR(100) NULL COMMENT '目标节点编码',
|
quantity DECIMAL(18, 6) NULL COMMENT '数量',
|
warehouse_id BIGINT NULL COMMENT '仓库ID',
|
location_id BIGINT NULL COMMENT '库区ID',
|
area_id BIGINT NULL COMMENT '库位ID',
|
line_id BIGINT NULL COMMENT '产线ID',
|
work_order_id BIGINT NULL COMMENT '工单ID',
|
process_id BIGINT NULL COMMENT '工序ID',
|
sales_id BIGINT NULL COMMENT '销售出库单ID',
|
quality_id BIGINT NULL COMMENT '质检记录ID',
|
operator_id BIGINT NULL COMMENT '操作人ID',
|
payload_json JSON NULL COMMENT '事件扩展数据',
|
content_hash VARCHAR(64) NULL COMMENT '事件内容SHA-256摘要',
|
prev_hash VARCHAR(64) NULL COMMENT '前一事件摘要',
|
creator VARCHAR(64) NULL DEFAULT '' COMMENT '创建者',
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updater VARCHAR(64) NULL DEFAULT '' COMMENT '更新者',
|
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted BIT(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (id),
|
UNIQUE KEY uk_mes_trace_event_no (event_no),
|
KEY idx_mes_trace_event_batch_time (pro_batch_id, event_time),
|
KEY idx_mes_trace_event_biz (biz_type, biz_id, biz_line_id),
|
KEY idx_mes_trace_event_nodes (from_node_id, to_node_id)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MES统一追溯事件';
|
|
-- 历史数据回填示例，确认数据质量后执行：
|
-- UPDATE mes_wm_batch w JOIN mes_pro_batch p ON p.code = w.code AND p.deleted = b'0'
|
-- SET w.pro_batch_id = p.id WHERE w.pro_batch_id IS NULL AND w.deleted = b'0';
|
-- UPDATE mes_qc_seed_quality q JOIN mes_pro_batch p ON p.code = q.batch_code AND p.deleted = b'0'
|
-- SET q.batch_id = p.id WHERE q.batch_id IS NULL AND q.deleted = b'0';
|
