-- 工艺路线工序产出按产品归属及默认报工库位（MySQL 8.x）
|
-- 先执行本脚本，再部署对应后端版本；历史工艺路线产品启用前需补齐仓库、库区、库位。
|
|
CREATE TABLE IF NOT EXISTS `mes_pro_route_process_output` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`route_process_id` bigint NOT NULL COMMENT '工艺路线工序编号',
|
`route_id` bigint NOT NULL COMMENT '工艺路线编号',
|
`process_id` bigint NOT NULL COMMENT '工序编号',
|
`product_id` bigint DEFAULT NULL COMMENT '归属产品编号，空为通用',
|
`item_id` bigint NOT NULL COMMENT '产出物料编号',
|
`sort` int DEFAULT NULL COMMENT '排序',
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) 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`),
|
KEY `idx_route_process_id` (`route_process_id`),
|
KEY `idx_route_id` (`route_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MES 工艺路线工序产出归属';
|
|
-- 当前分支保留的工艺参数模板和工序收率；目标库可能尚未执行对应历史迁移。
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process' AND column_name = 'param_template_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_route_process ADD COLUMN param_template_id BIGINT NULL COMMENT ''工艺参数模板编号'' AFTER process_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process' AND column_name = 'yield_rate'),
|
'SELECT 1', 'ALTER TABLE mes_pro_route_process ADD COLUMN yield_rate DECIMAL(10,4) NULL COMMENT ''工序收率（产出/投入，0~1）'' AFTER param_template_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = 'warehouse_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_route_product ADD COLUMN warehouse_id BIGINT NULL COMMENT ''报工产出仓库ID'' AFTER item_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = 'location_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_route_product ADD COLUMN location_id BIGINT NULL COMMENT ''报工产出库区ID'' AFTER warehouse_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_product' AND column_name = 'area_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_route_product ADD COLUMN area_id BIGINT NULL COMMENT ''报工产出库位ID'' AFTER location_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'param_template_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN param_template_id BIGINT NULL COMMENT ''工艺参数模板编号'' AFTER process_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'yield_rate'),
|
'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN yield_rate DECIMAL(10,4) NULL COMMENT ''工序收率（产出/投入，0~1）'' AFTER param_template_id');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'warehouse_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN warehouse_id BIGINT NULL COMMENT ''报工产出仓库ID''');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'location_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN location_id BIGINT NULL COMMENT ''报工产出库区ID''');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'area_id'),
|
'SELECT 1', 'ALTER TABLE mes_pro_work_order_process ADD COLUMN area_id BIGINT NULL COMMENT ''报工产出库位ID''');
|
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
