Sales Return Order Audit Verification: A Practical MySQL to Kingdee Cloud Sync Solution
What This Strategy Solves
After a sales return order is approved in WMS/MES, the "approved" status needs to be written back to the ERP as the basis for subsequent settlement and inventory reduction. The on-site situation at a retail enterprise was: the return order in Kingdee Cloud is first approved on the MES side, and MES writes the approval result into an interface table in its own MySQL database. When the ERP side polls, it frequently misses orders or pulls duplicates, causing the approval status to be inconsistent on both sides. We used the Qeasy Data Integration Platform to build an "audit verification" strategy that specifically targets records that are approved in MES but not yet updated in Kingdee, performing a directed writeback.
Data Flow and Field Mapping
The overall flow is: Kingdee Cloud (as the query source) → Qeasy → MySQL (as the writeback target). Note that the source/target here are reversed from the usual "master data push" — the source side is only responsible for "telling the middleware which orders need verification," the middleware pulls the latest approval date from Kingdee, and then updates the corresponding records in the MySQL interface table to "effective."
Key field mapping:
| Role | Field | Source | Description |
|---|---|---|---|
| Document number | fbillno | Kingdee Cloud | Unique return order ID, cross-system comparison key |
| Primary key ID | fid | Kingdee Cloud | Internal document primary key, used for idempotency |
| Inventory org | FStockOrgId | Kingdee Cloud | Distinguishes accounts in multi-org scenarios |
| Approval date | FApproveDate | Kingdee Cloud | Incremental starting point, scheduling basis |
| Instruction doc no. | instruction_doc_num | MySQL interface table | Compared with fbillno |
| Status field | status | MySQL interface table | Target field, set to 'A' by middleware |
| Return order flag | return_order | MySQL interface table | Distinguishes order types, prevents mis-update |
The source uses executeBillQuery to query incrementally by approval date, and the target uses execute to call a WebAPI executing an UPDATE with named parameters.
How to Configure on Qeasy
Source configuration points: In Qeasy, set Kingdee Cloud as the "query source," choose executeBillQuery (POST) for the API, and declare fbillno, fid, FStockOrgId.fnumber, FApproveDate as return fields in the request body. Put the pagination parameter Limit in otherRequest, and it is recommended to stabilize at 2000~5000 first before tuning. Note that idCheck should be left as false, because deduplication here is by document number, not by primary key idempotency.
Target configuration points: On the MySQL side, use execute to call a custom WebAPI, passing in two blocks: main_sql and main_params. main_sql is an UPDATE that sets status to 'A' with the condition instruction_doc_num=:fbillno and return_order='5' and status not in ('S','A') — the third condition is critical, preventing already-processed records from being overwritten.
For code mapping, a common practice among Qeasy customers is to maintain the fbillno ↔ instruction_doc_num mapping centrally in the Qeasy mapping table, along with the org code FStockOrgId.fnumber. This way, when the MES interface table structure changes, only the mapping is updated, not the strategy itself.
Implementation Steps
Phase 1: Full initialization. Run all approved return orders in Kingdee at once (push back a full month by business date) to smooth out historical differences. This round has no incremental starting point; Limit can be set to 5000, and after running, manually reconcile quantities on both sides.
Phase 2: Incremental switchover. Add the source query condition FApproveDate >= last successful execution time, and switch to incremental mode from then on. It is recommended to store the last execution time in Qeasy's global variable or database status table, rather than relying on Kingdee's server time, to avoid timezone drift.
Phase 3: Scheduling frequency go-live. Source */10 * * * * (every 10 minutes), target */2 * * * * (every 2 minutes). The source frequency is lower because Kingdee queries have performance overhead, while the target frequency is higher because the UPDATE itself is light and the status needs to be written to the database as soon as possible. This "slow source, fast target" asymmetric rhythm is very typical in Qeasy customer supply chain integrations.
Phase 4: Anomaly order rerun. The Qeasy platform comes with built-in failure retry and breakpoint resume, but orphan orders where fbillno does not exist on either side need to be manually pulled out for reconciliation. It is recommended to run a reconciliation report every morning.
Lessons Learned
Pitfall 1: Treating idCheck as an idempotency switch. During the first configuration, we set idCheck to true, but the fid returned by Kingdee had no corresponding field on the MySQL side, and the whole flow broke directly. The safer approach is: in writeback scenarios like audit verification, keep idCheck as false, and let idempotency be guaranteed by where status not in ('S','A') in the SQL.
Pitfall 2: Timezone inconsistency on approval dates. Kingdee server time and MySQL database time differed by 8 hours, and the calculated incremental starting point was always off by half a day, causing orders to be either missed or duplicated. In practice, let Qeasy's global time variable be stored uniformly in UTC, and the target converts to local time when writing to the database.
Pitfall 3: Hardcoding the return_order constant. return_order='5' was the business's standard at the time, but later the customer added a "pre-return" order type with a changed numbering system, and the old strategy treated pre-return orders as normal returns and updated them. The standard approach is to put this value into Qeasy's constant table, so that the business only needs to change it in one place.
Pitfall 4: Forgetting rate limiting during full initialization. The first full initialization had no pagination, and pulling 80,000 orders at once crashed Kingdee's query interface. Later, we added Limit=2000 on the source side, and the Qeasy-side concurrency was also compressed to 1. It ran slower but was stable.
Pitfall 5: Success status semantics misalignment. Whether Kingdee's "approved" and the MySQL interface table's 'A' are the same thing requires written confirmation from the business. We have seen a project where what the business considered "approved" was another field in Kingdee, and everything synced was wrong.
Applicable and Non-Applicable Scenarios
Applicable: MES/WMS approves first, ERP updates status later; return order volume is in the tens of thousands or below; single organizational dimension. Not applicable: Kingdee's approval flow itself has branches (partial approval, reject then re-approve); order volume is in the hundreds of thousands requiring sharding; need to write back approval details (approver, comments) — this strategy only writes back status, not details.