From fc1288355802c1b8e69fe08f544df675fb807572 Mon Sep 17 00:00:00 2001
From: liu <2021943741@qq.com>
Date: 星期六, 24 十月 2026 20:31:20 +0800
Subject: [PATCH] fix(erp): 修复销售订单审批未生成MPS

---
 yudao-module-erp/src/main/java/cn/iocoder/yudao/module/erp/service/sale/ErpSaleOrderServiceImpl.java |   64 ++++++++------------
 yudao-module-mes/src/main/java/cn/iocoder/yudao/module/mes/dal/mysql/pro/mps/MesProMpsMapper.java    |   10 ++
 sql/mysql/fix_sale_order_mps_link.sql                                                                |  103 ++++++++++++++++++++++++++++++++++
 3 files changed, 137 insertions(+), 40 deletions(-)

diff --git a/sql/mysql/fix_sale_order_mps_link.sql b/sql/mysql/fix_sale_order_mps_link.sql
new file mode 100644
index 0000000..77c6e6d
--- /dev/null
+++ b/sql/mysql/fix_sale_order_mps_link.sql
@@ -0,0 +1,103 @@
+-- ============================================================
+-- 閿�鍞鍗� -> 鐢熶骇涓昏鍒掞紙MPS锛夐摼璺暟鎹鏌ヤ笌淇
+--
+-- 閫傜敤鍦烘櫙锛氶攢鍞鍗曞凡缁忓鏍革紝浣嗗嬀閫夆�滈渶瑕佺敓浜р�濈殑鏄庣粏娌℃湁鐢熸垚 MPS銆�
+-- 浠g爜鍙戝竷鍚庯紝鏂板鏍稿崟鎹細鍦� MPS 鍒涘缓澶辫触鏃舵暣浣撳洖婊氬苟杩斿洖鏄庣‘閿欒銆�
+-- ============================================================
+
+-- 1. 妫�鏌ワ細闇�瑕佺敓浜х殑閿�鍞槑缁嗭紝鏄惁宸茬粡鍚屾涓� MES 浜у搧銆�
+-- mes_item_id 涓虹┖琛ㄧず娌℃湁 MES 璁板綍锛沬tem_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. 淇锛歁PS 宸插瓨鍦ㄤ絾閿�鍞槑缁嗙殑 mps_id 鏈洖濉椂锛岃ˉ榻愬叧鑱斻��
+-- 宸插彇娑� MPS锛坰tatus=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
+  );
diff --git a/yudao-module-erp/src/main/java/cn/iocoder/yudao/module/erp/service/sale/ErpSaleOrderServiceImpl.java b/yudao-module-erp/src/main/java/cn/iocoder/yudao/module/erp/service/sale/ErpSaleOrderServiceImpl.java
index 608d16a..a0f4ca6 100644
--- a/yudao-module-erp/src/main/java/cn/iocoder/yudao/module/erp/service/sale/ErpSaleOrderServiceImpl.java
+++ b/yudao-module-erp/src/main/java/cn/iocoder/yudao/module/erp/service/sale/ErpSaleOrderServiceImpl.java
@@ -31,17 +31,14 @@
 import cn.iocoder.yudao.module.mdm.api.unit.dto.MdmUnitMeasureRespDTO;
 import cn.iocoder.yudao.module.mes.api.mps.MesProMpsApi;
 import cn.iocoder.yudao.module.mes.api.mps.dto.MesProMpsCreateReqDTO;
+import cn.iocoder.yudao.module.mes.api.mps.dto.MesProMpsRespDTO;
 import cn.iocoder.yudao.module.system.api.storage.StorageAttachmentApi;
 import cn.iocoder.yudao.module.system.api.user.AdminUserApi;
 import cn.iocoder.yudao.module.system.api.user.dto.AdminUserRespDTO;
-import jakarta.annotation.PostConstruct;
 import jakarta.annotation.Resource;
 import lombok.extern.slf4j.Slf4j;
 import org.springframework.stereotype.Service;
-import org.springframework.transaction.PlatformTransactionManager;
-import org.springframework.transaction.TransactionDefinition;
 import org.springframework.transaction.annotation.Transactional;
-import org.springframework.transaction.support.TransactionTemplate;
 import org.springframework.validation.annotation.Validated;
 
 import java.math.BigDecimal;
@@ -67,6 +64,8 @@
 @Validated
 @Slf4j
 public class ErpSaleOrderServiceImpl implements ErpSaleOrderService {
+
+    private static final Integer MPS_STATUS_CANCELED = 3;
 
     @Resource
     private ErpSaleOrderMapper saleOrderMapper;
@@ -94,22 +93,6 @@
     private ErpAccountMapper accountMapper;
     @Resource
     private MesProMpsApi mpsApi;
-
-    @Resource
-    private PlatformTransactionManager transactionManager;
-
-    /**
-     * MPS 鍒涘缓浣跨敤鐙珛浜嬪姟锛圧EQUIRES_NEW锛夛細
-     * 瀹℃牳鏃惰嚜鍔ㄥ垱寤� MPS 灞炰簬灏藉姏鑰屼负鐨勮仈鍔紝澶辫触浠呰褰曟棩蹇楀苟鍥炴粴鑷韩锛�
-     * 涓嶈兘姹℃煋閿�鍞鍗曞鏍镐簨鍔★紙鍚﹀垯鍐呭眰寮傚父浼氭妸澶栧眰浜嬪姟鏍囪涓� rollback-only锛屽鑷存暣涓鏍歌鍥炴粴锛�
-     */
-    private TransactionTemplate mpsTransactionTemplate;
-
-    @PostConstruct
-    public void initMpsTransactionTemplate() {
-        mpsTransactionTemplate = new TransactionTemplate(transactionManager);
-        mpsTransactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
-    }
 
     @Resource
     private StorageAttachmentApi storageAttachmentApi;
@@ -237,31 +220,36 @@
             return;
         }
 
