Refund Inbound Sync in Practice: Incremental Landing from Wangdiantong to MySQL
What This Strategy Solves
A retail enterprise needs refund inbound orders from its ERP (Wangdiantong) consolidated into a self-built MySQL as the source for downstream BI and reconciliation. Once a document passes the ERP internal review, it must appear downstream within an 11-minute granularity; otherwise, store inventory and headquarters reconciliation start to drift. We use the Qeasy Data Integration Platform to host this strategy, packaging "incremental retrieval by time window + pagination + phased header/detail loading" into a stable scheduled job.
Data Flow and Field Mapping
Overall flow: Wangdiantong (QUERY) → Qeasy (intermediate layer) → MySQL (EXECUTE). The source is the wdt.stockin.order.query.refund API; the target is two REPLACE INTO statements writing the master table and the detail table.
Key field mapping (excerpt):
| Dimension | Wangdiantong Field | Intermediate Semantics | MySQL Master Field | MySQL Detail Field |
|---|---|---|---|---|
| Document No. | order_no | Master document number | order_no | - |
| Primary Key | stockin_id | Document unique ID | stockin_id | stockin_id |
| Warehouse | warehouse_no / warehouse_name | Warehouse code and name | warehouse_no / warehouse_name | - |
| Shop | shop_no / shop_name | Shop code and name | shop_no / shop_name | - |
| Status | status / process_status | Document status | status / process_status | - |
| Time | created_time / stockin_time / modified | Create/inbound/update time | created_time / stockin_time / modified | modified / created |
| Detail PK | rec_id | Detail row ID | - | rec_id |
| Goods | spec_no / goods_no / goods_name | Spec and goods code | - | spec_no / goods_no / goods_name |
| Quantity/Amount | goods_count / cost_price / total_cost | Quantity and cost | - | goods_count / cost_price / total_cost |
Code mapping is where this strategy is most prone to issues. We centralize management in Qeasy: the shop code shop_no, warehouse code warehouse_no, and goods spec spec_no all go into a unified code table; source fields pass through mapping before being written to the database, preventing dirty data from landing directly.
How to Configure on Qeasy
Source configuration: select the wdt.stockin.order.query.refund API, method POST; the incremental parameter start_time uses {{LAST_SYNC_TIME|datetime}}, and end_time uses {{CURRENT_TIME|datetime}}; by default only status=80 completed documents are queried; pagination page_size is controlled by the variable {{PAGINATION_PAGE_SIZE}}, defaulting to 40, range 1~50.
Target configuration: the id field is stockin_id, with idCheck enabled for primary-key deduplication; the master statement uses REPLACE INTO to implement "update if exists, insert if not"; the detail table likewise uses REPLACE INTO, receiving the details_list array via extend_sql_1. buildModel is off, autoFillResponse is on, automatically landing responses into the database.
Scheduling: source */11 * * * *, target 3-59/11 * * * *, staggered by 3 minutes to leave time for retrieval and assembly.
Implementation Steps
- Baseline Full Load: Manually run a full load the day before launch, writing all current
status=80documents into MySQL as the reconciliation starting point. - Incremental Start Point: Anchor
LAST_SYNC_TIMEto the launch time, rolling forward by time window for the first run. - Phased Loading: Write the master table first, then the detail table; any failure rolls back the entire batch to avoid orphan details.
- Scheduling Frequency: Pull every 11 minutes; keep a full-compensation task during off-peak hours at night to cover missed pulls.
- Reconciliation Mechanism: At 02:00 daily, compare MySQL's
modifiedagainst the ERP-side update time; differences exceeding a threshold trigger automatic alerts.
Lessons from the Trenches
- Don't use database time directly for the time window: Using
{{CURRENT_TIME}}asend_timelooks stable, but across time zones it produces a "future time" and the source returns empty. The safe approach is to uniformly use the integration platform server time and fix the format asyyyy-MM-dd HH:mm:ss. - Don't omit the
statusdefault: A typical mistake is not passingstatusand expecting everything back, but the API may also bring back "in editing" records. Explicitly querying only 80 gives stronger controllability. - REPLACE is not omnipotent: It relies on a unique primary key; if the source produces two different details under the same
stockin_id, they are silently overwritten. Adding a business unique-key check on the detail table is recommended. - Staggered scheduling is non-negotiable: Scheduling source and target at the same minute means that if the source slows down, the target runs empty. A 3-minute stagger is the empirical value.
- Bigger pagination is not always better: When pulled to 50, some timeout scenarios lose pages; 40 by default is safer, with automatic retry on failure.
Suitable and Unsuitable Scenarios
Suitable: retail chains with stable refund volume, a need for status filtering, and high reconciliation requirements. Unsuitable: scenarios requiring real-time second-level push, where the source writes back modified at high frequency, or where the detail table exceeds 10,000 rows — for the latter, splitting the detail strategy or switching to a streaming channel is recommended.