Syncing Kingdee Transfer Requisition to MySQL: A Flat-Table Incremental Integration Playbook

What This Strategy Solves

A retail chain running multi-warehouse transfers needs Kingdee Cloud transfer requisitions (STK_TRANSFERAPPLY) pushed into a MySQL data hub for scheduling and analytics. The catch: the source uses a header-line (master-detail) structure, while the target prefers a flat single table for analytical queries, and many fields are wrapped as base-data objects (FNumber/FName). We handle this on the Qeasy data integration platform with a "flat source pull + target single-table REPLACE INTO" pattern, ensuring encoding consistency and incremental stability.

Data Flow and Field Mapping

Data flow: Kingdee Cloud (executeBillQuery, FormId=STK_TRANSFERAPPLY) → Qeasy intermediate layer → MySQL single table tp_jd_transpferapply.

The source executeBillQuery returns a flattened result where each record represents one requisition line, and header fields are repeated on every line. The target stores a flat single table without master-detail separation, using FEntity_FEntryID (mapped to target FEntryID) as the deduplication primary key.

Key field mappings (excerpt; refer to source metadata for the complete list):

Object fields are always read as Object.FNumber for codes and Object.FName for display names. Centralized encoding mapping is one of the most common patterns seen among Qeasy customers.

How to Configure on Qeasy

Create a new strategy on Qeasy named "Kingdee Transfer Requisition → MySQL" with the following key points:

1. Source connection: Kingdee Cloud WebAPI. Choose the executeBillQuery interface with FormId=STK_TRANSFERAPPLY, POST method, pagination Limit=100 / StartRow=0.
2. Pull filter: Set FilterString to FModifyDate>='{{LAST_SYNC_TIME|datetime}}' for incremental pulls by last modified date.
3. Field mapping: In Qeasy's mapping panel, read object fields as {{FMATERIALID.FNumber}} for codes or names; pass scalar fields directly via {{FBillNo}}.
4. Target connection: MySQL, choose API batchexecute, method SQL. Target table tp_jd_transpferapply, primary key id, enable idCheck.
5. Write mode: Use REPLACE INTO with FEntity_FEntryID for deduplication; per-queue write batch limit 200.
6. Scheduling: Source crontab */8 * * * *, target crontab 3-59/8 * * * *. Stagger writes by 3 minutes so the read pipeline never collides with the write pipeline.

No extra scripts are needed; the source returns a flat shape and the target simply passes fields through.

Implementation Steps

Roll out the strategy in three phases for stability:

• Phase 1: Incremental starting point. Before the first run, lock in the initial LAST_SYNC_TIME on the Kingdee side, ideally back to the business go-live date. Qeasy's first pull uses FModifyDate>='initial_time' to fetch everything.
• Phase 2: Full-volume trigger. Manually trigger a full reload, then verify that the row count in tp_jd_transpferapply matches the line count of STK_TRANSFERAPPLY in Kingdee. Sample 3–5 FBillNo values and compare header fields against line fields.
• Phase 3: Scheduling cadence. Once verified, enable the 8-minute incremental rhythm. Many Qeasy customers run an "incremental + full dual-track" pattern: 8-minute increments by FModifyDate day-to-day, plus a weekly or monthly full reload as a safety net against extreme data loss.

Lessons from the Trenches

1. Wrong object-field level. A typical mistake is writing {{FMATERIALID}}, which returns the JSON object instead of the code. This is where things go wrong. Always write {{FMATERIALID.FNumber}} and store only the code string in the target.
2. Storing only FNumber or only FName. Reporting needs names for filtering, but codes are the join key. The safe practice is to store both FXXX_FNumber and FXXX_FName so you don't need to query Kingdee again.
3. Schedule collision. Source */8 and target */8 firing at the same time causes instant queue congestion. Use target 3-59/8 to stagger 3 minutes behind the source for smoother throughput.
4. REPLACE INTO side effects. In MySQL, REPLACE INTO is a delete-then-insert, which can shift auto-increment IDs and rewrite foreign keys. In projects that separate header and line phases, if the target table has foreign-key references, prefer INSERT ... ON DUPLICATE KEY UPDATE; only use REPLACE INTO when you have confirmed there are no foreign keys.
5. Extension field naming inconsistency. Extension fields like F_PRSH_Base_83g1 and F_PRSH_Base_qtr can carry different semantics across tenants. Confirm the meaning on the Kingdee side before deployment; never guess from the field name alone.

When to Use and When Not to Use

Use when: transfer requisitions must drive cross-warehouse scheduling analytics, feed downstream BI or e-commerce hubs, or the target requires a flat table for analytical queries while the source is header-line structured.

Do not use when: the target table already has foreign-key references sensitive to auto-increment changes, or the source documents must preserve master-detail structure for strict reverse lookup. In those cases, prefer a header + line dual-table model over a flat single table.