WMS Subcontracting Stock Orders Sync to MySQL: A Single-Strategy Implementation Guide
What This Strategy Solves
A retail or manufacturing business uses Wangdiantong (WMS) to manage warehouses, including subcontracting stock orders—goods shipped out to third-party processors and returned after production. These orders live in the WMS, but finance, ERP, and reconciliation systems analyze them in MySQL. We use Qeasy (the 轻易云 data integration platform) to bridge the two: the source system pulls orders incrementally by status, and the target system writes the header and 1:N detail rows via SQL.
Data Flow and Field Mapping
Direction: Wangdiantong (WMS) → Qeasy integration platform → MySQL.
Source (Wangdiantong) key parameters: warehouse_no, status (10 canceled, 30 pending audit, 50 push failed, 70 partially out, 80 completed), order_type (1 outbound, 2 inbound), outer_no, order_no.
Target (MySQL) key outputs: tp_wdt_stock_outside_wms header table (primary key order_id) and tp_wdt_stock_outside_wms_details_list detail table (1:N extension via details_list).
Key field mapping table:
| Meaning | Source (WMS) | Middle Layer | Target (MySQL) |
|---|---|---|---|
| Warehouse code | warehouse_no | warehouse_no | warehouse_no |
| Order status | status | status | status / wms_status |
| In/out category | order_type | order_type | order_type |
| External order no. | outer_no / api_outer_no | outer_no / api_outer_no | outer_no / api_outer_no |
| Receiver fields | receiver_* | receiver_* | receiver_province / receiver_city / receiver_district / receiver_address / receiver_mobile |
| Detail lines | details_list | details_list | spec_no / goods_no / num / inout_num |
How to Configure on Qeasy
In the 轻易云 platform, this strategy is set up as a two-stage flow: query + SQL execution. The source platform is registered as Wangdiantong·Qiqimen, with API wdt.vip.stock.outside.wms.query, using order_id as id, and idCheck=false to avoid strict validation causing missed rows. The target platform is registered as MySQL, with API execute SQL. The main statement uses REPLACE INTO for the header, while extend_sql_2 writes the detail table, and extend_params_2 binds to details_list. Set autoFillResponse=true so the source auto-fills the response structure, reducing script effort.
Centralize code mapping: maintain warehouse codes, status codes, and in/out categories in Qeasy's data dictionary. When source values change, only one place needs editing, avoiding scattered drift across strategy scripts.
Implementation Steps
Step 1—Establish the incremental starting point. On first go-live, do not pass warehouse_no; pull all historical orders once. After that, filter by status and only pull status >= 60 to avoid writing canceled orders.
Step 2—Configure phased scheduling. Set the source crontab to */11 * * * * (every 11 minutes). Set the target write crontab to 3-59/11 * * * * (offset by 3 minutes) so the target writes after the source has committed, avoiding headers written before details. Write headers and details through 1:N extension as two statements: write the header first to get lastInsertId, then bind details_list to write details.
Step 3—Daily operations. Monitor per-round write counts, SQL execution duration, and error alarms on the Qeasy dashboard. If one warehouse's push volume is abnormal, temporarily fill warehouse_no in the parameter to pull it separately for compensation.
Pitfalls Revisited
- Common mistake: writing header and details in one SQL. Subcontracting order detail rows are numerous; mixing them causes primary key conflicts or missing rows. The reliable approach is to use Qeasy's 1:N extension: main statement for the header,
extend_sql_2for the detail table, joined byorder_id. - Incremental and full loads are not separated. Going live directly with incremental mode misses history; going live with full-load mode repeatedly overwrites and raises load. The reliable approach is to run a full load on day one, then switch to incremental—each in a separate strategy ID so they don't interfere.
- Misinterpreting status values.
status=80means "completed", not "approved";order_typeis what distinguishes inbound vs outbound. A common pitfall is treatingstatusas the in/out category. Document the semantics in a data dictionary and select values explicitly during configuration. - Inbound and outbound mixed together causes data loss. The same
order_nomay have both inbound and outbound lines; deduplicating byorder_nodrops half of them. The reliable approach is to deduplicate byorder_id(the true unique identifier), notorder_no. - Side effects of
REPLACE INTO. When source fields change, the target'sREPLACE INTOdeletes and reinserts, which changes auto-increment primary keys and invalidates foreign key references. If foreign keys depend on it, switch toINSERT ... ON DUPLICATE KEY UPDATEto update without rebuilding rows.
When to Use and When Not to Use
Use when: Wangdiantong acts as the warehouse execution system and subcontracting stock details must land in MySQL for ERP, reconciliation, or BI use; detail row counts are stable and order status is enumerable.
Do not use when: sub-second real-time sync is required (this strategy is minute-level); batch cross-warehouse pushes with massive detail rows; or audit-grade change history is required (this strategy uses REPLACE and does not retain history).