-- =============================================================================
|
-- 诚发五金（CFWJ）MES 编码规则 + 条码配置补全脚本
|
-- 日期：2026-11-03
|
-- 目标库：本地 CFWJ（127.0.0.1:3306）。请勿在生产库执行。
|
--
|
-- 背景：
|
-- 1) 前端 MesAutoCodeRuleCode 里引用了 9 个规则 code，但 mes_md_auto_code_rule 里没有，
|
-- 这些页面的「生成」按钮必报「编码规则不存在」。
|
-- 2) mes_wm_barcode_config 为空，autoGenerateBarcode 直接 return，
|
-- 工具/设备/仓库等页面的条码页签永远显示「未找到对应条码数据」，
|
-- 条码清单页手工新增也因「条码配置不存在」失败。
|
-- 3) 条码配置（5906）/条码清单（5911）两个页面菜单在 CFWJ 里缺失，
|
-- 菜单树只剩孤立的查询按钮 5907/5912。
|
--
|
-- 脚本幂等：全部 INSERT ... SELECT ... WHERE NOT EXISTS，可重复执行。
|
-- =============================================================================
|
|
-- -----------------------------------------------------------------------------
|
-- 一、编号生成规则：补 9 条规则 + 规则组成
|
-- 组成口径沿用库内既有规则：
|
-- 有日期段：固定字符 + yyyyMMdd + 3 位流水（按天循环）
|
-- 无日期段：固定字符 + 3 位流水（不循环，避免每天清零撞已存在的编码）
|
-- -----------------------------------------------------------------------------
|
|
-- 1.1 规则主表
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'DV_CHECK_PLAN_CODE', '点检保养方案编码', '点检保养方案自动编码', 6, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'DV_CHECK_PLAN_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'PD_PROJECT_CODE', '产品设计项目编码', '产品设计项目自动编码', 15, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PD_PROJECT_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'PD_TASK_CODE', '产品设计任务编码', '产品设计任务自动编码', 15, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PD_TASK_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'PD_VERSION_CODE', '产品设计版本编码', '产品设计版本自动编码', 14, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PD_VERSION_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'PRO_CARD_CODE', '流转卡编码', '生产流转卡自动编码', 14, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PRO_CARD_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'PRO_PROCESS_CODE', '工序编码', '生产工序自动编码', 6, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PRO_PROCESS_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'QC_IQC_VENDOR_BATCH_CODE', '来料检验供应商批次编码', '来料检验单供应商批次号自动编码', 17, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'QC_IQC_VENDOR_BATCH_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'TM_TOOL_CODE', '工具编码', '工装工具自动编码', 6, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'TM_TOOL_CODE' AND deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_rule
|
(code, name, description, max_length, padded, padded_char, padded_method, status, creator)
|
SELECT 'PRODUCTRECPT_CODE', '产品入库单编码', '产品入库单自动编码', 14, b'1', '0', 1, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PRODUCTRECPT_CODE' AND deleted = b'0');
|
|
-- 1.1.1 修正最大长度（脚本可重复执行：早期版本 PD_TASK_CODE 误设为 14，会把流水号末位截掉）
|
UPDATE mes_md_auto_code_rule
|
SET max_length = 15
|
WHERE code = 'PD_TASK_CODE' AND deleted = b'0' AND max_length <> 15;
|
|
-- 1.2 规则组成（sort=1 固定字符 / sort=2 日期 / sort=3 流水号）
|
-- 分段类型 type：1 输入字符 2 当前日期 3 固定字符 4 流水号
|
-- 循环方式 cycle_method：3 按天；padded_method：1 左补齐
|
|
-- DV_CHECK_PLAN_CODE：CP_ + 3 位流水（不循环）
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 3, 'CP_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'DV_CHECK_PLAN_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, creator)
|
SELECT r.id, 2, 4, 3, 1, 1, b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'DV_CHECK_PLAN_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
-- PD_PROJECT_CODE：PDP_ + yyyyMMdd + 3 位流水
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 4, 'PDP_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_PROJECT_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, date_format, cycle_flag, creator)
|
SELECT r.id, 2, 2, 8, 'yyyyMMdd', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_PROJECT_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, creator)
|
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_PROJECT_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3 AND p.deleted = b'0');
|
|
-- PD_TASK_CODE：PTK_ + yyyyMMdd + 3 位流水
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 4, 'PTK_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_TASK_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, date_format, cycle_flag, creator)
|
SELECT r.id, 2, 2, 8, 'yyyyMMdd', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_TASK_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, creator)
|
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_TASK_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3 AND p.deleted = b'0');
|
|
-- PD_VERSION_CODE：PV_ + yyyyMMdd + 3 位流水
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 3, 'PV_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_VERSION_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, date_format, cycle_flag, creator)
|
SELECT r.id, 2, 2, 8, 'yyyyMMdd', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_VERSION_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, creator)
|
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PD_VERSION_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3 AND p.deleted = b'0');
|
|
-- PRO_CARD_CODE：PC_ + yyyyMMdd + 3 位流水
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 3, 'PC_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRO_CARD_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, date_format, cycle_flag, creator)
|
SELECT r.id, 2, 2, 8, 'yyyyMMdd', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRO_CARD_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, creator)
|
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRO_CARD_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3 AND p.deleted = b'0');
|
|
-- PRO_PROCESS_CODE：OP_ + 3 位流水（不循环）
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 3, 'OP_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRO_PROCESS_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, creator)
|
SELECT r.id, 2, 4, 3, 1, 1, b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRO_PROCESS_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
-- QC_IQC_VENDOR_BATCH_CODE：IQCVB_ + yyyyMMdd + 3 位流水
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 6, 'IQCVB_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'QC_IQC_VENDOR_BATCH_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, date_format, cycle_flag, creator)
|
SELECT r.id, 2, 2, 8, 'yyyyMMdd', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'QC_IQC_VENDOR_BATCH_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, creator)
|
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'QC_IQC_VENDOR_BATCH_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3 AND p.deleted = b'0');
|
|
-- TM_TOOL_CODE：TM_ + 3 位流水（不循环）
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 3, 'TM_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'TM_TOOL_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, creator)
|
SELECT r.id, 2, 4, 3, 1, 1, b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'TM_TOOL_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
-- PRODUCTRECPT_CODE：PR_ + yyyyMMdd + 3 位流水
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, fix_character, cycle_flag, creator)
|
SELECT r.id, 1, 3, 3, 'PR_', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRODUCTRECPT_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, date_format, cycle_flag, creator)
|
SELECT r.id, 2, 2, 8, 'yyyyMMdd', b'0', '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRODUCTRECPT_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2 AND p.deleted = b'0');
|
|
INSERT INTO mes_md_auto_code_part
|
(rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, creator)
|
SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '1'
|
FROM mes_md_auto_code_rule r
|
WHERE r.code = 'PRODUCTRECPT_CODE' AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3 AND p.deleted = b'0');
|
|
|
-- -----------------------------------------------------------------------------
|
-- 二、条码配置：18 个业务类型全覆盖
|
-- 条码格式 format：1 二维码；状态 status：0 启用；auto_generate_flag：1 自动生成
|
-- 内容格式只支持 {BUSINESSCODE} 占位符（后端仅替换这一个）
|
-- -----------------------------------------------------------------------------
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 102, 'WH-{BUSINESSCODE}', 'WH-WH_001', b'1', 0, '仓库条码：内容 = WH- + 仓库编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 102 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 103, 'LC-{BUSINESSCODE}', 'LC-LC_001', b'1', 0, '库区条码：内容 = LC- + 库区编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 103 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 104, 'AR-{BUSINESSCODE}', 'AR-AR_001', b'1', 0, '库位条码：内容 = AR- + 库位编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 104 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 105, 'PK-{BUSINESSCODE}', 'PK-PK_20261103001', b'1', 0, '装箱单条码：内容 = PK- + 装箱单编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 105 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 106, 'ST-{BUSINESSCODE}', 'ST-ST_001', b'1', 0, '库存条码：内容 = ST- + 库存编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 106 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 107, 'BT-{BUSINESSCODE}', 'BT-BT_20261103001', b'1', 0, '批次条码：内容 = BT- + 批次编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 107 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 109, 'SN-{BUSINESSCODE}', 'SN-SN_202611030001', b'1', 0, 'SN 条码：内容 = SN- + SN 码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 109 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 300, 'PC-{BUSINESSCODE}', 'PC-PC_20261103001', b'1', 0, '流转卡条码：内容 = PC- + 流转卡编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 300 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 301, 'WO-{BUSINESSCODE}', 'WO-WO_20261103001', b'1', 0, '工单条码：内容 = WO- + 工单编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 301 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 302, 'TF-{BUSINESSCODE}', 'TF-TF_20261103001', b'1', 0, '流转单条码：内容 = TF- + 流转单编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 302 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 400, 'EQ-{BUSINESSCODE}', 'EQ-MC_20261103001', b'1', 0, '设备条码：内容 = EQ- + 设备编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 400 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 500, 'TM-{BUSINESSCODE}', 'TM-TM_001', b'1', 0, '工具条码：内容 = TM- + 工具编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 500 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 600, 'MAT-{BUSINESSCODE}', 'MAT-ITEM_20261103001', b'1', 0, '物料条码：内容 = MAT- + 物料编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 600 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 601, 'VND-{BUSINESSCODE}', 'VND-VND_20261103001', b'1', 0, '供应商条码：内容 = VND- + 供应商编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 601 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 602, 'WS-{BUSINESSCODE}', 'WS-WS_001', b'1', 0, '工作站条码：内容 = WS- + 工作站编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 602 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 603, 'WP-{BUSINESSCODE}', 'WP-WP_001', b'1', 0, '车间条码：内容 = WP- + 车间编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 603 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 604, 'USR-{BUSINESSCODE}', 'USR-EMP001', b'1', 0, '人员条码：内容 = USR- + 员工工号', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 604 AND deleted = b'0');
|
|
INSERT INTO mes_wm_barcode_config
|
(format, biz_type, content_format, content_example, auto_generate_flag, status, remark, creator)
|
SELECT 1, 605, 'CLT-{BUSINESSCODE}', 'CLT-CLT_20261103001', b'1', 0, '客户条码：内容 = CLT- + 客户编码', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM mes_wm_barcode_config WHERE biz_type = 605 AND deleted = b'0');
|
|
|
-- -----------------------------------------------------------------------------
|
-- 三、菜单：补「条码配置」「条码清单」两个页面及其按钮（挂到 仓库管理 1075579）
|
-- 5907/5912 两个查询按钮已存在但父级缺失，本段补齐父级 5906/5911。
|
-- -----------------------------------------------------------------------------
|
|
INSERT INTO system_menu
|
(id, name, permission, type, sort, parent_id, path, icon, component, component_name,
|
status, visible, keep_alive, always_show, creator)
|
SELECT 5906, '条码配置', '', 2, 8, 1075579, 'barcode/config', 'fa:barcode', 'wls/barcode/config/index',
|
'MesWmBarcodeConfig', 0, b'1', b'1', b'1', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5906);
|
|
INSERT INTO system_menu
|
(id, name, permission, type, sort, parent_id, path, icon, component, component_name,
|
status, visible, keep_alive, always_show, creator)
|
SELECT 5911, '条码清单', '', 2, 9, 1075579, 'barcode', 'fa:qrcode', 'wls/barcode/index',
|
'MesWmBarcode', 0, b'1', b'1', b'1', '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5911);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5908, '条码配置创建', 'mes:wm-barcode-config:create', 3, 2, 5906, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5908);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5909, '条码配置更新', 'mes:wm-barcode-config:update', 3, 3, 5906, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5909);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5910, '条码配置删除', 'mes:wm-barcode-config:delete', 3, 4, 5906, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5910);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5913, '条码清单创建', 'mes:wm-barcode:create', 3, 2, 5911, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5913);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5914, '条码清单更新', 'mes:wm-barcode:update', 3, 3, 5911, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5914);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5915, '条码清单删除', 'mes:wm-barcode:delete', 3, 4, 5911, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5915);
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, status, creator)
|
SELECT 5916, '条码清单导出', 'mes:wm-barcode:export', 3, 5, 5911, 0, '1'
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5916);
|
|
-- 角色授权：所有有效业务角色可查看；生产管理员(166)/经理(168) 可维护。
|
-- 菜单：5906 条码配置 / 5907 查询 / 5911 条码清单 / 5912 查询
|
INSERT INTO system_role_menu (role_id, menu_id, creator)
|
SELECT r.id, m.id, '1'
|
FROM system_role r
|
JOIN system_menu m ON m.id IN (5906, 5907, 5911, 5912)
|
WHERE r.id IN (163, 164, 165, 166, 167, 168) AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM system_role_menu rm
|
WHERE rm.role_id = r.id AND rm.menu_id = m.id AND rm.deleted = b'0');
|
|
-- 菜单：5908/5909/5910 配置增改删；5913/5914/5915/5916 清单增改删导出
|
INSERT INTO system_role_menu (role_id, menu_id, creator)
|
SELECT r.id, m.id, '1'
|
FROM system_role r
|
JOIN system_menu m ON m.id IN (5908, 5909, 5910, 5913, 5914, 5915, 5916)
|
WHERE r.id IN (166, 168) AND r.deleted = b'0'
|
AND NOT EXISTS (SELECT 1 FROM system_role_menu rm
|
WHERE rm.role_id = r.id AND rm.menu_id = m.id AND rm.deleted = b'0');
|
|
-- =============================================================================
|
-- 校验（执行后可手工核对，不属于变更内容）
|
-- SELECT code,name FROM mes_md_auto_code_rule WHERE code IN ('DV_CHECK_PLAN_CODE','PD_PROJECT_CODE',
|
-- 'PD_TASK_CODE','PD_VERSION_CODE','PRO_CARD_CODE','PRO_PROCESS_CODE','QC_IQC_VENDOR_BATCH_CODE',
|
-- 'TM_TOOL_CODE','PRODUCTRECPT_CODE'); -- 期望 9 条
|
-- SELECT r.code, COUNT(p.id) FROM mes_md_auto_code_rule r JOIN mes_md_auto_code_part p ON p.rule_id = r.id
|
-- WHERE r.code = 'PRO_PROCESS_CODE' GROUP BY r.code; -- 期望 2 段，其余 2 段或 3 段
|
-- SELECT biz_type, content_format FROM mes_wm_barcode_config ORDER BY biz_type; -- 期望 18 条
|
-- =============================================================================
|
