A 3PL warehouse run from NetSuite over SFTP

#The situation

An integrator keeps its project equipment at a UPS third-party logistics warehouse. The warehouse exchanges XML files over SFTP and nothing else. Every order to ship, every inbound delivery and every receipt was passed back and forth by hand, and nobody could say for certain what stock sat at the warehouse for which project.

#What we built

A two-way file integration, built as Map/Reduce jobs, with two Suitelets alongside.

• Outbound. The job checks items, fulfilment requests and purchase orders due at the warehouse for missing data, converts them to the warehouse's order-creation and advance ship notice XML, and uploads the files. It stamps each record with a sent time and skips anything already stamped. A purchase order with a line not yet cleared to send stays back whole.
• Inbound. The job downloads and parses receipt, order-created and order-shipped files. Receipts become item receipts against the right purchase order. Shipments become item fulfilments with the carrier tracking numbers. Each processed file moves to an archive folder, and a file that fails moves to an error folder, so no file is processed twice.
• Stock check. A reconciliation Suitelet takes the warehouse's on-hand export, parses it in the browser, keeps a copy in the File Cabinet and compares it line by line with NetSuite quantities by project and item.
• Purchasing view. A Suitelet replaces a saved search with a SuiteQL query that shows, for each project and item, what was ordered, received and requested, and who requested it.

#What it does now

The jobs run on a schedule, and scheduled emails do the chasing that people used to do. Requesters get a list of their fulfilment requests that failed. A daily summary lists requests the warehouse has not picked after a set number of days. The warehouse team gets a tracking report of open purchase order lines, and each reported line is cleared so it does not appear twice.

Every error goes to an integration log record with the stage, the file and the context, and a cleanup job keeps the log small.

#Under the hood

• Sent timestamps on items and transactions mean the outbound side never sends a record twice.
• The inbound side groups all files for one transaction before it creates anything, so a receipt is built once from everything the warehouse sent.
• Archive and error folders on the SFTP server keep a record of each file.
• SFTP credentials live on a custom record with a password field.
• Conversion, extraction and transport are separate libraries, so a change to the warehouse's format touches one file.
• SuiteQL finds each line's originating sales order through the line-level created-from link.