A full WMS integration for a multi-subsidiary electronics distributor

#The situation

A distributor runs its warehouses on a WMS that only exchanges fixed-layout flat files over SFTP. Orders, receipts, returns, stock counts and light assembly work all happen in the warehouse. The books, the customers and the order book are in NetSuite. Without an integration, every movement on one side had to be keyed again on the other.

#What we built

A file-based integration in two Map/Reduce jobs, packaged with SDF.

NetSuite to the warehouse. Saved searches pull the data the warehouse needs, in nine groups: customers, vendors, items, purchase orders, transfer orders, return authorizations, fulfilment requests, work orders and invoices. The job converts each group to the warehouse's layout and writes it as a file to the SFTP server. Work orders for programmed products are merged with their sub-orders, and duplicate components removed, so the warehouse receives one pick list.

The warehouse to NetSuite. The inbound job lists the files waiting on the server and parses each one into rows. It then creates the matching NetSuite transactions, which are item fulfilments, receipts against purchase orders, returns and transfers, inventory adjustments, inventory counts, assembly builds and work-order completions. It also writes back shipment status, and stores serial numbers from the warehouse against their items.

A second warehouse site runs through its own profile of the same jobs, with its own folders.

#What it does now

Files move on a schedule in both directions. Before creating a record, the inbound job checks whether that warehouse document already produced a NetSuite transaction, such as a fulfilment, transfer or adjustment, and skips it if so. Processed files move to an archive folder. The job also reads the error files the warehouse sends back, so a rejection on the warehouse side reaches NetSuite.

At the end of each run, the summary stage collects the errors from every stage and emails them to the administrators, and a log record keeps the detail.

#Under the hood

• Custom records define the file layouts (field, position, body or line), so a layout change is a data change.
• The existing-transaction check makes it safe to process a file again.
• Extraction, conversion, transport and transaction handling are separate libraries.
• The SFTP secret sits in a credential field set through a Suitelet, and the jobs read it from there.
• Archive folders on both sides keep a record of every file.
• The scripts, records and searches move between accounts together as one SDF project.