1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| -- =============================================
| -- 修复：工序流转（生产流转卡）-> 流转卡编码 -> 生成，报“编码规则不存在”
| -- 原因：mes_md_auto_code_rule 中缺少 code = 'PRO_CARD_CODE' 的规则数据
| -- 说明：本脚本可重复执行，不会删除/覆盖已有规则
| -- 生成格式：LZK + yyyyMMdd + 3 位流水号（按天循环），例如 LZK20261019001
| -- =============================================
|
| -- 1. 流转卡编码规则主表
| INSERT INTO mes_md_auto_code_rule
| (code, name, description, max_length, padded, padded_char, padded_method, status, remark, creator, updater)
| SELECT 'PRO_CARD_CODE', '工序流转编码', '用于生成工序流转（生产流转卡）编码', 14, b'0', NULL, NULL, 0, NULL, '1', '1'
| FROM DUAL
| WHERE NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_rule WHERE code = 'PRO_CARD_CODE'
| );
|
| -- 2. 固定前缀 LZK
| INSERT INTO mes_md_auto_code_part
| (rule_id, sort, type, length, fix_character, serial_start_no, serial_step, cycle_flag, remark, creator, updater)
| SELECT r.id, 1, 3, 3, 'LZK', NULL, NULL, NULL, '固定前缀', '1', '1'
| FROM mes_md_auto_code_rule r
| WHERE r.code = 'PRO_CARD_CODE'
| AND NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 1
| );
|
| -- 3. 日期 yyyyMMdd
| INSERT INTO mes_md_auto_code_part
| (rule_id, sort, type, length, date_format, serial_start_no, serial_step, cycle_flag, remark, creator, updater)
| SELECT r.id, 2, 2, 8, 'yyyyMMdd', NULL, NULL, NULL, '日期', '1', '1'
| FROM mes_md_auto_code_rule r
| WHERE r.code = 'PRO_CARD_CODE'
| AND NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 2
| );
|
| -- 4. 3 位流水号（按天循环，从 1 开始，步长 1）
| INSERT INTO mes_md_auto_code_part
| (rule_id, sort, type, length, serial_start_no, serial_step, cycle_flag, cycle_method, remark, creator, updater)
| SELECT r.id, 3, 4, 3, 1, 1, b'1', 3, '流水号-按天循环', '1', '1'
| FROM mes_md_auto_code_rule r
| WHERE r.code = 'PRO_CARD_CODE'
| AND NOT EXISTS (
| SELECT 1 FROM mes_md_auto_code_part p WHERE p.rule_id = r.id AND p.sort = 3
| );
|
|
