Supplier Sync Status Writeback: A Bidirectional Loop Between MySQL and Kingdee Cloud

What This Strategy Solves

After supplier master data lands in a MySQL business table, lifecycle changes such as approval, disabling, and freezing still happen inside Kingdee Cloud. Business users often look up "can I actually use this supplier right now?" using a local code, only to find the MySQL flag is three months stale. We hit exactly this issue at a retail private-deployment project — a purchase order failed, and the post-mortem revealed the supplier had been frozen in Kingdee long before, while the enable flag in MySQL had never caught up.

This strategy writes Kingdee Cloud's real-time supplier status back to MySQL, so downstream business systems can read the current status locally without manual reconciliation.

Data Flow and Field Mapping

End-to-end direction: Kingdee Cloud (source) → Qeasy data integration platform (middleware, transformation and scheduling) → MySQL (target).

Worth mentioning: keeping the code mapping centralized at the strategy layer on Qeasy is much easier to maintain than scattering it across SQL statements. When adding new company books or organizational isolation later, one change is enough.

How to Configure on Qeasy

The source uses Kingdee Cloud's executeBillQuery, a POST query, with effect set to QUERY. The key point is to bring out both FSupplierId and FNumber, because the later writeback relies on these two fields for unique positioning.

The target is MySQL, using the WebAPI execute interface with method POST. A typical engineering choice: write the business table update as main_sql in otherRequest, with Qeasy doing parameter binding for supplier_short_code and yn_lock, rather than letting the source push every detail record — this saves bandwidth and avoids large transactions.

The main request body main_params is just a thin trigger; the real SQL goes through otherRequest. This is a common Qeasy pattern: header and body staged separately, decoupling the request structure from the specific SQL, so adjusting fields does not require changing the whole table schema.

Implementation Steps

1. Incremental starting point: First add a unique index on supplier_short_code in basic_supplier_info, and initialize yn_lock defaults to 0.
2. Full snapshot trigger: Run a full pull once — extract FDocumentStatus for every supplier in Kingdee Cloud and update MySQL. This step is best scheduled during business off-peak hours; Qeasy's scheduler supports "run now" plus off-peak batch runs.
3. Incremental and full dual-track: Daily incremental runs every hour (the source crontab is 3 */1 * * *, the target is 5 */1 * * *, a 2-minute offset leaves room for source-to-target transit); a weekly full snapshot covers misses and anomalies.
4. Validation mechanism: On Qeasy, add a value-range check on the yn_lock field. Any dirty data that is not 0/1 goes straight to the exception queue, never polluting the business table.

Pitfalls Revisited

• Status code dictionaries not aligned: Kingdee's FDocumentStatus is an enum string, while MySQL was previously written with Y/N. The first run had no mapping, and after the run yn_lock was all NULL. The safe approach is to maintain a single dictionary inside the Qeasy transform script, so changes on either side only require editing one place.
• Using name as a join condition: An earlier version tried to be lazy and matched on FName. Two suppliers shared a name and the update hit the wrong rows. From then on, every writeback strategy is forced to use supplier_short_code + company_code as a composite condition.
• No unique index on the MySQL table: The first full run lacked a unique index, so the job ran three times. yn_lock itself did not break, but the logs filled with warnings that made troubleshooting harder.
• Source and target scheduled at the exact same minute: Both crontabs were originally set on the hour. Whenever the Kingdee query was slow, it swallowed the whole hour window. Staggering by 2 minutes is the safe choice.
• company_code hardcoded in SQL: When the business side asked to onboard a new company book, one SQL was changed and another was missed, so the status was written back for only half the companies. The fix on Qeasy is to lift company_code into the request parameters and let the scheduling context inject it, so adapting to new organizations is just a configuration change.

When It Fits and When It Doesn't

This fits scenarios where supplier status needs to be read locally across multiple business systems, with Kingdee Cloud as the source, in a private deployment. It is not suited for pure one-way master data distribution, nor for business cases that need sub-minute real-time — this strategy operates at hourly granularity; for hard real-time, use an event-driven lightweight channel.