Real-Time Raw Material Inventory Validation: A Practical Guide to Stock Reconciliation Between MySQL and Kingdee Cloud

What Problem This Strategy Solves

In discrete manufacturing and raw-material-intensive enterprises, reconciling real-time inventory between WMS/MES and ERP is a chronic pain point. In one real project, a manufacturer spent three person-days per month on manual reconciliation, with discrepancies traced back to three recurring issues: material code mapping, warehouse master keys, and batch dimension alignment. This strategy focuses on high-frequency raw material inventory reconciliation, aiming to compress daily manual checks into minute-level automated validation.

Data Flow and Field Mapping

The data path follows a write-back validation pattern: Kingdee Cloud → Qeasy Integration Platform → MySQL. Real-time ERP inventory is pulled first, then written to the wms_warehouse_store_info table in an intermediate database for MES-side variance comparison.

Key point: idCheck=false means Kingdee side does not enforce strict primary-key validation; positioning relies on the composite key of material + warehouse + batch.

How to Configure on Qeasy

In the Qeasy Data Integration Platform, this strategy requires two metadata configurations:

• Source (Kingdee Cloud): API is executeBillQuery, method POST, number=FMaterialId_FNumber, id=FID. The typical approach is to treat it as a data-source view, pulling changes in incremental time windows.
• Target (MySQL): API is execute, method POST, where otherRequest.main_sql is a named-binding UPDATE statement:

update wms_warehouse_store_info
set kingdee_stock=:stock_numb
where part_no=:part_no and company_code=:company_code

main_params is an object binding the three variables :part_no, :stock_numb, and :company_code. idCheck=true means the update must hit at least one row, otherwise it errors into the retry queue — a critical safety net.

Implementation Steps

1. Initial Full Load: Stop incremental scheduling and run a one-shot script to fully sync FBaseQty for all warehouse-material pairs. We strongly recommend validating in a test set of books before switching to production.
2. Define the Incremental Starting Point: Since Kingdee does not natively expose an "inventory last-modified timestamp," we recommend two patterns:
   • Introduce an intermediate table kd_inventory_changelog, written by triggers or scheduled jobs with the latest update time;
   • Use a hash digest of FBaseQty and only sync on detected change.
3. Schedule Frequency: Source crontab is 5,15,25,35,45,55 7-22 * * *, target offset by 3 minutes at 8,18,28,38,48,58 7-22 * * *. This staggered timing avoids the race condition where a write-back has not yet landed before being overwritten.
4. Alerts and Replay: Strategy-level retries plus failure email alerts are the most common safety net we see at customer sites. For inventory-class critical paths, we recommend "alert after 3 failures + manual confirmation before replay."

Lessons from the Field

1. Hardcoded mappings in SQL: A typical mistake is embedding part_no directly in main_sql, forcing SQL changes every time a new warehouse is added. The stable approach is to use Qeasy's centralized code-mapping table — adding a new warehouse is just one new row.
2. Inadvertently disabling idCheck=true: Many customers turn off idCheck on first configuration, which silently updates 0 rows and lets variance grow. Inventory strategies should always keep it on.
3. Pulling the full table without pagination: Using executeBillQuery without pagination is a disaster — one customer pulled 800k rows in a single call and exhausted Qeasy's memory. Add a Top parameter and paginate by warehouse.
4. Timezone and date alignment: Mixing UTC and local time between Kingdee timestamps and MySQL update timestamps leads to "nothing changed but it keeps syncing." Unify both sides on local timezone and include update_time=NOW() in the SQL.
5. Always stagger the schedule: Triggering source and target in the same minute delays write-back detection by a full cycle. A 3-minute offset is the simplest effective defense.

When to Use and When Not to Use

Best suited for high-frequency reconciliation of raw materials, semi-finished goods, and subcontracted items in private deployments where ERP and MES/WMS live in separate sets of books. Not suited for transactional-strong-consistency inventory scenarios (prefer direct API calls or distributed transactions), nor for sub-minute real-time requirements — 10-minute granularity is already the practical ceiling for this design.