Interface Field Handbook: Querying WMS Orders with Unmaintained Material Weights — From MySQL to DingTalk Alerts

What Problem This Interface Solves

In WMS outbound operations, orders often appear where the material master data has an unmaintained or zero "gross weight." Such orders break downstream logistics billing and weight validation. A common practice is to periodically pull these abnormal line items from the MySQL outbound order database and push them to a DingTalk robot to remind operators to complete material weights. This interface is designed as a pure query capability for that "order data quality inspection" scenario.

Interface Capability Overview

• Data source: MySQL (on-premise deployment, WMS outbound order database)
• Request method: POST, method is select (direct MySQL query)
• Strategy type: QUERY (query only; results pushed to DingTalk robot, not written to a target system)
• Joined tables: outbound_order (o, outbound order header), outbound_orderitem (oi, outbound order line), base_goods (g, material master)
• Filter conditions: o.createTime >= :realTime AND o.houseId = 'specific_warehouse_id' AND (g.grossWeight IS NULL OR g.grossWeight = '0')
• Incremental mode: Incremental query by createTime using the :realTime placeholder
• Execution frequency: crontab 0-59/10 * * * *, runs every 10 minutes
• Response structure: Standard MySQL two-dimensional result set; id is the primary key and number is the business code in metadata

Typical Field Mapping

How to Configure on Qeasy

In the Qeasy data integration platform, this kind of direct MySQL query interface is typically wrapped by the "Database Adapter." There are three main points to focus on during configuration:

1. Data source configuration: Enter the MySQL connection information (database name, account, and a read-permission account is enough); the platform will automatically generate the select request template.
2. SQL template: Write the three-table JOIN statement in Qeasy's "Custom SQL" step, passing :realTime as a parameter. The platform automatically maintains the incremental value based on the last successful run time. Qeasy's field mapper automatically maps the metadata id to outOrderItemId and number to outorderno.
3. Target push: DingTalk robots have a native "Message Push" component in Qeasy. Assemble the query results into an alert message using the three variables {{outorderno}}, {{goodsCode}}, and {{goodsName}} — no extra coding required.

Cross-Solution Key Points

The following common lessons are distilled from multiple customers' MySQL + Kingdee Cosmos sales order integration projects:

1. Three-table JOIN is the standard: Order lines without weight must join the header table for order numbers and the material table for weights — there is no shortcut.
2. NULL and '0' must both be checked: The weight field in material master data is inconsistently typed — some store NULL, some store the string '0'. Missing either condition in the filter will leak data.
3. Be cautious with hardcoded warehouse IDs: Different customers use different houseIds in real scenarios. It is recommended to extract the warehouse ID as a parameter for easier multi-warehouse switching.
4. Use createTime, not updateTime, as the incremental anchor: Outbound orders rarely change after creation; using createTime for incremental queries is stable. updateTime, on the other hand, gets disturbed by various unrelated updates.
5. Every 10 minutes is sufficient: This kind of inspection query does not need to be real-time. A 10-minute interval catches problems promptly while not putting pressure on MySQL.
6. DingTalk alerts must carry "actionable information": Just pushing an order number is meaningless; you must include the material code and material name so operators can directly locate and maintain them.

Pitfall Recap

1. Pitfall 1: Inconsistent grossWeight types Some materials store decimal 0.00, some store string '0', and some NULL. The safe approach is to write the filter as (g.grossWeight IS NULL OR CAST(g.grossWeight AS CHAR) = '0' OR g.grossWeight = 0), covering all three cases.

2. Pitfall 2: Missing warehouse ID filter causes full-warehouse scan Early solutions did not restrict houseId, resulting in pulling orders from other warehouses and exploding DingTalk messages. Always hardcode o.houseId = 'specific_warehouse_id' in SQL.

3. Pitfall 3: Incremental time window overlap Running every 10 minutes, but the MySQL timezone and server timezone may differ by 8 hours, causing the same batch of orders to be pushed repeatedly. It is recommended to unify on the server timezone and enable "deduplicate by primary key" in Qeasy.

4. Pitfall 4: DingTalk message rate limit exceeded One batch may pull hundreds of line items, and a DingTalk robot has per-group message rate limits. The safe approach is to add an aggregation step in Qeasy, merging line items under the same order number into one message.

5. Pitfall 5: Material deleted but order still exists When a material in base_goods is physically deleted, the LEFT JOIN loses the record. The safe approach is to use LEFT JOIN + IS NULL check while using oi.goodsId as a fallback to ensure the problematic order can still be located.

When to Use

Applicable scenarios: WMS material master data governance, outbound order weight validation, logistics billing pre-check, scheduled DingTalk robot inspection. Not applicable: high-frequency real-time weight validation scenarios for billing (use a weight validation interface instead of an inspection query), and mature WMS systems where material weights are 100% maintained.