Qeasy Cloud
Get Started

Wangdiantong Transfer Order Sync to MySQL: A Single-Strategy Deep Dive

· 卢剑航· Integration Solutions· 29 views· 3 min read
MySQLWDT调拨单Incremental Sync轻易云供应链集成

What This Strategy Solves

Transfer orders document stock movement between warehouses. They feed both finance reconciliation and in-transit analytics. In one real project, a retail enterprise handled over a thousand transfer orders per day. Pulling everything via a full-volume API was slow and risked overwhelming the upstream; missed orders were even worse. This strategy uses a time-windowed incremental query plus REPLACE writes to land Wangdiantong transfer orders reliably into a MySQL staging database: fast, replayable, and ready for downstream consumers.

Data Flow and Field Mapping

Data flows: Wangdiantong (source) → Qeasy Data Integration Platform (middle layer) → MySQL (target).

The source calls wdt.stock.transfer.query, pulling incrementally by start_time / end_time. The target uses execute SQL: a main table write REPLACE INTO tp_wdt_stock_transfer and a detail table write REPLACE INTO tp_wdt_stock_transfer_details_list. Primary-key collisions overwrite, so the operation is naturally idempotent.

Key field mapping:

Business meaningSource (Wangdiantong)Middle layerTarget (MySQL)Notes
Transfer order numbertransfer_noPass-throughtransfer_noDocument unique ID
Order statusstatusPass-throughstatus10/20/30/40/42/44/46/50/60/62/64/66/70/80/90
Source warehouse codefrom_warehouse_noPass-throughfrom_warehouse_noWarehouse unique code
Target warehouse codeto_warehouse_noPass-throughto_warehouse_noWarehouse unique code
Created atcreatedPass-throughcreateddatetime
Modified atmodifiedPass-throughmodifiedIncremental cursor
Detail line IDrec_idPass-throughrec_id1:N detail identifier
Product codespec_no / goods_noPass-throughspec_no / goods_noMany-to-one mapping

Configuring on Qeasy

On the Qeasy Data Integration Platform's strategy canvas, this strategy is essentially two steps: a source Query and a target SQL Execute.

  1. Source: Pick API wdt.stock.transfer.query, POST method. start_time uses {{LAST_SYNC_TIME|datetime}} and end_time uses {{CURRENT_TIME|datetime}}; the scheduler maintains the incremental cursor automatically. status explicitly passes 50,60,70,80,90, filtering to statuses meaningful to downstream consumers (partial out, fully out, partial in, pending settlement, transfer completed).
  2. Target: SQL type Execute. Main statement: REPLACE INTO tp_wdt_stock_transfer (...) VALUES (...). The detail table uses extend_sql_2 writing to tp_wdt_stock_transfer_details_list with extend_params_2 set to details_list. idCheck off and buildModel off avoid misjudgment.
  3. Code mapping: Warehouse codes, product codes, and supplier codes all live in a centralized code-mapping registry rather than scattered across strategies. New strategies reference this registry directly; this is one of the typical Qeasy customer patterns.

Implementation Steps

Phase the schedule to stay safe:

  1. Incremental anchor: Run a one-time full-volume backfill first to populate historical transfer orders and record LAST_SYNC_TIME. Then run every 11 minutes (*/11 * * * *). Offset the source and target by 3 minutes (3-59/11 * * * *) so they don't hit MySQL simultaneously.
  2. Full-volume trigger: Run a full-volume replay during the daily low-peak window. With REPLACE idempotency, drifted rows self-correct without a complex reconciliation job.
  3. Schedule frequency: Bump to every 5 minutes during peak hours, every 30 minutes during off-peak. The source polling interval must always exceed the target write duration to avoid backlog.
  4. Validation: Use Qeasy's built-in data-validation module to check transfer_no against goods_count / goods_in_count / goods_out_count; anomalies go into an alert queue.

Pitfalls and Lessons

  • Pitfall 1: Leaving status empty. Without a value, the source returns every status, including massive numbers of cancelled orders that confuse downstream logic. The safe approach is to pass 50,60,70,80,90 explicitly and sync only what matters.
  • Pitfall 2: Not maintaining the incremental cursor. LAST_SYNC_TIME must advance only after a successful write; otherwise retries replay the same batch. Although REPLACE is idempotent, logs and alerts still flood.
  • Pitfall 3: Wrong 1:N extend parameter. extend_params_2 must equal the source response array name (for example, details_list). A mismatch silently drops details while the main table still lands — extremely hard to debug. The Qeasy pattern of staged header/detail writes is recommended: get the main table working first, then enable the detail table.
  • Pitfall 4: Misusing REPLACE. REPLACE deletes then inserts, so auto-increment IDs change and triggers fire twice. The safe approach is a composite unique index on transfer_id + rec_id, keeping the business key stable.
  • Pitfall 5: Warehouse code mismatch. Wangdiantong uses string codes while the business system uses numeric IDs. Passing them through directly causes collisions. The common Qeasy customer pattern here is centralized code mapping: a single mapping table in the platform translates source codes before writing.

When to Use and When Not

Use when transfer volume is high, near-real-time landing is required, the source exposes only a Query interface, and header/detail must land in the same database. Do not use when sub-second push is required, event-driven callbacks are needed, or the source has no time field to drive an incremental cursor — in those cases switch to an instant-inventory sync or message-push strategy.

Original content. Please credit the source when reposting: https://www.qeasy.cloud/insights/solutions/strat-mysql-wdt-5427-mysql-fefb0632

Comments