Customer-Warehouse Real-Time Inventory Sync in Practice: One Strategy from Kingdee Cloud to MySQL Reporting

What This Strategy Solves

In customer-warehouse operations, sales, procurement, and finance all ask the same question every day: how much of a given material is currently in the customer warehouse? The numbers must be fresh, yet they must not put load on the live ERP. The source is Kingdee Cloud's real-time inventory ledger; the target is a MySQL reporting database that downstream BI, reconciliation, and order tracking all read from. This single strategy does exactly one thing: it incrementally loads customer-warehouse real-time inventory from Kingdee Cloud into a staging table in MySQL — insert on new, update on change, and never physically delete a record (the "do not delete FF100003" rule in the strategy name is a hard project convention).

Data Flow and Field Mapping

The pipeline is unidirectional: Kingdee Cloud (executeBillQuery) → Qeasy integration platform middle layer → MySQL reporting DB (WebAPI writing SQL).

On the source side, key fields from Kingdee Cloud's inventory query API:

The target is a single MySQL table (illustratively named kingdee_STK_Inventory), written via an INSERT statement. Dimensions such as FMtoNo / FStockStatusId / FStockLocId / FStockUnitId / FStockOrgId / FOwnerId are not carried by this strategy — they are maintained by a separate upstream strategy. This strategy must not overwrite them — a boundary repeatedly emphasized on the customer site.

How to Configure It on Qeasy

We use the Qeasy data integration platform to host this pipeline. Three configuration points deserve attention:

1. Source metadata: API executeBillQuery, method POST, autoFillResponse on, the ID field is FID, idCheck off — inventory has no "bill number" concept, so using FID as the idempotency key is safer.
2. Target metadata: API is the custom WebAPI execute. The main parameter is main_params carrying bound variables, and main_sql holds the INSERT. Note the idCheck behavior differs between source and target: on the target it must be true, so Qeasy maintains batch and dedup by FID, avoiding duplicate writes within one run.
3. Centralized code mapping: For master-data codes like FStockId / FMaterialId, manage them through Qeasy's mapping tables. On the customer site we were bitten by hard-coded values scattered across strategies: two months later we changed one place and forgot another, and the two sides drifted apart. Centralizing mappings means adding a new warehouse six months later only requires one edit.

Implementation Steps

The safest rollout follows three phases:

• Phase 1: incremental baseline. Use a one-time full load to seed the MySQL table; subsequent schedules only process FID > last_max_id. We agreed to run at 12:15 and 18:15 daily, with the source cron at 10 12,18 * * * and the target delayed by 5 minutes to leave a fetch-and-transform buffer.
• Phase 2: full reload triggers. Only run a full reload when Kingdee Cloud re-initializes inventory, a new customer warehouse type is added, or the table schema changes. Repeated full reloads under normal operation are forbidden — they will thrash downstream BI reports.
• Phase 3: schedule tuning. Customer-warehouse stock turns faster than finished-goods stock (sales issue cadence is tight). If available-quantity lag is observed, insert an extra 14:00 run. The dual-track pattern — incremental daily, full reload at month start for reconciliation — is a common Qeasy customer pattern.

Pitfalls from the Field

Several typical pitfalls surfaced during deployment:

• ON DUPLICATE KEY UPDATE was initially left on — the safe approach is to disable it, because any source-side duplicate will overwrite downstream manually-maintained dimension fields. We later moved to pure INSERT plus a separate UPSERT strategy.
• Empty FLot strings caused SQL type errors. Handle them in Qeasy field cleaning by normalizing empty strings to NULL, or use NULLIF(:FLot,'') directly in main_sql.
• The "do not delete FF100003" rule was almost bypassed. If a record disappears from the source result set, Qeasy's default behavior can drop it from the target, leaving an orphan row. The correct fix is to disable "delete from target when missing on source" on the target configuration; it is insert-and-update only.
• Mixing customer warehouses with self-owned warehouses in one table requires pushing the warehouse filter down to the source. Otherwise all warehouses get loaded and downstream filtering on FStockId becomes slow. We fixed the warehouse-type filter on the source SQL itself to offload the database.
• Cron offset issues. Source at 12:10, target at 12:15 looks safe, but Kingdee occasionally responds slowly and tasks spill into the next window. The safe pattern is to add a "completion-time check" on the target side and alert if a run exceeds 18:30, so tasks never pile up overnight.

When This Strategy Applies and When It Doesn't

It applies when downstream systems (BI, reconciliation, dashboards) need frequent queries against customer / consignment / VMI warehouse stock, but the live ERP should not be exposed directly. It also applies when an inventory snapshot must be persisted into a reporting database. It does not apply to scenarios requiring sub-second, strongly-consistent inventory (e.g., picking terminals), nor to physical inventory initialization or frozen-stock accounting — those should use Kingdee Cloud's native inventory transactions and are out of scope here.