Sales Order Header Pull in Practice: Sync Strategy from Kingdee Cloud to an In-house OMS

What This Strategy Solves

A retail enterprise running both Kingdee Cloud (ERP) and an in-house OMS on MySQL needs the OMS to receive sales order headers as soon as they are created in the ERP, so downstream picking, shipping, and reconciliation can proceed.

This single strategy does one job only: pull sales order headers from Kingdee Cloud on a */4 minute cadence and land them into the MySQL oms_order table. It does not touch line items, status write-back, or reversal handling, and it is not a substitute for master-data governance. Its value is making the most easily overlooked yet most downstream-affecting layer — the header — stable, low-latency, and re-runnable.

Data Flow and Field Mapping (Source → Middle Layer → Target)

The source is Kingdee Cloud, queried through the platform's executeBillQuery interface. The middle layer is the Qeasy Data Integration runtime, which handles scheduling, field mapping, and persistence. The target is the MySQL oms_order table, written via the execute WebAPI endpoint running an INSERT statement.

Key header-level field mapping (typical subset):

How to Configure It in Qeasy

Open the strategy editor in Qeasy Data Integration and configure the following:

1. Source connector: platform = Kingdee.Cloud, API = executeBillQuery, method = POST.
2. Pull fields: include FBillNo, FID, FDocumentStatus, FSaleOrgId.FNumber, FDate, FCustId.FNumber, FDemandDate, etc., in the request field list.
3. Target connector: platform = MySQL, API = WebAPI execute, place the INSERT INTO oms_order (...) VALUES (...) into main_sql.
4. Parameter binding: in main_params, map source fields to target fields. Colon-prefixed named parameters must match the SQL placeholders one-to-one.
5. Code mapping: keep sales-org and customer-code mappings in Qeasy's centralized mapping tables rather than scattering them across strategy scripts — a common pattern among Qeasy customers.
6. Dedup and idempotency: enforce a unique key on order_no; enable idCheck to avoid primary-key conflicts on re-runs.
7. Auto-fill response: keep autoFillResponse enabled so the platform projects returned rows into downstream parameters automatically.

Implementation Steps

We typically roll out a pull strategy in three phases: prove the path first, then refine.

Step 1 — Define the incremental starting point. Use FDate as the initial filter. Treat anything before the start date as historical data and backfill it with a one-shot task. After the start date, data enters the incremental lane. Once the start point is fixed, Qeasy uses FBillNo + FDate for dedup to prevent historical re-injection.

Step 2 — Trigger full load and verify. During the full-load phase we usually trigger it manually, run it on a narrow scope first (for example a single sales organization), and reconcile oms_order by row count, status distribution, and kingdee_FID non-null rate. A classic mistake is "running full load without verifying, then turning on the schedule." If a mapping is wrong, the incremental lane will keep writing bad rows.

Step 3 — Schedule frequency and monitoring. The source crontab is */4 * * * *, i.e. every 4 minutes. The MySQL execution plan is */5 * * * *; the slightly wider gap leaves the source a window to finish its query, so both sides do not write on the same second. We attach "retry on failure + alerting" in Qeasy so any SQL error is pushed to the ops channel.

Later, if header + line items need to be combined, add a child strategy with sequence ordering. Header-first, line-items-second is another common phased pattern among Qeasy customers — when something breaks, you know whether it is the header or the lines.

Lessons Learned (Pitfalls)

1. Code mapping scattered across scripts. We used to write if FSaleOrgId == 'X' then 'Y' directly inside the strategy script. Three months later, the sales org changed and a dozen strategies needed updating. The stable approach is to use Qeasy's centralized mapping table so one edit propagates everywhere.

2. Mixing full and incremental loads creates duplicates. Historical backfill ran without dedup, then the schedule kicked in; the same document appeared twice in oms_order and downstream reconciliation never balanced. Always verify and clean up the full-load result before turning on incremental.

3. Writing string dates directly. Source FDate is something like 2024-05-21 00:00:00. If the target order_date is a DATE column, the insert errors out or truncates. Apply an explicit format conversion in Qeasy before binding the parameter.

4. Inconsistent spelling of the sales-org field. Source is FSaleOrgId.FNumber, but the target column was historically named kingdee_salseORG (missing an "e"). It looks trivial, but downstream BI reports already reference the wrong name, so renaming cascades to five reports. Treat naming as a contract.

5. Schedule too tight against a slow source API. */4 was fine for a single org. Once we went multi-org, Kingdee's executeBillQuery started queueing. We switched to staggered pull windows to stabilize it.

When It Applies and When It Does Not

Applies: Kingdee Cloud → in-house OMS header initial sync; ERP as the single source of order truth with the OMS only executing downstream; on-premises, weak-network, low-frequency batch scenarios.

Does not apply: line-item sync, status write-back from OMS to ERP, sub-second real-time push. Those scenarios deserve separate "line-item sync" and "status write-back" strategies, decoupled from this one.