Proposal for Auto Invoice creation from different CSV files

Proposal Summary 

This proposal documentation outlines the scope of setting up an auto invoicing in NetSuite from different file structures. 

Requirement 

The client aims to automate the creation of invoices by integrating with six different external applications, each with its unique data models from six different subsidiaries.  

Currently, they are using different applications and data models are different in the systems and manually enter the invoice data in their system. From their application, they created CSV files for importing in NetSuite. 

Presently, users manually handle the process by creating or uploading each data model, mapping it with sales order fields, and importing it to generate standard sales orders. The objective is to automate this process to enhance efficiency and accuracy. 

The client seeks automation for generating sales orders in NetSuite when users upload files via email. Currently, they input sales order data into NetSuite manually and desire to streamline and automate this workflow. 

  

Deliverables  

To streamline the process of mapping CSV file headers with NetSuite fields in the sales order generation workflow, we propose setting up custom records in NetSuite. 

Users will be able to easily navigate and configure mappings for each CSV file type, ensuring flexibility and efficiency in the setup process. 

Users can configure the external application type in the script deployment record. A field will be added to the script deployment to select the custom record of the external application. Based on the selected external application custom record in the script deployment, the User Interface will be displayed, and the sales order creation process will be executed. 

Import Process 

Step 1: Inbound Custom record creation 

In NetSuite, a user interface will be available for uploading the import file based on the external application. The user can take the Import page from the NetSuite menu. The user can select the CSV file in the user interface and the csv data will be added as the inbound custom record entries in NetSuite. 

Once the user clicks the import button, the inbound1(for body level) and inbound2(for line level) custom record entries will be created for the CSV file data. 

 

Step 2: Data Cross Checking 

A user interface will be provided for the cross checking of customer, item, and tax code from the CSV file with NetSuite.  

If any of the customer, item or tax code from CSV file is not available in NetSuite, the user can map those customers, item, or tax code to existing customers, item, or tax code records in NetSuite. 

The missing customers, items, or tax code will be displayed on the import page. A drop-down field will be added for each customer, item, or tax code records in the import page, and it will list the customers, items, or tax code custom record entries from NetSuite. 

The user can select the respective customers, items, or tax code custom record from NetSuite for the data from the CSV file. The data mapping of customer, Item and Tax code are stored in the custom record in the corresponding external application.  

 

Step 3: Intermediary sales order creation 

Once the cross-mapping is complete, the user can continue the process to create intermediary sales order custom record entries for the imported CSV file data.  

 

Custom Record for External Application: 

  • We’ll create a custom record in NetSuite for each external application that needs to be imported for sales order generation. 
  • Each custom record will represent a specific external application, providing a structured framework for mapping its headers with NetSuite fields. 
  • The custom record contains the fields to fill the name of the external application and for choosing the subsidiary for the external application. 
  • The custom record will contain the child custom records for field mapping, customer, tax, and item mappings, inbound 1 and inbound2 custom records. 

Inbound 1 for sales order Body field Mapping: 

  • Within each custom record, we’ll create child custom records to store the CSV file data for the NetSuite sales order body fields. 
  • The custom record will contain the field for identifying whether the sales order is created for this entry. 

Field Configuration: 

  • Each child custom record will include fields for: 
  • The Sales order body fields and the value from the CSV file. 

Inbound 2 for sales order Line-Item Mapping: 

  • Within each custom record, we’ll create child custom records to store the CSV file data for the NetSuite sales order line fields. 
  • The custom record will contain the field for identifying whether the sales order is created for this entry. 

 

Field Configuration: 

  • Each child custom record will include fields for: 
  • The Sales order line fields and the value from the CSV file. 

Child custom Record for Customer Data Mapping: 

  • The child custom records can be created for customer mapping.  
  • If any of the customers from import file does not exist in the NetSuite, the user maps the customer from the NetSuite custom record “GRW007 Party SuperType“ with the customer from import file. 

Field Configuration: 

  • Each child custom record will include fields for: 
  • CSV file Customer: To specify the external id of customer from the CSV file. 
  • NetSuite Customer: To select the corresponding customer from NetSuite. 

 

Child custom Record for Item Data Mapping: 

  • The child custom records can be created for Item mapping.  
  • If any of the Item from import file does not exist in the NetSuite, the user maps the Item from the NetSuite custom record “GRW007 Party SuperType“ with the item from import file. 

Field Configuration: 

  • Each child custom record will include fields for: 
  • CSV file Item: To specify the item from the CSV file. 
  • NetSuite Item: To select the corresponding Item from NetSuite. 

Child custom Record for Tax Code Data Mapping: 

  • The child custom records can be created for tax code mapping.  
  • If any of the tax code from import file does not exist in the NetSuite, the user maps the tax code from the NetSuite custom record “GRW007 Tax Code“ with the tax code from import file. 

