Practical Guide: Incremental Pull of Purchasing Price Lists from Kingdee Cloud Cosmos to MySQL Using Qeasy

What This Strategy Solves

In one manufacturing group's supply chain landscape, the purchasing price list lives entirely in Kingdee Cloud Cosmos — price objects, tax-included unit prices, effective/expiry dates, and audit/forbid status are all maintained by business users in the ERP. Downstream profit reporting, cost accounting, and marketing rebate calculations run on a MySQL data warehouse, and the business team has to reconcile the two sides every day.

What looks like a simple "pull by bill number" hides three real pitfalls: the price list entries keep changing by modification time, currency and organization are reference fields that need a second lookup, and audit status vs. forbid status must be handled separately — otherwise, invalidated prices leak into costing.

We use the Qeasy data integration platform to host this pipeline: the source side queries the price list via executeBillQuery in Kingdee Cloud Cosmos, and the target side lands in a MySQL staging table (kingdee_inter_oganization_price_new). The strategy runs every 10 minutes, with incremental conditions keyed off the last modification time.

Data Flow and Field Mapping

The overall flow is "Kingdee Cloud Cosmos → Qeasy runtime → MySQL". On the source side, executeBillQuery is used to query the price list header and entries; results land in the Qeasy runtime where field renaming, null normalization, and reference-field resolution happen; finally, an INSERT ... ON DUPLICATE KEY UPDATE writes into MySQL.

Key field mapping (abbreviated):

We deliberately let FItem IDs flow into the staging table and let the downstream reporting warehouse resolve foreign keys — this is a common Qeasy customer pattern of "foreign keys down, dimensions up", avoiding frequent wide-table recomputation.

How to Configure on Qeasy

In the Qeasy data integration platform's strategy designer, choose Kingdee Cloud Cosmos as the source and MySQL as the target, with strategy type "Query + Write".

Source-side configuration highlights: API = executeBillQuery, method = POST, effect = QUERY. Enable autoFillResponse: true so the platform auto-expands the response fields, saving manual mapping. Bind number to FBillNo and id to FMaterialId as the uniqueness write-back keys. Set idCheck: false here — the entry PK FEntity_FEntryID may re-trigger during incremental sync; we let the target-side upsert handle deduplication.

Target-side configuration highlights: API = WebAPI execute, method = POST. The main param main_params uses Qeasy's placeholder mechanism; main_sql is a named-parameter SQL: INSERT INTO ... ON DUPLICATE KEY UPDATE .... Named parameters map one-to-one with source fields; the platform binds them before execution.

Qeasy follows a pattern of centralized code mapping management: all organization, currency, and vendor FNumber → MySQL dictionary mappings live in one platform mapping table, so reference fields are resolved in one shot instead of being re-maintained in every strategy.

Implementation Steps

We usually proceed in three phases:

Phase 1 — Full baseline. Pull every active, audited, non-forbidden price list entry from Kingdee Cloud Cosmos in one shot to seed the MySQL table. Trigger manually once, then reconcile: count rows by FBillno, sample-check FTaxPrice and FEntryEffectiveDate, and confirm consistency.

Phase 2 — Incremental cursor. After the full load completes, switch the incremental filter to FModifyDate > last_sync_time. The first incremental cursor equals the moment the full load finished; after that, update the cursor on every successful run. Store this timestamp as a Qeasy platform variable — never hard-code it in SQL.

Phase 3 — Scheduling cadence. Set the source strategy crontab to */10 * * * * (every 10 minutes) and the target-side write strategy crontab to */3 * * * * — this is the typical Qeasy customer pattern of dual-track incremental and full: high-frequency incremental pulls on the source side, even higher-frequency short-cycle receive on the target side, to guarantee no missed records.

Monitor for one week after go-live, focusing on three things: whether the header-to-entry ratio is as expected (price lists are usually 1:N), how many updates hit ON DUPLICATE KEY UPDATE, and whether forbid/expiry status flips are timely.

Pitfall Retrospective

Lesson 1: Never use FCreateDate as the incremental cursor. We've seen customers write FCreateDate > now, only to find that after day one nothing is pulled again — price list changes are mostly about modifying prices and effective dates, not creating new entries. The safe approach is FModifyDate plus a platform variable holding the last successful timestamp.

Lesson 2: Handle forbid vs. unaudited separately. Rows with FForbidStatus=1 must be filtered out, otherwise costing will use invalidated prices. FDocumentStatus only represents created/audited state and is orthogonal to forbid — both fields must be checked.

Lesson 3: Entry-level FEntryID re-triggers during incremental sync. When a price list entry is updated, FEntryID stays the same but FTaxPrice changes. The target side must use ON DUPLICATE KEY UPDATE, not delete-then-insert, otherwise downstream sees a brief empty window.

Lesson 4: Currency FCurrencyID is a reference field. What Kingdee Cloud Cosmos returns as FCurrencyID.fnumber is not a direct currency code; it needs a second resolution or a mapping in the runtime layer, otherwise downstream reports show a wall of meaningless FItem IDs.

Lesson 5: Effective/expiry dates travel as strings — validate the format. FEntryEffectiveDate is a date type on the Kingdee side, but the runtime layer may serialize it as a string; the target MySQL columns are DATE/DATETIME. Confirm the format before writing — yyyy-MM-dd or with time — otherwise inserts fail or get truncated.

When It Fits and When It Doesn't

Fits: supply-chain master data sync (Kingdee Cloud Cosmos → MySQL DW/BI), one-way, header+entry structure, modification-time incremental, reference fields resolvable in the runtime. Doesn't fit: scenarios requiring write-back into Kingdee Cloud Cosmos, sub-second real-time latency, cross-org/cross-book scenarios with inconsistent coding systems, or situations where the price list itself is maintained on the MySQL side.