Production Requisition Sync in Practice: End-to-End Configuration from MySQL to Kingdee Cloud

What This Strategy Solves

On the shop floor of a discrete manufacturing enterprise, issuing materials against work orders is one of the most frequent posting actions in a single day. At milestones such as work order release, kitting analysis, and replenishment, the MES continuously generates material requisitions, which need to be pushed down to Kingdee Cloud as production requisition documents as quickly as possible. Otherwise the shop floor will hit a break: "there is stock on the books, but it cannot be picked." This article walks through a typical strategy — MOM-SCLL-Production Requisition-T02 — that pushes data from a MySQL requisition interface table to the SCLLD01_SYS document in Kingdee Cloud in near real time.

Data Flow and Field Mapping

The overall flow is: source MySQL → Qeasy data integration platform (handling cleaning, mapping, scheduling, and observability) → target Kingdee Cloud.

On the source side, a WebAPI select entry is used to pull the t1 requisition interface table with a parameterized SQL. It left joins the production order, BOM, and workshop value-set tables, filters on t1.status in ('N','E'), iface_type in ('INPUT','REPLENISH'), iface_sequence=1, and manufacturing site = T02, groups by iface_id, and uses pagination delegated to Qeasy for incremental processing.

On the target side, Kingdee's batchSave API is used. The document type is hard-coded as SCLLD01_SYS, FBillNo is the idempotency key, and FID is used for back-lookup. The key field mapping (source → intermediate expression → target) is as follows:

• Document number: CONCAT('MSCLL', DATE_FORMAT(creation_date,'%Y%m%d'), iface_id) → FBillNo
• Document date: accounting period or creation_date (fallback via the accounting period table) → FDate
• Production / issuing organization: t2.manufacturing_site_code → FPrdOrgId / FStockOrgId
• Material number: t1.material_code → body FMaterialId
• Requested quantity: t1.qty → body FQty
• Warehouse: t1.fn_num → body FStockId
• BOM number / internal id / line number: t3.head_attribute2, t3.head_attribute8, SUBSTRING_INDEX(wo_number,'_',-1) → corresponding body fields
• Workshop: the source side uses a case when block to map departments to Kingdee workshop codes, with a value-set fallback → body FWorkShopId
• Lot / plan tracking number: dynamically generated based on the production organization and whether the material is lot-managed → body FLot / FPlanTrackNo

How to Configure on Qeasy

We use the Qeasy data integration platform to host this strategy. The core configuration has four parts.

1) Source WebAPI integration. Set api=select, method=POST, replace :limit / :offset with placeholders, and ensure the main parameter main_params corresponds one-to-one with the placeholders in the SQL. The built-in paginator of the platform handles pagination. The benefit of this approach is that later database or table changes do not require modifying the main SQL.

2) Target Kingdee execution. Set api=batchSave, idCheck=true, fix FBillType to SCLLD01_SYS, reference intermediate results with {{variable}} for the other fields, and place the header and body in the same body payload.

3) Centralized code mapping management. For cross-department master data such as workshop, organization, material, and warehouse, we recommend maintaining everything centrally in Qeasy's data dictionary or a mapping table. The source SQL only does a left join to retrieve values; it no longer hard-codes case when inside the SQL. This is one of the most common patterns used by Qeasy customers on site, and it avoids the need to modify the SQL or release a new version when new workshops are added later.

4) Exception handling and observability. Enable the platform log center, persist Kingdee's returned FBillNo, FID, and error code errors[].message to the database, and route failed documents into an automatic retry queue to prevent dirty data from accumulating.

Implementation Steps

The strategy is typically rolled out in three phases.

Phase 1: Full-volume trigger. During the initial switchover, the source SQL has no time condition and performs a one-time full-volume backfill in ascending order of iface_id. The target side submits via batchSave. This round mainly verifies whether the code mapping and required fields are complete. Failed documents are first bounced back and the mapping is fixed one by one.

Phase 2: Incremental start point. Once the full volume is stable, switch the source SQL to an incremental condition based on t1.last_update_date > :last_sync_time, with the initial :last_sync_time set to the end of the full volume. We use Qeasy to persist this timestamp in the strategy instance's context, so platform restarts or rollbacks do not lose position.

Phase 3: Scheduling frequency. The source crontab is set to 6,16,26,36,46,55 * * * * (every 10 minutes, with the 55-minute slot grabbing the close before the hour), while the target side */1 * * * * receives batches. Header and body are submitted in two stages — the header is saved first to obtain FID, then the body is filled in. This "header-then-body staged" pattern is commonly used by Qeasy customers and can significantly reduce the whole-document failure rate.

Lessons Learned

1. Workshop mapping hard-coded in SQL. The case when block on the source side looked clear at first, but when production lines expanded to five departments, modifying the SQL required a full change. The safe approach is to extract it into the Qeasy data dictionary and have the source SQL only join to fetch values.
2. Document date falling in a closed accounting period. Using creation_date directly will be rejected by Kingdee at month-end reconciliation. We use mt_account_period.aft_quiet_time as a fallback: if the accounting period is null, fall back to creation_date.
3. Incremental start point drift. The time zones of the MySQL side and the Qeasy scheduler are inconsistent, causing last_update_date to miss rows. A typical mistake is to only look at logs without comparing row counts. The safe approach is to run a daily reconciliation, using Kingdee's FID to back-check the source row count.
4. High retry cost for whole-document batchSave failures. If the header write fails, the body is also discarded, so we later switched to a two-stage approach of "header save first, body in batches" — failures only roll back the body.
5. Missing FLot for lot-managed materials. The source SQL uses a subquery to check whether the material is lot-managed: if yes, include it; if not, leave it out. Parameter binding here is easy to miss, so we added a "do not write the field when the value is null" treatment on Qeasy, to prevent Kingdee from interpreting it as lot number 0.

Applicable and Non-Applicable Scenarios

Applicable: discrete manufacturing shop floors, high-frequency material requisitions, and private deployment projects requiring near-real-time posting between MES and Kingdee Cloud — especially scenarios driven by work orders with frequent replenishment.

Not applicable: cross-legal-entity multi-organization material requisitions that require strict approval flows (these should go through Kingdee's own document state machine rather than direct interface push); and early MES systems whose interface tables have not yet standardized status / iface_type — data governance must come first before synchronization can be discussed.