Real-Time Raw Material Inventory Sync: A Lightweight Implementation from Kingdee Cloud to MySQL

What This Strategy Solves

A manufacturing enterprise runs Kingdee Cloud alongside an in-house WMS. The production, warehouse, and procurement departments argue constantly about how much raw material is actually left: the numbers in Kingdee do not match the WMS. This is extremely common in multi-system environments.

The "Raw Material Real-Time Inventory" strategy addresses exactly that: it pushes raw-material-level inventory quantity and available quantity from Kingdee Cloud to a MySQL WMS business table at minute-level frequency, providing a single source of truth for downstream picking and kit-shortage analysis. It is a read-from-source, write-to-target sync, single-directional: Kingdee Cloud → Qeasy Integration Platform → MySQL.

Data Flow and Field Mapping

The source side uses Kingdee Cloud's executeBillQuery to query the instant inventory document. The target side is the MySQL table wms_warehouse_store_kingdee_info, written via a custom SQL INSERT ... ON DUPLICATE KEY UPDATE for upsert semantics. The middle layer, powered by Qeasy, handles fetching, transformation, and writing.

Key field mapping:

The source uses Kingdee's base-unit inventory (FBaseQty), not the packaged FQty. This must be handled carefully. Also, FStockId in Kingdee is an internal ID; it must be converted to a business code before being written, otherwise the downstream warehouse will not match.

How to Configure on Qeasy

Source action: select the Kingdee.Cloud platform, choose API executeBillQuery, tick fields FID / FStockId / FMaterialId / FBaseQty / FBaseAVBQty / FLot, etc. Add a filter to query raw-material category items only, reducing unnecessary data.

Target action: select the MySQL platform, choose API execute (WebAPI), pass bound parameters via main_params, and write the upsert SQL in otherRequest (main_sql). Use INSERT ... ON DUPLICATE KEY UPDATE with a composite unique index on part_no + stock_code to ensure re-pushes update instead of insert.

Middle layer key points: First, code mapping is centrally managed in Qeasy's field-mapping module. Warehouse internal IDs, material codes, and organization codes all live in one mapping table, so adding a new warehouse is a single edit. Second, disable autoFillResponse on the source to avoid response auto-filling interfering with debugging. Third, failed records go into the exception queue and can be re-pushed manually or via automatic retry.

Implementation Steps

Step 1, Incremental Starting Point: use Kingdee's FMODIFYDATE as the incremental cursor. Qeasy records the last sync timestamp. After the first full run, only changed data is fetched.

Step 2, Full Sync Trigger: before going live, run a one-time full sync to backfill historical inventory into MySQL. Schedule this during low-traffic overnight hours, and use batch commits rather than per-row INSERT.

Step 3, Scheduling Frequency: source cron */3 7-22 * * * (every 3 minutes during business hours), target cron */2 7-22 * * * (every 2 minutes), paused overnight. Note the target frequency should be slightly higher than the source to avoid backlog, but there is no need for seconds-level latency; minute-level is enough for picking and kit-shortage analysis.

Step 4, Validation Before Going Live: in the first week, sample three time slots per day to compare Kingdee and MySQL numbers. Confirm no omissions before handing over to business users.

Pitfall Review

First, writing the warehouse internal ID directly to the target DB. The typical shortcut is to dump FStockId straight into stock_code, but the WMS cannot interpret it and all reports are misaligned. The safe approach is to either fetch FStockId.FNumber in the source query, or add an internal-ID-to-business-code mapping in Qeasy.

Second, inconsistent units of measure. Whether Kingdee's base unit is "piece", "kilogram", or "box" depends on the material master. If the material master is not normalized, the same code can produce inventory in different units. The material master sync (a separate strategy) must be in place first; otherwise downstream receives numbers without knowing the unit.

Third, overnight cron colliding with Kingdee's daily settlement. We saw this fail on a real customer site: nightly cron hit Kingdee during daily settlement, the inventory table was locked, and the API timed out across the board. The safe approach is to constrain the schedule to 7-22 business hours and pause overnight; if 24-hour sync is required, confirm the daily settlement window first.

Fourth, primary-key conflict causing bulk-write failure. If the MySQL table uses an auto-increment ID as the primary key instead of a unique index on part_no+stock_code, ON DUPLICATE KEY UPDATE never matches and every row becomes a new insert, duplicating inventory indefinitely.

Fifth, confusing available quantity with on-hand quantity. FBaseQty (on-hand) and FBaseAVBQty (available) differ for many items. Business owners may need "available" or "on-hand"; align the semantics before mapping and do not assume.

Applicable and Non-Applicable Scenarios

Applicable: manufacturers and retailers running multiple systems that need a unified inventory view; data volume within hundreds of thousands with minute-level latency tolerance; environments with a material master sync already in place.

Not applicable: sub-second real-time inventory (requires message queues or change-data-capture); bidirectional sync where both Kingdee and the target system write inventory; dirty-data environments with unnormalized material masters or inconsistent units.