Flattened Pagination Query for Return & Exchange Replenishment Orders: Return Sync from GikyCloud to Kingdee Cloud Cosmos

What This Strategy Solves

Pushing return/exchange replenishment orders from the source system to the target may look like a simple "pull and write," but in practice the three things that most often go wrong are large document volume, many status branches, and field alignment after flattening. In one real engagement, every return order carried more than ten line items; without flattening, the target system either crashed on primary-key conflicts or silently dropped lines.

The core value of this strategy is: paginate the source system reliably, perform flattening + join-lookup on the integration platform, and then feed the target. This keeps pull performance stable and writes idempotent.

Data Flow and Field Mapping

End-to-end flow: Source ERP (paginated query API) → Integration platform (flatten, join-lookup, status filter) → Target ERP (write).

Key field mapping (simplified):

The join-lookup is what enriches the source order number with the customer code and warehouse code so the target system does not have to query back.

Configuration on the Integration Platform

We delivered this strategy on Qeasy, an integration platform we use in customer environments. Typical configuration points:

1. Data source registration. Register the source ERP API ass-business.returnchange.fullinfoget as a callable asset. Keep credentials in the private-deployment key vault — never inline them in the strategy JSON.
2. Request assembly. pageIndex is a loop variable, pageSize is fixed at 100, processStatusList is a comma-separated string of status codes, startModified comes from the scheduling context (last success time).
3. Flatten rule. In the Qeasy transformer, use returnChangeNo as the join key to unfold the header + line-item array into a flat structure that the target can write row by row.
4. Join-lookup action. The target side exposes a placeholder "write-empty" RESTful endpoint used internally for lookup. The mid result lands in Qeasy's temporary store first, then triggers the downstream strategy that writes the real target ERP.
5. Idempotency. Set idCheck = true on the target's number field, idCheck = false on the source side. Same-number documents will not be written twice.
6. Code mapping. Keep customer-code and warehouse-code mappings in the centralized mapping module, not inside this strategy. When a new warehouse is added, only one place changes.

Implementation Steps

We usually split this strategy into three phases:

• Phase 1: One-shot full load Triggered manually on cutover night. Loop pageIndex from 0 until the response is empty. After completion, record the maximum startModified as the incremental anchor.
• Phase 2: Incremental anchor Take the maximum modification time from Phase 1 plus one second as the new startModified, so edge records are not missed.
• Phase 3: Steady-state schedule Source crontab 35 * * * * (the 35th minute of every hour). Target placeholder crontab 1 1 * * * (early-morning write to avoid ERP peak contention).

A small dual-track trick: keep the full-load script disabled in steady state; only run it manually at the start of each month or after a major change. Daily traffic rides purely on incremental, which keeps source pressure low.

Lessons Learned

1. Wrong flatten key. A common mistake is flattening on tradeAfterId. The same return order can produce multiple rows as its status changes, causing duplicated lines. Use returnChangeNo + lineNo as the flatten key.
2. Hard-coded processStatusList. Source status codes are 4-digit numbers (e.g. 1001, 1004). A missing comma returns an empty page silently. Move the list to a global constant in the platform so business changes touch one place.
3. Pagination infinite loop. When the volume is exactly a multiple of pageSize, the last page is empty but pageIndex keeps incrementing. Use "page size < pageSize ⇒ end" as the termination condition.
4. Join-lookup timeout. The source order lookup has a 3-second timeout and drops requests at peak. Add a 1.5s pre-timeout in Qeasy and route failed documents into a retry queue.
5. Wrong idCheck toggle. Source-side idCheck must stay false; only the target-side write should flip it to true. Flipping it on the source side causes the very first pull to be rejected as a duplicate — the most common mistake for new engineers running this strategy.

When to Use and When Not to

Use when: the source supports time-based incremental query, the target writes line by line and requires idempotency, and the return volume is large with many line items.

Do not use when: sub-second real-time is required (use a message queue instead), document status rolls back so the modification time is unreliable, or the source has no pagination support (coordinate a vendor change first).