When we talk to other NetSuite users we typically hear about what works and what doesn’t work whether it is with their current process or system. We’ve had a lot of conversations with users about gaps that they’ve identified with shipping container management in NetSuite. The problem is that items shipped to the USA from overseas need to be received into inventory while they are shipping. However, the items are not available to the customer until they arrive in the U.S. The receiving process is a manual, time-consuming process with many steps. Another issue they’ve experienced is that when manually performing these tasks in native NetSuite there is no link between items received into the temporary “On the Water” location and Items transferred out to the final destination. It’s difficult to differentiate which PO’s have actually arrived from those that are still “On the Water” as you can see quantities for on the water but not which PO it is.
Since shipping container management was a recurring topic that came up in conversation, we developed a solution for this. The solution that we created is able to:
- Automate the “On The Water” receiving process in a repeatable, predictable process with as little human intervention as possible, while maintaining the integrity of the Inventory numbers and NetSuite’s native inventory processes.
- Give the users a PO level view of their “On the Water” inventory for planning and reporting purposes.
- Give the customer a view of past container receipts and contents to evaluate and report on vendor performance.
- Automate the generation of the Vendor Bill to AP at the container/PO level for easy Payment and Reconciliation.
The solution consists of the following components:
Data Import: Import Vendor Invoice/Packing data to a record called Container Items. These are the SKU’s, QTY’s and unit price of each item in the container according to the vendor documents.
A process to find the Container record (in another record called, Container Tracking) or create a new Container Tracking record.
The Container is uniquely identified by concatenating three Text fields; all provided in the import.
- Container Number
- Vendor Invoice Number
- Seal Number
A process to match the container item to a PO: Using a field called Vendor PO Number, the containers are matched to PO records. After Submit, a user event script attempts to match each new container item to a line item on PO. This is done by comparing:
- Vendor PO Number
- SKU (item name/id)
When a match is found a field on the PO line is updated with the Container Item ID and the Container Item status is set to Ready. If NO matching PO line item is found, the Container item status is set to “Manual Process.”
Scheduled Process: A Scheduled Script runs, according to the schedule setup by the user (i.e. every 15 minutes, 2 hours, 1x per day, etc.), that looks for Container Tracking records with a status of ‘Ready’ and then Attempts to process the items that are marked as ready in each “ready” container. If an item is NOT marked as ready and the container is, that item will be skipped and the process will skip to the next item marked as ready.
NOTE: There are customizable pieces, such as the default status for containers could be ‘Ready,’ in which case no manual intervention would be required to process the container or the default status can be ‘New’ so they aren’t processed until the user marks them as ready. Containers that are not marked as ready would not be processed and would stay in their current status until changed to ready. The Items in that container would NOT be received into inventory until they are marked as ready. In addition, the unit price and quantity tolerance as well as line item validation are configurable.
Once the item is received to “On the Water” the item is Added to a Vendor Bill for AP, linked to the container as well as the PO for reconciliation AND added to a Transfer Order destined for the items final location (warehouse/store).
The items on the Transfer orders are committed to the order (i.e. the Available column of the on the water location should always be 0).
When the item arrives at the warehouse the and the actual number received is verified the user sets the actual receipt number on the transfer order and clicks a button to receive the entire container in one action. Overages and shortages are handled in this process as well so you never have to receive inventory that was not in the container into your warehouse and never leave excess inventory “on the water”.
*The solution that we’ve outlined was created for a specific customer, but it can be customized based on individual needs and goals.