Qeasy Cloud
Get Started

Production Report Sync in Practice: End-to-End Implementation from MySQL to Kingdee Cloud Cosmos

· 何金辉· Integration Solutions· 10 views· 4 min read

What This Strategy Solves

A discrete manufacturer's MES (a MySQL database) writes tens of thousands of single-process production reports every day, which quality, production, and warehouse teams all rely on. Kingdee Cloud Cosmos, as the ERP, needs these reports for cost allocation and process accounting. When the two sides disagree on definitions, month-end cost reconciliation breaks down. The thing IT teams fear most is "the MES wrote it, the ERP didn't receive it" or "the ERP received it twice." This strategy pushes single-process reports from MES to Kingdee Cloud Cosmos, idempotently keyed by the inbound order number, achieving unique documents, consistent definitions, and replayability.

Data Flow and Field Mapping

The overall flow is MySQL → Qeasy Data Integration Platform → Kingdee Cloud Cosmos, divided into two layers:

  1. Source layer (MySQL): The source interface type is a SQL query. The main query produces columns such as "inbound order number / production order number / part number / plan tracking number / inbound quantity / date / work hours / process codes / supply organization." The production order number, inbound order number, and date are all computed inline in the source SQL (for example, the production order number uses a case when on delivery_org to prepend a prefix), and the inbound order number uses CONCAT('RKD', id) directly as the idempotency key.
  2. Target layer (Kingdee Cloud Cosmos): The target interface type is batchSave. It uses FBillNo = inbound order number as the document number, FDate receives the date, and FPrdOrgId / FWorkshipIdH use _function for organization/workshop mapping, then the entry body is attached.

Key field mapping (only the most error-prone fields are listed):

DimensionMySQL Source FieldQeasy TransformationKingdee Cloud Cosmos Target FieldNotes
Document No.Inbound order no. (CONCAT('RKD', id))Pass-throughFBillNoMust be idempotent; dedup depends on it
Document dateDate (update_time)Pass-throughFDateSource SQL has fallen back to a business date via config_id=351
Production order no.Production order no. (prefixed by delivery_org)Pass-throughProduction order no. on FBillNoEntryDon't mix prefixes across orgs
Production orgSupply org (delivery_org)_function case translationFPrdOrgIdT01.01→T01.06, T04→T04
Production workshopSupply org_function case translationFWorkshipIdHPaired with production org
Document type——ConstantFBillType = SCHBD01_SYSFixed type for production report
Inbound quantityconfirm_numbPass-throughEntry quantityRows where confirm_numb=0 are excluded

Most of the supply-organization translations live in the source SQL as codes like T01.01 / T04, while the real ERP organization and workshop codes are translated centrally inside Qeasy's _function expressions. Centralized code-mapping management is a very common pattern among Qeasy customers.

How to Configure on Qeasy

In customer environments, we usually configure in four steps: source interface → field mapping → target interface → scheduling.

  • Source interface: Choose the MySQL data source, paste the long SQL into main_sql, and treat :limit / :offset as the pagination parameters (limit is typically 200–500, depending on database load). Also reserve an incremental time variable in the :created_at form for the scheduler to pass in.
  • Field mapping: Drag the source columns out directly as variables. For FPrdOrgId / FWorkshipIdH, use inline _function case ... end expressions. For FBillType, write the constant SCHBD01_SYS. We recommend storing all _function snippets in Qeasy's "script library" so they can be reused by similar documents later.
  • Target interface: Kingdee Cloud Cosmos batchSave, building the JSON according to the Cosmos Model schema. Turn on idCheck=true so Cosmos uses FBillNo to intercept duplicate documents— even if Qeasy replays, the database won't be polluted.
  • Scheduling: source runs */3 * * * *, target runs */4 * * * *. The two are staggered to reduce instantaneous concurrency.

Implementation Steps

We generally roll this out in three phases to avoid hammering the MES with a full table scan right from the start.

  1. Phase 1: Align the incremental start point. Query MAX(update_time) in MySQL, subtract one hour, and use it as the initial created_at. In parallel, export the recent 7 days of FBillNo from Cosmos and check back against the source to determine which orders need to be backfilled. This step is to fix the "starting point" rather than guessing from zero.
  2. Phase 2: Full-volume trigger + reconciliation. Adjust :limit / :offset to a level that lets the historical data be pulled back in one or two passes and run a full backfill. At the same time, prepare a reconciliation SQL (joining RKD + id with Cosmos FBillNo); after the run, the row counts must match and any differences should be handled manually.
  3. Phase 3: Steady-state incremental. Switch scheduling back to */3 / */4, only pushing rows where update_time > last watermark. For the first two weeks, monitor duplicate rate and failure rate daily. Persist the watermark in sys_config or in a Qeasy global variable to avoid re-traversal after restarts.

Lessons Learned

  • The inbound order number as idempotency key is non-negotiable. Early on, one customer used "production order number + process" as the unique key, which caused data loss when the same single process was reported multiple times. We later standardized on RKD + id, computed once in the source SQL, with idCheck=true on the target side as a double safety net.
  • Don't use update_time directly as the business date. If you simply take date(update_time) at the source, records written past midnight get pushed to the next day. The recommended approach—seen often among Qeasy customers—is to centralize the date translation through a configuration item like config_id=351 to derive the business date.
  • Don't hard-code organization/workshop mapping in the SQL. Some customers put T01.01 → T01.06 directly in the SQL, so changing the org structure requires a DBA review. Moving that into Qeasy's _function expressions means IT can change it themselves, shrinking the change window from a day to ten minutes.
  • confirm_numb=0 dirty data. The MES occasionally writes zero quantities; filter them out in the source SQL (confirm_numb<>0) rather than getting them bounced by Cosmos.
  • Roll out header and body in stages. Go live with the header first, run for a week to confirm definitions, then bring the body online. Bundling the body in from day one makes troubleshooting extremely painful.

Suitable and Unsuitable Scenarios

Suitable: discrete-manufacturing MES→ERP single-process report synchronization, with daily volumes in the tens of thousands, requiring idempotency, replayability, and traceability. Unsuitable: multi-process parallel reporting, scenarios requiring splitting by process, and source databases that frequently rewrite history— for the latter, freeze writes at the source first before discussing synchronization.

Original content. Please credit the source when reposting: https://www.qeasy.cloud/insights/solutions/strat-mysql-kingdee-cloud-2246-shd-c17f8ca8

Comments