-        int mpsCount = 0;
+        int createdCount = 0;
+        int reusedCount = 0;
         for (ErpSaleOrderItemDO item : items) {
             if (item.getNeedProduction() == null || item.getNeedProduction() != 1) {
                 continue;
             }
-            try {
-                // 鐙珛浜嬪姟鍒涘缓 MPS锛氬け璐ュ彧鍥炴粴 MPS 鑷韩锛屼笉褰卞搷瀹℃牳浜嬪姟
-                Long mpsId = mpsTransactionTemplate.execute(txStatus -> {
-                    Long newMpsId = createMpsForSaleOrderItem(saleOrder, item);
-                    if (newMpsId != null) {
-                        // 鏇存柊閿�鍞鍗曟槑缁嗙殑 MPS 鍏宠仈
-                        saleOrderItemMapper.updateById(new ErpSaleOrderItemDO()
-                                .setId(item.getId())
-                                .setMpsId(newMpsId));
-                    }
-                    return newMpsId;
-                });
-                if (mpsId != null) {
-                    mpsCount++;
+
+            // 鍚屼竴閿�鍞槑缁嗗彧淇濈暀涓�鏉℃湁鏁� MPS锛岄伩鍏嶅弽瀹℃牳鍚庡啀娆″鏍镐骇鐢熼噸澶嶈鍒�
+            MesProMpsRespDTO existingMps = mpsApi.getMpsBySaleOrderItemId(item.getId());
+            if (existingMps != null && ObjectUtil.notEqual(existingMps.getStatus(), MPS_STATUS_CANCELED)) {
+                if (ObjectUtil.notEqual(item.getMpsId(), existingMps.getId())) {
+                    saleOrderItemMapper.updateById(new ErpSaleOrderItemDO()
+                            .setId(item.getId())
+                            .setMpsId(existingMps.getId()));
                 }
-            } catch (Exception e) {
-                log.error("[createMpsForSaleOrder] 鍒涘缓 MPS 澶辫触锛宻aleOrderItemId={}", item.getId(), e);
+                reusedCount++;
+                log.info("[createMpsForSaleOrder] 閿�鍞鍗曟槑缁�({}) 宸插瓨鍦ㄦ湁鏁� MPS({})锛岃烦杩囬噸澶嶅垱寤�",
+                        item.getId(), existingMps.getId());
+                continue;
             }
+
+            // 涓庨攢鍞鍗曞鏍稿叡鐢ㄤ簨鍔★紱浠讳竴鏄庣粏鍒涘缓澶辫触閮戒細鍥炴粴瀹℃牳鐘舵�佸拰鏈宸插垱寤虹殑 MPS
+            Long mpsId = createMpsForSaleOrderItem(saleOrder, item);
+            saleOrderItemMapper.updateById(new ErpSaleOrderItemDO()
+                    .setId(item.getId())
+                    .setMpsId(mpsId));
+            createdCount++;
         }
-        log.info("[createMpsForSaleOrder] 閿�鍞鍗�({}) 瀹℃牳瀹屾垚锛屽垱寤� MPS {} 鏉�", orderId, mpsCount);
+        log.info("[createMpsForSaleOrder] 閿�鍞鍗�({}) 瀹℃牳瀹屾垚锛屽垱寤� MPS {} 鏉★紝澶嶇敤 MPS {} 鏉�",
+                orderId, createdCount, reusedCount);
     }
 
     /**
diff --git a/yudao-module-mes/src/main/java/cn/iocoder/yudao/module/mes/dal/mysql/pro/mps/MesProMpsMapper.java b/yudao-module-mes/src/main/java/cn/iocoder/yudao/module/mes/dal/mysql/pro/mps/MesProMpsMapper.java
index 6225ad7..bbb7b79 100644
--- a/yudao-module-mes/src/main/java/cn/iocoder/yudao/module/mes/dal/mysql/pro/mps/MesProMpsMapper.java
+++ b/yudao-module-mes/src/main/java/cn/iocoder/yudao/module/mes/dal/mysql/pro/mps/MesProMpsMapper.java
@@ -64,14 +64,20 @@
     }
 
     default MesProMpsDO selectBySaleOrderItemId(Long saleOrderItemId) {
-        return selectOne(MesProMpsDO::getSaleOrderItemId, saleOrderItemId);
+        return selectOne(new LambdaQueryWrapperX<MesProMpsDO>()
+                .eq(MesProMpsDO::getSaleOrderItemId, saleOrderItemId)
+                .orderByDesc(MesProMpsDO::getId)
+                .last("LIMIT 1"));
     }
 
     default List<MesProMpsDO> selectListBySaleOrderItemIds(Collection<Long> saleOrderItemIds) {
         if (saleOrderItemIds == null || saleOrderItemIds.isEmpty()) {
             return Collections.emptyList();
         }
-        return selectList(MesProMpsDO::getSaleOrderItemId, saleOrderItemIds);
+        // 鍚屼竴閿�鍞槑缁嗗彲鑳藉瓨鍦ㄢ�滄棫璁″垝宸插彇娑堛�佹柊璁″垝閲嶆柊鍒涘缓鈥濈殑鍘嗗彶璁板綍锛涙寜 ID 鍗囧簭锛岃皟鐢ㄦ柟鍙栨渶鍚庝竴鏉″嵆鏈�鏂拌鍒�
+        return selectList(new LambdaQueryWrapperX<MesProMpsDO>()
+                .in(MesProMpsDO::getSaleOrderItemId, saleOrderItemIds)
+                .orderByAsc(MesProMpsDO::getId));
     }
 
     /**

--
Gitblit v1.9.3