Production Report Sync in Practice: End-to-End Implementation from MySQL to Kingdee Cloud Cosmos
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:
- 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 whenondelivery_orgto prepend a prefix), and the inbound order number usesCONCAT('RKD', id)directly as the idempotency key. - Target layer (Kingdee Cloud Cosmos): The target interface type is
batchSave. It usesFBillNo = inbound order numberas the document number,FDatereceives the date, andFPrdOrgId/FWorkshipIdHuse_functionfor organization/workshop mapping, then the entry body is attached.
Key field mapping (only the most error-prone fields are listed):
| Dimension | MySQL Source Field | Qeasy Transformation | Kingdee Cloud Cosmos Target Field | Notes |
|---|---|---|---|---|
| Document No. | Inbound order no. (CONCAT('RKD', id)) | Pass-through | FBillNo | Must be idempotent; dedup depends on it |
| Document date | Date (update_time) | Pass-through | FDate | Source SQL has fallen back to a business date via config_id=351 |
| Production order no. | Production order no. (prefixed by delivery_org) | Pass-through | Production order no. on FBillNoEntry | Don't mix prefixes across orgs |
| Production org | Supply org (delivery_org) | _function case translation | FPrdOrgId | T01.01→T01.06, T04→T04 |
| Production workshop | Supply org | _function case translation | FWorkshipIdH | Paired with production org |
| Document type | —— | Constant | FBillType = SCHBD01_SYS | Fixed type for production report |
| Inbound quantity | confirm_numb | Pass-through | Entry quantity | Rows 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
_functionexpressions. 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 / :offsetas the pagination parameters (limitis typically 200–500, depending on database load). Also reserve an incremental time variable in the:created_atform for the scheduler to pass in. - Field mapping: Drag the source columns out directly as variables. For
FPrdOrgId/FWorkshipIdH, use inline_function case ... endexpressions. ForFBillType, write the constantSCHBD01_SYS. We recommend storing all_functionsnippets 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 CosmosModelschema. Turn onidCheck=trueso Cosmos usesFBillNoto 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.
- Phase 1: Align the incremental start point. Query
MAX(update_time)in MySQL, subtract one hour, and use it as the initialcreated_at. In parallel, export the recent 7 days ofFBillNofrom 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. - Phase 2: Full-volume trigger + reconciliation. Adjust
:limit / :offsetto 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 (joiningRKD + idwith CosmosFBillNo); after the run, the row counts must match and any differences should be handled manually. - Phase 3: Steady-state incremental. Switch scheduling back to
*/3/*/4, only pushing rows whereupdate_time > last watermark.For the first two weeks, monitor duplicate rate and failure rate daily. Persist the watermark insys_configor 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, withidCheck=trueon the target side as a double safety net. - Don't use
update_timedirectly as the business date. If you simply takedate(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 likeconfig_id=351to derive the business date. - Don't hard-code organization/workshop mapping in the SQL. Some customers put
T01.01 → T01.06directly in the SQL, so changing the org structure requires a DBA review. Moving that into Qeasy's_functionexpressions means IT can change it themselves, shrinking the change window from a day to ten minutes. confirm_numb=0dirty 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.