namespace Admin.NET.Plugin.AiDOP.DataPlatform.Schema;
///
/// 中立层表（mdp_std_* / mdp_stg_* / dwd_*）DDL 唯一来源。
/// 服务通过 + 增量对齐；禁止在 *MdpSync* 服务内再写中立 CREATE。
///
///
/// 自 *MdpSync* 服务原文搬入；同表多处时保留列更多的一份（本轮无冲突合并）。
/// 已知必含列：mdp_std_work_order_schedule.approved_flag/void_flag；mdp_std_s6_report.start_work_date/task_no/domain。
/// mdp_std_fqc_result_detail 的 j1..j50 由原服务 Enumerable.Range 展开为静态列（与运行时一致）。
/// T8BaseInboundMdpSyncService 的 StgDdl/StdDdl 为插值模板，未搬入。
///
public static class MdpSchemaDefinition
{
private static readonly Dictionary Tables = new(StringComparer.OrdinalIgnoreCase)
{
["mdp_std_fqc_result"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_fqc_result (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
source_system VARCHAR(50) NOT NULL DEFAULT '',
bill_no VARCHAR(80) NULL,
inspect_time DATETIME NULL,
production_batch_no VARCHAR(255) NULL,
material_code VARCHAR(255) NULL,
material_name VARCHAR(255) NULL,
spec VARCHAR(255) NULL,
inspector VARCHAR(255) NULL,
inspect_qty DECIMAL(18,6) NULL,
source_bill_no VARCHAR(100) NULL,
production_order_no VARCHAR(255) NULL,
order_qty DECIMAL(18,6) NULL,
sap_work_order_no VARCHAR(64) NULL,
judgment VARCHAR(32) NULL,
qualified_qty DECIMAL(18,6) NULL,
unqualified_qty DECIMAL(18,6) NULL,
fire_no TEXT NULL,
inspect_spec_no VARCHAR(255) NULL,
inspect_spec_version VARCHAR(255) NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_fqc_result (tenant_id, source_row_id),
KEY idx_mdp_std_fqc_result_bill (tenant_id, bill_no),
KEY idx_mdp_std_fqc_result_inspect (tenant_id, inspect_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S7 FQC 检验单(结果)标准层'
""",
["mdp_std_fqc_result_detail"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_fqc_result_detail (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
std_result_id BIGINT NULL,
glid VARCHAR(100) NULL,
seq_no INT NULL,
inspection_item TEXT NULL,
accept_standard TEXT NULL,
tech_requirement TEXT NULL,
sampling_plan TEXT NULL,
sample_qty VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
upper_limit VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
lower_limit VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
size_specification VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
judgment VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
non_numeric_ok BIGINT NULL,
non_numeric_ng BIGINT NULL,
remark TEXT NULL,
j1 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j2 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j3 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j4 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j5 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j6 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j7 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j8 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j9 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j10 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j11 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j12 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j13 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j14 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j15 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j16 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j17 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j18 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j19 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j20 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j21 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j22 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j23 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j24 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j25 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j26 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j27 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j28 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j29 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j30 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j31 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j32 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j33 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j34 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j35 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j36 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j37 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j38 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j39 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j40 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j41 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j42 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j43 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j44 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j45 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j46 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j47 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j48 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j49 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
j50 VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_fqc_result_dtl (tenant_id, source_row_id),
KEY idx_mdp_std_fqc_result_dtl_head (tenant_id, glid),
KEY idx_mdp_std_fqc_result_dtl_stdres (std_result_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S7 FQC 检验单明细标准层(j1-j50样本)'
""",
["mdp_std_fqc_task"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_fqc_task (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
source_system VARCHAR(50) NOT NULL DEFAULT '',
bill_no VARCHAR(80) NULL,
production_order_no VARCHAR(255) NULL,
apply_org_id BIGINT NULL,
apply_time DATETIME NULL,
material_code VARCHAR(255) NULL,
product_name VARCHAR(255) NULL,
product_model VARCHAR(255) NULL,
production_batch_no VARCHAR(255) NULL,
order_qty DECIMAL(18,6) NULL,
qty DECIMAL(18,6) NULL,
applicant VARCHAR(50) NULL,
remark TEXT NULL,
priority VARCHAR(255) NULL,
inspect_start_time DATETIME NULL,
inspect_finish_time DATETIME NULL,
inspector VARCHAR(255) NULL,
inspect_progress VARCHAR(255) NULL,
sales_order_no VARCHAR(100) NULL,
sales_order_line VARCHAR(50) NULL,
apply_flag TINYINT NOT NULL DEFAULT 0,
domain VARCHAR(80) NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_fqc_task (tenant_id, source_system, source_row_id),
KEY idx_mdp_std_fqc_task_bill (tenant_id, bill_no),
KEY idx_mdp_std_fqc_task_apply (tenant_id, apply_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S7 FQC 检验任务标准层'
""",
["mdp_std_ipqc_inspection"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_ipqc_inspection (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
bill_no VARCHAR(64) NOT NULL,
product_model VARCHAR(255) NULL,
production_batch_no VARCHAR(255) NULL,
production_work_order VARCHAR(255) NULL,
result_judgement VARCHAR(32) NULL,
attachment TEXT NULL,
remark TEXT NULL,
inspector VARCHAR(255) NULL,
process_code VARCHAR(255) NULL,
process_name VARCHAR(255) NULL,
production_person VARCHAR(255) NULL,
sample_qty DECIMAL(18,6) NULL,
form_no VARCHAR(255) NULL,
version_no VARCHAR(255) NULL,
effective_date DATETIME NULL,
material_code VARCHAR(255) NULL,
material_name VARCHAR(255) NULL,
inspec_standard_version VARCHAR(255) NULL,
inspec_standard_code VARCHAR(255) NULL,
inspection_status VARCHAR(255) NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_ipqc_insp (tenant_id, source_row_id),
KEY idx_mdp_std_ipqc_insp_bill (tenant_id, bill_no),
KEY idx_mdp_std_ipqc_insp_effdate (tenant_id, effective_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6过程检验单头标准层'
""",
["mdp_std_ipqc_inspection_detail"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_ipqc_inspection_detail (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
std_head_id BIGINT NULL,
source_head_id VARCHAR(100) NULL,
inspection_item TEXT NULL,
inspection_basis TEXT NULL,
technical_standard TEXT NULL,
inspection_method TEXT NULL,
upper_limit VARCHAR(255) NULL,
lower_limit VARCHAR(255) NULL,
process_code VARCHAR(255) NULL,
process_name VARCHAR(255) NULL,
measurement_instrument VARCHAR(255) NULL,
inspector VARCHAR(255) NULL,
inspection_time DATETIME NULL,
sample_qty INT NULL,
inspection_qty DECIMAL(18,6) NULL,
unqualified_qty DECIMAL(18,6) NULL,
result_judgement VARCHAR(32) NULL,
non_numeric_ok INT NULL,
non_numeric_ng INT NULL,
remark TEXT NULL,
attachment TEXT NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_ipqc_insp_dtl (tenant_id, source_row_id),
KEY idx_mdp_std_ipqc_insp_dtl_head (std_head_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6过程检验单明细标准层(确定子集列;j1-j70后置)'
""",
["mdp_std_iqc_receipt_state"] =
"""
CREATE TABLE IF NOT EXISTS `mdp_std_iqc_receipt_state` (
`id` BIGINT NOT NULL,
`tenant_id` BIGINT NOT NULL,
`domain` VARCHAR(20) NOT NULL DEFAULT '',
`fbillno` VARCHAR(50) NOT NULL DEFAULT '',
`qc_nbr` VARCHAR(50) NULL,
`item_num` VARCHAR(100) NULL,
`location` VARCHAR(50) NULL,
`lot_serial` VARCHAR(100) NULL,
`rct_nbr` VARCHAR(50) NULL,
`pur_ord` VARCHAR(50) NULL,
`pur_line` INT NULL,
`potype` VARCHAR(20) NULL,
`qty_ordered` DECIMAL(18,4) NOT NULL DEFAULT 0,
`received_cum_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
`returned_cum_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
`sample_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
`receipt_pending_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
`receivable_detail_count` INT NOT NULL DEFAULT 0,
`source_system` VARCHAR(50) NULL,
`sync_batch_id` VARCHAR(50) NULL,
`sync_time` DATETIME NULL,
`create_time` DATETIME NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_mdp_std_iqc_rct` (`tenant_id`,`domain`,`fbillno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='L0 IQC收货标准层(只读贴源)'
""",
["mdp_std_operation_schedule"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_operation_schedule (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL,
source_system VARCHAR(50) NOT NULL DEFAULT '',
work_order VARCHAR(100) NOT NULL,
op_no VARCHAR(50) NULL,
work_center VARCHAR(100) NULL,
line_code VARCHAR(100) NULL,
item_code VARCHAR(100) NULL,
plan_date DATETIME NULL,
prod_date DATETIME NULL,
start_time DATETIME NULL,
end_time DATETIME NULL,
ord_qty DECIMAL(18,6) NULL,
comp_qty DECIMAL(18,6) NULL,
run_crew DECIMAL(18,6) NULL,
employee VARCHAR(200) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_std_operation_schedule (tenant_id, source_biz_key),
KEY idx_std_operation_schedule_work_order (tenant_id, work_order),
KEY idx_std_operation_schedule_batch (sync_batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2标准工序排程'
""",
["mdp_std_outsource_issue"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_outsource_issue (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
bill_no VARCHAR(24) NOT NULL,
issue_date DATETIME NULL,
outsource_no VARCHAR(60) NULL,
work_order VARCHAR(64) NULL,
department_code VARCHAR(20) NULL,
department_name VARCHAR(255) NULL,
issuer VARCHAR(255) NULL,
status VARCHAR(8) NULL,
status_desc VARCHAR(20) NULL,
remark VARCHAR(200) NULL,
create_user VARCHAR(24) NULL,
source_create_time DATETIME NULL,
eff_date DATETIME NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_outsrc_issue (tenant_id, bill_no),
KEY idx_mdp_std_outsrc_issue_date (tenant_id, issue_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5委外发料单头标准层'
""",
["mdp_std_outsource_issue_detail"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_outsource_issue_detail (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
std_head_id BIGINT NULL,
bill_no VARCHAR(24) NOT NULL,
line SMALLINT NOT NULL DEFAULT 0,
item_num VARCHAR(24) NULL,
item_name TEXT NULL,
um VARCHAR(8) NULL,
qty_ord DECIMAL(18,6) NULL DEFAULT 0,
location_from VARCHAR(8) NULL,
location_to VARCHAR(8) NULL,
status VARCHAR(8) NULL,
remark TEXT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_outsrc_issue_dtl (tenant_id, source_biz_key),
KEY idx_mdp_std_outsrc_issue_dtl_head (std_head_id),
KEY idx_mdp_std_outsrc_issue_dtl_bill (tenant_id, bill_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5委外发料单明细标准层'
""",
["mdp_std_production_issue"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_production_issue (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
domain VARCHAR(80) NOT NULL,
rec_id INT NOT NULL,
nbr VARCHAR(24) NULL,
issue_date DATETIME NULL,
status VARCHAR(8) NULL,
status_desc VARCHAR(20) NULL,
work_ord VARCHAR(64) NULL,
department VARCHAR(8) NULL,
department_desc VARCHAR(255) NULL,
qty_ord DECIMAL(18,5) NULL DEFAULT 0,
prod_line VARCHAR(8) NULL,
applicant_name VARCHAR(12) NULL,
issue_user VARCHAR(255) NULL,
user1 TEXT NULL,
remark VARCHAR(200) NULL,
create_user VARCHAR(24) NULL,
source_create_time DATETIME NULL,
pretreatment_state VARCHAR(50) NULL,
trans_type VARCHAR(24) NULL,
trans_type_text VARCHAR(50) NULL,
eff_date DATETIME NULL,
address VARCHAR(120) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_prod_issue (tenant_id, domain, rec_id),
KEY idx_mdp_std_prod_issue_nbr (tenant_id, nbr),
KEY idx_mdp_std_prod_issue_date (tenant_id, issue_date),
KEY idx_mdp_std_prod_issue_status (tenant_id, status),
KEY idx_mdp_std_prod_issue_workord (tenant_id, work_ord),
KEY idx_mdp_std_prod_issue_prodline (tenant_id, prod_line),
KEY idx_mdp_std_prod_issue_dept (tenant_id, department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5生产领料单头标准层'
""",
["mdp_std_production_receipt"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_production_receipt (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
domain VARCHAR(80) NOT NULL,
master_rec_id INT NULL,
detail_rec_id INT NOT NULL,
nbr VARCHAR(24) NULL,
line SMALLINT NOT NULL DEFAULT 0,
receipt_date DATETIME NULL,
status VARCHAR(8) NULL,
status_desc VARCHAR(20) NULL,
remark VARCHAR(200) NULL,
prod_line VARCHAR(8) NULL,
work_ord VARCHAR(64) NULL,
erp_work_ord VARCHAR(60) NULL,
department VARCHAR(8) NULL,
department_desc VARCHAR(255) NULL,
applicant_name VARCHAR(12) NULL,
item_num VARCHAR(24) NULL,
item_name VARCHAR(200) NULL,
item_spec VARCHAR(200) NULL,
um VARCHAR(8) NULL,
location_to VARCHAR(8) NULL,
location_to_desc VARCHAR(255) NULL,
lot_serial VARCHAR(120) NULL,
qty_rec DECIMAL(18,5) NULL DEFAULT 0,
qty_to DECIMAL(18,5) NULL DEFAULT 0,
location_from VARCHAR(8) NULL,
location_from_desc VARCHAR(255) NULL,
ord_nbr VARCHAR(48) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
closed_flag TINYINT NOT NULL DEFAULT 0,
closed_time DATETIME NULL,
task_no VARCHAR(80) NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_prod_receipt (tenant_id, domain, detail_rec_id),
KEY idx_mdp_std_prod_rcpt_nbr (tenant_id, nbr),
KEY idx_mdp_std_prod_rcpt_date (tenant_id, receipt_date),
KEY idx_mdp_std_prod_rcpt_workord (tenant_id, work_ord),
KEY idx_mdp_std_prod_rcpt_erp (tenant_id, erp_work_ord),
KEY idx_mdp_std_prod_rcpt_lot (tenant_id, lot_serial),
KEY idx_mdp_std_prod_rcpt_item (tenant_id, item_num),
KEY idx_mdp_std_prod_rcpt_locto (tenant_id, location_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5生产入库单标准层（扁平，一行=一条明细）'
""",
["mdp_std_production_return"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_production_return (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
domain VARCHAR(80) NOT NULL,
rec_id INT NOT NULL,
nbr VARCHAR(24) NULL,
return_date DATETIME NULL,
status VARCHAR(8) NULL,
status_desc VARCHAR(20) NULL,
work_ord VARCHAR(64) NULL,
department VARCHAR(8) NULL,
department_desc VARCHAR(255) NULL,
qty_ord DECIMAL(18,5) NULL DEFAULT 0,
prod_line VARCHAR(8) NULL,
applicant_name VARCHAR(12) NULL,
return_user VARCHAR(255) NULL,
user1 TEXT NULL,
user2 TEXT NULL,
remark VARCHAR(200) NULL,
create_user VARCHAR(24) NULL,
source_create_time DATETIME NULL,
trans_type VARCHAR(24) NULL,
trans_type_text VARCHAR(50) NULL,
eff_date DATETIME NULL,
ufld1 VARCHAR(50) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_prod_rtn (tenant_id, domain, rec_id),
KEY idx_mdp_std_prod_rtn_nbr (tenant_id, nbr),
KEY idx_mdp_std_prod_rtn_date (tenant_id, return_date),
KEY idx_mdp_std_prod_rtn_status (tenant_id, status),
KEY idx_mdp_std_prod_rtn_workord (tenant_id, work_ord),
KEY idx_mdp_std_prod_rtn_prodline (tenant_id, prod_line),
KEY idx_mdp_std_prod_rtn_dept (tenant_id, department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5生产退料单头标准层'
""",
["mdp_std_production_return_detail"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_production_return_detail (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
std_head_id BIGINT NULL,
domain VARCHAR(80) NOT NULL,
nbr_rec_id INT NOT NULL,
rec_id INT NULL,
line SMALLINT NOT NULL DEFAULT 0,
item_num VARCHAR(24) NULL,
item_name VARCHAR(200) NULL,
item_spec VARCHAR(200) NULL,
um VARCHAR(8) NULL,
qty_to DECIMAL(18,5) NULL DEFAULT 0,
qty_rec DECIMAL(18,5) NULL DEFAULT 0,
location_from VARCHAR(8) NULL,
location_from_desc VARCHAR(255) NULL,
location_to VARCHAR(8) NULL,
location_to_desc VARCHAR(255) NULL,
lot_serial VARCHAR(120) NULL,
status VARCHAR(8) NULL,
status_desc VARCHAR(20) NULL,
remark VARCHAR(500) NULL,
work_ord VARCHAR(64) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_prod_rtn_dtl (tenant_id, domain, nbr_rec_id, line),
KEY idx_mdp_std_prod_rtn_dtl_head (tenant_id, std_head_id),
KEY idx_mdp_std_prod_rtn_dtl_item (tenant_id, item_num),
KEY idx_mdp_std_prod_rtn_dtl_locto (tenant_id, location_to),
KEY idx_mdp_std_prod_rtn_dtl_lot (tenant_id, lot_serial)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5生产退料单明细标准层'
""",
["mdp_std_purchase_receipt"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_purchase_receipt (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
domain VARCHAR(24) NOT NULL,
receiver VARCHAR(24) NOT NULL,
line SMALLINT NOT NULL DEFAULT 0,
rct_date DATETIME NULL,
supp VARCHAR(20) NULL,
sort_name VARCHAR(255) NULL,
item_num VARCHAR(60) NULL,
item_name VARCHAR(200) NULL,
item_spec VARCHAR(200) NULL,
um VARCHAR(8) NULL,
qty_ordered DECIMAL(18,6) NULL DEFAULT 0,
qty_received DECIMAL(18,6) NULL DEFAULT 0,
lot_serial VARCHAR(120) NULL,
location VARCHAR(8) NULL,
ord_nbr VARCHAR(24) NULL,
ord_line SMALLINT NULL,
blanket_line INT NULL,
pur_ord VARCHAR(24) NULL,
pur_line SMALLINT NULL,
sales_job VARCHAR(200) NULL,
address1 VARCHAR(200) NULL,
req VARCHAR(20) NULL,
req_line INT NULL,
dop_req VARCHAR(255) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
approved_flag TINYINT NOT NULL DEFAULT 1,
approved_time DATETIME NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_std_pur_rct (tenant_id, domain, receiver, line),
KEY idx_mdp_std_pur_rct_date (tenant_id, rct_date),
KEY idx_mdp_std_pur_rct_item (tenant_id, item_num),
KEY idx_mdp_std_pur_rct_supp (tenant_id, supp),
KEY idx_mdp_std_pur_rct_purord (tenant_id, pur_ord),
KEY idx_mdp_std_pur_rct_salesjob (tenant_id, sales_job),
KEY idx_mdp_std_pur_rct_req (tenant_id, req),
KEY idx_mdp_std_pur_rct_dopreq (tenant_id, dop_req)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S5采购收货单标准层'
""",
["mdp_std_s4_iqc"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_s4_iqc (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
po_no VARCHAR(50) NULL, po_line VARCHAR(50) NULL,
supplier_code VARCHAR(50) NULL, item_code VARCHAR(50) NULL,
receipt_qty DECIMAL(18,6) NULL DEFAULT 0,
sample_qty DECIMAL(18,6) NULL DEFAULT 0,
defect_qty DECIMAL(18,6) NULL DEFAULT 0,
qc_result VARCHAR(20) NULL, receipt_date DATETIME NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_std_s4_iqc (tenant_id, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_std_s4_return"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_s4_return (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
po_no VARCHAR(50) NULL, po_line VARCHAR(50) NULL,
supplier_code VARCHAR(50) NULL, item_code VARCHAR(50) NULL,
return_qty DECIMAL(18,6) NULL DEFAULT 0,
return_reason VARCHAR(200) NULL, return_status VARCHAR(50) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_std_s4_return (tenant_id, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_std_s4_shipment"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_s4_shipment (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
shipment_no VARCHAR(50) NULL, po_no VARCHAR(50) NULL, po_line VARCHAR(50) NULL,
supplier_code VARCHAR(50) NULL, item_code VARCHAR(50) NULL,
ship_qty DECIMAL(18,6) NULL DEFAULT 0, ship_date DATETIME NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_std_s4_shipment (tenant_id, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_std_s4_shortage"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_s4_shortage (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
work_order VARCHAR(100) NULL, supplier_code VARCHAR(50) NULL, item_code VARCHAR(50) NULL,
shortage_qty DECIMAL(18,6) NULL DEFAULT 0, risk_level VARCHAR(20) NULL, need_date DATETIME NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL, sync_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_std_s4_shortage (tenant_id, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_std_s6_report"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_s6_report (
id bigint NOT NULL AUTO_INCREMENT,
tenant_id bigint NOT NULL DEFAULT 0,
factory_id bigint DEFAULT 1,
source_system varchar(50) NOT NULL DEFAULT 'T8',
work_order_no varchar(100) NOT NULL,
report_date datetime DEFAULT NULL,
report_qty decimal(18,6) DEFAULT NULL,
start_work_date datetime DEFAULT NULL,
task_no varchar(80) DEFAULT NULL,
domain varchar(80) DEFAULT NULL,
ztid varchar(50) DEFAULT NULL,
source_row_id varchar(100) NOT NULL,
source_biz_key varchar(200) NOT NULL,
sync_batch_id varchar(100) NOT NULL,
sync_time datetime NOT NULL,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_mdp_std_s6_report (tenant_id, source_system, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_std_work_order_schedule"] =
"""
CREATE TABLE IF NOT EXISTS mdp_std_work_order_schedule (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL,
source_system VARCHAR(50) NOT NULL DEFAULT '',
work_order VARCHAR(100) NOT NULL,
doc_type VARCHAR(32) NOT NULL DEFAULT 'PROD_TASK',
sales_order_no VARCHAR(100) NULL,
item_code VARCHAR(100) NULL,
item_name VARCHAR(200) NULL,
site_code VARCHAR(50) NULL,
status VARCHAR(50) NULL,
priority DECIMAL(18,6) NULL,
urgent_flag TINYINT NOT NULL DEFAULT 0,
qty_ordered DECIMAL(18,6) NULL,
qty_completed DECIMAL(18,6) NULL,
order_date DATETIME NULL,
due_date DATETIME NULL,
release_date DATETIME NULL,
prod_line VARCHAR(100) NULL,
specification VARCHAR(200) NULL,
lot_serial VARCHAR(100) NULL,
drawing_no VARCHAR(100) NULL,
project VARCHAR(200) NULL,
work_order_type VARCHAR(50) NULL,
labor_variance DECIMAL(18,6) NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
approved_flag TINYINT NOT NULL DEFAULT 1,
void_flag TINYINT NOT NULL DEFAULT 0,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_std_wo_sched_type (tenant_id, doc_type, work_order),
KEY idx_std_work_order_schedule_batch (sync_batch_id),
KEY idx_std_work_order_schedule_due (tenant_id, due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2标准工单排程'
""",
["mdp_stg_fqc_pull"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_fqc_pull (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S7 FQC 执行器贴源层（任务/结果/明细共表，source_table 区分）'
""",
["mdp_stg_ipqc_inspection"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_ipqc_inspection (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
source_table VARCHAR(100) NOT NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
raw_data JSON NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_stg_ipqc_insp (tenant_id, source_table, source_row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6过程检验单头贴源层'
""",
["mdp_stg_ipqc_inspection_detail"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_ipqc_inspection_detail (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NULL DEFAULT 1,
source_system VARCHAR(50) NOT NULL DEFAULT '',
source_table VARCHAR(100) NOT NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
raw_data JSON NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_stg_ipqc_insp_dtl (tenant_id, source_table, source_row_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6过程检验单明细贴源层'
""",
["mdp_stg_ipqc_pull"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_ipqc_pull (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S6 IPQC 执行器贴源层'
""",
["mdp_stg_outsource_issue_pull"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_outsource_issue_pull (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S5委外发料执行器贴源层（头/明细共表，source_table 区分）'
""",
["mdp_stg_production_issue"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_production_issue (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S5生产领料执行器贴源层'
""",
["mdp_stg_production_receipt"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_production_receipt (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S7成品入库执行器贴源层'
""",
["mdp_stg_production_return"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_production_return (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S5生产退料执行器贴源层（头/明细共表，source_table 区分）'
""",
["mdp_stg_purchase_receipt"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_purchase_receipt (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
tenant_id BIGINT NOT NULL,
source_system VARCHAR(50) NULL,
source_table VARCHAR(200),
source_row_id VARCHAR(200),
source_biz_key VARCHAR(300) NULL,
raw_data JSON,
sync_batch_id VARCHAR(100),
sync_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_batch (sync_batch_id),
KEY idx_src (source_table, source_row_id),
KEY idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S5采购收货贴源层'
""",
["mdp_stg_s4_iqc"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_s4_iqc (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
source_system VARCHAR(50) NOT NULL DEFAULT '',
source_table VARCHAR(100) NOT NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
raw_data JSON NOT NULL,
create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_stg_s4_iqc (tenant_id, source_table, source_row_id),
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_stg_s4_return"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_s4_return LIKE mdp_stg_s4_iqc
""",
["mdp_stg_s4_shipment"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_s4_shipment LIKE mdp_stg_s4_iqc
""",
["mdp_stg_s4_shortage"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_s4_shortage LIKE mdp_stg_s4_iqc
""",
["mdp_stg_s6_report"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_s6_report (
id bigint NOT NULL AUTO_INCREMENT,
tenant_id bigint NOT NULL DEFAULT 0,
source_system varchar(50) DEFAULT NULL,
source_table varchar(200) DEFAULT NULL,
source_row_id varchar(200) DEFAULT NULL,
source_biz_key varchar(300) DEFAULT NULL,
raw_data json DEFAULT NULL,
sync_batch_id varchar(100) DEFAULT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
sync_time datetime DEFAULT CURRENT_TIMESTAMP,
process_status varchar(20) NOT NULL DEFAULT 'PENDING',
process_message varchar(500) DEFAULT NULL,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
["mdp_stg_schedule"] =
"""
CREATE TABLE IF NOT EXISTS mdp_stg_schedule (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id VARCHAR(64) NULL,
source_system VARCHAR(50) NOT NULL DEFAULT '',
source_table VARCHAR(100) NOT NULL,
source_row_id VARCHAR(100) NOT NULL,
source_biz_key VARCHAR(200) NULL,
sync_batch_id VARCHAR(100) NOT NULL,
sync_time DATETIME NOT NULL,
process_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
process_message VARCHAR(500) NULL,
raw_data JSON NOT NULL,
create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_mdp_stg_schedule (tenant_id, source_table, source_row_id),
UNIQUE KEY uk_source_key (source_system, source_table, source_biz_key),
KEY idx_mdp_stg_schedule_batch (sync_batch_id),
KEY idx_mdp_stg_schedule_biz (source_biz_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2生产排程贴源层'
""",
["dwd_order_schedule_trans"] =
"""
CREATE TABLE IF NOT EXISTS dwd_order_schedule_trans (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL DEFAULT 0,
factory_id BIGINT NOT NULL DEFAULT 1,
stat_date DATE NOT NULL,
doc_type VARCHAR(32) NOT NULL DEFAULT 'PROD_TASK',
work_order VARCHAR(100) NOT NULL,
sales_order_no VARCHAR(100) NULL,
item_code VARCHAR(100) NULL,
item_name VARCHAR(200) NULL,
site_code VARCHAR(50) NULL,
prod_line VARCHAR(100) NULL,
status VARCHAR(50) NULL,
urgent_flag TINYINT NOT NULL DEFAULT 0,
qty_ordered DECIMAL(18,6) NULL,
qty_completed DECIMAL(18,6) NULL,
order_date DATETIME NULL,
due_date DATETIME NULL,
release_date DATETIME NULL,
first_plan_date DATETIME NULL,
last_plan_date DATETIME NULL,
first_start_time DATETIME NULL,
last_end_time DATETIME NULL,
operation_count INT NOT NULL DEFAULT 0,
scheduled_qty DECIMAL(18,6) NULL,
completed_op_qty DECIMAL(18,6) NULL,
schedule_cycle_days DECIMAL(18,6) NULL,
schedule_satisfaction_flag TINYINT NOT NULL DEFAULT 0,
wip_qty DECIMAL(18,6) NULL,
resource_person_count DECIMAL(18,6) NULL,
calc_batch_id VARCHAR(100) NOT NULL,
calc_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_dwd_order_schedule_trans (tenant_id, doc_type, work_order, calc_batch_id),
KEY idx_dwd_order_schedule_trans_batch (calc_batch_id),
KEY idx_dwd_order_schedule_trans_stat (tenant_id, stat_date),
KEY idx_dwd_order_schedule_trans_order (tenant_id, sales_order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S2订单工单排程DWD'
""",
["dwd_requirement_examine_detail"] =
"""
CREATE TABLE IF NOT EXISTS dwd_requirement_examine_detail (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
factory_id BIGINT NULL,
stat_date DATE NOT NULL,
row_id BIGINT NOT NULL,
parent_row_id BIGINT NULL,
examine_id BIGINT NULL,
order_entry_id BIGINT NULL,
bill_no VARCHAR(100) NULL,
morder_no VARCHAR(100) NULL,
num VARCHAR(100) NULL,
item_number VARCHAR(100) NULL,
item_name VARCHAR(200) NULL,
bom_number VARCHAR(100) NULL,
model VARCHAR(200) NULL,
kitting_time DATE NULL,
item_type VARCHAR(50) NULL,
erp_cls_name VARCHAR(80) NULL,
qty DECIMAL(18,4) NULL,
wastage DECIMAL(18,4) NULL,
-- ── 齐套三量的标准名（B3 决定：不另起别名列）──────────────────────────────
-- need_count / use_qty / lack_qty 就是本表「需求量 / 已满足量 / 缺口量」的标准列名，
-- 不新增 required_qty / satisfied_qty / shortage_qty 别名列：
-- 别名列 = 同一个数字有两个写入点，迟早对不上；而重算一套更会凭空造出与源系统不一致的口径。
-- 恒等式 lack_qty + use_qty = need_count 由源系统保证，
-- 实测（aidopdev，只读）1019060/1019060 行全部成立，最大绝对偏差 0.0000，三列均无 NULL。
need_count DECIMAL(18,4) NULL COMMENT '标准列·需求量（齐套恒等式：lack_qty + use_qty = need_count）',
sqty DECIMAL(18,4) NULL,
use_qty DECIMAL(18,4) NULL COMMENT '标准列·已满足量（不要新增 satisfied_qty 别名）',
self_lack_qty DECIMAL(18,4) NULL,
lack_qty DECIMAL(18,4) NULL COMMENT '标准列·缺口量（不要新增 shortage_qty 别名）',
mo_qty DECIMAL(18,4) NULL,
make_qty DECIMAL(18,4) NULL,
purchase_qty DECIMAL(18,4) NULL,
purchase_occupy_qty DECIMAL(18,4) NULL,
satisfy_time DATE NULL,
have_ic_subs VARCHAR(10) NULL,
substitute_code VARCHAR(100) NULL,
create_time DATETIME NULL,
source_system VARCHAR(50) NOT NULL DEFAULT '',
sync_batch_id VARCHAR(100) NOT NULL,
calc_batch_id VARCHAR(100) NOT NULL,
calc_time DATETIME NOT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- ⚠️ 以下三列必须与 UpdateScripts/1.0.516.sql 的 ALTER ... ADD COLUMN 保持「同名同类型同顺序」。
-- 本表没有 SqlSugar 实体、也没有 UpdateScripts 的 CREATE，schema 由「本 DDL + ALTER 脚本」两处共同定义：
-- 新库走本 DDL 一次建全，旧库走 ALTER 追加。ALTER ADD COLUMN 默认追加到表尾，
-- 故这三列在此也必须放在 update_time 之后，否则新库与迁移库的列序会分叉。
bom_level INT NULL COMMENT 'BOM 层级 ← raw_data.$.level；1=成品根，>1=投入料',
material_role VARCHAR(32) NULL COMMENT '物料角色枚举：FINISHED_GOOD_ROOT / INPUT_MATERIAL',
is_current_flag TINYINT NOT NULL DEFAULT 0 COMMENT '1=属于当前快照批次；同一 (tenant, factory) 作用域内恒只有一个批次为 1',
UNIQUE KEY uk_dwd_req_exam_detail (tenant_id, row_id, calc_batch_id),
KEY idx_req_exam_tenant_batch (tenant_id, calc_batch_id),
KEY idx_req_exam_bill (tenant_id, bill_no),
KEY idx_req_exam_item (tenant_id, item_number),
KEY idx_req_exam_current (tenant_id, is_current_flag, morder_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S1需求明细核验DWD'
""",
["dwd_s4_purchase_execution"] =
"""
CREATE TABLE IF NOT EXISTS dwd_s4_purchase_execution (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL, factory_id BIGINT NOT NULL DEFAULT 1, stat_date DATE NOT NULL,
po_no VARCHAR(50) NULL, po_line VARCHAR(50) NULL,
supplier_code VARCHAR(50) NULL, item_code VARCHAR(50) NULL,
order_qty DECIMAL(12,3) NULL DEFAULT 0, delivery_qty DECIMAL(12,3) NULL DEFAULT 0,
received_qty DECIMAL(12,3) NULL DEFAULT 0, returned_qty DECIMAL(12,3) NULL DEFAULT 0,
shortage_qty DECIMAL(12,3) NULL DEFAULT 0,
due_date DATE NULL, actual_arrival_date DATE NULL, risk_level VARCHAR(20) NULL,
source_system VARCHAR(20) NULL DEFAULT '',
sync_batch_id VARCHAR(100) NULL, sync_time DATETIME NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_dwd_s4_pe (tenant_id, stat_date, po_no, po_line, item_code),
KEY idx_dwd_s4_pe_date (tenant_id, stat_date),
KEY idx_dwd_s4_pe_supplier (tenant_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
""",
};
/// 按表名取 CREATE TABLE IF NOT EXISTS（或 LIKE）原文。
public static string Ddl(string tableName)
{
if (string.IsNullOrWhiteSpace(tableName))
throw new ArgumentException("表名不能为空", nameof(tableName));
if (!Tables.TryGetValue(tableName.Trim(), out var ddl))
throw new InvalidOperationException($"未登记中台表 DDL：{tableName}");
return ddl;
}
/// 已登记的中立表名（测试与清单用）。
public static IReadOnlyCollection TableNames => Tables.Keys;
}