Kingdee Sales Outbound to MySQL Sync: A Single-Strategy Tutorial

What This Strategy Solves

Retail and e-commerce businesses commonly run a dual-track architecture: Kingdee Cloud as the financial and supply chain system of record, and MySQL as the warehouse for e-commerce workloads. Once a sales outbound document is approved in Kingdee, downstream e-commerce platforms, logistics, and reporting tools need consistent entry-level data as soon as possible. This strategy uses a flat, single-table channel to sync every entry from SAL_OUTSTOCK into MySQL, supporting both real-time incremental sync and historical full loads.

Data Flow and Field Mapping

The flow is unidirectional: Kingdee Cloud → Qeasy middle layer → MySQL. The middle layer flattens Kingdee object fields (such as FCustomerID.FNumber and FSaleOrgId.FName) into target columns, using the entry ID as the primary key when writing to the target table tp_jd_out_stock.

Kingdee's executeBillQuery returns a flat structure: each row is one entry, with header fields repeated on every row. This is the key design premise — the target side does not need header-entry separation, and the entry row ID is enough as the unique dedup key.

How to Configure on Qeasy

On customer sites we usually split this strategy into three layers: source, middle, and target. The source uses Kingdee Cloud's executeBillQuery, with FormId = SAL_OUTSTOCK, method POST, and the request body using a FilterString like FModifyDate>='{{LAST_SYNC_TIME|datetime}}' or FApproveDate>='{{LAST_SYNC_TIME|datetime}}' or FCancelDate>='{{LAST_SYNC_TIME|datetime}}' — matching any of the three timestamps triggers a pull, so no change is missed.

The middle layer is the field mapping. A common pattern we see with Qeasy customers is to land both FName and FNumber for object fields: codes for cross-table joins and traceability, names for human-readable reports. Extension base data fields such as F_PRSH_Base_re5 and F_PRSH_Base_83g follow the same rule. No scripts are required — everything passes through with {{field}} template variables.

The target uses MySQL's batchexecute, with the id key enabled for idCheck. The write SQL uses REPLACE INTO tp_jd_out_stock (...) VALUES (...), deduping on FEntity_FEntryID for upsert behavior. Batch size is held to 200 rows per write to avoid overloading MySQL with large transactions.

Implementation Steps

Step 1: Define the incremental starting point. Set LAST_SYNC_TIME in the strategy metadata. For the first full load, give it a timestamp earlier than the business go-live date so that all three time fields match.

Step 2: Configure schedules with offset windows. Source */7 * * * *, target 3-59/7 * * * * — source pulls run three minutes before target writes, so the data the source just read is already materialized in the middle layer when MySQL writes happen, avoiding the illusion of an empty result.

Step 3: Full load first, then dual-track incremental. On go-live day, manually trigger a full backfill to confirm the entry count matches Kingdee, then switch to the scheduled incremental flow. After that, the system runs purely on the incremental filter.

Step 4: Bring monitoring online. On Qeasy, watch the ratio of source pulled rows, target written rows, and REPLACE affected rows — this is the fastest way to localize issues.

Pitfalls We Have Seen

1. Object fields landed without the dual-column pattern. An early version only stored FName, leaving downstream unable to do code-based joins and forcing a backfill. The safe approach is to land both code and name for every base-data field, and to add _FNumber / _FName columns up front.
2. Forgetting FCancelDate. Only watching FApproveDate is not enough — cancellation in Kingdee is a separate action. Cover modify, approve, and cancel times in the incremental filter, otherwise cancelled documents linger downstream.
3. Pagination size not tuned. A default Limit that is too large triggers Kingdee timeouts; too small leads to many round trips. On Qeasy, expose PAGINATION_PAGE_SIZE and PAGINATION_START_ROW as template variables so they are easy to tune.
4. Using INSERT and hitting duplicate key errors. In incremental scenarios the same entry is pulled repeatedly. You must use REPLACE INTO or INSERT ... ON DUPLICATE KEY UPDATE, otherwise the job fails as soon as it runs.
5. Coarse transaction granularity on the target. Writing thousands of REPLACE rows at once locks the table. Keep batches to 200 rows so MySQL can stay concurrent.

When to Use and When Not to Use

Use when: e-commerce mid-platforms, retail stores, or reporting platforms need flat, entry-level sales outbound data, the system of record is Kingdee Cloud, and the warehouse is MySQL. Do not use when: header-entry structure must be preserved, you need to write back to Kingdee at the document level, or the downstream is not MySQL — those scenarios call for a separate strategy.