Transfer Order Sync in Practice: Incremental Landing from Wangdiantong to MySQL with Header-Detail Dual-Track

What This Strategy Solves (Scenario and Value)

Transfer order sync looks like simple document movement, but once warehouses span regions, SKUs scale up, and statuses keep bouncing between "pending review → partially shipped → received," three problems show up together: missed records, duplicates, and misaligned details. In one retail project with multiple warehouses, ledger drift between the two sides reached thousands of rows within three months. The root causes almost always came down to misaligned incremental windows, headers and details not bound to the same write, and status fields being overwritten incorrectly.

The strategy we run on Qeasy — pulling transfer orders from Wangdiantong into MySQL — has three concrete goals: idempotency on transfer_id, incrementality via a time window, and writing the header and detail rows within the same transaction.

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

The source is the wdt.stock.transfer.query API on Wangdiantong QiMmen; the target is the execute action on MySQL. Qeasy acts as the middle layer, doing field cleaning, status normalization, and header-detail splitting.

Both header and detail tables use REPLACE INTO, which means as long as transfer_id (plus rec_id for the detail table) matches, the whole row is overwritten — naturally implementing "latest version overwrites the previous one."

How to Configure It on Qeasy

1. Source API

• API: wdt.stock.transfer.query, method POST.
• Required: start_time injected with {{LAST_SYNC_TIME|datetime}}, end_time with {{CURRENT_TIME|datetime}}, so every run forms a sliding window.
• Optional but recommended: from_warehouse_no and to_warehouse_no. When the customer splits channels by warehouse, these must be specified, otherwise the "does not support pushing multiple warehouse numbers at once" limitation will bite.
• Keep idCheck off, turn on autoFillResponse so the platform auto-handles pagination.

2. Target SQL

• Entry is execute; main SQL targets jry_wdt_stock_transfer, extend SQL targets jry_wdt_stock_transfer_details_list.
• main_params carries the main dynamic parameters, extend_params_2 carries the 1:N extension parameters, and field names must align with the source's details_list array.
• Both tables use REPLACE INTO; use named placeholders like :transfer_id to avoid string concatenation.
• Recommend turning idCheck on so the platform uses transfer_id for idempotency checks.

3. Middle-layer processing

• Centralized code mapping: maintain Wangdiantong's warehouse codes and custom dictionaries in one mapping table. One change in source code propagates to all downstream strategies.
• Header/detail staging: land the header rows first, then batch the detail rows; never write row-by-row in a loop.

Implementation Steps

Phase 1: Incremental starting point On first go-live, set LAST_SYNC_TIME to the business-acceptable earliest rewind point, e.g. 00:00:00 of the launch day. Once it runs cleanly, switch to the platform's auto-maintained rolling cursor.

Phase 2: Full-volume trigger Spin up a separate full-volume job indexed by transfer_no for historical transfer orders, run it once, then disable it, to prevent stepping on the incremental job. A typical mistake is sharing one cursor between full and incremental — after the full run finishes, it overwrites the incremental anchor and the next 12 hours of data are lost.

Phase 3: Scheduling cadence Set the source strategy cron to */11 * * * * and the target to 3-59/11 * * * *, offset by 3 minutes to leave a window for source pulling and middle-layer processing. Many customer sites miss this nuance; see the pitfall section below.

Phase 4: Status-machine guard The source status field has many values (10 through 90). Add a status filter on the Qeasy side and only persist states >= 40 (after approval) downstream, so "editing" documents are not stored and don't trigger repeated detail-table rewrites.

Pitfall Retrospective

1. Incremental window and cursor out of sync: source pulls by start_time but the cursor uses created. Wangdiantong's modified is the safe choice. The reliable approach is to use the same time field for both the cursor and start_time.
2. Header and detail written in separate transactions: if the middle layer fails after the header lands, the detail never gets written, and there's no way to recover later. Always bind header and detail to the same execute call.
3. status overwritten by REPLACE: when writing back, if you only write detail fields without status, the target status is overwritten with the old value. Explicitly list the write field set; don't take shortcuts.
4. Full and incremental sharing one cursor: this is the trap every new team falls into at least once. The full-volume job must use an independent cursor and be retired after one run.
5. Not specifying from_warehouse_no: in a multi-warehouse scenario, the API throws "does not support pushing multiple warehouse numbers at once." Either split channels by warehouse or filter upstream.

Applicable and Inapplicable Scenarios

Applicable: Wangdiantong as the WMS backbone, MySQL as the data middle platform or BI base, multi-warehouse transfers requiring idempotent landing by transfer_id. Inapplicable: scenarios requiring strong-consistent transactions, scenarios where you must write back to Wangdiantong to undo approvals, or small-volume scenarios where incrementality isn't worth it — direct API calls are simpler in those cases.