Integration Patterns and Middleware

Overview

• What you’ll learn:
  • How to connect iDempiere with external systems using integration architecture patterns including point-to-point, hub-and-spoke, ESB, and event-driven approaches
  • How to implement EDI processing, message queue integration, and RESTful API patterns for reliable system-to-system communication
  • How to handle errors, retries, and idempotency in integration workflows, with a practical e-commerce integration example
• Prerequisites: Lesson 40 — System Architecture Design, familiarity with REST APIs and messaging concepts
• Estimated reading time: 25 minutes

Introduction

No ERP system operates in isolation. In a modern enterprise, iDempiere must exchange data with e-commerce platforms, CRM systems, warehouse management systems, payment gateways, banking interfaces, government tax portals, and countless other applications. The way you design these integrations — the patterns you choose, the error handling you implement, and the data transformations you manage — determines whether your integrations are reliable and maintainable or fragile and unpredictable.

This lesson covers the foundational integration patterns and technologies you will encounter when connecting iDempiere to the outside world. We will move from architectural theory to practical implementation, culminating in a worked example of integrating iDempiere with an e-commerce platform.

Integration Architecture Patterns

Before diving into specific technologies, it is important to understand the architectural patterns that govern how systems communicate. Choosing the right pattern shapes everything that follows.

Point-to-Point Integration

Each system connects directly to every other system it needs to communicate with. System A talks to System B, System B talks to System C, and so on.

• Advantages: Simplest to implement for a small number of connections. No middleware infrastructure required. Low latency (direct communication).
• Disadvantages: The number of connections grows exponentially — N systems require up to N(N-1)/2 connections. Tightly couples systems together. A change in one system’s API requires updates in every connected system. Becomes unmanageable beyond 4-5 integrated systems.
• When to use: When you have only 2-3 systems to integrate and the integration requirements are simple and unlikely to change.

Hub-and-Spoke (Message Broker)

A central hub routes messages between systems. Each system connects only to the hub, not to every other system. The hub handles message routing, transformation, and delivery.

• Advantages: Reduces connection complexity to N connections (one per system). Centralizes message routing and transformation logic. Easier to add new systems — connect them to the hub.
• Disadvantages: The hub is a single point of failure (must be made highly available). All traffic flows through the hub, which can become a bottleneck. Hub-specific knowledge required.
• When to use: Medium-complexity integration landscapes with 5-15 systems. When you need centralized message transformation and routing.

Enterprise Service Bus (ESB)

An evolution of hub-and-spoke, the ESB distributes integration logic across a bus rather than concentrating it in a single hub. It provides routing, transformation, protocol mediation, and orchestration as a shared infrastructure service.

• Advantages: Handles complex integration scenarios (protocol conversion, content-based routing, orchestration). Enterprise-grade reliability and monitoring. Standard tooling for non-developers to manage integrations.
• Disadvantages: Significant infrastructure and licensing cost. Complex to set up and maintain. Can become a monolithic bottleneck if not carefully managed. Often seen as heavyweight for modern architectures.
• When to use: Large enterprises with diverse integration requirements across many systems and protocols. Organizations with dedicated integration teams.

Event-Driven Architecture (EDA)

Systems publish events (notifications of state changes) to a shared event bus or message broker. Other systems subscribe to events they care about and react independently. There is no direct coupling between publisher and subscriber.

• Advantages: Loose coupling — publishers do not know or care who is listening. Naturally supports asynchronous processing. Highly scalable (add subscribers without affecting publishers). Enables real-time data flow.
• Disadvantages: Eventual consistency (subscribers may process events at different rates). Harder to debug (no single request/response trace). Requires robust event infrastructure. Event ordering and deduplication must be handled.
• When to use: Modern, microservices-oriented architectures. When multiple systems need to react to the same business events. When real-time data propagation is important.

EDI Standards and Processing

