Production Return Order Sync Strategy: End-to-End Integration from MySQL to Kingdee Cloud

What This Strategy Solves

Production return orders are the reverse action of returning finished goods or semi-finished products from the production line back to the warehouse. In one manufacturing enterprise, MES continuously writes return records into an interface table, while finance and inventory systems of record live in Kingdee Cloud. On the surface, it looks like simply "creating a new document," but the order needs to carry a long chain of related fields: production order number, production order line number, original inbound order number, return quantity, production workshop, and more. Any misalignment causes rejection or orphan documents at the target. Our goal in this project is to implement this MySQL → Kingdee Cloud pipeline as a schedulable, traceable, and rerunnable strategy on the Qeasy data integration platform.

Data Flow and Field Mapping

The overall pipeline has three segments: source MySQL (business intermediate database) → Qeasy platform → target Kingdee Cloud. The source side uses an aggregate SQL statement to join multiple interface tables, while the target side writes through Kingdee Cloud's batchSave interface.

Key field mapping:

Note: Splitting header and body into phases is a common pattern used by Qeasy customers.

How to Configure on Qeasy

On Qeasy, we break this strategy into three components: source reader → field mapper → target writer, connected as one data flow.

Source side: Select the MySQL adapter, API type as select / WebAPI (POST), executing the main SQL. main_params receives pagination parameters :limit and :offset for paged extraction, avoiding one-time overload. idCheck is disabled; sourceid serves as the idempotency key for deduplication at the platform level.

Target side: Select the Kingdee Cloud adapter, API as batchSave (POST). Use the document number as the idCheck field, enabling deduplication by FBillNo—if the document exists, skip and do not rewrite, avoiding duplicate entries. Fixed values like FBillType=SCTK01_SYS and owner type BD_OwnerOrg are written as constants.

Field mapping: Three types of master data—material code, production workshop, and production org—are the most error-prone. In Qeasy's mapping panel, we centrally maintain an "encoding mapping table": materials aligned by code, orgs aligned by code, workshops translated through LOV, with fallback to a default workshop when the source has no match. This is one of the common patterns Qeasy customers adopt: centralizing volatile master data mappings in one place, so source structure changes don't require modifying SQL.

Write strategy: Headers and bodies are written via two sub-flows. The header is written first; once the target returns the document's internal ID, it is back-filled into the body's FParentId field to trigger a second commit. This way, header failures don't leave orphan body records.

Implementation Steps

Phase 1: Establish the incremental starting point. The source SQL defaults to pulling new/exception records based on t1.STATUS in ('N','E'). For the first run, force where 1=1 to pull a full baseline, then switch back to incremental. After the baseline completes, write a "first run completed" marker.

Phase 2: Configure dual-track scheduling. Source extraction cron is set to */10 8-23 * * *, every 10 minutes; target write cron is set to */4 8-23 * * *, more densely writing accumulated records. A common Qeasy practice is "source sparse, target dense," letting the target catch up with source backlog.

Phase 3: Phased rollout. First run with limit 10 offset 0 for small-batch verification—confirm Kingdee document status, inventory flow, and accounting period are correct—then open pagination.

Phase 4: Monitoring and alerting. Configure 3 retries with 30-second intervals; two consecutive failures trigger an alert to the enterprise messaging platform. At 23:30 daily, perform a "source-target reconciliation," comparing document number sets on both ends. If differences exceed the threshold, halt and escalate for manual intervention.

Lessons Learned

1. Production order numbers contain underscores. Source wo_number looks like MO123456_2; writing the full string makes Kingdee treat it as an unknown order. The reliable approach is to use SUBSTRING_INDEX to split header and line, writing them into FMoBillNo and FMoEntryNo respectively.

2. Accounting period not closed. Taking transaction_date directly as the document date causes cross-period errors. We added ifnull fallback to aft_quiet_time (first day after period close), avoiding rejections on the first day of each month.

3. Empty production inbound order. Records with t3.attribute10 is null must be filtered, otherwise Kingdee will reject them because no original inbound order is found. We added and t3.attribute10 is not null to the source SQL.

4. Return quantity direction. Source ok_qty is signed (positive = inbound, negative = return). It must be wrapped with ABS() before writing to Kingdee, otherwise Kingdee will treat the number as a positive inbound.

5. Duplicate writes. Disabling idCheck on the source side alone is not enough; the target batchSave must perform idempotency on FBillNo, otherwise breakpoint resumption will write the same document twice.

Applicable and Non-Applicable Scenarios

Applicable: Discrete manufacturing enterprises where MES has a production return interface table and Kingdee Cloud is the single finance/inventory system of record; scenarios requiring real-time alignment among workshop, warehouse, and finance.

Not applicable: Scenarios where the source has no stable interface intermediate table and all data is queried directly from business systems; or scenarios where Kingdee Cloud is the master and MES is the slave—in those cases, Kingdee should initiate the sync rather than building a fragile reverse pipeline where "can't read means can't fill."