如何通过代码同步SAP Business One销售订单与BP主数据地址？

已有Visual Studio代码可更新SAP Business Partner（BP）主数据地址，但修改BP地址后，已创建的未结销售订单地址不会自动同步更新，仅新订单会使用新地址。需要实现：修改BP主数据地址时，自动更新该BP对应的所有已创建未结销售订单的地址。

当前SQL查询尝试关联BP与销售订单，但存在逻辑问题，无法正确筛选出需要更新的订单。

首先需要正确关联BP主数据（OCRD+CRD1）和销售订单（ORDR+RDR12），找出地址不一致的未结订单：

SELECT 
    h.DocEntry AS OrderDocEntry,
    h.DocNum AS OrderNumber,
    h.CardCode AS BP_Code,
    h.CardName AS BP_Name,
    -- 订单地址字段
    ao.StreetS AS Order_Street,
    ao.ZipCodeS AS Order_ZipCode,
    ao.CityS AS Order_City,
    ao.CountryS AS Order_Country,
    ao.StateS AS Order_State,
    ao.BlockS AS Order_Block,
    -- BP主数据地址字段
    ac.Street AS BP_Street,
    ac.ZipCode AS BP_ZipCode,
    ac.City AS BP_City,
    ac.Country AS BP_Country,
    ac.State AS BP_State,
    ac.Block AS BP_Block
FROM 
    dbo.ORDR AS h
INNER JOIN 
    dbo.RDR12 AS ao ON h.DocEntry = ao.DocEntry
INNER JOIN 
    dbo.OCRD AS c ON h.CardCode = c.CardCode
INNER JOIN 
    dbo.CRD1 AS ac ON c.CardCode = ac.CardCode 
WHERE 
    h.DocStatus = 'O' -- 仅筛选未结订单
    AND (
        ao.StreetS <> ac.Street 
        OR ao.ZipCodeS <> ac.ZipCode 
        OR ao.CityS <> ac.City 
        OR ao.CountryS <> ac.Country 
        OR ao.StateS <> ac.State 
        OR ao.BlockS <> ac.Block
    )

基于上述查询，编写UPDATE语句将BP主数据的地址同步到对应的销售订单：

UPDATE 
    dbo.RDR12
SET 
    StreetS = ac.Street,
    ZipCodeS = ac.ZipCode,
    CityS = ac.City,
    CountryS = ac.Country,
    StateS = ac.State,
    BlockS = ac.Block
FROM 
    dbo.RDR12 AS ao
INNER JOIN 
    dbo.ORDR AS h ON ao.DocEntry = h.DocEntry
INNER JOIN 
    dbo.OCRD AS c ON h.CardCode = c.CardCode
INNER JOIN 
    dbo.CRD1 AS ac ON c.CardCode = ac.CardCode
WHERE 
    h.DocStatus = 'O'
    AND (
        ao.StreetS <> ac.Street 
        OR ao.ZipCodeS <> ac.ZipCode 
        OR ao.CityS <> ac.City 
        OR ao.CountryS <> ac.Country 
        OR ao.StateS <> ac.State 
        OR ao.BlockS <> ac.Block
    )

在你的Visual Studio代码中，修改BP主数据地址的逻辑执行完成后，立即执行上述UPDATE语句：

1. 加入CardCode过滤，只更新当前修改的BP对应的订单，避免全表扫描提升效率：

   WHERE 
       h.DocStatus = 'O'
       AND h.CardCode = 'TARGET_BP_CODE' -- 替换为实际修改的BP编码
       AND (
           ao.StreetS <> ac.Street 
           OR ao.ZipCodeS <> ac.ZipCode 
           OR ao.CityS <> ac.City 
           OR ao.CountryS <> ac.Country 
           OR ao.StateS <> ac.State 
           OR ao.BlockS <> ac.Block
       )
   

2. 开启事务处理：将修改BP地址和更新订单地址的操作放在同一个事务中，确保数据一致性。

• SAP Business One的销售订单地址是创建时快照存储的，默认不会自动同步BP主数据的地址变更，必须通过手动或代码批量更新。
• RDR12表存储销售订单的送货/开票地址，CRD1表存储BP的地址信息，两者通过CardCode关联，订单表头ORDR通过DocEntry关联RDR12。

内容的提问来源于stack exchange，提问作者user12422846