Production Warehousing Sync in Practice: Field Mapping and Pitfalls from MySQL to Kingdee Cloud

What This Strategy Solves (Scenario and Value)

Pushing production warehousing orders from a MES/WMS-style source system into an ERP is a critical step in supply chain integration. A common pain point we have seen on customer sites: once the WMS confirms an inbound production order, it must appear in Kingdee Cloud almost immediately, because that single record feeds cost accounting and downstream AR/AP. If sync is delayed or bill numbers collide, the finance team can easily end up reconciling discrepancies worth millions. This strategy does exactly one thing: reliably writes production warehousing records from MySQL into Kingdee Cloud in an incremental way, while keeping three categories of mapping rules — organization codes, dates, and bill numbers — centrally maintained so ongoing maintenance costs stay low.

Data Flow and Field Mapping (Source → Middle Layer → Target)

The MySQL source pulls a view through SQL; the Kingdee Cloud target writes through the batchSave API. The key field mapping is summarized below:

For FPrdOrgId and FStockOrgId, we use a pattern that comes up again and again on Qeasy projects: a centralized code-mapping table. Through a _function expression, source T01.01 maps to target T01.06, and T04 maps to T04. Adding a new organization later is a one-line change rather than a redeploy.

How to Configure It on Qeasy

On the Qeasy Data Integration Platform, this strategy is modeled as a single A→B sync task.

• Source metadata: type SQL, api select, primary parameter main_params carries limit/offset. The main query uses :created_at placeholders, which the platform replaces with the current scheduling window at run time. autoFillResponse=true, so field labels become response fields automatically.
• Target metadata: type EXECUTE, method POST, api batchSave, idCheck=true so that FBillNo is used for deduplication and reruns do not create dirty data.
• Scheduling: source crontab is */3 * * * *, target crontab is */4 * * * *. Source runs one beat faster than target so upstream data is ready before the write attempt.
• Runtime parameters: a sys_config row with config_id=351 holds the incremental starting date, so you can re-trigger historical data without editing the SQL.

Implementation Steps (Phased Scheduling)

1. Initialize environment and code mapping: in Qeasy's code-mapping module, maintain the delivery_org → FPrdOrgId/FStockOrgId table. Every strategy that depends on this mapping reuses the same record so the rules do not scatter across SQL files.
2. Set the incremental starting point: for the first run, set config_key of config_id=351 to the go-live date and leave config_value empty — the platform will pull records with update_time > go-live date. On the go-live day, set config_value to the go-live date. The CASE WHEN block in the SQL will then backfill the date automatically and prevent missing records.
3. Trigger full sync: temporarily enlarge limit/offset to cover the entire table and run a one-shot historical backfill. After it finishes, immediately restore the incremental configuration. We once hit an OOM here because the full load was not paginated; the safe approach is 5,000 rows per batch.
4. Enable scheduled runs: source polls every 3 minutes, target writes every 4 minutes. Under Qeasy's "header-then-entries phased" mode, the header is written first to obtain the inner bill id, then the entries are written back with that id.
5. Monitoring and reconciliation: wire "source row count vs. target write count vs. failed retries" into the Qeasy dashboard and alert on any threshold breach.

Pitfalls and Lessons Learned

1. Hard-coding organization codes in SQL means a redeploy per change. This is a classic anti-pattern. Lifting all organization mapping into Qeasy's centralized code-mapping module cut new-organization rollout time to about five minutes.
2. Source builds the prefix, target builds it again, and the numbers collide. Source builds CONCAT('RKD', id) and passes the result directly as FBillNo. Do not let Kingdee Cloud's numbering rule overwrite it, otherwise idCheck will reject duplicate numbers.
3. Hard-coding the incremental starting point as 2025-01-01. We once ran for six months without noticing that old data was being re-pulled. The reliable pattern is a sys_config row plus a platform variable, shared across all strategies as a single watermark table.
4. batchSave rolls back the entire batch on any entry failure. This is a Kingdee Cloud quirk: one bad row kills the whole batch. We turned on Qeasy's "per-row graceful degradation" mode: failed rows go to an error queue for later manual or automatic retry, and they no longer block the rest.
5. Date field type mismatch. Source is DATE, target expects STRING. Without DATE_FORMAT, the value carries a time component and Kingdee Cloud rejects it with "invalid date format". Format every date in SQL to YYYY-MM-DD; do not leave type conversion to the target.

Suitable and Unsuitable Scenarios

Suitable: discrete manufacturers with multiple organizations and plants, where production warehousing must flow from MES/WMS into ERP in near real time, and organization codes need centralized maintenance. Unsuitable: flows that require complex approval routing, need entries split by process, or require strong validations on the Kingdee Cloud side (e.g., mandatory cost center). Those flows need pre-cleaning rather than a simple sync.