Production Return Material Sync in Practice: Incremental Configuration from MySQL to Kingdee Cloud and Lessons Learned
What This Strategy Solves
In a manufacturing enterprise, production workshops generate large volumes of return material orders every day: when a workshop discovers over-issued or wrong materials, those materials must be returned to the warehouse. Once the MES on the source side writes to MySQL, finance and warehouse reconciliation depends on Kingdee Cloud. If the two ends are not connected, inventory falls into the awkward situation of "on the books but not in reality." This strategy does something straightforward: it pushes the new return material order data from MySQL into Kingdee Cloud on a scheduled cadence, generating the corresponding return material documents.
Data Flow and Field Mapping
The overall flow follows a three-stage pattern: MySQL → Qeasy data integration platform (intermediate layer) → Kingdee Cloud. The source side pulls data through SQL, the intermediate layer cleans and maps, and the target side performs batch writes via batchSave.
| Business Meaning | MySQL Source Field (Intermediate Variable) | Kingdee Cloud Target Field | Notes |
|---|---|---|---|
| Document Number | 单据编号 (concatenated from MSCTL + date + iface_id) | FBillNo | Encoding rules are generated centrally in the intermediate layer |
| Document Date | 单据日期 (taken by accounting period) | FDate | Uses accounting period table mt_account_period |
| Issue Order Primary Key/Number | 生产领料单主键, 生产领料单号 | Body association fields | Used to trace back to source |
| Production Order Number/Line | 生产订单号, 生产订单行号 | Body production order fields | Split via _ |
| Material Code | 物料编码 | Body material | Encoding mapping managed centrally |
| Return Quantity | qty | Body quantity | Numeric field |
| Return Reason | return_reason | Body remark | Text field |
| Warehouse | fn_num | Body warehouse | |
| Production Workshop | Department → workshop mapping (case when) | FWorkShopId | Department code to workshop code |
| Production Organization | manufacturing_site_code | FStockOrgId, FPrdOrgId | Receiving org and production org share the same source |
| Plan Tracking Number | tracking_num (empty when T02) | Body tracking number | Conditional assignment |
It is worth emphasizing: for encoding mappings we adopt a "centralized management" approach. Writing a case when for department-to-workshop directly in the source SQL seems convenient, but when the workshop changes you must modify the SQL. A more reliable approach on Qeasy is to push such mappings down into the platform's dictionary table, maintained by operations, while the SQL only performs the match.
How to Configure on Qeasy
Source side (Source) node:
- Platform: MySQL, API type
select, request method POST, effect set toQUERY. - Write the main query into
main_sql. Key filter conditions:iface_type = 'RETURN',status in ('N','E'),iface_sequence = 1, source tenant filtering, deduplicated byt1.iface_id. - Critical filter:
and t5.wo_input_record_id > 0 and t5.attribute15 <> ''— this is the necessary condition linking the document header to the issue order. It is mandatory. - Pagination parameters
:limitand:offsetare auto-injected by the intermediate layer; the initial full pull relies on this pagination.
Target side (Target) node:
- Platform: Kingdee Cloud, API
batchSave, effectEXECUTE, method POST. FBillTypeis hardcoded asSCTLD01_SYS, which is Kingdee's standard document type for production return material orders.- Document header fields use
{{variable name}}to reference intermediate layer results, e.g.,{{单据编号}},{{生产组织}}. - Turn on
idCheck; the platform deduplicates byFBillNoto prevent duplicate postings.
Intermediate layer: Qeasy automatically generates field mappings, type conversions, and variable bindings. The only places that require manual intervention are workshop mapping, organization code, and date format.
Implementation Steps
We typically follow a "full first, then incremental" dual-track rollout.
Stage 1: Full baseline. The source main_sql carries no time condition, letting the intermediate layer paginate through all historical data. The target crontab is temporarily set to 0 */1 * * * (hourly) to avoid one-time large-batch pressure on the Kingdee interface. After the full run, reconcile document number counts on both sides.
Stage 2: Switch to incremental. Append a time window condition at the end of the source SQL (e.g., creation_date > last max time), and switch the pagination starting point to the "last cursor." This step is usually done by enabling "Incremental Configuration" in the intermediate layer, and the platform records the cursor for you.
Stage 3: Finalize scheduling frequency. Source crontab is set to */5 * * * *, pulling every 5 minutes; target crontab is set to */1 * * * *, executing writes every minute. The source frequency is lower than the target, so the target side can "accumulate a batch and write a batch," improving throughput.
Stage 4: Fault tolerance and retry. Qeasy retries failed tasks by default. We additionally configure Kingdee's "document number already exists" as an idempotent success to prevent duplicate pushes.
Lessons Learned
First, putting department-to-workshop mapping in the SQL. A typical mistake is that when a new workshop is added, one place in the SQL gets missed, causing the production workshop to be blank on some return material orders. A reliable approach is to extract the mapping into Qeasy's dictionary table, and only do a join in the SQL — change one place, takes effect everywhere.
Second, the date was not taken through the accounting period. Using creation_date directly causes issues at month-end: return material orders crossing the accounting period get incorrectly attributed to the next month. We use aft_quiet_time from mt_account_period as a boundary: dates before the period cutoff use the period date, otherwise the original date is kept.
Third, omitting the filter t5.attribute15 <> '' in the source SQL causes return material orders without an upstream issue order number to also be pushed, and the Kingdee side reports an error directly. This is a hard constraint linking return material and issue orders.
Fourth, the production order number concatenation was changed. We used to use SUBSTRING_INDEX(t1.wo_number, '_', 1) to take the first half, but after a source system upgrade the format of wo_number changed and the order number field became empty. It is recommended to add a layer of regex validation in the intermediate layer, so anomalies trigger an alert directly rather than being silently written.
Fifth, crontab went live without phased rollout, and the first full run knocked the Kingdee interface offline. Phased rollout of header and body is critical: first bring up the header mapping and get document creation working, then append body fields such as material, quantity, and warehouse.
Applicable and Non-Applicable Scenarios
Applicable: "new document" type syncs such as production return, issue, and replenishment in MES-to-ERP links with stable data volume and clear field structure. Non-applicable: scenarios requiring complex approval flows or state-machine round-trips (such as writing back to MES after return material review), and high-frequency IoT writes that need real-time per-record push (in those cases a message queue is recommended over scheduled polling).