Practical Tutorial: E-commerce Purchase Receipt to Financial VMI Purchase Receipt (Document Number Write-back)

What This Strategy Solves (Scenario and Value)

For a retail company, once an e-commerce purchase receipt is pushed down to the financial system, a VMI (Vendor Managed Inventory) purchase receipt is generated on the financial side. If the document number produced there is not written back to the SQL Server side, the two systems are completely disconnected: finance has to look in two places when auditing, and business users have to match documents manually. This strategy does one thing only — document-number write-back. It writes the VMI purchase receipt number back into the integration number field of the SQL Server purchase receipt, so either side can trace the other at any time.

Data Flow and Field Mapping (Source → Middle Layer → Target)

The data flow is straightforward: the source is the integration platform's internal write-back records, auto-filled via _autoFillResponse; the target is the SQL Server purchase receipt table T_STK_InStock.

Key field mapping:

There are no detail rows (children) in this write-back, because only one header field is updated and no document body is pushed down.

How to Configure on the Integration Platform

When configuring this strategy on the Qeasy data integration platform, there are several typical points:

1. Select "Request Empty Operation (QUERY)" for the source interface and set autoFillResponse to true. The source does not need an external request; the platform automatically picks up sId, number, BillNo, Id, and strategyId from the process context.
2. Select update (EXECUTE) for the target interface and use otherRequest.main_sql to write a parameterized UPDATE statement rather than field-by-field mapping:
   sqlCopy code

   update a1
   set F_CDPS_INTEGRATIONNO = :number
   from t_STK_InStock a1
   where a1.FID = :sId
   

3. main_params is assembled automatically by the platform based on the source response. As long as source field names match the SQL parameter names (:sId, :number), binding is complete — no extra scripts needed.
4. Centralized code mapping management: keep mappings such as sId → FID and number → F_CDPS_INTEGRATIONNO in one place. If the VMI document type changes later, only this strategy needs editing.
5. No scripts required: all mappings here are DIRECT. There is no need to write Python or JS, which keeps maintenance costs low.

Implementation Steps (Phased Scheduling)

The recommended rollout order is:

• Phase 1: Confirm the incremental starting point. The prerequisite strategy "e-commerce purchase receipt → VMI purchase receipt" must work first, otherwise the financial side cannot generate a VMI number and there is no number in the platform context to write back.
• Phase 2: Trigger a full backfill once. On first go-live, trigger manually to backfill all historical purchase receipts that have not yet been written back, and verify that the SQL executes correctly and the field is updated.
• Phase 3: Configure scheduling frequency. Source cron 1 1 6 6 * (1:01 on June 6) is the production rhythm reference for write-back records; target cron 5-59/35 * * * * (at minutes 5 and 40 of every hour) scans and executes the UPDATE. This dual-track of incremental and full-volume keeps daily write-backs low-latency while allowing fast catch-up after incidents.
• Phase 4: Reconciliation validation. After write-back, run a SQL check on the coverage of F_CDPS_INTEGRATIONNO IS NOT NULL and its match rate against the financial-side number.

Pitfalls Recap

1. A common mistake is treating write-back as a "push-down". This strategy updates only one header field, with no children. Misunderstanding it as carrying detail rows will produce looping mappings, causing the strategy to spin idle or even error out.
2. sId and number are not interchangeable. sId is the SQL Server internal ID FID and only goes into the WHERE clause; number is the financial-side document code and only goes into the SET clause. Swapping them will write someone else's document number onto the wrong record.
3. Null value write-back will overwrite existing values. If the financial side fails to generate a number, number is empty and a direct UPDATE will clear the existing integration number. The safer approach is to add AND :number IS NOT NULL in SQL, or add a precondition filter in the strategy.
4. Scheduling must align with the prerequisite strategy. If the financial-side push runs hourly, the write-back must start later than it, otherwise it will pick up an empty number.
5. Watch database permissions in on-premise deployment. The SQL Server account needs UPDATE permission on T_STK_InStock and write permission on the schema that holds F_CDPS_INTEGRATIONNO. Otherwise failure details will not surface on the integration platform and troubleshooting becomes painful.

Applicable and Non-applicable Scenarios

Applicable: header-level document number write-back, cross-system document linkage tracing, reconciliation field backfill, centralized cross-system code mapping maintenance. Not applicable: scenarios that require detail-row write-back, scenarios requiring complex business validation (such as amount or tax), or one-time large-volume initialization (which should go through a full-volume sync strategy instead of a number write-back).