BDS Reconciliation Banniu After-sales Claim Order Sync Strategy Tutorial
What This Strategy Solves
In one retail engagement, after-sales claim orders lived inside a third-party work order platform (Banniu), while the finance and reconciliation teams needed the same data inside their in-house MySQL data warehouse—rebuilt per bill number, with cost allocation and supplier settlement logic. With no automated channel in place, customer service exported CSVs by hand and handed them to the data team, which meant a typical 3-day delay and painful reverse-tracing whenever an attachment was missing. We used the Qeasy Data Integration Platform to wire a single strategy: periodically pull Banniu work orders tagged as "claim," then write them into MySQL's claim header table and detail table, aligning the two systems on the same reconciliation view.
Data Flow and Field Mapping
The flow is one-way: Banniu (source) → Qeasy middleware → MySQL (target). The middleware handles pagination aggregation, field normalization, and primary-key deduplication. On landing, a main SQL writes the header row and an extended SQL uses lastInsertId to write the detail rows.
| Domain | Source (Banniu work order) | Middleware mapping | Target (MySQL header / detail) |
|---|---|---|---|
| Bill number | task.id / custom number | bill_no | supplier_return_change.bill_no |
| Related trade no | trade_no | passthrough | trade_no |
| Status | status enum | status dictionary | status |
| Responsible party | responsible_party | centralized code mapping | responsible_party, responsible_party_detail |
| Cost party | cost_party | passthrough | cost_party |
| Warehouse | warehouse_code/name | code alignment | warehouse_code, warehouse_name |
| Logistics provider | logistic_name | passthrough | logistic_name |
| Supplier | suppiler_code/name | code mapping | suppiler_code, suppiler_name |
| Amounts | claim_amount / confirm_* | numeric normalization | claim_amount, confirm_claim_amount, etc. |
| Create time | create_time | timezone unification | create_time |
The detail table (supplier_return_change_detail) is written through extend_sql_1, with the header's lastInsertId injected as order_id to tie header and lines together.
Configuring This Strategy in Qeasy
In Qeasy, this strategy is decomposed into a source operator and a target operator:
- Source operator: platform "Banniu", API
task.list(GET, RESTful, QUERY). Configure request parameters:project_id(the group ID where claim work orders live),page_size=50,page_num=1, plusstar_modified/end_modifiedas the incremental window. SetidCheck=trueandautoFillResponse=trueso Qeasy takes over pagination. - Target operator: platform "MySQL", API
executeReturn(POST, EXECUTE). SetbuildModel=true. Put header fields intomain_paramsand the detail array intoextend_params_1. Themain_sqlandextend_sql_1are written as SQL templates with<{field: }>placeholders. The detail row's foreign key uses<{lastInsertId: }>so Qeasy auto-injects the header's generated ID. - Scheduling:
crontabis*/30 7-23 * * *, covering business hours of the after-sales flow.
A common pattern among Qeasy customers is centralized code mapping: high-churn dictionaries (responsible party, supplier, warehouse) are kept in a dedicated mapping table that the strategy consults at runtime, so business teams change mappings, not the strategy itself.
Implementation Steps
We staged the rollout into three phases:
- Phase 1: Align the incremental starting point. Pick a historical "waterline" in Banniu, set
star_modifiedto that point, run a single sample withpage_size=10andpage_num=1, and manually verify that header-to-detail rows are truly 1:N. Only widen the scope once it checks out. - Phase 2: Trigger the full backfill. Bump
page_sizeto 50 and pushstar_modifiedto the earliest meaningful timestamp, then run one full historical backfill. Pause business-side writes during the backfill, or enable idempotency protection. - Phase 3: Daily scheduling. Switch to the
*/30 7-23 * * *incremental cadence. The source side uses a sliding window ofstar_modified = last success timeandend_modified = current run time. The target side relies on a unique index onbill_nofor idempotency, so network jitter or reruns will not cause duplicate writes.
The dual-track (full + incremental) pattern is widely adopted by Qeasy customers: full backfills run as ad-hoc plans, while daily traffic runs on incremental schedules, with a safe rerun path whenever something goes wrong.
Pitfalls and Lessons Learned
- Detail rows silently dropped. The first attempt only configured
main_paramsand forgot to enableextend_sql_1, so the entire detail array was discarded. The safe rule: any 1:N source must wire the main SQL and the extended SQL together, with<{lastInsertId: }>carrying the foreign key. - Timezone drift on
star_modified. Banniu's default timezone differs from the MySQL warehouse's, so passing the value through shifts the incremental window by 8 hours and causes the same bill to be written twice. Normalize the timezone inside the Qeasy middleware. - Hard-coded dictionaries. Encoding responsible-party values directly into the SQL means every dictionary change requires a redeployment. Move the dictionary into Qeasy's centralized code-mapping table instead.
- Detail column order swapped. The source fields
claim_amount_detailandconfirm_claim_amount_detailare easy to mix up. MySQL'sVARCHARwill accept the swap silently, but the reconciliation numbers will be wrong. Enable Qeasy's field length and type checks. - Schedule colliding with rate limits.
*/30looks sparse, but Banniu's interface has QPS thresholds and parallel runs will trigger 429s. Add Qeasy's rate-limiting backoff and a retry window.
When to Use and When Not
Use it when: Banniu work orders and a MySQL reconciliation/settlement warehouse need 1:N synchronization, the business side wants bill_no-based idempotency, and dictionaries change frequently.
Avoid it when: real-time second-level push is required (this strategy runs at a 30-minute minimum), the source is a direct database connection rather than a work-order system, or the target is a non-relational store.