Getting shipping data onto the 856 without anyone touching it

The business case is in EDI didn’t go away when everything went API. This is the plumbing: a scheduled Map/Reduce that copies carton contents and tracking from the shipping system’s package records onto the package records the EDI provider reads when it builds the 856 (the same pattern applies whether that provider is OpenText, SPS Commerce, TrueCommerce or another), plus the inbound side where 850s become sales orders.

1. A copy that runs once misses tracking that arrives later

The original job selected fulfillments that had shipping packages but no EDI package records yet, created them, and never looked again:

// input: shipped fulfillments with no EDI package rows yet
filters: [
  ['status', 'anyof', 'Shipped'],
  'AND', ['edi_package.internalid', 'anyof', '@NONE@']
]

That works for parcel, where the tracking number exists at label time. It fails for freight. An LTL shipment is marked shipped when it leaves the dock, and the carrier’s PRO number arrives hours or days later. By then the EDI rows exist, so the @NONE@ filter excludes the fulfillment forever, and the 856 goes out with blank tracking.

The fix adds a second input: package records whose tracking has changed since they were last copied. The detail that took the most time is which modified date to watch. When tracking arrives, the shipping system updates its own package record, not the fulfillment, so filtering on the fulfillment’s last-modified date catches nothing:

SELECT pk.id, pk.fulfillment, pk.tracking
FROM   ship_package pk
JOIN   edi_package  ep ON ep.fulfillment = pk.fulfillment
                      AND ep.carton    = pk.carton
WHERE  pk.lastmodified > SYSDATE - (2/24)
  AND  NVL(ep.tracking, '-') <> NVL(pk.tracking, '-')

Tracking now reaches the EDI rows within one run of the carrier assigning it.

2. Two items, one SKU

Some fulfillments produced no EDI package rows at all. The shipping system was reporting component SKUs for a kit, but the fulfillment contained an assembly with the same item number — the business had a kit and an assembly sharing a SKU. Matching carton lines to fulfillment lines by SKU found nothing, and the job skipped the shipment without a word.

The immediate fix was renaming the kit so the two could never be confused. The design lesson: match on the item’s internal identity wherever the systems allow it, and when you must match on SKU, log every carton that matched zero lines. A silent skip is the worst outcome on an ASN.

3. The ASN went before the data arrived

On one shipment the shipping system had the tracking number at label time but didn’t push its package records into the ERP for several hours. The EDI provider picked up the fulfillment in between and built an 856 with no cartons.

You can’t fix another vendor’s sync delay from the ERP, but you can stop sending on incomplete data. The saved search the EDI provider uses to select fulfillments for the 856 now requires the EDI package rows to exist with tracking populated, filtered on the records the provider actually reads:

['edi_package.tracking', 'isnotempty', '']

A shipment waiting on data now waits a cycle instead of going out wrong.

4. Inbound 850 failures that name no document

On the inbound side, failed 850 imports were reported by email: long, unformatted, mixing successes with failures, and missing the one thing anyone needed — which purchase order failed. Investigating meant opening the provider’s portal and searching by time.

The failures themselves were ordinary and fixable:

• Price verification. The 850 price didn’t match the ERP’s price for that customer and item. Partner pricing has to be maintained like any other price list, or every change becomes an import failure.
• Record collisions. The import tried to save an order that another process had changed a moment earlier. Scripts that touch new orders need to tolerate an import in flight, and the import needs a retry.

The improvement worth asking any EDI provider for is structure: a fixed block in the notification carrying the document type, partner, PO number and error. Once that exists, the email can be parsed into an exception queue in the ERP — one row per failed document, with the reason and a link — instead of a mailbox someone has to read.

What to take from it

1. Copy shipping data on change, not once. Freight tracking arrives late.
2. Watch the modified date of the record that actually changes.
3. Don’t let two items share a SKU, and never skip a carton silently.
4. Gate the 856 on complete data, filtered on what the EDI provider reads.
5. Turn import failures into structured exceptions with a document number.

Related: EDI didn’t go away when everything went API, and is the error you’re chasing the cause, or just a symptom?