Inventory

Automated sync of inventory data from NetSuite to HotWax Commerce to ensure accurate inventory counts.

NetSuite serves as the central system of record for tracking product inventories across various retail locations. Inventory transactions in Netsuite, encompassing the creation and reception of purchase orders (POs) in warehouses, as well as the fulfillment of online orders from these warehouses, are vital components for maintaining real-time and precise inventory records.

However, while HotWax Commerce receives frequent updates regarding online orders fulfilled from the warehouse using NetSuite, it does not receive regular updates on new inventory arrivals in the warehouses in response to purchase orders. This discrepancy causes HotWax Commerce to fall out of synchronization with inventory data of NetSuite.

To ensure that HotWax Commerce remains continually synchronized with the latest inventory information, the daily synchronization of inventory data is paramount. This synchronization process revolves around the extraction of inventory data from NetSuite and the subsequent update of HotWax Commerce's inventory records. This process is typically scheduled during off-peak hours, often taking place in the late hours of the night or in the early morning.

Key Objectives

  • Automate the daily synchronization of inventory data from Netsuite to HotWax Commerce.

  • Ensure that HotWax Commerce reflects the most recent inventory numbers, as recorded in NetSuite.

Actions

  1. A Scheduled Script, equipped with the capability to utilize the Search Task from the N/task module, is executed. The objective of this script is to retrieve inventory records for all products stored within NetSuite. This is achieved through the execution of a saved search, specifically tailored for this purpose. Once the search is complete, the script generates a CSV file containing the inventory data and puts it within NetSuite's File Cabinet.

The Search Task function, utilized here, can generate and store a file solely within NetSuite's File Cabinet. It lacks the capability to place the file in an SFTP location. As a result, another scheduled script is necessary to accomplish this task.

SuiteScript

HC_generateCSV_InventoryItems
  1. A secondary Scheduled Script is triggered, which is responsible for retrieving the CSV file generated by the previous script from NetSuite's File Cabinet and subsequently relocating it to the designated SFTP location. An essential aspect to consider is that a 15-minute time gap is introduced between these two scripts. This delay is introduced to allow the asynchronous task, internally generated by NetSuite when executing the Search Task function of the N/Task module, to complete. Without this interval, the second script might run before the asynchronous task finishes, leading to an empty folder in NetSuite File Cabinet. This script not only transfers the files but also archives them from the NetSuite File Cabinet.

SuiteScript

HC_uploadCSV_InventoryItems

SFTP Locations

/home/{sftp.user}/netsuite/inventoryitem/csv
  1. The final step in the workflow involves a scheduled job within HotWax Commerce OMS. This job is responsible for reading the Inventory Sync CSV files from the SFTP location. It utilizes this data to reset the inventory numbers for products within the HotWax Commerce system.

Job in HotWax Commerce

Import inventory
FTP Config: RESET_INVENTORY

Why Search Task

In this particular use case, we have employed the Search Task function of the N/Task module for exporting inventory data from NetSuite. This approach is distinct from other scenarios where we utilized Map Reduce scripts and regular saved searches using the N/Search module. Our decision to implement Search Task for this specific purpose is based on our practical experience with various methods of exporting data from NetSuite, which revealed compelling reasons for this choice:

Handling Extensive Inventory Records

Initially, we attempted to synchronize inventory data from NetSuite to HotWax Commerce using Suite Script with a standard saved search through the N/Search module. However, this approach proved to be impractical when dealing with a high volume of inventory records. The script execution time exceeded the maximum threshold of one hour for Scheduled Scripts. NetSuite's documentation reinforced that SuiteScript is best suited for handling smaller data sets, making it evident that an alternative approach was required.

Efficiency of Map Reduce Scripts

Subsequently, we transitioned to Map Reduce scripts to manage the synchronization of inventory between NetSuite and HotWax Commerce. Map Reduce scripts are designed to handle large datasets efficiently and demonstrated significantly improved performance compared to Scheduled Scripts. Notably, even if the script execution extends beyond one hour, Map Reduce jobs are designed to manage NetSuite resources effectively, allowing them to complete the task. Based on our experience, processing a million inventory records required approximately two hours.

Harnessing SearchTask Function

To optimize the process and achieve rapid synchronization, we adopted SuiteScript in combination with the Search Task function from the N/Task module. The results were remarkable, with the execution time reduced to a mere 10-15 seconds for a million inventory records. The search was executed swiftly, the CSV file was generated, and it was deposited in NetSuite's File Cabinet within a matter of seconds. This extraordinary speed and efficiency led us to the conclusion that the Search Task of the N/Task module was the ideal choice for synchronizing inventory records from NetSuite to HotWax Commerce.

Inventory Movement Between B2C and B2B Warehouse Locations

For retailers operating a single physical warehouse but catering to both B2C and B2B customers, managing inventory effectively is crucial. The B2C warehouse handles fulfillment of online orders, while the B2B warehouse manages bulk operations. In this scenario, retailers configure two distinct logical locations in NetSuite: one for B2C (eCommerce) and another for B2B (wholesale) operations. This setup allows them to allocate inventory strategically between B2C and B2B business needs.

In the event where inventory in the B2C warehouse is depleted, retailers transfer stock from the B2B warehouse to replenish it, and vice versa when the B2B warehouse needs restocking, retailers transfer inventory from the B2C warehouse.

The effectiveness of this process relies on timely synchronization. If inventory movements to or from the B2C warehouse are updated in HotWax Commerce during the daily sync with NetSuite, there's a risk of operating on outdated inventory levels. The daily sync, scheduled during off-peak hours such as late at night or early in the morning, can result in missed sales opportunities on eCommerce if recent inventory transfers to the B2C warehouse are not promptly reflected in HotWax Commerce. Additionally, it can lead to overpromising on eCommerce if inventory transfers from the B2C warehouse are not timely updated in HotWax Commerce.

Therefore, it's essential for HotWax Commerce to sync recent inventory updates from NetSuite whenever retailers move inventory between B2C and B2B warehouse. This ensures that the inventory levels in NetSuite are accurately reflected in HotWax Commerce and, consequently, on the eCommerce platform, preventing underselling and overselling of stock.

Actions

  1. Map Reduce Script in NetSuite runs every 15 minutes, generates an inventory transfer CSV file and places it at the designated SFTP location. This file contains inventory deltas, specifying increases or decreases in a product's inventory at the B2C warehouse.

SuiteScript

Export inventory transfer records

HC_MR_ExportedInventoryTransferCSV.js

SFTP Locations

/home/{sftp-username}/netsuite/inventorytransfer/import
  1. A scheduled job in HotWax Commerce reads the CSV file from the SFTP location and adjusts the inventory records in HotWax Commerce. When inventory is transferred to the B2C warehouse, the scheduled job increases the inventory count for the product. Conversely, when inventory is transferred from the B2C warehouse, the job reduces the product's inventory count.

Job in HotWax Commerce

Import inventory transfer records

Import Inventory Transfer
FTP Config: IMP_INV_TRANS

Last updated