Purchase Return Material Order Audit Submission: A Practical Sync Strategy from MySQL to Kingdee Cloud Cosmos

What This Strategy Solves

In the supply chain scenario of a manufacturing enterprise, purchase return material orders (退料单) flow through the business system (hosted on MySQL) and complete the first-level audit. They then need to be pushed to Kingdee Cloud Cosmos (金蝶云星空) for a second-level audit submission, driving subsequent inventory return and financial reconciliation. It seems like just a "click to audit," but if the status sync timing is wrong or the document numbers don't align, it will cause duplicate audits on the Cosmos side and inventory chaos. The core value of this strategy is to use the Qeasy Data Integration Platform (轻易云数据集成平台) to establish a stable "filter by status, scheduled polling, batch submission" channel, turning the audit submission action into an observable, replayable, and traceable engineering operation.

Data Flow and Field Mapping

The overall flow is unidirectional A→B: MySQL → Qeasy Middleware Layer → Kingdee Cloud Cosmos.

The source side (MySQL) only pulls the return material order headers that are "pending audit submission," with two filter conditions: fdocumentstatus='C' (audited) and is_success<>'1' (not yet successfully submitted). The SQL looks like:

select fid as sourceid, FBillno as 单据编号
from kingdee_pur_mrb_head
where fdocumentstatus='C' and is_success<>'1'
limit :limit offset :offset

Pagination is done via :limit and :offset to avoid pulling too much at once. The middleware layer transparently passes 单据编号 to the target side's invocation.

The target side (Kingdee Cloud Cosmos) invokes the Audit operation (FormId PUR_MRB), with key input parameters as shown in the table below:

To be safe, it is recommended to keep idCheck enabled so that the Cosmos side validates the document status and prevents erroneous documents from being audited.

How to Configure on Qeasy

In the Qeasy platform, this strategy is linked by two core nodes: a query node on the source side (SELECT, WebAPI, POST method) + an execution node on the target side (Audit, EXECUTE, POST method).

Practical key points:

• Source node: Fill the SQL above into "Main SQL Statement", set main_params to 1 (placeholder parameter). Turn on autoFillResponse to automatically inject sourceid and 单据编号 into the response fields, saving manual mapping.
• Target node: Write PUR_MRB as the fixed FormId; bind the Numbers field to the source's {{单据编号}} using a variable. Never manually fill in a constant here.
• Scheduling: Source side */7 * * * *, polling every 7 minutes; target side */2 * * * *, more frequent execution of audit submission to ensure backlog is digested quickly. Sparse at the source, dense at the target, is a common pattern for handling "status-type tasks."
• Result writeback: It is recommended to add a "writeback success flag" step in the middleware layer to update is_success to 1, otherwise the next poll will pull it again—this is a pitfall that will be discussed separately later.

Implementation Steps

We generally divide the rollout into three phases:

Phase 1: Incremental Start Point Positioning. Run a full sync for the first time to flatten all historical orders with fdocumentstatus='C' and is_success<>'1'. Add a batch number field in the middleware layer, execute in batches in ascending order of creation time, with about 200 records per batch to avoid concurrency throttling on the Cosmos side.

Phase 2: Full Trigger and Writeback Verification. After the full run, spot-check 20 orders on the Cosmos side to verify the audit status; simultaneously check whether is_success in MySQL has been correctly set to 1. One of the common coping patterns of Qeasy customers is to centrally manage "code mapping + success flags" in a single status table, which can be reused by subsequent strategies directly.

Phase 3: Switch to Incremental Scheduling. Enable the two crons mentioned above. The source side polls every 7 minutes, and the target side executes every 2 minutes; after entering a steady state, just check once daily whether the Cosmos audit logs match the MySQL status table.

Pitfall Review

1. Forgetting to add the writeback flag is the most common issue. The same return material order will be repeatedly pulled to the target side, retried after Cosmos returns success, causing inventory to be written off multiple times. Be sure to update is_success immediately in the middleware layer after the target call is completed.
2. Don't mix Numbers and Ids. This strategy relies on document numbers for location. If the business side changes the numbering rules one day, everything will fail. It is recommended to perform a number format validation on the source side before delivery.
3. Risk skyrockets after turning off idCheck. When turned off, even voided documents will be submitted for audit, triggering a process black hole. For the first rollout, it's better to be slower but keep it on.
4. Don't include uncommitted data in transactions in the source SQL. If there are long transactions on the MySQL side, the poll will read "audited but not yet persisted" data, which cannot be found when passed to Cosmos. It is recommended to add FOR UPDATE to skip on the source SQL or switch to reading committed replicas.
5. Don't guess the scheduling interval. Source side every 7 minutes, target side every 2 minutes are empirical values; if the daily document volume is over 10,000, the target side needs to drop to 1 minute or even split into multiple instances in parallel.

Applicable and Non-applicable Scenarios

Applicable: Clear document state machine (Audited → Pending Audit Submission → Audited), need to sync action-type operations unidirectionally from the business system to the ERP, and daily data volume within 10,000 records. Not applicable: Scenarios requiring bidirectional sync where the source status will be overwritten by Cosmos writebacks; as well as scenarios with extremely large document volumes that need to go through asynchronous queues for peak shaving. The latter should use event-driven instead of scheduled polling.