Purchase Return Material Order Audit Result Callback: A Practical Sync Solution on Qeasy

What This Strategy Solves

In the MES workflow of a manufacturing client, a purchase return material order is initiated on the shop floor and submitted for audit. After the audit, the result (pass / reject / message) must be written back to an interface table in MySQL so that ERP and shop-floor systems stay in sync. The problem: the audit flow runs inside the MES, which does not directly update that interface table. Once the callback is delayed or lost, what the shop floor sees drifts away from what ERP sees, and the discrepancy is only discovered three months later when the numbers stop matching. This strategy uses the Qeasy Data Integration Platform to close that loop.

Data Flow and Field Mapping

The chain is a one-way "query → writeback" flow. The source side is an internal node in the Qeasy integration platform (a no-op query placeholder that triggers audit-result collection), and the target side is the MySQL interface table wms_po_result_iface. Both segments are orchestrated as independent strategies, joined by the document number.

Key field mapping:

How to Configure It on Qeasy

Source strategy (Qeasy → collect audit result): choose API "request no-op", effect = QUERY, method = POST. Declare response fields explicitly: is_sucess, result_message, SuccessEntitys. Leave request bodies empty. Set autoFillResponse to true so the structure returns normally even without external request parameters.

Target strategy (Qeasy → MySQL): choose API update, effect = EXECUTE, method = SQL. Write main_sql inside otherRequest and bind with named parameters:

update ty_mes.wms_po_result_iface
set message = :message,
    STATUS = :is_success,
    attribute14 = :time,
    attribute15 = :Id
where instruction_doc_num = :sourceid and return_order in (1,2,3)

main_params is passed in as an object whose fields correspond one-to-one with the named parameters.

Two lessons on centralized management: first, keep the is_sucess → STATUS semantic mapping inside Qeasy's field-mapping script, not scattered across SQL. Second, the value rule for instruction_doc_num is owned entirely by the upstream strategy; the target strategy only does matching and does not infer semantics.

Implementation Steps

We usually go live in three phases: incremental starting point → full-volume trigger → scheduling cadence.

Incremental starting point: first confirm where the MES audit result lands (usually a MES interface view or staging table). Use the document's last-modified time as the incremental anchor, and only callback the most recent 24 hours of data on the first run to validate the chain.

Full-volume trigger: once the check passes, switch to a full scan and run one historical backfill to close the backlog. This step is best placed in a business off-peak window.

Scheduling cadence: source crontab is set to */3 * * * *, target */2 * * * *. The misalignment is intentional—the source collects every 3 minutes, the target writes every 2 minutes, leaving a 1-minute processing window so the target does not run empty before the source finishes. Phased rollout (header first, lines later) is a common pattern among Qeasy customers: write back the header status first, then add line items once it stabilizes.

Pitfalls and Lessons

Pitfall 1: status field semantics mismatch. The source returns the string Y/N, but the target STATUS column historically held 1/0. Passing through directly breaks the ERP-side judgment logic. The safe approach is to normalize once inside the Qeasy mapping script, not by altering the SQL.

Classic mistake 2: incomplete match on instruction_doc_num. The SQL in the source material explicitly adds and return_order in (1,2,3). This is because the same instruction document number can map to multiple return types in the interface table. If a new engineer lazily matches only on the document number, rows that should not be touched will be updated together.

Pitfall 3: no idempotency. Audit results can be pushed repeatedly (for example, the MES retries). Without an idempotency marker such as attribute15 = :Id, the same audit message will be overwritten again and again. The safe approach is to generate a unique Id on each callback, write it into attribute15, and rely on it for traceability.

Classic mistake 4: hard-coded time format in scripts. If the time field is hard-coded to yyyy-MM-dd HH:mm:ss inside a Qeasy script, time zone or daylight-saving differences will shift the value. Prefer the platform's built-in time functions.

Pitfall 5: source and target scheduled exactly in sync. Many clients start by setting both crontab entries to */1 * * * *. The target then runs faster than the source and triggers empty-run alerts. Staggered schedules plus a "did the source produce new data?" pre-check at the target are the standard practice for dual-track incremental-plus-full-volume rollouts.

When This Applies and When It Does Not

Applies when: MES owns the audit flow, the result needs to be asynchronously written back to a MySQL / ERP interface table, and the callback tolerance is on the order of minutes. Does not apply when: real-time (second-level) bidirectional coordination is required, or when the audit flow already runs inside the ERP and no cross-system sync is needed.