Practical Tutorial on the Material Cost Settlement Sync-Return Strategy: Closed-Loop Design from Kingdee Cosmic to MySQL

What this strategy solves (scenario and value)

After a material cost settlement is saved in Kingdee Cosmic, the business team usually wants to see "success or failure" in MySQL right away. The shortest closed loop we implemented: as soon as Kingdee returns a modification result, MySQL marks the row identified by HEADER_ID with the sync result. In one real project, the customer originally did a nightly batch write-back, so the shop floor was still seeing an "unacknowledged" state in MySQL in the morning, and tracing issues meant digging back many hours. After switching to near-real-time write-back, diagnosis time dropped from hours to minutes.

Data flow and field mapping

The overall direction is Kingdee Cosmic → Qeasy → MySQL. The source is a "request empty operation" action (effect=QUERY) that only pulls back the return info for a given HEADER_ID. The target is a single update SQL that writes the sync_kingdee column in the MySQL business table.

Key field mapping:

The target only accepts a main_params object; the core SQL is: update ty_report.hme_cost_acc_header set sync_kingdee=:is_success where HEADER_ID=:HEADER_ID.

How to configure it on Qeasy

On the Qeasy Data Integration Platform, this strategy is configured as a two-step "source component + target component" flow, with no transformation script — field binding is handled by the platform's built-in mapping.

Source component points: turn on autoFillResponse so the values in the response body are passed to the next step as variables; leave is_sucess and result_message empty so they are filled at runtime.

Target component points: method=SQL, idCheck=true, buildModel=false. In parameter binding, map the source's HEADER_ID and is_sucess into main_params, and let main_sql in otherRequest reference those named parameters.

Implementation steps

Roll out in three phases.

Phase 1: incremental start point. First confirm the sync_kingdee column exists in MySQL's hme_cost_acc_header, with a default of "N" or NULL. The source starts from "the list of HEADER_IDs most recently submitted successfully" to avoid an empty run.

Phase 2: full-trigger. Manually trigger once on day one to backfill any historical rows that never got a write-back. The source crontab is set to */7 * * * *, pulling Kingdee's return every 7 minutes; the target crontab is */2 * * * *, writing back to MySQL as soon as data sits in the queue. The two schedules are offset to avoid a thundering herd.

Phase 3: steady run. After one week of observation, confirm the update latency of sync_kingdee stays in the minute range, then roll out the manual inspection script.

Pitfalls and lessons learned

Pitfall 1: inconsistent spelling of is_sucess. The source field is is_sucess (clearly following Kingdee's spelling habit), but the target SQL was written as is_success. A typical mistake is to follow IDE hints and "fix" it, which then breaks the binding. The safer practice is to keep the field name as-is and align the MySQL column name to it, not the other way around.

Pitfall 2: SQL error when result_message is empty. When Kingdee returns success, the message text is an empty string; writing into a NOT NULL column will throw. Either allow NULL on the MySQL side, or add a default fallback on the source side.

Pitfall 3: HEADER_ID is float. If the MySQL primary key is bigint, passing float directly loses precision. The safer practice is to add a type-conversion step on the platform so values are normalised to string before comparison, or change the MySQL primary key to VARCHAR.

Pitfall 4: misaligned crontab causing slow write-back. Source runs every 7 minutes and target every 2 minutes — that looks fine, but if one source round pulls 200 records, the actual landing can take more than ten minutes. The easy trap is to look at schedule frequency only and ignore per-batch volume.

Pitfall 5: mixing "sync return info" with "sync master data" in one strategy. In one real project, the customer, for convenience, stuffed the return write-back into the master-data push strategy; when the master-data push failed, the write-back also failed, and they interfered with each other during debugging. The safer practice is to split header and body into separate phases and keep write-back as its own strategy.

When it applies and when it does not

Applies: closed-loop scenarios where the business side needs a fast "success/failure" tag right after a document is saved; the typical combination of MySQL as the query store and Kingdee as the business system.

Does not apply: scenarios that require returning detail lines (bodies) or many business fields — this strategy only writes back a HEADER_ID-level success flag and is not suitable for full document mirroring.