Sales Delivery Sync in Practice: An Incremental Integration from Kingdee Cloud to MySQL

What This Strategy Solves

In a real project at a pharmaceutical distribution enterprise, sales delivery bills from the ERP needed to land in the central MySQL database in near real time to feed downstream BI, reconciliation, and logistics tracking. What looks like a simple "bill sync" actually raises three problems: how to flatten Kingdee's master-detail structure into a single table, whether to pull codes or display names for master data fields, and what cadence keeps the source system safe while never missing a row. We used the Qeasy Data Integration Platform to carry this pipeline, pulling SAL_OUTSTOCK from Kingdee Cloud into a MySQL flat table on a 7-minute cadence, and the design has been running steadily at the customer site ever since.

Data Flow and Field Mapping

The overall direction is: Kingdee Cloud → Qeasy middle layer → MySQL. On the source side, executeBillQuery (POST paginated query, FormId=SAL_OUTSTOCK) increments on FApproveDate and returns a flattened result (each row = one delivery entry, with header fields repeated on every row). On the target side, everything lands in a single MySQL table fky_jd_out_stock, where FEntity_FEntryID is the deduplication key and REPLACE INTO provides upsert behavior.

Key field mapping (excerpt):

Codes and names follow a dual-track mapping: customers, materials, warehouses, and lots use FNumber for cross-system joins, while organizations, departments, sales reps, and carriers use FName so business users can read them directly.

How to Configure It in Qeasy

Within the Qeasy Data Integration Platform, this strategy centers on two building blocks: source query and target execution.

• Source (Kingdee side): metadata.api=executeBillQuery, effect=QUERY. The request lists each field to pull using {{field.subproperty}} syntax, such as {{FEntity_FEntryID}}, {{FBillNo}}, {{FCustomerID.FNumber}}. Pagination is driven by {{PAGINATION_PAGE_SIZE}} and {{PAGINATION_START_ROW}}. The incremental condition lives in FilterString: FApproveDate>='{{LAST_SYNC_TIME|datetime}}'.
• Target (MySQL side): metadata.api=batchexecute, effect=EXECUTE, idCheck=true to enable primary-key deduplication. The request array injects upstream values into target columns via {{}} templates. Writes use REPLACE INTO keyed on FEntity_FEntryID, with a per-queue batch limit of 200.
• Scheduling: the source runs on */7 * * * * (pull every 7 minutes); the target runs on 3-59/7 * * * * (write every 7 minutes, offset by 3 minutes) so reads and writes do not collide.

No custom scripts are configured (Scripts is empty). All transformations happen at the field mapping layer.

Implementation Steps

We split this rollout into three phases to avoid flooding production with a full backfill.

1. Initialize the incremental anchor: before going live, seed LAST_SYNC_TIME with the maximum historical approval date and start incrementing from there. If you worry about retroactive edits in the source, run a small-window full refresh first, then switch to incremental.
2. One-shot full trigger: at first launch or after a major version change, temporarily switch the schedule to a one-shot run to pull every bill in the needed window. Confirm the target row count matches the source, then return to the */7 cadence.
3. Steady-state scheduling: source pulls on */7 * * * *, target writes on 3-59/7 * * * *, offset by 3 minutes. Day to day, monitor three things: the progress of the FApproveDate watermark, the uniqueness of FEntity_FEntryID on the target, and any backlog in the write queue.

Lessons Learned

• Mistake 1: Pulling only display names for master data. During one engagement, reconciliation broke because customer codes did not line up, and we had to backfill a column. The safe approach is to configure both code and name from day one: join fields use FNumber, display fields use FName. Do not wait until things break.
• Mistake 2: Dumping everything at once without phasing. When Kingdee returns tens of thousands of rows, full-page pulls combined with REPLACE INTO cause heavy primary-key contention. The safe approach is header and detail phased, validate on a small window first, then widen, and keep batch writes within 200 rows per queue message.
• Mistake 3: Implicit type conversion gone wrong. FRealQty is a float on the source but a string on the target. Without explicit formatting, scientific notation or precision truncation can break reconciliation. Format numbers explicitly at the mapping layer rather than relying on the database's implicit cast.
• Mistake 4: Wrong incremental anchor. Using FCreateDate as the incremental condition causes bills still under approval to be missed. Always use FApproveDate as the incremental boundary for Kingdee bills.
• Mistake 5: Source and target scheduled at the same minute. Concurrent reads and writes can overwhelm the Kingdee API. A 3-minute offset is a battle-tested value: source pulls first, target writes second is the most stable rhythm.

Where This Fits and Where It Does Not

It fits when ERP and the middle platform need near real-time (minute-level) landing for reconciliation or BI, and the volume is moderate enough for a flat table. It does not fit when the source has complex master-detail write-back requiring strict transactional consistency, or when downstream must preserve a true master-detail structure rather than accept repeated header fields on every row. In those cases, switch to a separate header and detail table design.