-- ============================================= -- 采购申请去价格 + 询价比价 / 采购台账衔接改造 -- 目标库:mom-ntsh(MySQL 8.4) -- 说明: -- 1. 采购申请(表头 + 明细)不再保存任何价格字段,价格在询价或采购台账阶段确定 -- 2. 采购申请审批通过后,由采购员决定「发起询价」或「直接生成采购台账」 -- 3. 脚本可重复执行:删列/加列均先用 information_schema 预检查 -- 4. 不包含 tenant_id(本项目不使用多租户) -- ============================================= -- --------------------------------------------- -- 一、删除采购申请表头价格字段 -- --------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_request' AND column_name = 'total_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request DROP COLUMN total_price', '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_purchase_request' AND column_name = 'total_product_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request DROP COLUMN total_product_price', '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_purchase_request' AND column_name = 'total_tax_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request DROP COLUMN total_tax_price', '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_purchase_request' AND column_name = 'discount_percent'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request DROP COLUMN discount_percent', '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_purchase_request' AND column_name = 'discount_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request DROP COLUMN discount_price', '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_purchase_request' AND column_name = 'deposit_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request DROP COLUMN deposit_price', '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_purchase_request_items' AND column_name = 'product_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request_items DROP COLUMN product_price', '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_purchase_request_items' AND column_name = 'total_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request_items DROP COLUMN total_price', '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_purchase_request_items' AND column_name = 'tax_percent'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request_items DROP COLUMN tax_percent', '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_purchase_request_items' AND column_name = 'tax_price'); SET @sql = IF(@exists > 0, 'ALTER TABLE erp_purchase_request_items DROP COLUMN tax_price', '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_purchase_request' AND column_name = 'inquiry_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE erp_purchase_request ADD COLUMN inquiry_id BIGINT NULL COMMENT ''关联询价单编号'' AFTER order_no', '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_purchase_request' AND column_name = 'inquiry_no'); SET @sql = IF(@exists = 0, 'ALTER TABLE erp_purchase_request ADD COLUMN inquiry_no VARCHAR(64) NULL COMMENT ''关联询价单号'' AFTER inquiry_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 = 'srm_inquiry' AND column_name = 'purchase_request_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE srm_inquiry ADD COLUMN purchase_request_id BIGINT NULL COMMENT ''关联采购申请编号'' 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 = 'srm_inquiry' AND column_name = 'purchase_request_no'); SET @sql = IF(@exists = 0, 'ALTER TABLE srm_inquiry ADD COLUMN purchase_request_no VARCHAR(64) NULL COMMENT ''关联采购申请单号'' AFTER purchase_request_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 = 'erp_purchase_order' AND column_name = 'purchase_request_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE erp_purchase_order ADD COLUMN purchase_request_id BIGINT NULL COMMENT ''来源采购申请编号'' AFTER source_id', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'erp_purchase_order' AND index_name = 'idx_purchase_request_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE erp_purchase_order ADD INDEX idx_purchase_request_id (purchase_request_id)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'srm_inquiry' AND index_name = 'idx_purchase_request_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE srm_inquiry ADD INDEX idx_purchase_request_id (purchase_request_id)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;