Electronic Data Interchange (EDI) is the automated exchange of structured business documents between organizations using standardized formats. Despite being decades old, EDI remains the dominant integration method for supply chain operations, especially in retail, manufacturing, and logistics.

EDI Standards

• ANSI X12: The dominant EDI standard in North America. Documents are called Transaction Sets and identified by numbers — 850 (Purchase Order), 810 (Invoice), 856 (Advance Ship Notice), 997 (Functional Acknowledgment).
• UN/EDIFACT: The international standard, widely used in Europe and Asia. Documents are called Messages — ORDERS (Purchase Order), INVOIC (Invoice), DESADV (Dispatch Advice).
• XML-based EDI: Modern alternatives like cXML (commerce XML), UBL (Universal Business Language), and RosettaNet use XML instead of the traditional fixed-format segments. Easier to parse and validate, but less universally adopted in legacy supply chains.

EDI Processing in iDempiere

iDempiere does not include a native EDI engine, but the Import/Export framework provides the foundation for EDI processing. A typical EDI integration involves:

1. Receive: Incoming EDI files arrive via AS2, SFTP, or a VAN (Value-Added Network). An EDI translator (either a standalone tool like Bots, or a cloud service like SPS Commerce or TrueCommerce) converts the raw EDI format into a structured format (CSV, XML, JSON).
2. Import into iDempiere: The translated data is loaded into iDempiere’s import tables (I_Order, I_Invoice, I_BPartner) via CSV import or direct database insert. The Import Loader process then validates and creates the corresponding business documents.
3. Export from iDempiere: Outbound documents (invoices, advance ship notices) are extracted from iDempiere using SQL queries or API calls, transformed into the required EDI format by the translator, and transmitted to the trading partner.

For organizations with high EDI volume, consider building a dedicated plugin that automates the receive-translate-import and export-translate-send workflows, including acknowledgment handling and error notification.

Message Queue Integration

Message queues provide asynchronous, reliable communication between systems. They decouple the sender from the receiver in both time and availability — the sender can publish a message even if the receiver is temporarily offline, and the receiver processes messages at its own pace.

Apache Kafka

Kafka is a distributed event streaming platform designed for high throughput and durability. Messages are organized into topics, and consumers read from topics using consumer groups. Key characteristics for iDempiere integration:

• Durable message storage: Kafka retains messages on disk for a configurable period (days, weeks, or indefinitely), allowing consumers to replay events if needed.
• Ordering guarantees: Messages within a partition are strictly ordered. Use a consistent partition key (e.g., business partner ID, document number) to ensure related messages are processed in order.
• Consumer groups: Multiple instances of the same consumer can share the workload of processing a topic. Kafka automatically balances partitions across group members.