Field Configuration: 

  • Each child custom record will include fields for: 
  • CSV file Tax code: To specify the Tax code from the CSV file. 
  • NetSuite Tax code: To select the corresponding Tax code from NetSuite. 

By implementing this solution, we aim to optimize the automation of sales order generation in NetSuite while ensuring accuracy, efficiency, and flexibility in handling diverse CSV file formats. 

 

Data field Mapping: 

  • Within each custom record, we’ll create child custom records to map CSV file headers with corresponding NetSuite sales order fields. 
  • These child records will allow users to define mappings for each field, ensuring accurate data transfer during sales order generation. 

Field Configuration: 

  • Each child custom record will include fields for: 
  • CSV File Header: To specify the header name as it appears in the CSV file. 
  • GRW data model: To select the corresponding field in NetSuite’s sales order record. 

 

Intermediary Sales order creation: 

The custom records GRW007 Order SuperType , GRW007 Sales Order , GRW007 Order Item SuperType , GRW007 Order Item Subtype Sales Order will be created for the inbound1 and inbound 2 custom records on scheduled basis.  

Also, the standard sales orders will be created in “pending fulfillment” status for the inbound1 and inbound 2 custom records on scheduled basis.  

Once the standard sales order creation is complete, the Successful flag will be checked in the inbound1 and inbound 2 custom records. 

Auto Invoice Creation 

A scheduled process will transform the sales order into invoices considering the accounting preferences in NetSuite. 

User Interface for Import status 

A user interface will be provided to the user to view the current status of the CSV import. The page will show the following details: 

  • Inbound 1 custom records 
  • Inbound 2 custom records  
  • Related custom sales order  
  • standard sales order  
  • Invoice Record 

File storage: 

The imported files will be stored in the process folder in the file cabinet. 

If the script fails to create the sales order due to any error, the script will create the error file and add the error file to the error folder in the file cabinet. The unsuccessful files will exist in the error folder with the error message.  

 

Assumptions 

  • The field mapping of custom sales records will be provided by Grow Wire team 
  • File header is provided in the Dutch and English languages. 
  • File type will always be CSV and header names will not be altered in the future. If altered, it needs to be updated the mapping associated with custom record. 
  • We force the exclusion of special characters to prevent file corruption. 
  • Date field value will be in the same format as that in NetSuite.   
  • We assume that the date field value will be a valid one within the open posting period.  
  • Subsidiary and location fields will be set during automation. 
  • We will not set the subtotal, or total values in the sales order record through script, instead they will be calculated by NetSuite automatically based on the gross amount we set at the line level. Validation will be provided for the sales order subtotal. 
  • The back-ordered/available quantity column will not be updated by script and will be populated based on NetSuite data.  
  • Customer terms will be sourced from customer records and will be updated through our script only if provided in the file. 
  • The unit of Measure from the NetSuite will be set in the sales order and invoice after the cross mapping. 
  • The payment method will not be defined in the sales order if not available in the file.  
  • Document numbers will be auto generated based on NetSuite setup if the field value is not available in the file. 
  • The external id of the sales order and invoice will be set based on the data in the CSV file. 
  • Body level and line-level tax amounts will be set based on the value provided on the file.  
  • Applicable only to inventory, non-inventory and service items.  
  • The invoicing automation will take place as scheduled. 
  • The auto-invoicing process will work irrespective of shipment status advanced shipping is enabled in the account. 
  • The preferences “Show Unfulfilled Items on Invoices “and “Invoice in Advance of Fulfillment” are not enabled in the account. 
  • The script will ensure that the date format in the file matches the company’s information date format. 
  • It will handle data of up to 4000 records. 
  • If specified items in the file aren’t active or present in NetSuite after the cross mapping, an error will occur during sales order creation. 
  • Any errors during the process require file correction and reupload. 
  • File headers must remain unchanged. Any header name changes will require custom updates. It’s the user’s responsibility to ensure accurate header names during file upload. 
  • If the file size exceeds 10 MB, the script will need updates. 
  • We will not create item fulfillment and customer deposit in this scope. 

 

Risks 

  • As per NetSuite standard functionality, the amount field will be rounded to 2 numbers after the decimal values. 
  • The script will not handle any other file type other than the mentioned csv. We need to update the script if it is any other file type.  
  • The file headers cannot be changed after the script development. If there is any header name change, we need to update the script accordingly. It is the responsibility of the user who uploads a file to make the header’s name accurately entered.  
  • If the file size exceeds 10 MB, we need to update the script accordingly.  

Leave a comment

Your email address will not be published. Required fields are marked *