Proposal summary
The project will facilitate the data transfer between NetSuite and another third party application Canada Cartage. The order details from NetSuite will be sent to the external system for further shipment processing. After this, an acknowledgement will be sent back from the external system to NetSuite. Upon the acceptance of the acknowledgement, create a corresponding IF record in the NetSuite.
Requirement
The Sales Orders that are created in Canada Subsidiary(OX Tools CA) in NetSuite will be sent to the third party system Canada Cartage. This system will process the order details and the shipment will be processed based on the configuration of Canada Cartage. If the shipment is created, an acknowledgement containing the shipment details will be sent back to NetSuite. An Item Fulfillment with the status Shipped will be created in the NetSuite on receiving that request from Canada Cartage.
Our Solution
Prerequisites
- SFTP Server Credentials
Host, Username, password and port (not mandatory) of the SFTP Server are required to read and write the data.
- Sandbox/Development Instances
While doing development, it is adequate to have a testing environment to execute the test cases for both Canada Cartage and NetSuite.
- Domain Whitelisting
IP Whitelisting is not supported as a best practice in NetSuite. So we prefer domain whitelisting to identify the requests from NetSuite. Please whitelist *.netsuite.com. Please note that * is a wild card.
Description of the Solution
A script will be scheduled every 15 minutes that captures every essential data from the Sales Orders in NetSuite with the following criteria
- Subsidiary: OX Tools CA
- Status : Pending Fulfillment
- Canada Cartage Order Sent Flag : False
- Date Created : On or after “Implemetation of the proposed solution”
- Sales Orders does not contains backordered items
With every information in the XML structure in the specified 940 format (mentioned in the email attachment), the request is sent to the SFTP File Server to a specified folder. Canada Cartage will fetch these data either real time or on a scheduled basis based on their convenience and process the data accordingly. After successful creation of the shipment, Canada Cartage should send the shipment data in the specified 945 format (mentioned in the email attachment), to a different folder in the file server. This data will be processed to create an Item Fulfillment with the necessary data.
Flow
A NetSuite user creates a Sales Order with different items. Upon the next trigger of the scheduled process, a request is sent to the third party SFTP file server (940 format) with the necessary details. The third party system Canada Cartage will fetch the orders from the file server, then process the request and will be stored as a record in their system. The record will be processed at a later stage (whenever the shipment is completed) either manually or automatically. However after the shipment is generated, a Shipment Acknowledgement Notice (945 format) is sent back to the SFTP file server. This is fetched by NetSuite on a scheduled basis. This acknowledgement is a confirmation to NetSuite to create an Item Fulfillment record with the status Shipped in NetSuite based on the data provided by the Canada Cartage.
Mapping
NetSuite to Canada Cartage
Field Mapping (Mandatory fields only)
| Attributes | NetSuite Field IDs | Notes |
| <cust_code></cust_code> | ||
| <production_test></production_test> | P or T | |
| <order_date></order_date> | trandate | Transaction Date(mm/dd/yyyy) |
| <delivery_date></delivery_date> | shipdate | (mm/dd/yyyy) |
| <cust_order_num></cust_order_num> | internalid | Unique |
| <st_name></st_name> | entity (text) | Customer Name |
| <st_addr1></st_addr1> | addr1 (order – shipping address) | |
| <st_city></st_city> | city(order – shipping address) | |
| <st_prov_code></st_prov_code> | state(order – shipping address) | |
| <st_postal_code></st_postal_code> | zip(order – shipping address) | |
| <st_country></st_country> | country(order – shipping address) | |
| <scac_code></scac_code> | shipmethod | |
| <carrier_name></carrier_name> | shipcarrier | |
| <item_code></item_code> | item (sublist: item) | |
| <order_qty></order_qty> | quantity (sublist: item) |
Canada Cartage to NetSuite
Field Mapping
| Attributes | NetSuite Field IDs | Notes |
| <cust_code></cust_code> | ||
| <production_test></production_test> | P or T based on deployment | |
| <cust_order_num></cust_order_num> | Created From * | |
| <cust_po_num></cust_po_num> | ||
| <cust_delivery_num></cust_delivery_num> | ||
| <direct_order_num></direct_order_num> | ||
| <ship_date></ship_date> | Date * | |
| <scac_code></scac_code> | ||
| <carrier_name></carrier_name> | ||
| <ship_service_id></ship_service_id> | ||
| <ship_service_name></ship_service_name> | ||
| <total_pieces></total_pieces> | ||
| <total_weight></total_weight> | ||
| <tracking_number></tracking_number> | ||
| <item_code></item_code> | Name (Item) * | |
| <item_lvl2></item_lvl2> | ||
| <line_num></line_num> | ||
| <cust_line_num></cust_line_num> | ||
| <order_qty></order_qty> | Quantity (Item) * | |
| <ship_qty></ship_qty> | ||
| <serial_num></serial_num> | ||
| Status | default to Shipped status | |
| Shipping Carrier, Shipping Method and many similar fields will be defaulted when the record is transformed from the Sales order to Item Fulfillment. |
Assumptions
- Sales orders will always be created using the Canada Subsidiary to initiate the process. Item Fulfillments will be transformed from the sales orders. The process needs to be done only when sales orders are created through the Canadian subsidiary – OX Tools CA.
- All types of items need to be dealt in the Sales order creation.
- Backordered items are a concern when a Sales Order is included in the process. For example, a Sales Order contains a backordered item, and upon creation of the record, a request is sent to Canada Cartage. When Canada Cartage successfully completes the shipment, an acknowledgement is sent back to NetSuite. In this case, when NetSuite tries to create an Item Fulfilment, it will try to create the record using a backordered item and it may fail. Sales orders will always be created without any backordered items.
- Whenever a sales order is successfully created in Canada Cartage (after creation of the order in NetSuite), and when an Item Fulfillment is created in NetSuite (after the acknowledgement request from Canada Cartage), flag these records to be able to identify in the future. Create custom fields – checkbox fields – to make it available for report generation using saved searches.
- There are chances of various kinds of errors (due to character limit and other miscellaneous issues) in the process. At any time of errors, error logs need to be generated. Error handling needs to be done. Store all kinds of errors during integration and create a new custom record to store error logs and identify and resolve these types of threats in future.
Risks
- The effort for implementing processes in the Canada Cartage system is not included in this scope. Please let us know in case you need any further assistance with the task.
- Field mapping is done based on the given documentation and some assumptions. So OTGN need to confirm the proposed field mapping as per the Canada Cartage system
- The script will not be sent sales order that contains backordered items to Canada Cartage
- Orders that dated prior to this implementation will not be sent to Canada Cartage through this method.
Time
| Task | Time Required |
| Analysis | 2h |
| Connection establishment between NetSuite and Canada Cartage using an SFTP Server | 5h |
| Create XML Schema Definition based on Sales Order | 4h |
| Send payload, process feedback and error handling (for 940) | 4h |
| Receive and process payload from Canada Cartage (for 945) | 6h |
| Create Item Fulfillment based on the XML Schema Definition from 3PL | 2h |
| Testing | 3h |
| Documentation and Deployment | 2h |
| Total | 28 hours |