Automating Distributor Order Backhaul: From Excel Emails to Direct API Integration
The starting point: a typical "Excel by email" flow
In many brands, distributors still fill orders into Excel files and email them to sales coordinators, who consolidate, reformat, and key them into the ERP — then call or message the distributor about shipping. An order takes half a day to move, with misaligned rows, missed orders, and duplicate entries along the way.
The goal of this tutorial: distributors (or their ERPs) push orders to the integration platform via API or file drop; the platform cleanses, maps, and deduplicates them automatically, writes sales orders into Kingdee Cloud, and pushes status back to the distributor.
The end-to-end pipeline
- Collection: the distributor ERP pushes via webhook, or uploads order files to an agreed directory on a schedule; the platform parses everything into a standard order structure.
- Cleansing and mapping: distributor SKU to brand material code, unit conversion, price and credit-limit validation.
- Writing to ERP: call the Kingdee Cloud WebAPI to create the sales order (Save, Submit, Audit).
- Status feedback: query ERP document status on a schedule and push it back to the distributor.
Step 1: define a canonical order model
Regardless of whether the upstream is an API or Excel, normalize everything into one model with externalOrderNo and distributorCode as the idempotency key carried through every retry.
Step 2: field mapping and code conversion
Maintain a mapping table from distributor SKU to brand material code, convert units (e.g. case to base unit), and validate prices against the allowed band of the price list. Keep mapping tables on the platform so new SKUs are added through a mapping workflow, not code changes.
Step 3: write into Kingdee Cloud
The standard Kingdee Cloud WebAPI write sequence is: authenticate with AuthService.ValidateUser, then DynamicFormService.Save with FormId SAL_SaleOrder, followed by Submit and Audit. Before writing, always run an idempotency check with ExecuteBillQuery filtered by the external order number — if it exists, skip the write and return the existing bill number.
Step 4: incremental scheduling and status feedback
Prefer webhooks for order collection; for file drops, scan every five minutes and record watermarks. Pull changed bills every 10-15 minutes with ExecuteBillQuery filtered by audit time, and push them to the distributor with exponential-backoff retries. Run a daily full reconciliation comparing the day's distributor orders against generated ERP bills, alerting on any mismatch.
Acceptance checklist
- The same external order number pushed ten times produces exactly one sales order.
- Bad SKUs create readable exception records that can be fixed and replayed — no silent drops.
- Distributors see the bill number and status within 15 minutes of ERP approval.
- No manual spreadsheets anywhere; coordinators handle exception tickets only.