-- ============================================
|
-- 供应商统一迁移至 SRM
|
-- 将 ERP/MES 表的 supplier_id/vendor_id 从 erp_supplier.id 迁移为 srm_supplier.id
|
-- 删除 srm_supplier.erp_supplier_id 桥接字段
|
-- 执行前请备份数据库！
|
-- ============================================
|
|
-- 1. 迁移 ERP 表 supplier_id (erp_supplier.id → srm_supplier.id)
|
UPDATE erp_purchase_request r
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.supplier_id
|
SET r.supplier_id = s.id;
|
|
UPDATE erp_purchase_order o
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = o.supplier_id
|
SET o.supplier_id = s.id;
|
|
UPDATE erp_purchase_in i
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = i.supplier_id
|
SET i.supplier_id = s.id;
|
|
UPDATE erp_purchase_return r
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.supplier_id
|
SET r.supplier_id = s.id;
|
|
UPDATE erp_finance_payment p
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = p.supplier_id
|
SET p.supplier_id = s.id;
|
|
UPDATE erp_stock_in si
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = si.supplier_id
|
SET si.supplier_id = s.id;
|
|
-- 2. 迁移 MES 表 vendor_id (erp_supplier.id → srm_supplier.id)
|
UPDATE mes_wm_arrival_notice a
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = a.vendor_id
|
SET a.vendor_id = s.id;
|
|
UPDATE mes_wm_item_receipt r
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.vendor_id
|
SET r.vendor_id = s.id;
|
|
UPDATE mes_wm_outsource_receipt r
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.vendor_id
|
SET r.vendor_id = s.id;
|
|
UPDATE mes_wm_outsource_issue i
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = i.vendor_id
|
SET i.vendor_id = s.id;
|
|
UPDATE mes_wm_return_vendor r
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = r.vendor_id
|
SET r.vendor_id = s.id;
|
|
UPDATE mes_qc_iqc q
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = q.vendor_id
|
SET q.vendor_id = s.id;
|
|
UPDATE mes_pro_work_order w
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = w.vendor_id
|
SET w.vendor_id = s.id;
|
|
UPDATE mes_wm_material_stock ms
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = ms.vendor_id
|
SET ms.vendor_id = s.id;
|
|
UPDATE mes_wm_batch b
|
INNER JOIN srm_supplier s ON s.erp_supplier_id = b.vendor_id
|
SET b.vendor_id = s.id;
|
|
-- 3. 删除 srm_supplier 的 erp_supplier_id 桥接字段
|
ALTER TABLE srm_supplier DROP INDEX idx_erp_supplier_id;
|
ALTER TABLE srm_supplier DROP COLUMN erp_supplier_id;
|
|
-- 4. srm_supplier_apply 如果存在 erp_supplier_id 则删除
|
ALTER TABLE srm_supplier_apply DROP COLUMN IF EXISTS erp_supplier_id;
|
