Practical Tutorial: Syncing Sales Orders (OMS) from JkyQM to MySQL
What this strategy solves
The Sales Order is the entry document for OMS data aggregation, with the source in JkyQM Qimen and the target landing in MySQL's oms_sale_order master table and oms_sale_order_detail detail table. In real projects, this looks like a simple "pull and land," but if the master-detail foreign key, incremental boundary, and memo concatenation are not well designed, the numbers on both sides will diverge within a month. This strategy provides a single-direction, single master-detail structure landing path for sales orders, suitable as a reference when using Qeasy (轻易云) to land OMS data.
Data flow and field mapping
Data flow: JkyQM Qimen → MySQL (private deployment). The source pulls via the Qimen API jackyun.tradenotsensitiveinfos.list.get in pages, and the target executes the master SQL plus the detail extended SQL through executeReturn.
Master-detail structure mapping:
JkyQM Sales Order (master)
├── tradeNo / tradeId
├── shopCode, shopName, companyName, customerCode
├── warehouseCode, warehouseName
├── goodsDetail (array)
│ ├── Line: goodsNo, goodsName, sellCount, sellPrice, cost…
│ └── ...
└── Lands into
MySQL oms_sale_order (master)
├── trade_no, shop_code, shop_name, customer_code…
└── oms_sale_order_detail (detail, 1:N)
└── order_id = master table lastInsertId
Key field comparison (excerpt):
| Source field | Target field | Mapping type | Description |
|---|---|---|---|
tradeNo | trade_no | DIRECT | Cross-system unique identifier |
tradeType / tradeStatus | trade_type / trade_status | DIRECT | Order type and status |
shopName / ShopName | shop_name | DIRECT | Watch field casing on source |
sellerMemo+buyerMemo+appendMemo | remark | TRANSFORM | Three-segment memo concatenation |
tradeTime | trade_date | DIRECT | Order time, YYYY-MM-DD HH:mm:ss |
gmtModified | updated_time | DIRECT | Incremental sync judgment field |
goodsDetail.* | oms_sale_order_detail.* | Whole-array loop | One row per detail line |
| — | order_id (detail) | TRANSFORM | Take master lastInsertId |
How to configure on Qeasy
When implementing this strategy at the customer site, we typically use Qeasy's "source API + target SQL" combination rather than writing scripts. Here are the typical configuration points:
- Source API: Method
POST, APIjackyun.tradenotsensitiveinfos.list.get, primary key set totradeNo, idempotency field set totradeIdwithidCheckenabled. - Target API: Method
POST, APIexecuteReturn, primary key set toid(auto-increment), core parameters aremain_paramsandextend_params_1. - Detail row loop: Set the target
extend_params_1to the source array namegoodsDetail; the platform will executeextend_sql_1element by element, achieving 1:N detail landing. - Master-detail foreign key: The detail row's
order_iduses the<{lastInsertId:}>placeholder, which the platform automatically fills with the auto-increment ID after the masterINSERT—no manual association needed. - Centralized code mapping management: Although this strategy uses DIRECT for primary codes, when customers later connect to ERP systems such as Kingdee, we recommend putting the goods and shop code mappings into a "Code Hub" and using
_findCollectionto look up, avoiding duplicating mapping tables in every strategy—this is one of the most common patterns among Qeasy customers. - Constants and defaults: Use placeholders with defaults such as
<{sale_org_id: 0}>and<{date: CURRENT_TIMESTAMP}>in the SQL template; when the source has no value, the default is automatically applied, avoiding NULLs everywhere.
Implementation steps
We recommend proceeding in the order of "align the boundary first, then enable scheduling," in four stages:
Stage 1: Align the incremental starting point
- Confirm whether the source incremental field is
gmtModifiedorstartConsignTime. This strategy usesgmtModifiedas the incremental judgment. - In Qeasy, bind the "last modified time" variable to
endModified. For the first run, use "full mode" to pull historical data: clear the target table and load everything in.
Stage 2: Full trigger + code mapping
- After the full run passes, immediately switch back to "incremental mode" and use
startModified/endModifiedfor sliding pull. - The three major code categories—goods, shop, and warehouse—first land via DIRECT, then extend with
_findCollectionlater if needed.
Stage 3: Scheduling frequency
- Source: every 15 minutes (
*/15 * * * *). The Qimen API has rate limits; too frequent requests will be throttled. - Target: every 5 minutes (
*/5 * * * *). The target is just landing data, so the frequency can be higher. - The two ends running at different frequencies is a common Qeasy customer pattern—"source pulls every 15 minutes, target writes every 5 minutes" forms a dual-track rhythm of incremental and full data, neither overwhelming the source system nor making the data lag too long.
Stage 4: Validation and reconciliation
- For the first week after launch, reconcile daily: source
tradeNocount vs targettrade_nocount, plus the master-detail ratio (master rows × average detail rows ≈ detail rows). - From the second week, switch to weekly reconciliation. If discrepancies appear, first check whether the incremental boundary is missing records.
Pitfall review
- Inconsistent source field casing: Some fields in JkyQM are
shopName, while some interfaces returnShopName. If the master mapping misses one, the value will stay NULL. The safe approach is to list both names in Qeasy's field mapping and prefer the non-null value. - Wrong priority when concatenating the three memo segments:
sellerMemo+buyerMemo+appendMemomust be selected according to business rules. A typical mistake is concatenating directly, which leaks the seller's internal memo to the buyer side. The standard approach is to use a TRANSFORM expression to take one segment or concatenate them according to business priority. - Detail foreign key not filled: Many people write
order_idinextend_sql_1by taking a value from the source, but the source does not have this value at all, resulting in all detail rows being NULL or failing to associate with the master. The correct approach is to have the detail foreign key only take the masterlastInsertId, which is injected by the platform. - Missing records at incremental boundary: The Qimen API requires
startModifiedandendModifiedto exist simultaneously and the interval cannot exceed 7 days. If you schedule hourly but set the window incorrectly, data outside the boundary will be missed. The safe approach is to bind{{LAST_SYNC_TIME}}and{{CURRENT_TIME}}variables in the source filter conditions instead of hardcoding the time. - Target frequency higher than source: Some customers also set the target to every 15 minutes, resulting in very high idle time at the target and unnecessarily delayed landing. Source pulling every 15 minutes combined with target writing every 5 minutes is a more stable combination.
Applicable and non-applicable scenarios
Applicable: JkyQM as the e-commerce middle platform, MySQL as the OMS landing database, and only one-way sync of the sales order master-detail structure. Typical examples include retail, footwear and apparel, and cosmetics enterprises with large online order volumes.
Not applicable: Scenarios that require two-way sync (OMS modifying orders and pushing back to JkyQM), complex order state machines (requiring multiple conditional branches), or pushing orders to ERP/WMS for downstream fulfillment—these should be split into separate strategies rather than piled into one.