Sales Order Header Status Refresh: A Lightweight Bi-Directional Sync Between MySQL and Kingdee Cloud

What This Strategy Solves

In a private deployment where MES and ERP coexist, once a sales order is approved, closed, or voided on the Kingdee Cloud side, the MES front-end still shows "created". Business users then keep comparing the two tables by hand. We use the "Sales Order Header Status Refresh" strategy to write back only a handful of status fields rather than mirroring the entire order. This keeps business visibility on the MES side while minimizing interface pressure and conflict surface.

Data Flow and Field Mapping

The flow is one-way writeback: Kingdee Cloud → Qeasy → MySQL (the MES-side order header table).

Business Meaning	Kingdee Cloud Field (Source)	MySQL Field (Target)	Notes
Bill Number	FBillNo	so_number	Unique key for locating the row
Internal FID	FID	KINGDEE_ID	Used for secondary verification
Document Status	FDocumentStatus	DOCUMENT_STATUS	A created / B approved
Close Status	FCloseStatus	CLOSE_STATUS	A open / B closed
Closer	FCloserName	CLOSER_NAME	Nullable
Close Date	FCloseDate	CLOSE_DATE	Nullable
Cancel Status	FCancelStatus	CANCEL_STATUS	A not voided / B voided
Canceller	FCancellerName	CANCELLER_NAME	Nullable
Cancel Date	FCancelDate	CANCEL_DATE	Nullable
Sync Flag	Derived	SYNC_FLAG	1 synced, for troubleshooting
Business Date	FDate	DATE	For traceability

How to Configure It on Qeasy

In the Qeasy data integration platform, the source action for this strategy is Kingdee Cloud's executeBillQuery, and the target action is MySQL's execute (a WebAPI that runs SQL).

Source side: Use executeBillQuery on Kingdee Cloud to pull sales orders whose status changed in the last cycle. The typical filter targets changes in FDocumentStatus or FCloseStatus, rather than a full scan of approved orders. Only status-related fields are selected in the request to keep the row count low per run.

Target side: A parameterized update statement keyed on so_number = :fbillno writes the status fields back to the MES order header table in one shot. We use a "single SQL for the header" pattern instead of splitting header and body into stages, on purpose: keep the strategy thin and stable. When line-level changes are truly required, build a separate strategy for that.

For code mapping, a common Qeasy pattern is "centralized code mapping": master data such as sales organizations and customers is kept in a mapping table. This strategy only cares about status fields and avoids stuffing too many case when expressions into the SQL.

Implementation Steps

Step 1, set the initial full-sync baseline. At go-live, run a full trigger once so that historical orders are flushed to a consistent state, then verify the numbers on both sides match. In Qeasy, this initial run is usually triggered manually.

Step 2, switch to incremental. The source side uses the last modified time of FModifyDate or the status fields as a cursor, which Qeasy maintains automatically. The target side only updates rows that have actually changed.

Step 3, set the schedule frequency. The source side runs every 10 minutes (7,17,27,37,47,57 * * * *), and the target side runs 1 minute later (8,18,28,38,48,58 * * * *) to leave a small landing window for the source query. This is a typical "full + incremental dual-track" arrangement: full sync as a safety net, incremental as the workhorse.

Step 4, monitoring and reconciliation. The Qeasy console reports success/failure/retry counts, and on the business side a three-way reconciliation (ERP status, Qeasy log, MySQL field) is performed on 5 random orders every week.

Lessons Learned

Wrong unique key. Early on we used FID as the unique key, but later when the MES side introduced archive tables we found that FID can be reused. Switching to so_number made it stable. The safe rule: for status writeback, always key on the business document number.
Inconsistent status semantics. Kingdee Cloud's FCloseStatus and FCancelStatus are independent dimensions and cannot be merged into a single "valid/invalid" field. If the MySQL side only has one column, expand the dimension in the mapping layer rather than dropping it.
Wrong write mode. We started with replace into and watched the same order repeatedly overwrite its history. For status refresh, update is always preferable to insert/replace because its semantics are "stick the current state on top," not "rebuild a row."
Schedule interval too short. When the source side ran every 1 minute, the executeBillQuery calls on Kingdee Cloud queued up during peak hours. After moving to every 10 minutes, both latency and interface pressure dropped. Ten-minute granularity is plenty for low-frequency changes like status.
Skipping the sync flag. The SYNC_FLAG field looks redundant, but it has saved us many times during incident analysis — a quick check reveals which order was written in which cycle.

When It Fits and When It Does Not

Fits: ERP and MES run in parallel and the MES side needs minute-level visibility into the order's approve/close/void status; line items are stable and only the header status changes.

Does not fit: order lines change frequently; full bi-directional sync between the two systems is required; or the ERP side uses a complex workflow where a document goes through multiple status rollbacks — in that case, an event-driven real-time approach is preferable to scheduled polling.