Purchase Requisition Sync in Practice: A Field-Level Passthrough from WMS to SQL Server Staging Tables

What This Strategy Solves (Scenario and Value)

The purchase requisition is one of the source documents in a supply chain. In one real project, a retailer used the WMS flagship edition as the procurement entry point, and all internal BI plus custom ERP modules relied on SQL Server staging tables for joins and aggregations. The pain points were obvious: scattered API calls, inconsistent field naming, and line items that often lost their link back to the header. We used the Qeasy Data Integration Platform to take over this link, pushing WMS purchase requisitions to two SQL Server staging tables every five minutes on an incremental basis, so downstream consumers could just read from the tables instead of each calling the API independently.

Data Flow and Field Mapping

The flow is one-way: WMS Flagship Edition → SQL Server. The source side is a QUERY-type API, purchase.PurchaseApply.search. The target side is an EXECUTE-type insert that writes the header and line tables in a 1:1 fashion.

Key field mapping for the header table (t_mid_purchase_apply):

Key field mapping for the line table (t_mid_purchase_applyEntry):

The header purchase_apply_no is propagated to the line table as the header-line foreign key. Aggregated fields on the source header such as num, ref_num, and provider_no are intentionally not written to the target header — that is a deliberate design boundary.

How to Configure It on Qeasy

On the Qeasy Data Integration Platform, this strategy is intentionally lightweight:

1. Source platform: Connect to the WMS Flagship Edition, pick the purchase.PurchaseApply.search API. Use {{DAYS_AGO_3|datetime}} as start_time, default end_time to 2025-12-31 23:59:59, page_size=100, and let the paginator drive page_no.
2. Target platform: Connect to SQL Server. The main statement is insert into t_mid_purchase_apply(...), the 1:1 extension statement is insert into t_mid_purchase_applyEntry(...), and extend_params_1 is set to detail_list.
3. Field mapping: All DIRECT. Just drag fields to their targets. The only naming caveat is remark → remark2.
4. Scheduling: Source uses */5 * * * *, target uses 2-59/5 * * * *. The two-minute offset avoids read/write contention — a common "staggered scheduling" pattern among Qeasy customers.
5. No encoding mapping: Because this lands into staging tables rather than a business system, there is no need for shop/customer/material crosswalk tables.
6. No scripts: No _findCollection, no _mongoQuery, no custom functions. Everything relies on field passthrough plus header-line linkage.

Implementation Steps

We usually break the rollout into four phases:

1. Schema and permissions: Create t_mid_purchase_apply and t_mid_purchase_applyEntry in SQL Server first. The header primary key is purchase_apply_no; the line primary key is the composite (purchase_apply_no, rec_id). Build an index on purchase_apply_no + created.
2. Incremental anchor configuration: Use DAYS_AGO_3 for start_time instead of a fixed historical date, to avoid a one-off backfill of ancient data. Point end_time to a far-future timestamp so the platform naturally converges on the current time.
3. One-shot full trigger: At first go-live, temporarily set end_time to the day before cutover and run a deliberate full backfill. Verify that the line-to-header ratio and the line count match the source detail_list length, then switch back to the regular schedule.
4. Frequency and monitoring: Pull from the source every five minutes, write to the target offset by two minutes. In Qeasy, attach alerts to three metrics: source-side pulled rows, target-side written rows, and the line-per-header ratio. Investigate immediately if any of them goes abnormal.

Lessons Learned

1. Line rows are easy to lose: extend_params_1 must be detail_list, and the :purchase_apply_no bind in the 1:1 extension statement must come from the header. If you treat the line as a plain array and forget the foreign key, you will end up with "orphan lines".
2. The remark name collision: Both the header and the lines have a remark on the source side. To disambiguate, the target side renames the line column to remark2. During mapping, double-check whether you are wiring the header remark or the line remark. A typical mistake is to skip it or wire it twice.
3. Do not translate status codes in the staging table: status is an integer enum on the source side (5/10/20/23/25/30). The safe approach is to store it as-is and let the consumer layer translate it to a label via CASE. Translating it early loses semantics and makes future downstream integrations painful.
4. Do not hard-code time windows: Writing end_time as 2025-12-31 looks harmless, but in 2026 the pipeline will silently stop pulling. Using a variable or a far-future timestamp lets the platform converge naturally on the current time — a small but critical piece of any incremental-plus-full dual-track strategy.
5. Staggered scheduling is not decoration: When both source and target use */5 * * * *, you get contention where a new header round arrives just as the previous header is still being written. Setting the target to 2-59/5 * * * * gives the source a window to breathe and noticeably improves chain stability.

When This Applies and When It Does Not

Applies: WMS is the procurement entry point, and downstream BI, reporting, or in-house systems need stable, traceable purchase requisition data with no semantic rework.

Does not apply: scenarios where WMS purchase requisitions must be converted directly into ERP purchase orders — state machines, approval flows, and document conversion rules belong in a separate strategy. It also does not apply when SKU or vendor cross-system encoding mapping is required; the staging tables should not carry that burden.