WMS Refund Orders to MySQL: Incremental Pull with Master-Detail Landing on Qeasy

What this strategy solves

In retail and distribution, refund/exchange orders sitting in Wangdiantong usually need a queryable, reconcilable mirror on the MySQL side — for after-sales reports, stock reversal auditing, and finance settlement. The trick is that refund orders have both a header and line items, and the status machine is long (pending review, pending finance audit, waiting for receipt, completed, plus outsourcing and supplementary payment states). A daily full pull eats the daytime window; polling by status drifts whenever the schema changes. In customer projects we lean on Qeasy (the 轻易云 data integration platform) to do an incremental pull keyed on "last modified time," landing the master and detail tables through two SQL statements with REPLACE INTO for idempotence. Here is the strategy broken down.

Data flow and field mapping

The source is Wangdiantong · Enterprise Qimen, API wdt.refund.query, POST with paging. The middleware is the Qeasy integration flow. The target is a MySQL instance on the public cloud. Key field mapping:

The master table has roughly 70 columns covering receiver, logistics, refund amounts, warehouses, push status, and so on. The detail table is a 1:N expansion with spec, suite, stock position, unit price, actual refund quantity.

How to configure on Qeasy

On the source side, pick the Wangdiantong · Enterprise Qimen connector. API is wdt.refund.query, method POST. The request body reuses the interface fields directly; the three template variables are what matter:

• start_time = {{LAST_SYNC_TIME\|datetime}} — the platform fills the last successful window start automatically.
• end_time = {{CURRENT_TIME\|datetime}} — current time at each run.
• page_size = {{PAGINATION_PAGE_SIZE}} — the platform paginator handles paging, no manual loop.

On the target side, choose MySQL, API execute, type SQL. We recommend turning idCheck on so the platform uses refund_id for idempotency checks. Leave buildModel off and let field mapping be driven by named parameters in the main statement.

The main statement is a REPLACE INTO fky_wdt_refund (...) VALUES (:col1, :col2, ...) where the named parameters share names with source fields, removing the need to write most mappings. The detail table uses extend_sql_1 bound to the refund_order_list array; the platform expands it into multiple rows and runs another REPLACE INTO fky_wdt_refund_order_list. extend_params_1 must point at the source-side array name, otherwise the 1:N expansion will not work.

The source schedule is */13 * * * *, the target is offset to 3-59/13 * * * *, so source pulls and target writes do not collide on the same second.

Implementation steps

Step one — one-shot full backfill: temporarily set start_time to a historical far point (for example 90 days ago), trigger a manual run, and load all historical refund orders into MySQL. We typically do this on the night before go-live and switch back to incremental afterwards.

Step two — switch to incremental. time_type=0 means "last modified time," so even when a refund order moves from "waiting for receipt" to "completed," it will be re-synced and overwrite the latest state into MySQL. The primary key is stable, so REPLACE INTO will not bloat the table.

Step three — define the schedule. Source */13 * * * *, target 3-59/13 * * * *, giving a rhythm of "source pulls every 13 minutes, target lands a few seconds later." If the refund volume is low, push page_size to its 40 ceiling to reduce paging calls.

Step four — reconciliation. On the MySQL side, run a daily SELECT COUNT(*) and compare it against the source API row count for the same window. Then run a left join on refund_id to verify the detail row counts match. A common pattern we see among Qeasy customers is to centralize code mapping: keep Wangdiantong status codes and warehouse codes in the platform's mapping layer so other strategies (such as sales order sync) can reuse them instead of hardcoding inside every SQL.

Lessons from the field

First, do not casually flip time_type to 1 (settlement time). Settlement time only ticks at settlement, so mid-flow status changes will not surface in MySQL and reports lag. We keep it fixed at 0; settlement-specific reporting is served by separate views.

Second, keep the master and detail tables in separate statements rather than a single INSERT with array expansion. MySQL does not gracefully expand a single parameter into multiple rows, and a column count mismatch will throw an error. Qeasy's extend_sql_1 is built exactly for this 1:N case: the platform splits the array and each SQL only handles a single row.

Third, never set page_size above the API's 40 limit. Going over gets silently truncated — you think the sync is done while data is actually missing.

Fourth, while REPLACE INTO is convenient, it does a delete-then-insert that forces a primary key index rebuild; performance degrades when the detail table is large. If daily refund rows exceed 500K, prefer INSERT ... ON DUPLICATE KEY UPDATE — same effect, but only the changed columns are updated.

Fifth, persist LAST_SYNC_TIME immediately after each successful run rather than waiting for the whole batch to finish. If a network blip kills a batch mid-way, the next round will re-run from this checkpoint instead of skipping data.

When it fits, when it does not

Fits: daily refund/exchange volume between 10K and 500K rows, with header-plus-detail structure, where you need a status-aware mirror for downstream reconciliation or after-sales analytics. Does not fit: scenarios that require sub-minute real-time feedback into ERP business documents — incremental polling is the wrong shape for that. It also does not fit use cases that only need aggregate metrics without per-order landing; for those, a BI pull is lighter.