Field Manual for Xiaomi Inventory Query (with stock_age_XM_VIEW): A Practical Tutorial from SQL Server to Xiaomi Supply Chain
What This Interface Solves
In VMI (Vendor Managed Inventory) collaboration scenarios between suppliers and Xiaomi, suppliers need to report real-time inventory from their own ERP/WMS systems to the Xiaomi supply chain platform, broken down by material, batch, and stock age. This interface queries detail data that conforms to Xiaomi's specifications from a SQL Server inventory view (including stock age), enabling inventory visibility and fine-grained collaboration by age segments.
Interface Capability Overview
- Authentication: The source SQL Server uses standard database credentials with JDBC connections; the target Xiaomi supply chain uses OAuth/app-key based authentication (using platform-issued appId/appSecret).
- Request Structure: The source side is a read-only SQL query against the
dbo.inventoryXMview, where the SQL is automatically assembled by the Qeasy adapter according to the strategy template. The target side is a POST write interface withrequest.bodyas a JSON array. - Response Structure: The source side returns a rowset, each row containing product_code, stock_num, in_stock_time, in_stock_qty, etc. The target side returns write result codes and details.
- Pagination/Incremental Mode: The source side performs incremental pulls by
in_stock_time+product_code, with Qeasy's incremental cursor persisting the last watermark; the target side performs whole-group overwrite/append writes bygroup_level: STOCK, without traditional pagination. - Execution Frequency: Commonly configured as crontab
50 23,5,11,17 * * *, i.e., 4 polls per day.
Typical Field Mapping
| Field Name | Type | Meaning | Practical Notes |
|---|---|---|---|
| factory_code | string | Factory/supplier code | Must strictly match Xiaomi's factory dimension; recommend dictionary validation in Qeasy |
| product_code | string | Customer material code (Xiaomi-side product unique ID) | Serves as business primary key; Qeasy's field mapper automatically participates in deduplication |
| vendor_product_code | string | Supplier-internal material code | Used for ERP/WMS reverse lookup; recommend a separate column, do not mix into product_code |
| component_code | string | Component/material type code | Used for BOM hierarchy; can be empty but reserve the field slot |
| product_desc | string | Material description/name | Watch UTF-8 encoding for Chinese to avoid '?' garbled characters |
| product_class | string | Product category | Static dictionary; usually constant-mapped in Qeasy |
| product_level | string | Product level | String type; do not convert to int even if value is '1' |
| common_status | string | Common status (Y/N) | Only report valid materials (Y); invalid data will pollute the target side |
| stock_num | int | Real-time inventory quantity | Negative indicates reservation/lock; confirm with business whether to report |
| stock_unit | string | Unit of measure (commonly PCS) | When units differ, unify at source side first; Qeasy can do unit conversion |
| stock_org | string | Inventory organization/warehouse org | Maps to Xiaomi-side organization code; recommend configuring a mapping table in Qeasy |
| asn_stock_type | string | ASN inventory type | Only meaningful in ASN scenarios; can be left empty for regular inventory |
| in_stock_time | string | Inbound time (batch dimension) | Key field for stock age calculation; must be ISO8601 format |
| in_stock_qty | string | Inbound quantity (batch detail) | Paired with in_stock_time to form stock_age groups |
In metadata, id/number are composed of {{product_code}}{{stock_num}}{{in_stock_time}}{{in_stock_qty}} to ensure no loss or duplication for same-product multi-batch scenarios.
How to Configure on Qeasy
On the Qeasy data integration platform, this interface is typically implemented using a dual-adapter combination: "SQL Server Adapter + Xiaomi Supply Chain Write Adapter". The source adapter assembles SQL per the strategy template and incrementally pulls dbo.inventoryXM; Qeasy's field mapper automatically aggregates in_stock_time and in_stock_qty into the target-side stock_age array, and writes group_level: STOCK as fixed. Scheduling uses Qeasy's visual crontab to configure 50 23,5,11,17 * * * without writing scheduler scripts. Incremental watermarks, failure retries, and checkpoint resume are all managed by the Qeasy runtime.
Cross-Scenario Practical Points
- Stable Composite Primary Key: For multi-batch sync, do NOT use only product_code as the unique key—this causes severe data loss. Always use the four-tuple
product_code+stock_num+in_stock_time+in_stock_qty. - stock_age Is an Aggregated Product: The source does not have this column; it is aggregated from in_stock_time/in_stock_qty on the target side. Implement this with an array transformation node in Qeasy's field mapper; do NOT try to assemble it in SQL.
- Align Stock Age Definition First: The segmentation rules ("30-day/60-day/90-day") must be agreed with the Xiaomi side, otherwise reported groups will be rejected.
- Single-Side Maintenance of Encoding Dictionaries: factory_code, stock_org, and product_class are strongly recommended to be centrally maintained as mapping tables in Qeasy—one change applies across the chain, avoiding SQL modifications.
- Distinguish Null from Zero: When inventory is 0 but the record is valid, report normally; only when the record does not exist should you skip reporting—otherwise it will be misjudged as missing data.
- Unit and Precision Final Check: When PCS/case/pallet are mixed, unify conversion in the source view; Qeasy only carries data and does not handle unit conversion.
Pitfall Review
- Garbled Characters: When SQL Server source fields are varchar instead of nvarchar, Chinese product_desc will become '?' when synced to Xiaomi. The safe approach is to define as nvarchar in the source view, or force UTF-8 parsing in Qeasy.
- Time Zone Drift: in_stock_time uses local time strings rather than UTC, causing stock age to drift 8 hours daily. Recommend storing as UTC at ingest and converting as needed in Qeasy mapping.
- Batch Merging Loses Details: Someone lazily used GROUP BY in SQL to merge multiple in_stock_times for the same product_code, leaving only one stock_age entry. Always preserve each batch row; let the target side do the aggregation.
- Crontab Collision: If sync duration exceeds 6 hours across the 4 time points, concurrent batches will occur. Enable the "same-strategy mutex" switch in Qeasy.
- Invalid Material Contamination: Materials with common_status='N' get reported together, causing the target side to reject the entire batch. The safe approach is to add WHERE filtering in source SQL, or filter them out in Qeasy's pre-filter node.
When to Use
This interface is suitable for supplier→Xiaomi supply chain inventory visibility, VMI collaboration, batch-level stock age reporting, etc. Prefer it when the source database is SQL Server and stock age grouping output is required. If only coarse-grained summaries are needed, or the source is not SQL Server, consider switching to a lighter inventory summary interface to avoid unnecessary complexity.