347 lines
17 KiB
SQL
347 lines
17 KiB
SQL
-- ============================================================
|
||
-- MOM系统同步ERP字段 ALTER TABLE 脚本
|
||
-- 将ERP系统的以下表的字段同步到MOM系统对应表:
|
||
-- - erp_md_item -> md_item
|
||
-- - erp_md_item_type -> md_item_type
|
||
-- - erp_md_unit -> md_unit_measure
|
||
-- - erp_md_bom, erp_md_bom_line -> md_product_bom
|
||
-- 生成日期: 2026-01-26
|
||
-- 兼容 MySQL 5.7+
|
||
-- ============================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
-- ============================================================
|
||
-- 辅助存储过程: 安全添加字段 (字段不存在时才添加)
|
||
-- ============================================================
|
||
DROP PROCEDURE IF EXISTS `proc_add_column_if_not_exists`;
|
||
|
||
DELIMITER //
|
||
CREATE PROCEDURE `proc_add_column_if_not_exists`(
|
||
IN p_table_name VARCHAR(64),
|
||
IN p_column_name VARCHAR(64),
|
||
IN p_column_definition VARCHAR(500)
|
||
)
|
||
BEGIN
|
||
DECLARE v_count INT DEFAULT 0;
|
||
|
||
SELECT COUNT(*) INTO v_count
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = p_table_name
|
||
AND COLUMN_NAME = p_column_name;
|
||
|
||
IF v_count = 0 THEN
|
||
SET @sql = CONCAT('ALTER TABLE `', p_table_name, '` ADD COLUMN `', p_column_name, '` ', p_column_definition);
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
SELECT CONCAT('Added column: ', p_table_name, '.', p_column_name) AS result;
|
||
ELSE
|
||
SELECT CONCAT('Column already exists: ', p_table_name, '.', p_column_name) AS result;
|
||
END IF;
|
||
END //
|
||
|
||
-- 辅助存储过程: 安全创建索引 (索引不存在时才创建)
|
||
CREATE PROCEDURE `proc_add_index_if_not_exists`(
|
||
IN p_table_name VARCHAR(64),
|
||
IN p_index_name VARCHAR(64),
|
||
IN p_column_name VARCHAR(64)
|
||
)
|
||
BEGIN
|
||
DECLARE v_count INT DEFAULT 0;
|
||
|
||
SELECT COUNT(*) INTO v_count
|
||
FROM information_schema.STATISTICS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = p_table_name
|
||
AND INDEX_NAME = p_index_name;
|
||
|
||
IF v_count = 0 THEN
|
||
SET @sql = CONCAT('CREATE INDEX `', p_index_name, '` ON `', p_table_name, '` (`', p_column_name, '`)');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
SELECT CONCAT('Created index: ', p_index_name) AS result;
|
||
ELSE
|
||
SELECT CONCAT('Index already exists: ', p_index_name) AS result;
|
||
END IF;
|
||
END //
|
||
|
||
DELIMITER ;
|
||
|
||
|
||
-- ============================================================
|
||
-- 1. md_item 表新增字段 (物料产品表)
|
||
-- ============================================================
|
||
|
||
-- 新增租户ID字段
|
||
CALL proc_add_column_if_not_exists('md_item', 'tenant_id', 'varchar(20) DEFAULT NULL COMMENT ''租户ID''');
|
||
|
||
-- 新增二级分类编码
|
||
CALL proc_add_column_if_not_exists('md_item', 'item_sub_type_code', 'varchar(32) DEFAULT NULL COMMENT ''二级分类编码''');
|
||
|
||
-- 新增二级分类名称
|
||
CALL proc_add_column_if_not_exists('md_item', 'item_sub_type_name', 'varchar(50) DEFAULT NULL COMMENT ''二级分类名称''');
|
||
|
||
-- 新增默认仓库ID
|
||
CALL proc_add_column_if_not_exists('md_item', 'default_warehouse_id', 'bigint(20) DEFAULT NULL COMMENT ''默认仓库ID''');
|
||
|
||
-- 新增默认仓库名称
|
||
CALL proc_add_column_if_not_exists('md_item', 'default_warehouse_name', 'varchar(50) DEFAULT NULL COMMENT ''默认仓库名称''');
|
||
|
||
-- 新增重量
|
||
CALL proc_add_column_if_not_exists('md_item', 'weight', 'decimal(18,6) DEFAULT NULL COMMENT ''重量(kg)''');
|
||
|
||
-- 新增体积
|
||
CALL proc_add_column_if_not_exists('md_item', 'volume', 'decimal(18,6) DEFAULT NULL COMMENT ''体积(m³)''');
|
||
|
||
-- 新增删除标志
|
||
CALL proc_add_column_if_not_exists('md_item', 'del_flag', 'char(1) NOT NULL DEFAULT ''0'' COMMENT ''删除标志 (0存在 1删除)''');
|
||
|
||
-- 新增备注字段
|
||
CALL proc_add_column_if_not_exists('md_item', 'remark', 'varchar(500) DEFAULT NULL COMMENT ''备注''');
|
||
|
||
-- 创建索引
|
||
CALL proc_add_index_if_not_exists('md_item', 'idx_item_tenant', 'tenant_id');
|
||
|
||
|
||
-- ============================================================
|
||
-- 2. md_item_type 表新增字段 (物料分类表)
|
||
-- ============================================================
|
||
|
||
-- 新增租户ID字段
|
||
CALL proc_add_column_if_not_exists('md_item_type', 'tenant_id', 'varchar(20) DEFAULT NULL COMMENT ''租户ID''');
|
||
|
||
-- 新增删除标志
|
||
CALL proc_add_column_if_not_exists('md_item_type', 'del_flag', 'char(1) NOT NULL DEFAULT ''0'' COMMENT ''删除标志 (0存在 1删除)''');
|
||
|
||
-- 扩展ancestors字段长度 (从255扩展到500)
|
||
-- 注意: 这个操作是安全的，扩展长度不会丢失数据
|
||
ALTER TABLE `md_item_type`
|
||
MODIFY COLUMN `ancestors` varchar(500) DEFAULT NULL COMMENT '所有层级父节点';
|
||
|
||
-- 创建索引
|
||
CALL proc_add_index_if_not_exists('md_item_type', 'idx_item_type_tenant', 'tenant_id');
|
||
|
||
|
||
-- ============================================================
|
||
-- 3. md_unit_measure 表新增字段 (计量单位表)
|
||
-- ============================================================
|
||
|
||
-- 新增租户ID字段
|
||
CALL proc_add_column_if_not_exists('md_unit_measure', 'tenant_id', 'varchar(20) DEFAULT NULL COMMENT ''租户ID''');
|
||
|
||
-- 新增主单位ID
|
||
CALL proc_add_column_if_not_exists('md_unit_measure', 'primary_id', 'bigint(20) DEFAULT NULL COMMENT ''主单位ID''');
|
||
|
||
-- 新增是否主单位标志
|
||
CALL proc_add_column_if_not_exists('md_unit_measure', 'primary_flag', 'char(1) NOT NULL DEFAULT ''Y'' COMMENT ''是否主单位(Y/N)''');
|
||
|
||
-- 新增换算比例
|
||
CALL proc_add_column_if_not_exists('md_unit_measure', 'change_rate', 'decimal(18,6) DEFAULT 1.000000 COMMENT ''与主单位换算比例''');
|
||
|
||
-- 新增删除标志
|
||
CALL proc_add_column_if_not_exists('md_unit_measure', 'del_flag', 'char(1) NOT NULL DEFAULT ''0'' COMMENT ''删除标志 (0存在 1删除)''');
|
||
|
||
-- 创建索引
|
||
CALL proc_add_index_if_not_exists('md_unit_measure', 'idx_measure_tenant', 'tenant_id');
|
||
|
||
|
||
-- ============================================================
|
||
-- 清理: 删除辅助存储过程 (可选)
|
||
-- ============================================================
|
||
-- DROP PROCEDURE IF EXISTS `proc_add_column_if_not_exists`;
|
||
-- DROP PROCEDURE IF EXISTS `proc_add_index_if_not_exists`;
|
||
|
||
|
||
-- ============================================================
|
||
-- 4. md_product_bom 表新增字段 (产品BOM关系表)
|
||
-- 同步自ERP: erp_md_bom, erp_md_bom_line
|
||
-- ============================================================
|
||
|
||
-- 新增BOM编码 (来自 erp_md_bom.bom_code)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'bom_code', 'varchar(32) DEFAULT NULL COMMENT ''BOM编码''');
|
||
|
||
-- 新增BOM名称 (来自 erp_md_bom.bom_name)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'bom_name', 'varchar(100) DEFAULT NULL COMMENT ''BOM名称''');
|
||
|
||
-- 新增版本号 (来自 erp_md_bom.version)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'version', 'varchar(20) DEFAULT NULL COMMENT ''版本号''');
|
||
|
||
-- 新增版本说明 (来自 erp_md_bom.version_desc)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'version_desc', 'varchar(200) DEFAULT NULL COMMENT ''版本说明''');
|
||
|
||
-- 新增状态 (来自 erp_md_bom.status)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'status', 'varchar(20) DEFAULT ''APPROVED'' COMMENT ''BOM状态(DRAFT/APPROVED/OBSOLETE)''');
|
||
|
||
-- 新增行号 (来自 erp_md_bom_line.line_no)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'line_no', 'int(11) DEFAULT 0 COMMENT ''BOM行号''');
|
||
|
||
-- 新增损耗率 (来自 erp_md_bom_line.loss_rate)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'loss_rate', 'decimal(5,2) DEFAULT 0.00 COMMENT ''损耗率(%)''');
|
||
|
||
-- 新增供应方式 (来自 erp_md_bom_line.supply_type)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'supply_type', 'varchar(20) DEFAULT ''PURCHASE'' COMMENT ''供应方式(PURCHASE/PRODUCE/OUTSOURCE)''');
|
||
|
||
-- 新增租户ID
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'tenant_id', 'varchar(20) DEFAULT NULL COMMENT ''租户ID''');
|
||
|
||
-- 新增删除标志 (来自 erp_md_bom.del_flag)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'del_flag', 'char(1) NOT NULL DEFAULT ''0'' COMMENT ''删除标志(0存在 1删除)''');
|
||
|
||
-- 创建索引
|
||
CALL proc_add_index_if_not_exists('md_product_bom', 'idx_bom_item', 'item_id');
|
||
CALL proc_add_index_if_not_exists('md_product_bom', 'idx_bom_tenant', 'tenant_id');
|
||
CALL proc_add_index_if_not_exists('md_product_bom', 'idx_bom_code', 'bom_code');
|
||
|
||
|
||
-- ============================================================
|
||
-- 5. md_item 表新增BOM相关字段 (来自 erp_md_bom)
|
||
-- 物料档案表关联默认BOM信息
|
||
-- ============================================================
|
||
|
||
-- 新增默认BOM ID (关联 erp_md_bom.bom_id)
|
||
CALL proc_add_column_if_not_exists('md_item', 'default_bom_id', 'bigint(20) DEFAULT NULL COMMENT ''默认BOM ID''');
|
||
|
||
-- 新增默认BOM编码 (关联 erp_md_bom.bom_code)
|
||
CALL proc_add_column_if_not_exists('md_item', 'default_bom_code', 'varchar(32) DEFAULT NULL COMMENT ''默认BOM编码''');
|
||
|
||
-- 新增默认BOM名称 (关联 erp_md_bom.bom_name)
|
||
CALL proc_add_column_if_not_exists('md_item', 'default_bom_name', 'varchar(100) DEFAULT NULL COMMENT ''默认BOM名称''');
|
||
|
||
-- 新增基本数量 (来自 erp_md_bom.base_qty)
|
||
CALL proc_add_column_if_not_exists('md_item', 'base_qty', 'decimal(18,4) DEFAULT 1.0000 COMMENT ''基本数量''');
|
||
|
||
-- 新增默认BOM版本号 (关联 erp_md_bom.version)
|
||
CALL proc_add_column_if_not_exists('md_item', 'default_bom_version', 'varchar(20) DEFAULT NULL COMMENT ''默认BOM版本号''');
|
||
|
||
-- 创建索引
|
||
CALL proc_add_index_if_not_exists('md_item', 'idx_item_default_bom', 'default_bom_id');
|
||
|
||
|
||
-- ============================================================
|
||
-- 6. md_product_bom 表补充字段 (来自 erp_md_bom_line)
|
||
-- ============================================================
|
||
|
||
-- 新增基本用量 (来自 erp_md_bom_line.base_qty)
|
||
-- 注: quantity字段已存在，这里添加base_qty作为原始用量
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'base_qty', 'decimal(18,6) DEFAULT 1.000000 COMMENT ''基本用量(同步自ERP)''');
|
||
|
||
-- 新增产品物料编码 (来自 erp_md_bom.item_code)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'item_code', 'varchar(32) DEFAULT NULL COMMENT ''产品物料编码''');
|
||
|
||
-- 新增产品物料名称 (来自 erp_md_bom.item_name)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'item_name', 'varchar(100) DEFAULT NULL COMMENT ''产品物料名称''');
|
||
|
||
-- 新增产品物料规格 (关联查询)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'item_spec', 'varchar(200) DEFAULT NULL COMMENT ''产品物料规格''');
|
||
|
||
-- 新增子件规格 (来自 erp_md_bom_line.specification)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'specification', 'varchar(200) DEFAULT NULL COMMENT ''子件规格型号''');
|
||
|
||
-- 新增计量单位名称 (来自 erp_md_bom_line.unit_name)
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'unit_name', 'varchar(20) DEFAULT NULL COMMENT ''计量单位名称''');
|
||
|
||
-- 确保enable_flag字段存在
|
||
CALL proc_add_column_if_not_exists('md_product_bom', 'enable_flag', 'char(1) DEFAULT ''Y'' COMMENT ''是否启用(Y/N)''');
|
||
|
||
|
||
-- ============================================================
|
||
-- 字段变更汇总
|
||
-- ============================================================
|
||
--
|
||
-- md_item (物料产品表) 新增字段:
|
||
-- - tenant_id: 租户ID
|
||
-- - item_sub_type_code: 二级分类编码
|
||
-- - item_sub_type_name: 二级分类名称
|
||
-- - default_warehouse_id: 默认仓库ID
|
||
-- - default_warehouse_name: 默认仓库名称
|
||
-- - weight: 重量(kg)
|
||
-- - volume: 体积(m³)
|
||
-- - del_flag: 删除标志
|
||
-- - remark: 备注
|
||
--
|
||
-- md_item_type (物料分类表) 新增字段:
|
||
-- - tenant_id: 租户ID
|
||
-- - del_flag: 删除标志
|
||
-- - ancestors: 字段长度从255扩展到500
|
||
--
|
||
-- md_unit_measure (计量单位表) 新增字段:
|
||
-- - tenant_id: 租户ID
|
||
-- - primary_id: 主单位ID
|
||
-- - primary_flag: 是否主单位
|
||
-- - change_rate: 与主单位换算比例
|
||
-- - del_flag: 删除标志
|
||
--
|
||
-- md_product_bom (产品BOM关系表) 新增字段:
|
||
-- - bom_code: BOM编码 (来自 erp_md_bom)
|
||
-- - bom_name: BOM名称 (来自 erp_md_bom)
|
||
-- - version: 版本号 (来自 erp_md_bom)
|
||
-- - version_desc: 版本说明 (来自 erp_md_bom)
|
||
-- - status: BOM状态 (来自 erp_md_bom)
|
||
-- - line_no: 行号 (来自 erp_md_bom_line)
|
||
-- - loss_rate: 损耗率(%) (来自 erp_md_bom_line)
|
||
-- - supply_type: 供应方式 (来自 erp_md_bom_line)
|
||
-- - base_qty: 基本用量 (来自 erp_md_bom_line)
|
||
-- - item_code: 产品物料编码 (来自 erp_md_bom)
|
||
-- - item_name: 产品物料名称 (来自 erp_md_bom)
|
||
-- - item_spec: 产品物料规格
|
||
-- - specification: 子件规格型号 (来自 erp_md_bom_line)
|
||
-- - unit_name: 计量单位名称 (来自 erp_md_bom_line)
|
||
-- - enable_flag: 是否启用
|
||
-- - tenant_id: 租户ID
|
||
-- - del_flag: 删除标志
|
||
--
|
||
-- md_item (物料产品表) 新增BOM关联字段:
|
||
-- - default_bom_id: 默认BOM ID (来自 erp_md_bom)
|
||
-- - default_bom_code: 默认BOM编码 (来自 erp_md_bom)
|
||
-- - default_bom_name: 默认BOM名称 (来自 erp_md_bom)
|
||
-- - base_qty: 基本数量 (来自 erp_md_bom)
|
||
-- - default_bom_version: 默认BOM版本号 (来自 erp_md_bom)
|
||
--
|
||
-- ============================================================
|
||
-- ERP BOM字段映射说明:
|
||
-- ============================================================
|
||
--
|
||
-- erp_md_bom (BOM表头) -> md_item (关联字段) + md_product_bom (表头字段)
|
||
-- ┌─────────────────────┬───────────────────────┬──────────────────────────┐
|
||
-- │ ERP字段 │ MOM md_item字段 │ MOM md_product_bom字段 │
|
||
-- ├─────────────────────┼───────────────────────┼──────────────────────────┤
|
||
-- │ bom_id │ default_bom_id │ - │
|
||
-- │ bom_code │ default_bom_code │ bom_code │
|
||
-- │ bom_name │ default_bom_name │ bom_name │
|
||
-- │ item_id │ - │ item_id │
|
||
-- │ item_code │ - │ item_code │
|
||
-- │ item_name │ - │ item_name │
|
||
-- │ unit_name │ - │ - │
|
||
-- │ base_qty │ base_qty │ - │
|
||
-- │ version │ default_bom_version │ version │
|
||
-- │ version_desc │ - │ version_desc │
|
||
-- │ status │ - │ status │
|
||
-- │ enable_flag │ - │ enable_flag │
|
||
-- │ del_flag │ - │ del_flag │
|
||
-- └─────────────────────┴───────────────────────┴──────────────────────────┘
|
||
--
|
||
-- erp_md_bom_line (BOM明细) -> md_product_bom (明细字段)
|
||
-- ┌─────────────────────┬──────────────────────────┐
|
||
-- │ ERP字段 │ MOM md_product_bom字段 │
|
||
-- ├─────────────────────┼──────────────────────────┤
|
||
-- │ line_id │ bom_id │
|
||
-- │ bom_id │ (通过bom_code关联) │
|
||
-- │ line_no │ line_no │
|
||
-- │ item_id │ bom_item_id │
|
||
-- │ item_code │ bom_item_code │
|
||
-- │ item_name │ bom_item_name │
|
||
-- │ specification │ bom_item_spec │
|
||
-- │ unit_name │ unit_name │
|
||
-- │ base_qty │ base_qty / quantity │
|
||
-- │ loss_rate │ loss_rate │
|
||
-- │ supply_type │ supply_type │
|
||
-- │ remark │ remark │
|
||
-- │ del_flag │ del_flag │
|
||
-- │ tenant_id │ tenant_id │
|
||
-- └─────────────────────┴──────────────────────────┘
|
||
--
|
||
-- ============================================================
|