Create invoice from CSV file.

Create invoices from uploaded CSV files. Users will be manually updated the CSV file in the file cabinet using a custom record. Using this CSV file we need to create invoices for each item in the CSV. The Invoice creation SO mapping will be based on the saved search(Search-id: 14235).

Requirements

The objective is to BILL (create an invoice) for the qty that were sold in that time frame.

This saved search shows the open sales orders for those items.

To take the sale thru file, and invoice only those matching items and qty. It will create multiple invoices as different per Sales Order. Check to make sure the total on those invoices matches the total on the sell thru file. If not, email rohit@balajiwireless.com

To make a custom record that stores the imports, so Sept 1st to Sept 15th Import will have a record, in which it will show the total amount and the invoices created from it and how it all matches. 

Once live, I will take the sell thru file, Import it into the custom record, and click BUTTON to run the script. The script will do the rest and let me know all records processed. 

If Item SKU doesn’t match, check for SCP Item No. as the 2nd match. That is on the item record and the sell thru report. If that also doesn’t match, email me to troubleshoot it. 

Our Solution

1. We will set up three custom records for achieving this task.
2. The first record for uploading the CSV file (Name: “SELL THRU REPORT” *Primary Parent Record)
3. The second record for storing the item details from the uploaded CSV file (Name: “SELL THRU ITEM RECORD” *Child record of SELL THRU REPORT).
4.  The third record helps to invoice creation detail (Name: “SELL THRU BILL RECORD” *Child of SELL THRU ITEM RECORD). 

Custom record 1

             This custom record helps to upload/save the CSV file in the file cabinet.

• The custom record name will be “SELL THRU REPORT”.
• In this custom record, we will add the flowing field.

Field Name	Type	Description
Upload file	Document	To upload the CSV files.
Inactive	Check box	To inactivate the record.
Is the record is already processed	Check box	Flag the custom record after the processing. This will help up us to create multiple-time file processing.
Error file	Document	To attach error execution file on Invoice creation.
Total invoice amount	Currency field	To display the total invoice amount.
SELL THRU ITEM PROCESS COMPLETED	Check box	Identify the “SELL THRU ITEM RECORD” process is complete.
The total amount of item	Currency	The total amount is in the CSV file item.
Total invoice created amount.	Currency	The total amount is in the CSV file item.

Custom record 2

This record helps to store the item details, Which will be taken from the uploaded CSV file. This record contains details of each individual item in the uploaded CSV file. We will group CSV line based on its SKU  and purchase cost. Based on this item setup record we will generate the invoices for the sales orders which is listed in the given saved search.

• This custom record is used to store each item’s details for the creation invoice record.
• This custom record name will be “SELL THRU ITEM RECORD”.
• This will be the child record of the first custom record (SELL THRU REPORT).

Field Name	Type	Description
Item SKU	Free form text	To store items name.
SCP Item No.	Free form text	To store item SCP value.
Inactive	Check box	To inactivate the record.
Created From	List/Record	Help to identify the created from “SELL THRU REPORT”.
Total quantity	Integer field	Helps to identify the total quantity of the specific SKU.
Purchase cost	Currency field	Identify the corresponding purchase cost

Custom record 3

To store the details related to item-wise sales orders and their invoices. 

• This custom record name will be “SELL THRU BILL RECORD”.
• This record will be the child record of the “SELL THRU ITEM RECORD”.

Field Name	Type	Description
Created From	List/Record	Help to identify the created from “SELL THRU ITEM RECORD”.
Total Quantity Invoiced	Integer field	Helps to identify the total quantity invoiced for the specific sales order.
Invoice Subtotal	currency	Subtotal of an invoice created from sales order
SO Number	List/Record	To display the sales order document number
Invoice Number	List/Record	To display the invoice document number

Technical overview

1. Users need to manually upload the CSV file in the “SELL THRU REPORT”  custom record for processing the file.
2. We will create a folder in the file cabinet for uploading CSV files.
3. The user must be uploaded a CSV file into this folder using the custom record.
4. On the saves button action, we will run a scheduled script for creating “SELL THRU ITEM RECORD”. And done validation for user uploaded file. If the file type is CSV then only we consider the file for further processing.
5. This process only happens in the creation context of the “SELL THRU REPORT”.
6. The “SELL THRU ITEM RECORD” record is created to store each individual item-total quantity based on its purchase cost.
7. If the same item contains in multiple rows. We will take the sum of the “Net unit sold” of those items from the CSV file. Also, create a “SELL THRU ITEM RECORD” record with its total sum quantity (One record for one item SKU). if the same item SKU have multiple purchase cost we will create separate “SELL THRU ITEM RECORD” based on the purchase cost.
8. After processing the “SELL THRU ITEM RECORD” creation script we will flag the “SELL THRU ITEM PROCESS COMPLETED” field in the “SELL THRU REPORT” record. It will help us to identify the process that is completed.
9. We will display the button on the “SELL THRU REPORT” for creating invoices. The button only displays the view mode of the record. The button name will be “Create Invoice”.
10. On the button action. We will check the type of uploaded file.
11. If the file type is not CSV we will show an alert for acknowledgment and never consider the record for processing.
12. If the file type is CSV we will run a scheduled script in the background. The script will fetch each individual sales orders which are included in the saved search.
13. The script will check the same items SKU and purchase cost combination record “SELL THRU ITEM RECORD” exist. 
14. If the record exists we will bill the corresponding quantity based on the  “pending quantity for invoicing”.
15. After creating the invoice we will subtract the billed quantity from the item record.

For invoice creation, we will check the following criteria.

1. Using the given saved search We will find the sales order for invoice creation (Search Id: 14235). This will be based on the SKU and SCP numbers.
2. Compare each item’s SKU total quantity with the corresponding sales order search column “pending quantity for invoicing” and “item rate” value.
3. We will fetch all sales orders from the saved searches which contain the item SKU or SCP number and the same “item rate”. Using these sales order values we will create invoices.
4. If the same item SKU contains multiple orders. The first-come order will be taken for processing first. If quantity will remain then only the second sales order will be considered for invoice creation.
5. After creating the invoices, we will set up the corresponding “SELL THRU REPORT” custom record value in the invoice.
6. After the process is completed we will check the total amount of invoices created and the total amount in the CSV file. If it is a mismatch we will send an email to “ rohit@balajiwireless.com”.
7. Also, we will attach an error file in CSV format. If any error is faced on the invoice creation. The error file column contains the items SKU, SCP Number, sales order, error.
8. If any item mapping fails over Netsuite item and CSV file item. We will create a CSV file with detail of mapping failed items and send an email to “rohit@balajiwireless.com” after the invoicing process is complete.

Assumption

1. We will create invoices for each individual sales order for CSV listed item. 
2. For creating invoices for the same item we will use multiple sales orders until the whole item quantity will bill.
3. We assume the total amount of items in the CSV file = SUM(Net Units Sold * Purchase Cost).
4. We assume the total amount of created invoice created = Sum of the subtotal of Invoice record.
5. The purchase cost in CSV considers as the item rate in Netsuite.
6. We are assuming that a sales order does not repeat the same item in multiple lines. (this will be the rare case our developer will handle this at the time of implementation)
7. We will only transform corresponding sales orders for invoice creation.
8. The same CSV template will be used used for file upload. The CSV header must be the same and same order. Otherwise, we will not process that CSV file.

Note

1. The invoices are order-specific. A single invoice for single order. While processing single order.
2. Can we do the implementation directly in production? We believe these orders and searches do not exist in the sandbox. This will be a risk factor in the development.