Return-Order Sync in Practice: Pulling Returns from the Marketing Cloud into SQL Server Header and Line Tables

What This Strategy Solves (Scenario and Value)

In one retail project, the return workflow starts in the marketing cloud: stores or distributors submit return orders there and complete audit. But the financial and inventory final posting lives in the ERP running on SQL Server. Without bridging the two, you end up with "return orders audited in the marketing cloud that never appear in SQL Server," which inflates inventory and delays reconciliation.

What the return-order sync strategy does is straightforward: pull audited return orders from the marketing cloud on a schedule, broken down by distributor, and land them into the return-order header and line tables on SQL Server so both sides stay consistent. This strategy is one link in the full purchase-to-return chain and typically fires as sequence B, right after purchase receiving.

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

The overall flow is Marketing Cloud (B) → Qeasy middle layer → SQL Server (A). The source side is the marketing cloud's /erp/api/order/query/saleReturnOrder query API; the target side is two INSERT statements on SQL Server.

Key field mapping:

There is a classic trap here: the tenantId from the marketing cloud belongs to its own system, while wldwid on SQL Server is the ERP's internal partner-unit identifier. The two are not the same coding scheme. On one real project we saw exactly this — no mapping layer, both sides treated the raw string as the primary key, and three months later reconciliation was completely off. The reliable approach on Qeasy is to build a centrally managed code-mapping table (distributor id ↔ ERP partner-unit id), pass the pulled tenantId through that mapping first, and then write it into wldwid. Every other strategy that touches the same distributor can share that one table, so changes only need to happen in one place.

How to Configure It on Qeasy

Source-side configuration points:

• API: marketing cloud /erp/api/order/query/saleReturnOrder, method POST.
• Required: distributor id (tenantId).
• Incremental window: use beginTime together with the built-in variable {{LAST_SYNC_TIME|datetime}}, so each run only picks up records that changed after the last successful sync. endTime defaults to the current scheduling time.
• Status filter: status=1, so only audited return orders are pulled — this avoids polluting the ERP with un-audited documents.
• Document-number precedence: if the upstream passes yxyNumber or number, the time conditions are ignored — this is a hidden rule of the API and easy to miss on first setup.

Target-side configuration points:

• Use main_params to carry header parameters, and extend_params_1 (array, default itemList) to carry the line items.
• SQL uses named-parameter syntax (:djbh, :spid and so on); Qeasy binds by field name, do not concatenate strings.
• The main SQL writes to gxkphz, the extension SQL writes to gxkpmx, and both SQL statements sit in the same strategy with staged header-then-line execution — this is the recommended pattern on Qeasy for documents with detail lines such as returns or purchases: first write the header and obtain djbh, then batch the lines. If any line fails the whole thing rolls back, so you never get an "orphan header."
• Turn on idCheck so Qeasy validates against the primary key before writing, to prevent duplicate inserts.

Implementation Steps

In the full chain, return-order sync typically appears as sequence B. Schedule it in three phases: incremental start point → full-volume trigger → daily scheduling.

1. Incremental start point: in Qeasy, set a first-sync timestamp (e.g. the day before go-live) and start pulling from that point via beginTime.
2. Full-volume trigger: before the first cutover, run a manual or scheduled job to backfill historical audited return orders. Only switch to incremental mode after the backfill completes. Do not skip this step — old orders will never enter otherwise.
3. Daily scheduling: source side */6 7-23 * * * (every 6 minutes during business hours), target side */5 7-23 * * * (slightly denser than source, so the downstream can keep up). Stagger the two by a minute or two so they do not run at exactly the same moment and overwhelm the database.

Pitfall Recap

• Code mapping not centrally managed: the distributor id ↔ ERP partner-unit id mapping was scattered across multiple strategies. A new distributor was added once and someone forgot to update one of them, so that return order landed in ERP with an empty partner unit and only surfaced at month-end reconciliation.
• Header and lines not staged: in an earlier design, someone tried to splice all the return lines into a single SQL loop. After one line failed the header was already written, producing "headers without tails." After moving to staged execution, failures roll back as a whole and the data stays clean.
• Incremental window used creation time: the marketing cloud API's timeType defaults to creation time, but return orders can be modified after audit, so creation time alone misses updates. Switching timeType to update time lets amended orders be caught up.
• Scheduling frequency too aggressive: at one point both links were scheduled at the same minute. The source side was still mid-batch when the target side fired and the database connection pool was exhausted. Staggering by 1-2 minutes fixed it immediately.
• idCheck left off: the same return order got inserted twice on a re-run, triggering negative-inventory alerts in ERP. Turning on idCheck solved it.

Applicable and Non-applicable Scenarios

Applicable: return documents need to be aggregated between the marketing cloud (distributor-side system) and the ERP on SQL Server, where documents have a clear "audited" status and header-and-line consistency is required.

Not applicable: when sub-second real-time is required, when the return flow needs to write back from ERP into the marketing cloud, or when the distributor side is not the marketing cloud but other channels — those scenarios call for a bidirectional sync design or a near-real-time channel instead.