-- =============================================
|
-- 采购退货增强：BPM 审批 + 附件 + 退款/换货 + 财务冲红
|
-- 目标库：mom-ntsh（MySQL 8.4）
|
-- 说明：
|
-- 1. 脚本可重复执行：列用 information_schema 预检查 + PREPARE，字典/菜单用先查后插
|
-- 2. 不包含 tenant_id（本项目不使用多租户）
|
-- 3. 人工前置：需在 BPM 后台创建并发布流程模型 purchase_return_vendor_approve
|
-- （节点：质量部发起 -> 采购部审批 -> 质量部审批 -> 仓库审批），否则提交审批时会提示未发布
|
-- =============================================
|
|
-- ---------------------------------------------
|
-- 一、mes_wm_return_vendor 审批与费用字段
|
-- ---------------------------------------------
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'process_instance_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN process_instance_id VARCHAR(64) NULL COMMENT ''BPM流程实例ID'' AFTER status',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'process_definition_key');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN process_definition_key VARCHAR(64) NULL COMMENT ''BPM流程定义Key'' AFTER process_instance_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'contract_no');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN contract_no VARCHAR(64) NULL COMMENT ''采购合同号（创建时从采购订单快照）'' AFTER purchase_order_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'handle_type');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN handle_type TINYINT NULL COMMENT ''处理方式：1退款 2换货'' AFTER remark',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'refund_amount');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN refund_amount DECIMAL(24,6) NULL COMMENT ''退款金额（默认按采购单价×退货数量计算，可修改）'' AFTER handle_type',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'red_flush_payment_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN red_flush_payment_id BIGINT NULL COMMENT ''负数冲红付款单ID'' AFTER refund_amount',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_vendor' AND column_name = 'replacement_notice_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE mes_wm_return_vendor ADD COLUMN replacement_notice_id BIGINT NULL COMMENT ''换货到货通知单ID'' AFTER red_flush_payment_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------
|
-- 二、erp_finance_payment 冲红来源字段
|
-- ---------------------------------------------
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_finance_payment' AND column_name = 'source_type');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_finance_payment ADD COLUMN source_type TINYINT NOT NULL DEFAULT 1 COMMENT ''来源类型：1普通付款 2采购退货冲红'' AFTER remark',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_finance_payment' AND column_name = 'source_id');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_finance_payment ADD COLUMN source_id BIGINT NULL COMMENT ''来源单据ID（采购退货单）'' AFTER source_type',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_finance_payment' AND column_name = 'source_no');
|
SET @sql = IF(@exists = 0,
|
'ALTER TABLE erp_finance_payment ADD COLUMN source_no VARCHAR(64) NULL COMMENT ''来源单号'' AFTER source_id',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- invoice_id 需允许为空（冲红单不来票）；仅当当前为 NOT NULL 时才 MODIFY
|
SET @nullable = (SELECT is_nullable FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'erp_finance_payment' AND column_name = 'invoice_id');
|
SET @sql = IF(@nullable = 'NO',
|
'ALTER TABLE erp_finance_payment MODIFY COLUMN invoice_id BIGINT NULL COMMENT ''关联的来票编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------
|
-- 三、字典：退货处理方式；退货状态补「审批中」
|
-- ---------------------------------------------
|
|
INSERT INTO system_dict_type (name, type, status, remark)
|
SELECT 'MES 供应商退货处理方式', 'mes_wm_return_vendor_handle_type', 0, '采购退货-处理方式：1退款 2换货'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_dict_type WHERE type = 'mes_wm_return_vendor_handle_type' AND deleted = 0);
|
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 0, '退款', '1', 'mes_wm_return_vendor_handle_type', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_wm_return_vendor_handle_type' AND value = '1' AND deleted = 0);
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 1, '换货', '2', 'mes_wm_return_vendor_handle_type', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_wm_return_vendor_handle_type' AND value = '2' AND deleted = 0);
|
|
-- 退货状态新增「审批中」（状态值 1，复用 CONFIRMED；历史 0/2/3/4/5 完全不动）
|
INSERT INTO system_dict_data (sort, label, value, dict_type, status, color_type, css_class)
|
SELECT 1, '审批中', '1', 'mes_wm_return_vendor_status', 0, '', ''
|
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'mes_wm_return_vendor_status' AND value = '1' AND deleted = 0);
|
|
-- ---------------------------------------------
|
-- 四、菜单：采购退货新增「提交审批」按钮
|
-- ---------------------------------------------
|
|
INSERT INTO system_menu (id, name, permission, type, sort, parent_id, path, icon, component, status, visible, keep_alive, always_show)
|
SELECT 5837, '供应商退货提交审批', 'mes:wm-return-vendor:submit-approval', 3, 7, 5830, '', '', '', 0, 1, 1, 1
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM system_menu WHERE id = 5837);
|
