Qeasy Cloud
Get Started

Practical Tutorial: Syncing Sales Orders (OMS) from JkyQM to MySQL

· 吕修远· Integration Solutions· 3 views· 4 min read

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 fieldTarget fieldMapping typeDescription
tradeNotrade_noDIRECTCross-system unique identifier
tradeType / tradeStatustrade_type / trade_statusDIRECTOrder type and status
shopName / ShopNameshop_nameDIRECTWatch field casing on source
sellerMemo+buyerMemo+appendMemoremarkTRANSFORMThree-segment memo concatenation
tradeTimetrade_dateDIRECTOrder time, YYYY-MM-DD HH:mm:ss
gmtModifiedupdated_timeDIRECTIncremental sync judgment field
goodsDetail.*oms_sale_order_detail.*Whole-array loopOne row per detail line
—order_id (detail)TRANSFORMTake 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:

  1. Source API: Method POST, API jackyun.tradenotsensitiveinfos.list.get, primary key set to tradeNo, idempotency field set to tradeId with idCheck enabled.
  2. Target API: Method POST, API executeReturn, primary key set to id (auto-increment), core parameters are main_params and extend_params_1.
  3. Detail row loop: Set the target extend_params_1 to the source array name goodsDetail; the platform will execute extend_sql_1 element by element, achieving 1:N detail landing.
  4. Master-detail foreign key: The detail row's order_id uses the <{lastInsertId:}> placeholder, which the platform automatically fills with the auto-increment ID after the master INSERT—no manual association needed.
  5. 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 _findCollection to look up, avoiding duplicating mapping tables in every strategy—this is one of the most common patterns among Qeasy customers.
  6. 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 gmtModified or startConsignTime. This strategy uses gmtModified as 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 / endModified for sliding pull.
  • The three major code categories—goods, shop, and warehouse—first land via DIRECT, then extend with _findCollection later 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 tradeNo count vs target trade_no count, 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

  1. Inconsistent source field casing: Some fields in JkyQM are shopName, while some interfaces return ShopName. 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.
  2. Wrong priority when concatenating the three memo segments: sellerMemo + buyerMemo + appendMemo must 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.
  3. Detail foreign key not filled: Many people write order_id in extend_sql_1 by 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 master lastInsertId, which is injected by the platform.
  4. Missing records at incremental boundary: The Qimen API requires startModified and endModified to 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.
  5. 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.

Original content. Please credit the source when reposting: https://www.qeasy.cloud/insights/solutions/strat-mysql-p2ea595-5916-oms-e891c8c4

Comments