Authoritative Tutorial: Jushuitan Refund & Return Query API Field Manual (Non-Qimen)

What Problem Does This API Solve

The Jushuitan "Refund & Return Query" API is used to pull after-sales orders (returns, refunds-only, exchanges, re-deliveries, etc.) from e-commerce platforms back into a local data warehouse. It is commonly seen in scenarios such as localizing after-sales data, return/exchange analysis, financial reconciliation, and inventory back-flushing for retail companies. Once landed in MySQL, the data can be linked with sales orders and inventory flows to close the after-sales loop of the supply chain.

API Capability Overview

• Endpoint: /open/refund/single/query (non-Qimen direct-connect version; field set differs from the Qimen variant P2-010)
• Authentication: Jushuitan Open Platform Access Token, application-level authorization
• Request Structure: POST with form/JSON body. modified_begin / modified_end define the modification time range; date_type selects the time field type (0 = modified, 2 = io_date outbound time); it cannot be empty together with the online order number, and the interval must not exceed 7 days
• Pagination/Incremental: Incremental based on modification time. Across many customer projects, schedules are typically hourly (15 */1 * * *), deduplicated by as_id on write
• Response Structure: Single-layer after-sales object with flat header fields and an items array for line items that must be flattened on write
• Write Mode: MySQL side typically uses REPLACE INTO with primary key deduplication; the AfterTargetGenerate script converts empty values to null and filters out emoji and characters outside the BMP

Typical Field Mapping

Header Core Fields

Line Item (items_*) Core Fields

How to Configure on Qeasy Cloud

On the Qeasy Cloud Data Integration platform, configuring this API typically follows four steps:

1. Adapter Selection: Choose /open/refund/single/query from the "Jushuitan · Non-Qimen" adapter; authentication and time-range encapsulation are pre-built.
2. Field Mapper: Map header fields 1:1; line items go through the "line-item flattening" node and are automatically prefixed with items_ before being written to the target table.
3. Incremental Strategy: Use the "Incremental by modified" component, persisting the last successful modified_end into a schedule variable so the window slides automatically without breaching the 7-day cap.
4. Post-Write Processing: The platform's built-in AfterTargetGenerate hook normalizes empty values and filters emoji and non-BMP characters; the default write into MySQL uses REPLACE INTO to avoid task interruption from duplicate primary keys.

Cross-Project Best Practices

1. The 7-day time window is a hard limit: Long backfill tasks must be sliced into 7-day chunks; you cannot request a whole month at once.
2. date_type defines the incremental baseline: The default 0 (modified) covers most after-sales status changes; if pulling by io_date, note that modified stops changing once Confirmed.
3. Line items must be flattened: Writing the items JSON array unflattened breaks the schema and tanks query performance.
4. Status fields are financial triggers: status transitioning from WaitConfirm to Confirmed is the real signal to flush inventory; Cancelled must be filtered out separately.
5. Amount fields are granular: refund, payment, buyer_apply_refund, buyer_receive_refund, and urefund cannot be used interchangeably; reconciliation scripts must pin down each semantic.
6. Multi-org dimensions are mandatory: owner_co_id, drp_co_id_from, drp_co_id_to are almost always used in distribution and multi-org customers; pre-build indexes.

Pitfall Post-Mortems

1. Time interval over 7 days errors out directly: One customer requested a 30-day window at once and got "time interval exceeds limit". The safe approach is to add a "7-day slicing" component in the Qeasy Cloud scheduler.
2. Emoji and special characters break MySQL writes: Buyer nicknames and remarks containing emoji triggered utf8 errors; filtering must happen at the AfterTargetGenerate stage.
3. Un-flattened items gives an empty query result: Reading the items field directly off the header table returns a JSON string; the business side thinks no data was pulled. Always flatten.
4. Cancelled orders wrongly back-flush inventory: Ignoring the Cancelled status caused every after-sales record to be deducted, leading to negative stock. Adding a status='Confirmed' filter fixes it.
5. as_id duplicate insert fails: Occasional re-push combined with INSERT instead of REPLACE INTO killed the task. Switch to REPLACE INTO and add a unique key.

When to Choose This API

This API fits: e-commerce retail that needs localized after-sales data, reconciliation with ERP/WMS, return/exchange analysis, and inventory back-flushing. Boundary notes: if you need real-time platform push, prefer Qimen or message-driven push; this API only polls by modification time and a single window cannot exceed 7 days.