Intercompany order sync creates duplicate records when integrating with legacy ERP via REST API

ERP Microsoft Dynamics 365
, , , , , , , ,
1

We’re running a phased ERP migration where D365 needs to sync intercompany orders with our legacy SAP system via REST API integration. The integration creates duplicate intercompany sales orders in D365 about 40% of the time.

Here’s the flow: Legacy ERP sends purchase order to D365 via REST API, D365 should create corresponding intercompany sales order. But we’re seeing duplicate sales orders with identical order numbers, sometimes created minutes apart. This breaks our order reconciliation process between companies.

The REST API endpoint receives the order payload from legacy ERP, and our custom X++ service processes it. We’ve added logging and can confirm the API is only called once per order from the legacy system, yet duplicates still appear. Has anyone dealt with duplicate order creation in intercompany scenarios during legacy ERP integration?

2

This is a classic idempotency issue. REST APIs should be idempotent, meaning calling the same endpoint with the same payload multiple times produces the same result, not duplicates. Your X++ service needs to check if an order with that external reference already exists before creating a new one. Add a unique constraint on the legacy order ID field.

3

Don’t forget about D365’s change tracking and duplicate detection rules. Intercompany orders have complex relationships between legal entities. If your integration doesn’t properly set the OriginalOrderId and IntercompanyRelationType fields, D365’s duplicate detection won’t work. The system might legitimately think these are different orders if key identifying fields differ even slightly.

4

Good point about response times. Our API responses average 15-20 seconds for order creation. But I just discovered something - the duplicate orders have different timestamps but identical data. Could this be a concurrency issue in our X++ code? Maybe multiple threads processing the same order?

5

I’ve implemented this exact scenario - D365 intercompany integration with legacy SAP via REST API. Here’s the complete solution addressing all three focus areas:

Root Cause - Multi-Factor Issue: Your duplicate orders stem from three interconnected problems: legacy ERP retry logic, missing idempotency in your REST API implementation, and race conditions in intercompany order processing.

1. Legacy ERP Integration Patterns:

SAP and other legacy systems typically implement retry mechanisms that D365 integrations must handle gracefully:

  • SAP sends orders via RFC/HTTP with 30-second default timeout
  • If D365 doesn’t respond within timeout, SAP marks the call as failed and retries
  • Your D365 logs show one call because SAP’s retry appears as a new request with identical payload
  • The first request might still be processing when the retry arrives

Solution for Legacy System:

  • Implement message queue pattern: Have legacy ERP send to Azure Service Bus first
  • D365 pulls from queue, processes, and acknowledges (prevents retry)
  • Add correlation ID from legacy system to track message lifecycle
  • Configure SAP timeout to 60+ seconds to account for D365 processing time

2. REST API Idempotency Implementation:

Your custom X++ service must implement proper idempotency checks:

// Add idempotency check in your X++ service:
public SalesOrder createIntercompanyOrder(OrderPayload _payload)
{
    // Check for existing order using legacy reference
    SalesTable existingOrder = SalesTable::findByExternalRef(_payload.legacyOrderId);

    if (existingOrder.RecId != 0)
        return existingOrder; // Return existing, don't create duplicate
}

Key API Implementation Points:

  • Add ExternalOrderReference field to SalesTable (if not exists)
  • Create unique index on ExternalOrderReference + DataAreaId
  • Always check this field before creating new intercompany orders
  • Return HTTP 200 with existing order details if duplicate detected (not 409 Conflict)
  • Use ttsbegin/ttscommit properly to ensure atomic operations

3. Duplicate Order Prevention in Intercompany Flow:

Intercompany orders in D365 have specific mechanisms to prevent duplicates:

Implement Pessimistic Locking:

ttsbegin;
select forupdate salesTable
    where salesTable.ExternalOrderRef == _payload.legacyOrderId;

if (!salesTable.RecId)
{
    // Safe to create - we have exclusive lock
    salesTable.clear();
    salesTable.initValue();
    // ... create order
}
ttscommit;

Set Proper Intercompany Fields:

  • IntercompanyOriginalSalesId: Legacy system’s order ID
  • IntercompanyChain: Link to purchase order in buying legal entity
  • IntercompanyAutoCreateOrders: Set to ‘No’ to prevent cascading duplicates
  • ExternalItemNumber: Map legacy item codes correctly

Complete Solution Architecture:

  1. Message Deduplication Layer:

    • Add Azure API Management in front of your D365 REST endpoint
    • Configure caching policy with 60-second window using legacy order ID as key
    • Returns cached response for duplicate requests within window

  2. Database-Level Protection:

    • Create unique index: `CREATE UNIQUE INDEX idx_ExtOrderRef ON SalesTable(ExternalOrderReference, DataAreaId)
    • This prevents duplicates even if application logic fails

  3. Enhanced Logging:

    • Log correlation ID from legacy system in every API call
    • Track: Request received → Duplicate check → Order creation → Response sent
    • Monitor for requests with same correlation ID arriving multiple times

  4. Order Reconciliation Fix:

    • Build reconciliation report comparing D365 intercompany orders to legacy system
    • Use ExternalOrderReference as matching key
    • Flag any D365 orders without legacy reference (manual cleanup needed)
    • Schedule daily reconciliation batch job to detect discrepancies early

Testing the Solution:

  1. Send same order payload twice within 10 seconds - should return same order
  2. Simulate SAP timeout by delaying D365 response - verify retry handling
  3. Test concurrent API calls with identical payload - verify locking prevents duplicates
  4. Validate intercompany chain creation doesn’t trigger duplicate purchase orders

After implementing these changes, your duplicate rate should drop to zero. The key is defense in depth: prevent at API layer, database layer, and application logic layer.

6

I’ve seen this with SAP integrations specifically. SAP’s IDoc mechanism can send duplicate messages if it doesn’t receive acknowledgment quickly enough. Even if your logs show one API call, the legacy system might be retrying at the transport layer. Check your API response times - if they’re over 30 seconds, SAP might timeout and retry while the first request is still processing in D365.

7

Yes, concurrency is likely your problem. If your custom X++ service doesn’t implement proper locking, two simultaneous API calls can both check for existing orders, find none, and both create new records. You need to use pessimistic locking on the intercompany order table. Wrap your order creation logic in a transaction with SELECT FORUPDATE to prevent race conditions. Also check if your batch framework is processing the same order multiple times.