Practical Tutorial: Syncing Kingdee Cloud Master Material Data to MySQL

What This Strategy Solves

Master material data is the "root" of supply chain integration. In a private deployment of a manufacturing enterprise, Kingdee Cloud Sky serves as the ERP master data source, while MES, SRM, and in-house business platforms consume a material mirror table in MySQL. When a material is added or modified on the Kingdee side, downstream systems fail to receive timely updates, causing BOM lookup failures, mismatched procurement specifications, and workshop picking errors. This strategy implements "Kingdee Cloud Sky → MySQL" material add/modify sync, anchoring the root firmly in the ERP.

Data Flow and Field Mapping

The overall pipeline is Kingdee Cloud Sky (QUERY) → Qeasy Integration Platform middleware → MySQL (WebAPI EXECUTE). The Kingdee side uses the executeBillQuery API to pull incremental materials by FNumber; the middleware performs field mapping, unit/category conversion, and code cleaning; the MySQL side executes an INSERT … ON DUPLICATE KEY UPDATE statement into basic_material_info to upsert records.

Key field correspondence (source → target):

The recommended practice is to centralize this mapping in Qeasy's "Field Mapping" configuration rather than scattering logic across scripts.

How to Configure on Qeasy

1. Source (Kingdee Cloud Sky): Create a new source platform connection, select executeBillQuery, pull incrementally by ascending FNumber, and enable pagination with a single-fetch cap to avoid pulling the entire material table at once.
2. Target (MySQL): Create a WebAPI-type writer, HTTP Method=POST, effect=EXECUTE; main_params carries field values, main_sql uses INSERT … ON DUPLICATE KEY UPDATE to write into basic_material_info, with the unique key set to material_info_no.
3. Strategy orchestration: Link source and target into a SYNC strategy, sequence=A indicates the active pull direction; schedules */2 * * * * (source) and */3 * * * * (target) are staggered by minutes to reduce burst concurrency.
4. Parameterization: Place group, unit, brand and other code values in Qeasy's "Dictionary Mapping" component for one-to-one source-to-target maintenance; adding a new group only requires updating the dictionary, not the main flow.
5. Response handling: Enable autoFillResponse so the platform auto-fills the write response into the context for idempotency checks.

Implementation Steps

We typically break material sync into three phases:

• Phase 1: Incremental Starting Point. Confirm the most recent material modification timestamp or max FMasterId in the source as the initial starting point for the first incremental run. This avoids the embarrassment of "only 5 records on day one."
• Phase 2: Full Refresh Trigger. Add a "full rebuild" switch in the strategy; normally inactive, manually triggered when material group structure changes or dictionary upgrades occur, performing a complete comparison.
• Phase 3: Scheduling Frequency. In production, the source runs */2 * * * * and the target */3 * * * *, staggered; during off-hours, frequency can drop to */10 * * * * to save database pressure.

The dual-track of incremental and full refresh is our standard for material sync: daily incremental keeps data current, periodic full refresh serves as a safety net.

Pitfall Review

1. Code mapping scattered across scripts. A typical mistake is writing a JS conversion for every field; three months later, nobody remembers the rules. The robust approach is to centralize all code-value mappings in Qeasy's dictionary table with naming like src_group → tgt_group.
2. idCheck not enabled. The MySQL target defaults to idCheck=true, which checks existence by material_info_no before writing; do not disable it for convenience, or ON DUPLICATE KEY will fail.
3. Full pull in one shot. When the source executeBillQuery has no pagination parameters, all materials are pulled into memory at once. A retail enterprise's first launch overwhelmed Qeasy workers as a result. Always set pagination and batch limits.
4. Unit/group code values not aligned. Kingdee's "base unit" and the downstream MySQL unit_no follow different naming conventions; a unit mapping table must be prepared in advance, or materials with the same spec but different units will be incorrectly merged.
5. Schedule collisions. Running material sync, sales order sync, and purchase order sync in the same 5-minute window causes periodic IO spikes on the source database. Staggered design is fundamental discipline.

Applicable and Non-Applicable Scenarios

Applicable: ERP serves as the master data source, multiple downstream business systems need a material mirror, and the data volume is within hundreds of thousands of records, typical for manufacturing or retail enterprises. Not applicable: Scenarios requiring bidirectional material editing sync, source data volume in the millions, or second-level real-time requirements—these should use event streams or a dedicated MDM platform instead.