Practical Guide to Material Cost Settlement Sync-Back: Pulling Results from Kingdee and Writing Back to MySQL

What This Strategy Solves

After a material cost settlement is created in Kingdee Cosmic, the MES-side reporting table has no way of knowing whether that save actually succeeded, what the HEADER_ID is, or what the error message was. Downstream reconciliation, period close, and recalculation all need to wait for this "sync return" layer to land in the database. A common pain point we see on-site: MES calls the save API, Kingdee returns OK, but MES never writes HEADER_ID and the sync result back into its own business table — so the two sides drift apart during reconciliation.

We use the Qeasy data integration platform to host this "short-loop writeback", pulling Kingdee's sync result back in near real time and updating the cost settlement header table in MySQL.

Data Flow and Field Mapping

The pipeline has two segments. The first segment is Kingdee writing the new material cost settlement result back into the Qeasy integration platform (an internal state landing). The second segment is Qeasy, on a schedule, using a SQL update to write the result back into the MySQL business table.

Key field mapping:

Note: is_sucess is the spelling on the Kingdee interface itself — we don't rename it arbitrarily. If the client later wants to standardize on is_success, we recommend doing that once in Qeasy's centralized field mapping rather than scattering the change across multiple strategies.

How to Configure on Qeasy

Source configuration (WebAPI, QUERY, POST):

• API: select "request no-op", type WebAPI, effect = QUERY.
• Set autoFillResponse to true so the platform's internal state (source ID, success flag, return message) is surfaced directly as response fields.
• Keep only four response fields: HEADER_ID, sourceid, is_sucess, result_message. Don't tick the rest, or you'll pollute downstream with junk fields.

Target configuration (SQL, EXECUTE):

• Type is WebAPI, but effect is EXECUTE, executed as SQL.
• The main_params request parameter is an object holding two variables: HEADER_ID and is_success.
• The actual write SQL lives in otherRequest.main_sql. Template: update ty_report.hme_cost_acc_header set sync_kingdee=:is_success where HEADER_ID=:HEADER_ID.
• Enable idCheck (true). The platform uses HEADER_ID for idempotency, so duplicate writebacks will not double-post.

For scheduling, we typically set the source strategy's crontab to */7 * * * * and the target SQL update strategy's crontab to */2 * * * *. The "pull" runs slightly slower than the "write-back" so the target side never overwrites with empty values from a not-yet-refreshed source.

Implementation Steps

1. Incremental starting point: Manually create one material cost settlement in Kingdee and confirm that HEADER_ID, is_sucess, and result_message are all present in the return payload.
2. Full trigger: Click "Run Now" on both the source and target strategies in Qeasy, and check whether the sync_kingdee column on the MySQL cost settlement header table gets updated.
3. Schedule frequency: In steady state, source polls every 7 minutes, target executes every 2 minutes — a "near real-time" writeback rhythm.
4. Regression check: Sample 100 rows via SQL and verify that sync_kingdee matches Kingdee's save log. The required discrepancy is 0.

If the client also needs MES process-cost writeback, you can add a separate "process cost detail sync" strategy after this one — but it must be a standalone strategy, not mixed into the same SQL. That's where we see the most rollovers.

Pitfalls We Hit On-Site

1. A classic mistake is using Kingdee's is_sucess field name directly as a MySQL column name. The safe approach is to do one alias conversion in Qeasy's field mapping: each side keeps its own spelling, and never hard-code it in code.
2. Do not put the UPDATE SQL inside the main_params string. main_sql must live in otherRequest so the platform can do parameter binding. Putting it in the wrong slot creates both SQL-injection risk and full-table update incidents.
3. If you turn off autoFillResponse, fields will disappear. This strategy depends on the platform surfacing the source ID and success flag internally. In a client environment where it was disabled, HEADER_ID was still there but is_sucess became null — every row ended up marked "not synced".
4. Keep idCheck enabled. Kingdee's interface occasionally re-callbacks during network jitter. Disabling idempotency lets sync_kingdee get overwritten with the wrong value.
5. Don't use the same crontab on both ends. When source and target run at the same cadence, the target sometimes reads stale state from the previous round of the source, producing "looks successful but actually old" dirty data.

When This Applies — And When It Doesn't

Applies: lightweight writeback between Kingdee Cosmic and an MES / reporting database (MySQL); landing the success/failure flag of a document save; syncing HEADER_ID-style primary keys to downstream reconciliation.

Doesn't apply: scenarios that need to bring back detail lines (table body) in bulk — those should be modeled as a header-then-body phased strategy on Qeasy, not stuffed into a single UPDATE. It also doesn't fit two-way real-time online transactions; for millisecond-level consistency, use a direct API connection instead of platform scheduling.