Sales Order Price Write-Back: A Practical Sync Solution from Kingdee Cloud to MySQL

What this strategy solves

In a manufacturing company, sales orders often enter a MySQL business database first (from CRM or e-commerce front ends) and are subsequently pushed to Kingdee Cloud as standard documents for financial settlement and inventory posting. After settlement, the tax-included unit price confirmed by finance may differ slightly from the price at order time, and this final price must be written back to the MySQL order detail table, keyed by sales order + material, so that the front end, marketing, and BI reports can keep using it. Manual export is too slow; database triggers tend to miss records. We use the Qeasy data integration platform to host this as an independent, re-runnable, and monitorable sync strategy.

Data flow and field mapping

The flow is: Kingdee Cloud → Qeasy → MySQL. The source side pulls sales order header + entry data through executeBillQuery; the target side writes the tax price back to a business detail table using execute.

Key field mapping (Kingdee Cloud → MySQL):

The target SQL (desensitized):

update mbs_order_bom
   set kingdee_tax_price = :FTaxPrice
 where bom_uuid = :F_bomUuid

Note that the original crontab value 1 1 1 1 1 is clearly not a real schedule and must be replaced before go-live.

How to configure it on Qeasy

In Qeasy we model this as a paired source + target strategy. Key configuration points:

1. Source: bill query pull. Use API executeBillQuery, method POST, effect QUERY. Default Kingdee paging Limit/StartRow is sufficient. Declare FBillNo, FMaterialId.Fnumber, FTaxPrice, FID, FSaleOrderEntry_FEntryID, and FDocumentStatus in the request body so the mapping and branching can be done inside Qeasy.
2. Target: DB write update. Use API execute, method POST, effect EXECUTE. Bind values via main_params to avoid SQL injection. Place the actual update statement in main_sql, and always use a unique business key (such as bom_uuid) in the WHERE clause so a wrong join cannot mass-overwrite prices.
3. Centralized code mapping. Kingdee's material code is Fnumber; the MySQL side usually uses its own UUID. We maintain a material mapping table in Qeasy's mapping module. The pulled FMaterialId.Fnumber is resolved through this mapping before being matched to mbs_order_bom.bom_uuid. This is the single place to look when troubleshooting; scattering it across scripts makes incidents painful.
4. Idempotency and replay. Bring FID and FSaleOrderEntry_FEntryID back through the source side, and on the target side perform an existence check against these two keys before updating, so repeated writes cannot clobber the price.

Implementation steps

Splitting go-live into three phases keeps things stable:

• Phase 1: Incremental start. Use status = posted and last modified time ≥ last successful run as the incremental filter. Run for 1–2 days and watch volume, error rate, and accuracy. Encode the filter in the source request body in Qeasy rather than filtering downstream.
• Phase 2: Full backfill. During a low-peak window (e.g., early morning), relax the filter and do a full backfill to verify historical data, including any historical price adjustments in Kingdee.
• Phase 3: Steady-state scheduling. Move to near-real-time scheduling (e.g., every 5–15 minutes). Configure Qeasy alerts: N retries, N consecutive failures → notify the on-call channel (WeCom/DingTalk). Replace the placeholder crontab value 1 1 1 1 1 with a real cron expression before go-live.

Lessons learned

1. Common mistake: treating the price as tax-exclusive. The Kingdee field name contains "Tax", but identically named fields on different documents can mean different things. Confirm the exact definition with sales and finance before writing back.
2. The join key must be order + material, not just one of them. Order-number-only will overwrite the whole order's price; material-only will mix orders. The safe pattern is FBillNo + FMaterialId.Fnumber.
3. Don't forget the database/schema and shard key in the UPDATE. The example only shows update mbs_order_bom; in production you must fully qualify the database and shard key, otherwise the statement can land on the wrong node.
4. Front-end cache is not refreshed after write-back. The MySQL price changed but the Redis/CDN quote cache still serves the old price. Either trigger a cache invalidation from Qeasy after a successful write-back, or align on a short cache TTL.
5. Handling finance-side price adjustments. In Kingdee, an adjustment does not always change the original sales order's FTaxPrice; it may live on a separate adjustment document. If the business wants "final settlement price", looking at FTaxPrice alone is not enough — you need an associated query on the source side or additional logic on the target side.

When to use and when not to use

Use when: sales orders enter MySQL first and are then settled in Kingdee Cloud, and the final tax-included unit price must be readable on the MySQL side — typical for retail, manufacturing, and e-commerce. Do not use when: the unit price is fully maintained inside Kingdee Cloud and MySQL does not need it; or when the price is finalized on the MySQL side and there is no need to write back at all — in that case use a one-way push from MySQL to Kingdee instead.