-- ============================================================ -- 销售订单 -> 生产主计划(MPS)链路数据检查与修复 -- -- 适用场景:销售订单已经审核,但勾选“需要生产”的明细没有生成 MPS。 -- 代码发布后,新审核单据会在 MPS 创建失败时整体回滚并返回明确错误。 -- ============================================================ -- 1. 检查:需要生产的销售明细,是否已经同步为 MES 产品。 -- mes_item_id 为空表示没有 MES 记录;item_or_product 不是 PRODUCT 表示不能用于 MPS。 SELECT so.id AS sale_order_id, so.no AS sale_order_no, soi.id AS sale_order_item_id, mdm.id AS mdm_item_id, mdm.code AS item_code, mdm.name AS item_name, mi.id AS mes_item_id, mi.mdm_item_id AS mes_mdm_item_id, mit.item_or_product AS item_or_product, mi.status AS mes_item_status FROM erp_sale_order so JOIN erp_sale_order_items soi ON soi.order_id = so.id AND soi.deleted = b'0' JOIN mdm_item mdm ON mdm.id = soi.product_id AND mdm.deleted = b'0' LEFT JOIN mes_md_item mi ON mi.mdm_item_id = mdm.id AND mi.deleted = b'0' LEFT JOIN mes_md_item_type mit ON mit.id = mi.item_type_id AND mit.deleted = b'0' WHERE so.deleted = b'0' AND soi.need_production = 1 AND (mi.id IS NULL OR COALESCE(mi.status, 1) <> 0 OR COALESCE(mit.item_or_product, '') <> 'PRODUCT') ORDER BY so.id, soi.id; -- 2. 修复:历史 MES 物料按编码已存在、但 mdm_item_id 为空时,补齐 MDM 关联。 -- mdm_item.code 是全局唯一编码;仅更新两边都未删除、关联为空且编码相同的数据。 UPDATE mes_md_item mi JOIN mdm_item mdm ON mdm.code = mi.code AND mdm.deleted = b'0' LEFT JOIN mes_md_item linked ON linked.mdm_item_id = mdm.id AND linked.deleted = b'0' SET mi.mdm_item_id = mdm.id, mi.updater = '1', mi.update_time = NOW() WHERE mi.deleted = b'0' AND mi.mdm_item_id IS NULL AND linked.id IS NULL; -- 3. 修复:MPS 已存在但销售明细的 mps_id 未回填时,补齐关联。 -- 已取消 MPS(status=3)不回填。 UPDATE erp_sale_order_items soi JOIN ( SELECT sale_order_item_id, MAX(id) AS mps_id FROM mes_pro_mps WHERE deleted = b'0' AND status <> 3 AND sale_order_item_id IS NOT NULL GROUP BY sale_order_item_id ) mps ON mps.sale_order_item_id = soi.id SET soi.mps_id = mps.mps_id, soi.updater = '1', soi.update_time = NOW() WHERE soi.deleted = b'0' AND (soi.mps_id IS NULL OR soi.mps_id <> mps.mps_id); -- 4. 检查:已经审核、需要生产,但仍没有有效 MPS 的历史订单。 SELECT so.id AS sale_order_id, so.no AS sale_order_no, soi.id AS sale_order_item_id, mdm.code AS item_code, mdm.name AS item_name, soi.count AS plan_quantity FROM erp_sale_order so JOIN erp_sale_order_items soi ON soi.order_id = so.id AND soi.deleted = b'0' JOIN mdm_item mdm ON mdm.id = soi.product_id AND mdm.deleted = b'0' LEFT JOIN mes_pro_mps mps ON mps.sale_order_item_id = soi.id AND mps.deleted = b'0' AND mps.status <> 3 WHERE so.deleted = b'0' AND so.status = 20 AND soi.need_production = 1 AND mps.id IS NULL ORDER BY so.id, soi.id; -- 5. 历史单据重试(按单执行):先确认第 1、4 步检查结果已经正常,再填写订单 ID。 -- 将该订单退回“待审核(10)”,然后在页面重新点击“审批”;新代码会自动生成 MPS。 SET @sale_order_id = 0; -- 改为需要重试的销售订单 ID;0 表示不执行任何更新 UPDATE erp_sale_order so SET so.status = 10, so.updater = '1', so.update_time = NOW() WHERE so.id = @sale_order_id AND @sale_order_id > 0 AND so.deleted = b'0' AND so.status = 20 AND COALESCE(so.out_count, 0) = 0 AND EXISTS ( SELECT 1 FROM erp_sale_order_items soi LEFT JOIN mes_pro_mps mps ON mps.sale_order_item_id = soi.id AND mps.deleted = b'0' AND mps.status <> 3 WHERE soi.order_id = so.id AND soi.deleted = b'0' AND soi.need_production = 1 AND mps.id IS NULL );