Requirement

The Client requires to integrate the orders from Channel Advisor to Netsuite. They require the orders to be pulled from multiple posting accounts as their Channeladvisor  account pulls order data into multiple posting accounts for the different countries and marketplaces. 

For Third Party Logistics / FBA(Fulfillment by Amazon (FBA) Location Orders

The Client need  to retrieve the orders from Channeladvisor (CA)  posting accounts for Third Party Logistics(Third Party Logistics) (Delivery/ Fulfillment by Amazon (FBA) or Warehouse (Grayslake)

The orders will be integrated to Netsuite only when the status is “Shipped” in Channeladvisor. These are orders being fulfilled by their Third Party Logistics or by Amazon and they do not want to include orders in other statuses.

For Warehouse Location Orders

We need to retrieve orders from channeladvisor for orders to be fulfilled from warehouse locations and most probably, it will be Wholesale orders. These orders need to be integrated into the Netsuite as soon as possible. 

After the Sales Order is marked as complete / shipped, the client requires the order tracking information to be sent back to Channeladvisor.

Solution

Order Sync

Create Custom records 

• To store the Channeladvisor API Details. DistributionCenterIDs will also be stored in the custom record
• To handle the error notification for all Channeladvisor orders integration to Netsuite
• To store the Customer ID of the customers (customer mapping) in the marketplace who place Third Party Logistics / Fulfillment by Amazon (FBA) orders

A Channeladvisor synchronization tab will be created in the Sales order and item fulfillment  all custom record details will be accessible from the sales order and item fulfillment record. The Channeladvisor API response details will be saved in the custom record for viewing the order details in the Netsuite itself which will be helpful in sorting out the errors if any.

For Third Party Logistics / Fulfillment by Amazon (FBA) Location Orders

Customer Record

• Customer records will be already created in the Netsuite and hardcoded in a custom record. 
• ‘TaxJar Marketplace field’ in the Custom record will be used to identify  the Customer name in the Marketplace which will be mapped to a Customer record in the Netsuite. The corresponding customer record will be used to create a sales order.
• If the customer does not exist in Integration Custom record, we will be throwing an error notification in the order Sync Custom record

Create a sales order

A sales order should be created for every order retrieved from channeladvisor Third Party Logistics or by Amazon  with shipped status in every hour by retrieving all orders that are not shipped and for the particular distribution profile ID. If the sales order already  exists in the Netsuite, we will not be updating any details in the order.

• Every orders will be created with ‘Closed‘ Status after creating item fulfillment and cash sale record
• Document Number will be the Order ID in the Channeladvisor order ID.
• Sales Tax will be eliminated if the marketplace is Amazon
• If the marketplace is different from Amazon, the sales tax total field will be filled in the ‘TaxJar Tax Total’ custom field on the order.
• The Sales Agency in Netsuite will be the agency corresponding to the  State in the billing address on the order. 
• Item records will be available in the Netsuite and if any item is not available, we will be throwing an error in the custom record
• The ‘gross amount’ field in the item line level of the sales order will be updated with  the price in the Channel advisor.
• Payment method can be ‘Cash’ and ‘Gift Card’. For all orders from Channeladvisor with gift card as the payment method,  the corresponding payment method will be updated in the sales order. For every orders with payment method other than ‘Gift card’ will be entered as ‘Cash’
• Discounts will be added as a line item in the sales order. The discount rate will be available in the orders data fetched from Channeladvisor
• Location in the sales order will be Channeladvisor Location specified in the order data fetched from Channeladvisor API

Create Item fulfillment Record With Shipped status

• All details in sales order will be used to create the item fulfilment record
• Find out the list of lot inventory items and sort by Expiry date for the  third party Channeladvisor Location specified in the order data fetched from Channeladvisor API. The oldest (earlier expiry) Lot Number should be used for the Item Fulfillment Record creation
• If the item quantities are not available for fulfilment, it will be updated in the custom record and will be notified to the assigned role.

Create a Cash sale

• The Sales Order record will be transformed to a cash sale record and after saving the cash sale record, the sales orders will be in the closed state.

For Warehouse Location Orders

Every order created in the Channel advisor needs to be integrated with a sales order in the Netsuite.

Create a Customer record

•  Subsidiary will be setting as Great Lakes Gelatin / Wellness
• Customer billing and shipping address in the order will be using to create or update customer record
• Customer Email address will be used as a unique key to identify the customer record in Netsuite
• Any updates in the customer data while creating an order in Channeladvisor will be reflected in the customer record.

Create a sales order

A sales order should be created for every order retrieved from channeladvisor Third Party Logistics or by Amazon  with shipped status in every hour by adding a date filter to obtain the most recently placed order.  If the sales order already  exists in the Netsuite, we will not be updating any details in the order.

• Every orders will be created with ‘Pending fulfilment ‘ Status 
• Document Number will be the Order ID in the Channeladvisor order ID.
• ‘TaxJar Marketplace field’ in the Customer record will be used to identify  the Customer name in the Marketplace. 
• Sales Tax will be eliminated if the marketplace is Amazon
• If the marketplace is different from Amazon, the sales tax total field will be filled in the ‘TaxJar Tax Total’ custom field on the order.
• The Sales Agency in Netsuite will be the agency corresponding to the  State in the billing address on the order. 
• Item records will be available in the Netsuite and if any item is not available, we will be throwing an error in the custom record and the sales order will not be created
• Item SKU will be used as a unique identifier to identify the item record in the Netsuite
• Payment method can be ‘Cash’ and ‘Gift Card’
• Discounts will be added as a line item in the sales order
• Location will be  Grayslake Warehouse location

Fulfillment Sync

After the Sales Order is marked as complete / shipped, the client requires the order tracking information to be sent back to Channeladvisor.

A Script will be implemented in the item fulfillment record. Whenever an item fulfillment record is created, the script will be sending the tracking information from Netsuite to the Channeladvisor.

For complete Single Shipment

• Delivery Status will be sent as  “Complete” only when all the items are shipped in a single item fulfillment record. 
• Shipped date will be the script running time which will also be time at which the item fulfilment record is created 
• Tracking numbers will be obtained from the item fulfillment record and send to the Channeladvisor
• Order ID in Channeladvisor will be stored in the custom record and sales order record and which will be used to update the shipped status and tracking number in the channeladvisor

For partial  Shipments

• We will be sending the product Sku or product Channel Advisor ID saved in the item record along with the quantity and shipping tracking number to partially ship the items
• Tracking numbers will be obtained from the item fulfillment record 
• Channeladvisor Order ID will be used for sending the data.
• Shipped date will be the script running time which will also be time at which the item fulfilment record is created 

Inventory Stock Update 

A custom field will be available in the item records to indicate that the items will be synced with Channeladvisor. If the custom checkbox is checked, then the stock quantity will be updated to the Marketplace. Otherwise, the item’s availability will not be updated in the Channeladvisor. 

Create a custom field in item record to store the channeladvisor ID of the product. Once the value is stored in the field, we will make the field disabled to avoid user error

A script will be running every hour(or every 30 minutes) to check all item records having a custom field  checked or not. 

• We can create a custom field in the item record to store the channeladvisor product ID in the Netsuite. If the channel advisor ID is already available in the custom field, the script will fetch the channel advisor ID from the item record
• If the channel advisor ID for the product is not available in the item record, we will be fetching channel advisor ID using the SKU of the item from item record 

Once we  retrieve the channel advisor ID, we will be updating the stock quantity for every item in the Channeladvisor.

For updating the quantity, the script will check the ’quantity available’ field in Netsuite corresponding to Grayslake Warehouse  in the item record. 

• If the item quantity is greater than zero, the stock quantity will be updated in the Channeladvisor with “In stock” status 
• If the item quantity is less than or equal to zero, then the the quantity will be deleted from channel advisor

Error Handling

Email Notification for Order Sync

• For all orders, the error while creating a sales order or customer record will be updated in the order Sync custom record
• If any of the item records are not existing record, we will be updating it in the custom record
• A saved search will be created and scheduled to send email notification to the administrator or any specific roles for any errors created in the custom record

Email Notification for Inventory Sync

The script can send email to a specific recipient(say administrator) if there is any error while running the script to update the stock quantity

Assumptions

• Applicable only to Inventory, Lot Numbered Inventory Item, Item Group and Kit Items
• ‘Allow override’ in the auto generated numbers setting for transaction is enabled in the Netsuite account to override the document numbers
• Line item to be selected as Discount item in the sales order will be hardcoded in the script and should be available in the Netsuite
• A custom field will be available in the item records to indicate that the items will be synced with Channeladvisor. This Custom field will be updated manually through Netsuite UI
• Expired or damaged items stock quantity should be updated accurately in the Netsuite
• Script will only check the ’quantity available’ field in Netsuite corresponding to Grayslake Warehouse  in the item record
• Tracking Number will be  provided by a shipping carrier to track the progress of a shipment and which will be available in the item fulfillment record
• We won’t consider the Unit Of Measure (UOM) while creating the order. The script will use the default unit (Sales Unit) for the item
• Subsidiary will be setting as Great Lakes Gelatin / Wellness in the customer record for warehouse orders
• Grayslake will be  the Warehouse location used for creating sales order in the Netsuite and we will be hardcoding this location
• We will not be updating the orders already created in the Netsuite. If the order is updated in the Channeladvisor after integrating an order from channeladvisor to Netsuite, we will not be updating that in the corresponding Netsuite sales order.
• Deletion of the item fulfillment record will not be reverting the shipment status in the channeladvisor
• Inventory Sync will be a One way sync from Grayslake Warehouse location to the Channeladvisor and the marking of ‘custom field’  to identify the item records which require Inventory Sync should be done manually by the Wellmore Holdings.
• We will be hardcoding the tax code corresponding to a Zero tax for orders which have the ‘Amazon’ as the Marketplace

Additional Recommendations

• Providing sandbox environment for both ChannelAdvisor and NetSuite 
• Deletion of the item fulfillment records can be avoided by implementing a condition in the script to throw an error while deleting the item fulfilment record with shipped status

Questions

• We need to hardcode a single currency in the sales order. Could you please confirm the currency used for the sales order creation? 

Or

There may be chances that the marketplace will accept different currencies and thus the currencies accepted by the marketplace need to be considered while creating the sales order in Netsuite. If there are multiple currencies involved in transactions, please let us know the primary currency and different currencies to be used in the transactions.

• Which shipping methods and shipping classes are used for fulfilling the orders in the channeladvisor?
• Does there any chance for partial shipments through different shipping carriers? If yes, we have to consider this case and send the shipping tracking numbers accordingly to the Channeladvisor.
• We have seen that ‘Gift card’ payment has been accepted through Channeladvisor. Are you referring  to NetSuite Gift Certificate or is it something specific to the business and “gift card”is referring as a Payment Method
• For the Kit and Item group, the items and its members will have different SKU. Which one would you like to send to the Channeladvisor while creating the sales order?
• Could you please confirm whether we need to consider Bin Management while creating Item fulfillment records?
• Could you please confirm whether there are Serialized Inventory Items involved in the business process?

Budget and Time

Estimated Effort : 70 hours