// Example: iDempiere plugin publishing order events to Kafka
Properties props = new Properties();
props.put("bootstrap.servers", "kafka-broker:9092");
props.put("key.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("value.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("acks", "all");  // Wait for all replicas to acknowledge

KafkaProducer<String, String> producer = new KafkaProducer<>(props);

// In a Model Validator after document completion
public String docValidate(PO po, int timing) {
    if (timing == TIMING_AFTER_COMPLETE && po instanceof MOrder) {
        MOrder order = (MOrder) po;
        String json = convertOrderToJson(order);
        ProducerRecord<String, String> record =
            new ProducerRecord<>("idempiere.orders",
                order.getDocumentNo(), json);
        producer.send(record);
    }
    return null;
}

RabbitMQ

RabbitMQ is a traditional message broker implementing the AMQP (Advanced Message Queuing Protocol) standard. It excels at routing messages using exchanges and bindings, supporting patterns like direct routing, topic-based routing, fan-out (broadcast), and headers-based routing.

• Acknowledgments: Consumers explicitly acknowledge message processing. If a consumer dies before acknowledging, the message is redelivered to another consumer — guaranteeing at-least-once delivery.
• Dead letter exchanges: Messages that cannot be processed (expired, rejected, or exceeding retry limits) are routed to a dead letter exchange for investigation.
• Lighter weight than Kafka: Simpler to set up and operate for moderate message volumes. Better suited for traditional request-reply patterns and complex routing rules.

Asynchronous Processing Patterns

When integrating iDempiere with message queues, several patterns are essential:

• Command pattern: The queue message represents an instruction to perform an action (e.g., “create this order”). The consumer validates the command, executes it, and publishes a result event.
• Event notification pattern: The queue message announces that something happened (e.g., “order 12345 was completed”). Subscribers react independently to the event.
• Request-reply pattern: The sender publishes a request message with a correlation ID and a reply-to queue. The consumer processes the request and publishes the response to the reply-to queue. The original sender correlates the response using the correlation ID.

RESTful API Integration

REST APIs are the most common integration method for modern web-based systems. iDempiere provides REST API capabilities through community plugins, and you will frequently need to integrate with external REST APIs.

iDempiere as an API Provider

The iDempiere REST API plugin exposes iDempiere data and operations as RESTful endpoints. Typical capabilities include:

• CRUD operations: Create, read, update, and delete records in any iDempiere table via HTTP methods (POST, GET, PUT, DELETE).
• Process execution: Trigger iDempiere processes via API calls, passing parameters and receiving results.
• Document operations: Complete, void, reverse, and close documents through API endpoints.
• Authentication: Token-based authentication (typically JWT) with role-based access control inherited from iDempiere’s security model.

iDempiere as an API Consumer

When iDempiere needs to call external APIs (e.g., fetching shipping rates, validating tax IDs, syncing inventory with a marketplace), implement the integration in a plugin using Java’s HTTP client libraries:

// Example: Calling an external API from an iDempiere plugin
HttpClient client = HttpClient.newBuilder()
    .connectTimeout(Duration.ofSeconds(10))
    .build();

HttpRequest request = HttpRequest.newBuilder()
    .uri(URI.create("https://api.shipping.example/v2/rates"))
    .header("Authorization", "Bearer " + apiToken)
    .header("Content-Type", "application/json")
    .POST(HttpRequest.BodyPublishers.ofString(requestJson))
    .timeout(Duration.ofSeconds(30))
    .build();

HttpResponse<String> response = client.send(request,
    HttpResponse.BodyHandlers.ofString());

if (response.statusCode() == 200) {
    // Parse and process the response
    JSONObject rates = new JSONObject(response.body());
    // Update iDempiere records with the fetched rates
}

SOAP Web Services (Legacy)

SOAP (Simple Object Access Protocol) web services use XML messaging with strict contracts defined in WSDL (Web Services Description Language). While SOAP has largely been replaced by REST for new integrations, you will encounter it when integrating with legacy enterprise systems, government portals, and older ERP systems.

• iDempiere includes legacy SOAP web service support through the org.adempiere.webservices bundle.
• SOAP services offer stronger typing, built-in error handling (SOAP faults), and WS-Security for enterprise authentication.
• Use Java’s JAX-WS libraries or Apache CXF for SOAP client implementations in iDempiere plugins.

Webhook Patterns

Webhooks invert the polling model: instead of iDempiere periodically checking an external system for changes, the external system pushes notifications to iDempiere when events occur. Implementing webhook reception in iDempiere requires:

1. A servlet or REST endpoint registered in iDempiere to receive incoming HTTP POST requests.
2. Signature verification (typically HMAC-SHA256) to confirm the webhook came from the expected source.
3. Immediate acknowledgment (return HTTP 200) followed by asynchronous processing to avoid timeout issues.
4. Idempotency handling — webhooks may be delivered more than once.

Idempotency in Integrations

In distributed systems, messages can be delivered more than once due to network retries, consumer restarts, or message broker redelivery. Idempotency ensures that processing the same message multiple times has the same effect as processing it once.

Implementing Idempotency

• Unique message identifiers: Assign each message a unique ID (UUID) at the source. Before processing, check if a message with that ID has already been processed. Store processed message IDs in a database table with a unique constraint.
• Natural business keys: Use existing business identifiers (external order number, invoice number, shipment tracking number) as idempotency keys. Before creating a new document, check if one already exists with that external reference.
• Database constraints: Rely on unique indexes in iDempiere tables as a safety net. For example, a unique index on (AD_Client_ID, DocumentNo, C_DocType_ID) prevents duplicate document creation.

// Idempotency check before processing an incoming order
String externalOrderId = incomingMessage.getExternalOrderId();

// Check if we already processed this order
String sql = "SELECT C_Order_ID FROM C_Order WHERE ExternalReference = ? AND AD_Client_ID = ?";
int existingOrderId = DB.getSQLValue(null, sql, externalOrderId, clientId);

if (existingOrderId > 0) {
    log.info("Order already exists for external ID " + externalOrderId + ", skipping");
    return; // Idempotent - no duplicate created
}

// Proceed with order creation
MOrder order = new MOrder(ctx, 0, null);
order.set_ValueOfColumn("ExternalReference", externalOrderId);
// ... populate order fields
order.saveEx();

Error Handling and Retry Strategies

Integration errors are inevitable. Network failures, service outages, data validation errors, and rate limits will all occur. A robust error handling strategy distinguishes reliable integrations from brittle ones.

Classifying Errors

• Transient errors: Temporary failures that will resolve on their own — network timeouts, HTTP 503 (Service Unavailable), database connection pool exhaustion, rate limiting (HTTP 429). These should be retried.
• Permanent errors: Failures that will not resolve without intervention — invalid data (HTTP 400), authentication failure (HTTP 401), resource not found (HTTP 404), business rule violations. These should not be retried but instead sent to a dead letter queue for investigation.

Retry with Exponential Backoff

For transient errors, retry the operation with increasing delays between attempts:

// Exponential backoff retry pattern
int maxRetries = 5;
long baseDelay = 1000; // 1 second

for (int attempt = 0; attempt < maxRetries; attempt++) {
    try {
        executeIntegration();
        break; // Success
    } catch (TransientException e) {
        if (attempt == maxRetries - 1) throw e; // Final attempt failed
        long delay = baseDelay * (long) Math.pow(2, attempt);
        // Add jitter to prevent thundering herd
        delay += ThreadLocalRandom.current().nextLong(0, delay / 2);
        Thread.sleep(delay);
        log.warning("Retry attempt " + (attempt + 1) + " after " + delay + "ms");
    }
}

Dead Letter Queues

Messages that fail processing after all retry attempts should be routed to a dead letter queue (DLQ) rather than being silently dropped. The DLQ serves as a holding area for investigation. Implement monitoring and alerting on DLQ depth, and build tooling to inspect, fix, and replay failed messages.

Circuit Breaker Pattern

If an external system is consistently failing, continuing to send requests wastes resources and may overwhelm the struggling system. The circuit breaker pattern tracks failure rates and “opens” the circuit (stops sending requests) when failures exceed a threshold. After a cooldown period, the circuit enters a “half-open” state where a single test request is allowed through. If it succeeds, the circuit closes (normal operation resumes); if it fails, the circuit opens again.

Data Transformation and Mapping

Data rarely flows between systems without transformation. iDempiere uses specific data formats, field lengths, reference values, and conventions that may differ significantly from external systems.

Common Transformation Tasks

• Field mapping: External system field names and structures rarely match iDempiere’s. Maintain explicit mapping configurations (JSON, XML, or database tables) rather than hardcoding mappings in code.
• Value translation: Convert external code values to iDempiere reference values (e.g., external status codes to iDempiere document statuses, external unit codes to iDempiere UOM IDs).
• Data format conversion: Date formats, number formats, currency representations, and address structures vary across systems and regions.
• Aggregation and splitting: An external order with 100 line items might need to be split into multiple iDempiere orders based on warehouse or organization rules. Conversely, multiple external events might need to be aggregated into a single iDempiere document.

Middleware Platforms

When integration complexity exceeds what you can reasonably manage with custom code, middleware platforms provide pre-built connectors, visual mapping tools, and operational monitoring.

Apache Camel

Apache Camel is an open-source integration framework that implements Enterprise Integration Patterns (EIP). It provides a domain-specific language (DSL) for defining message routes between endpoints. Camel supports over 300 components (connectors) for different systems and protocols.

// Apache Camel route: File pickup, transform, and send to iDempiere
from("file:/edi/incoming?noop=true")
    .unmarshal().csv()
    .process(exchange -> {
        // Transform CSV to iDempiere import format
        List<List<String>> rows = exchange.getIn().getBody(List.class);
        String importData = transformToIdempiere(rows);
        exchange.getIn().setBody(importData);
    })
    .to("jdbc:idempiere?useHeadersAsParameters=true")
    .log("Imported ${header.CamelFileName} into iDempiere")
    .to("file:/edi/processed");

MuleSoft and Other Commercial Options

MuleSoft Anypoint Platform, Dell Boomi, Microsoft Azure Integration Services, and AWS Step Functions provide cloud-based integration platforms with visual designers, pre-built connectors, and managed infrastructure. These are appropriate for large enterprises with diverse integration landscapes and the budget for commercial tooling. For smaller deployments, Apache Camel or custom plugin code is usually sufficient.

Practical Example: E-Commerce Integration

Let us walk through the design of a complete integration between iDempiere and an e-commerce platform (such as WooCommerce, Shopify, or Magento). This example combines multiple patterns covered in this lesson.

Integration Requirements

• Products and inventory levels must be synchronized from iDempiere to the e-commerce platform.
• Customer orders placed on the e-commerce platform must be created as Sales Orders in iDempiere.
• Shipment tracking information from iDempiere must be pushed back to the e-commerce platform.
• Customer records must be synchronized bidirectionally.

Architecture Design

Use an event-driven architecture with a message queue (Kafka or RabbitMQ) as the integration backbone:

1. iDempiere Model Validators publish events to the message queue when products are updated, inventory changes, or shipments are completed.
2. E-commerce webhooks push new order and customer events to a webhook receiver that publishes them to the message queue.
3. Integration workers (consumer processes) subscribe to queue topics, transform the data between iDempiere and e-commerce formats, and call the appropriate APIs to synchronize the data.
4. Error handling: Failed messages go to dead letter queues with alerting. A retry scheduler periodically attempts to reprocess failed messages.

Data Mapping Considerations

• Map e-commerce product SKUs to iDempiere M_Product.Value (Search Key).
• Map e-commerce customer email addresses to iDempiere C_BPartner records, creating new business partners for first-time customers.
• Map e-commerce order statuses to iDempiere document actions (draft, complete, void).
• Handle unit of measure conversions, currency conversions, and tax calculation differences between the systems.
• Store external system IDs in iDempiere custom columns (e.g., ShopifyOrderID) for cross-reference and idempotency.

Inventory Synchronization Detail

Inventory synchronization requires particular care because stock levels change frequently and errors directly impact customer experience (overselling or underselling):

1. An iDempiere Model Validator fires on M_Transaction (the table that records every inventory movement) after save.
2. The validator calculates the new available quantity for the affected product-warehouse combination using MStorageOnHand.
3. It publishes an inventory update event to the message queue with the product SKU, warehouse, and new quantity.
4. The integration worker consumes the event and calls the e-commerce platform’s API to update the stock level.
5. To handle rapid-fire inventory changes, the worker batches updates (e.g., only send the latest quantity for each SKU every 30 seconds) to avoid API rate limits.

Summary

Integration is where iDempiere meets the broader enterprise ecosystem. The patterns you choose — point-to-point vs. event-driven, synchronous vs. asynchronous, polling vs. webhook — should be driven by your specific requirements for reliability, latency, volume, and operational complexity. The key principles to remember are:

• Design for failure — every integration will experience errors, so build in retries, dead letter queues, and circuit breakers from the start.
• Ensure idempotency — duplicate messages are inevitable in distributed systems.
• Prefer asynchronous patterns for better scalability and resilience.
• Start simple (point-to-point) and evolve to more sophisticated patterns (event-driven, middleware) as complexity demands.
• Document your data mappings thoroughly — they are the most common source of integration bugs.

In the next lesson, we will focus on authentication and Single Sign-On — how to secure your iDempiere deployment with LDAP, OAuth2, SAML, and custom authentication providers.

// Example: iDempiere plugin publishing order events to Kafka
Properties props = new Properties();
props.put("bootstrap.servers", "kafka-broker:9092");
props.put("key.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("value.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("acks", "all");  // Wait for all replicas to acknowledge

KafkaProducer<String, String> producer = new KafkaProducer<>(props);

// In a Model Validator after document completion
public String docValidate(PO po, int timing) {
    if (timing == TIMING_AFTER_COMPLETE && po instanceof MOrder) {
        MOrder order = (MOrder) po;
        String json = convertOrderToJson(order);
        ProducerRecord<String, String> record =
            new ProducerRecord<>("idempiere.orders",
                order.getDocumentNo(), json);
        producer.send(record);
    }
    return null;
}

// Example: Calling an external API from an iDempiere plugin
HttpClient client = HttpClient.newBuilder()
    .connectTimeout(Duration.ofSeconds(10))
    .build();

HttpRequest request = HttpRequest.newBuilder()
    .uri(URI.create("https://api.shipping.example/v2/rates"))
    .header("Authorization", "Bearer " + apiToken)
    .header("Content-Type", "application/json")
    .POST(HttpRequest.BodyPublishers.ofString(requestJson))
    .timeout(Duration.ofSeconds(30))
    .build();

HttpResponse<String> response = client.send(request,
    HttpResponse.BodyHandlers.ofString());

if (response.statusCode() == 200) {
    // Parse and process the response
    JSONObject rates = new JSONObject(response.body());
    // Update iDempiere records with the fetched rates
}

// Idempotency check before processing an incoming order
String externalOrderId = incomingMessage.getExternalOrderId();

// Check if we already processed this order
String sql = "SELECT C_Order_ID FROM C_Order WHERE ExternalReference = ? AND AD_Client_ID = ?";
int existingOrderId = DB.getSQLValue(null, sql, externalOrderId, clientId);

if (existingOrderId > 0) {
    log.info("Order already exists for external ID " + externalOrderId + ", skipping");
    return; // Idempotent - no duplicate created
}

// Proceed with order creation
MOrder order = new MOrder(ctx, 0, null);
order.set_ValueOfColumn("ExternalReference", externalOrderId);
// ... populate order fields
order.saveEx();

// Exponential backoff retry pattern
int maxRetries = 5;
long baseDelay = 1000; // 1 second

for (int attempt = 0; attempt < maxRetries; attempt++) {
    try {
        executeIntegration();
        break; // Success
    } catch (TransientException e) {
        if (attempt == maxRetries - 1) throw e; // Final attempt failed
        long delay = baseDelay * (long) Math.pow(2, attempt);
        // Add jitter to prevent thundering herd
        delay += ThreadLocalRandom.current().nextLong(0, delay / 2);
        Thread.sleep(delay);
        log.warning("Retry attempt " + (attempt + 1) + " after " + delay + "ms");
    }
}

// Apache Camel route: File pickup, transform, and send to iDempiere
from("file:/edi/incoming?noop=true")
    .unmarshal().csv()
    .process(exchange -> {
        // Transform CSV to iDempiere import format
        List<List<String>> rows = exchange.getIn().getBody(List.class);
        String importData = transformToIdempiere(rows);
        exchange.getIn().setBody(importData);
    })
    .to("jdbc:idempiere?useHeadersAsParameters=true")
    .log("Imported ${header.CamelFileName} into iDempiere")
    .to("file:/edi/processed");

// Example: iDempiere plugin publishing order events to Kafka
Properties props = new Properties();
props.put("bootstrap.servers", "kafka-broker:9092");
props.put("key.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("value.serializer", "org.apache.kafka.common.serialization.StringSerializer");
props.put("acks", "all");  // Wait for all replicas to acknowledge

KafkaProducer<String, String> producer = new KafkaProducer<>(props);

// In a Model Validator after document completion
public String docValidate(PO po, int timing) {
    if (timing == TIMING_AFTER_COMPLETE && po instanceof MOrder) {
        MOrder order = (MOrder) po;
        String json = convertOrderToJson(order);
        ProducerRecord<String, String> record =
            new ProducerRecord<>("idempiere.orders",
                order.getDocumentNo(), json);
        producer.send(record);
    }
    return null;
}

// Example: Calling an external API from an iDempiere plugin
HttpClient client = HttpClient.newBuilder()
    .connectTimeout(Duration.ofSeconds(10))
    .build();

HttpRequest request = HttpRequest.newBuilder()
    .uri(URI.create("https://api.shipping.example/v2/rates"))
    .header("Authorization", "Bearer " + apiToken)
    .header("Content-Type", "application/json")
    .POST(HttpRequest.BodyPublishers.ofString(requestJson))
    .timeout(Duration.ofSeconds(30))
    .build();

HttpResponse<String> response = client.send(request,
    HttpResponse.BodyHandlers.ofString());

if (response.statusCode() == 200) {
    // Parse and process the response
    JSONObject rates = new JSONObject(response.body());
    // Update iDempiere records with the fetched rates
}

// Idempotency check before processing an incoming order
String externalOrderId = incomingMessage.getExternalOrderId();

// Check if we already processed this order
String sql = "SELECT C_Order_ID FROM C_Order WHERE ExternalReference = ? AND AD_Client_ID = ?";
int existingOrderId = DB.getSQLValue(null, sql, externalOrderId, clientId);

if (existingOrderId > 0) {
    log.info("Order already exists for external ID " + externalOrderId + ", skipping");
    return; // Idempotent - no duplicate created
}

// Proceed with order creation
MOrder order = new MOrder(ctx, 0, null);
order.set_ValueOfColumn("ExternalReference", externalOrderId);
// ... populate order fields
order.saveEx();

// Exponential backoff retry pattern
int maxRetries = 5;
long baseDelay = 1000; // 1 second

for (int attempt = 0; attempt < maxRetries; attempt++) {
    try {
        executeIntegration();
        break; // Success
    } catch (TransientException e) {
        if (attempt == maxRetries - 1) throw e; // Final attempt failed
        long delay = baseDelay * (long) Math.pow(2, attempt);
        // Add jitter to prevent thundering herd
        delay += ThreadLocalRandom.current().nextLong(0, delay / 2);
        Thread.sleep(delay);
        log.warning("Retry attempt " + (attempt + 1) + " after " + delay + "ms");
    }
}

// Apache Camel route: File pickup, transform, and send to iDempiere
from("file:/edi/incoming?noop=true")
    .unmarshal().csv()
    .process(exchange -> {
        // Transform CSV to iDempiere import format
        List<List<String>> rows = exchange.getIn().getBody(List.class);
        String importData = transformToIdempiere(rows);
        exchange.getIn().setBody(importData);
    })
    .to("jdbc:idempiere?useHeadersAsParameters=true")
    .log("Imported ${header.CamelFileName} into iDempiere")
    .to("file:/edi/processed");