Proposal Summary

OX Tools USA would like to include an option for importing Item fulfillment for US-based Sales Orders.

Requirement

A custom import screen will be provided for administrator roles where users can input the import file and submit for importing Item fulfillment.
Through this import new IF created or existing one updated, which have the STACKABLE ITEMS checkbox (body field), Pallet Informations, Box informations and other basic information like created from, posting period, date, customer (populated from order), item related information etc. are added/updated.
This import is for IFs of sales orders with normal inventory items.

Our Solution

The requirement can be achieved by developing a record that can be accessed under the “Transactions” tab in the landing page, for administrator role users where they can upload the CSV file of the data that needs to be imported.

Creating new IF

Date field in the IF will be set as the date of import for the newly created IF.
Locations in the Item fulfillment will be the same as the related sales order.
Upon submission, a background script will run to create a new IF which has the STACKABLE ITEMS checkbox, Pallet Informations, Box informations and other basic information like created from, posting period, date, customer (populated from order), item related information etc. added.

Updating Existing IF

For update the file will have the column (3) ITEM FULFILLMENT filled with the Internal id of the IF record.
Upon submission, a background script will run to search for the matching IF with the internal id provided in the ITEM FULFILLMENT column and update the data in the record as same as values in the file.
Basic details in IF like Date, Posting period, customer, locations etc are not updated.

In order to avoid the risk of script failure due to script execution limit, the uploaded file will be splitted to multiple files by considering 100 transactions per file and it will be processed separately in the background.

Mapping:

INTERNAL ID	Internal id of related sales order(Created from field)
SALES ORDER NUMBER	Created from field sales order number
ITEM FULFILLMENT (will be filled for updating existing IF)	Internal id of IF
REF NO:	Reference number of IF (document number)
POSTING PERIOD	posting period field
ITEM NUMBER	Items in item sublist
ITEM INTERNAL ID	Internal id on item
QUANTITY FULFILLED	Quantity
STATUS	Picked/ Packed/ Shipped
EXPECTED SHIP DATE	Custom fieldIt will only be one value for each transaction
EXPECTED DELIVERY DATE	Custom field Date It will only be one value for each transaction
STACKABLE ITEMS	STACKABLE ITEMS check box(body field) will be true or false. This is repeated the same in all the columns for the same IF.
SHIPPING CARRIER	shipping carrier (under the shipping tab of the IF.)
SHIPPING CARRIER SCAC	shipping carrier scac (under the shipping tab of the IF.)
1.Pallet Number2.Pallet Length3.Pallet Width4.Pallet Weight5.Pallet Height6.Pallet Tracking Number(this field will have value in Shipped status)	Under the Pallet information tabPallet#LengthWidthWeightHeightTRACKING NUMBER(this field will add/update value in shipped status otherwise it is blank)
1.BOX NUMBER2.BOX HEIGHT3.BOX LENGTH4.BOX WIDTH5.BOX WEIGHT6.BOX CONTENTS7.BOX TRACKING NUMBER8.CORRESPONDING PALLET # (same content inside as pallet number in the pallet information)	Under the Box information tabBOX #HEIGHTLENGTHWIDTHWEIGHTPACKAGE CONTENTSTRACKING NUMBERCORRESPONDING PALLET #

Error Handling , File Creation and Emailing

Script will create custom records for recording the errors and success on IF creation. Record will have fields depicting the time stamp, current user, success file, error file.
And Email will be sent to the current user along with this custom record link.

Assumptions

Only admin users can navigate to the custom importing page.
The field mapping for CSV and IF is the same as the number of columns provided.
All the combinations will be given within the file for every line to find the items by combining those column values.
The CSV file uploaded for updating the existing IFs should have the internal id of IF
Date field in the IF will be set as the date of import for new IF creation.
All mandatory fields in the Item Fulfillment record should be there in the file that is been uploaded
Currently, considered around 1700 transactions and 3 items per transaction within a single file (approximately 5000-6000 lines per file)

Risks

The file uploaded should be in CSV format with size less than 10 mb.
There will be a delay in the update based on the availability of the queue.
Since this is a scheduled process once the process ends the email intimation will be given instead of real time status update.
While Updating existing IFs the column fields in the file should exactly match with the existing IF record with the internal id of that IF.
The text datas in the same column should follow the same format.
QUANTITY FULFILLED should be all committed in the related sales order.
Only normal inventory items are considered. So inventory details are not configured
The lines on the file should be the same when preparing the import file for IF update. Because, we may need to delete and recreate the Pallet and Box information custom record upon update to ensure the data accuracy as we do not have any unique identifier for the Box and Pallet Lines
List field(Shipping Carrier) the values should be in exact match with the List Value in NetSuite
We can include the requested ship date. However, the user needs to ensure the date should be in a single predefined format. That we will update you at the development stage.

Item fulfillment Upload