-- ============================================= -- 到货通知单补充采购合同号 -- 背景:采购退货选择「换货」后,系统重新生成到货通知单并重新质检, -- 需要在到货通知单上重新关联原采购合同号,便于采购/财务追溯。 -- 目标库:mom-ntsh(MySQL 8.4) -- 说明:脚本可重复执行;不包含 tenant_id(本项目不使用多租户) -- ============================================= SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_arrival_notice' AND column_name = 'contract_no'); SET @sql = IF(@exists = 0, 'ALTER TABLE mes_wm_arrival_notice ADD COLUMN contract_no VARCHAR(64) NULL COMMENT ''采购合同号(创建时从采购订单快照,换货到货沿用退货单合同号)'' AFTER purchase_order_code', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 历史数据回填:从采购订单补合同号 UPDATE mes_wm_arrival_notice n JOIN erp_purchase_order o ON o.id = n.purchase_order_id AND o.deleted = 0 SET n.contract_no = o.contract_no WHERE (n.contract_no IS NULL OR n.contract_no = '') AND n.purchase_order_id IS NOT NULL;