Proposal summary
This proposal describes the script optimization for creating invoices automatically using call data.
Requirement
Client would like to implement an automation to create invoices in NetSuite with following options:
- Manual modifications to the import files are unnecessary before importing.
- However, if scripting is possible, it should be relatively straightforward to automate the process of eliminating irrelevant tabs and line items that are not required, such as “Calls to Mobile” and “SMS Outgoing.” Subsequently, the modified file can be saved in CSV format, prepared for importing.
- Proper error handling should be implemented.
Existing Script: Process Call Time Imports
The NetSuite User adds the files to the file cabinet in the pending folder. And manually execute the scheduled script to fetch the files from this file cabinet and fetch the data from the following custom records:
- Call Time Import Defaults
- CallIdentifierID and Label Map
- Customer Traffic Agreement
- Iridium Traffic Agreement Plan
- Satellite Phone
- Satellite Phone Rental Booking
Using the data from the custom record, the script is creating invoices for the data in the file.
Deliverables
We will be implementing a scheduled script for creating the invoices from the excel file added in a folder of the file cabinet in NetSuite.
File Preparation
We will automate the file preparation from the excel raw data containing multiple files to a single sheet with the “Usage details” sheet name.
- When examining the new sample data provided, consists of various tabs within the data file, and we will consider the data specifically within the “Usage Details” tab.
- The current CSV file being used for the call import script is a modified version derived from the “Usage Details” tab
- Prior to the modifications, the file did not contain any values for the Caller Identifier ID.
Error Handling
We will create a new script with proper error handling for the Invoice creation process using Call details. Once the script failed to create the invoice due to any error, the script will send a consolidated email to the user with the error file. Also, the error files will be saved in the file cabinet in the new folder. Only the successful files will be moved to the Processed folder in the file cabinet. The unsuccessful files will exist in the error folder with the error message.
The script will handle the issues that we are currently facing due to the large amount of data by the new script architecture.
Fetching data and Invoice generation in NetSuite
Based on the Service Number in the CSV file, the data for invoice generation can be fetched from the 2 different custom records in NetSuite. And based on that, the invoices are created for Paying and Rental types. We have added the details of the two options as below:
Customer Traffic Agreement List
- Using the Service Number (Mobile number) from the import file, the active “Customer Traffic Agreement” custom record with customer value can be identified in the NetSuite.
The “Customer Traffic Agreement List” contains all airtime customers we bill monthly, which can possibly have multiple handsets. When we obtain the data from the Customer traffic agreement custom record, the rental Type is considered as PAYING. - If the file has multiple lines for the same customer, all lines will be merged into one invoice.
- The rate values from the “Iridium Traffic Agreement Plan” custom record are sourced to the fields under the “Rate Information iridium” subtab in the “Customer traffic agreement” custom record.
- Hence the rate will be fetched from the field “Iridium – fixed” (custrecord_iridium_fixed_customer) from the custom record “Customer traffic agreement”.
- The CallIdentifierID and Label Map custom record is identified using the CallIdentifierID from the CSV file and rental type Paying.
- The subsidiary and business are fetched from the customer record. And based on the subsidiary, Business, and rental type, the Call Time Import Defaults custom record is identified in the NetSuite.
- If we obtain more than one custom entry record from the custom record Customer traffic agreement, we will create an invoice for the recent record that we have found since only one active record at the same time.
- When we create the invoice record, each line in the CSV file is considered a separate line item in the invoice record.
- If the file contains multiple lines for the same customer, a single invoice will be created with multiple lines for the customer. After each line record with an originator number, a total call charges line for that specific originator number against the item “Call Time by Originator” will be added. The amount for this line should be the sum of the individual amounts associated with the corresponding originator number.
Field mapping:
| Field in invoice | Record in NetSuite | Field in custom record/CSV file |
| Invoice template | Call Time Import Defaults | Invoice form (custrecord_cti_invoice_form) |
| Customer | Customer traffic agreement | Customer (custrecord_customer_rental_agreement) |
| Subsidiary | Customer | Subsidiary of customer record |
| Reference # | Customer traffic agreement | CUSTOMER PO# (custrecord_customer_po) |
| Memo | Call Time Import Defaults | Default Memo (custrecord_cti_default_memo) |
| Location | Call Time Import Defaults | Default Location (custrecord_cti_location) |
| Originator number | Service Number from CSV file | |
| Business | Customer | Business(custentity_business) |
| Item | Call Time Import Defaults | Default Item (custrecord_cti_item) |
| Rate in the item line | Customer traffic agreement | Iridium – fixed (custrecord_iridium_fixed_customer) |
| Description in the item line | CallIdentifierID and Label Map | Label Description |
| Quantity in item line | Volume from CSV file in minutes | |
| Amount in item line | Quantity * rate | |
| Date in item line | Date from CSV file | |
| Time GMT in item line | Time from CSV file | |
| Destination number in item line | DestinationNo from CSV file | |
| Unit | Unit from CSV file | |
| Calltocalltype | Usage Type from CSV file | |
| Call identifierId | CallIdentifierID from CSV file | |
| originator_country | OriginatorCountry from CSV file | |
| destination_country | DestinationCountry from CSV file | |
| Originator Number | Service Number from CSV file | |
| CTA(Paying) | Customer Traffic Agreement | Related Customer Traffic Agreement internal id |
Satellite Phone List
- If the Service Number (Mobile number) does not exist in any of the Customer Traffic Agreement custom records, then the script will search for theService Number (Mobile number) in the “Satellite Phone List” custom record. The Satellite Phone List” stores the rental handsets. If we obtain the data from the custom record Satellite Phone Rental Booking Record, then the rental Type is considered as RENTAL.
- Call Time Import Defaults custom record is identified in the NetSuite.
- The custom record “Iridium Traffic Agreement Plan” is linked to the “Satellite Phone” custom record using field “Rate Plan(custrecord_rate_plan_satellite_phone)”
- When we are creating the invoice record, each line in the CSV file is considered as a separate line item in the invoice record.
- Units will be either Mins or SMS based on the values from the CSV file.
- From the “Satellite Phone”, the Satellite Phone Rental Bookings for the applicable dates will be identified and create the individual invoice for the customer in the “Satellite Phone Rental Bookings”.
- If the same customer has different originator numbers in the same billing period, multiple invoices will be created for different originators in NetSuite.
Field mapping:
| Field in invoice | Record | Field in custom record |
| Invoice template | Call Time Import Defaults | Invoice form (custrecord_cti_invoice_form) |
| Customer | Satellite Phone Rental Booking | Customer (custrecord_satphone_customer) |
| Subsidiary | Customer | Subsidiary of customer record |
| Reference # | Satellite Phone Rental Booking | Customer reference info (custrecord_cus_reference_info) |
| Memo | Call Time Import Defaults | Default Memo (custrecord_cti_default_memo) |
| Location | Call Time Import Defaults | Default Location (custrecord_cti_location) |
| Originator number | Service Number from CSV file | |
| Booking Details (custbody_rental_booking_link) | Satellite Phone Rental Booking | Related Satellite Phone Rental Booking |
| Business | Customer | Business(custentity_business) |
| Item | Call Time Import Defaults | Default Item (custrecord_cti_item) |
| Rate in the item line | Iridium Traffic Agreement Plan | Iridium – fixed (custrecord_iridium_fixed) |
| Description in the item line | CallIdentifierID and Label Map | Label Description |
| Quantity in item line | Volume from CSV file | |
| Amount in item line | Quantity * rate | |
| Date in item line | Date from CSV file | |
| Time GMT in item line | Time from CSV file | |
| Destination number in item line | DestinationNo from CSV file | |
| Unit | Unit from CSV file | |
| Calltocalltype | Usage Type from CSV file | |
| Call identifierId | CallIdentifierID from CSV file | |
| Originator country | OriginatorCountry from CSV file | |
| Destination country | DestinationCountry from CSV file | |
| Originator Number | Service Numberfrom CSV file | |
| Rental Booking | Satellite Phone Rental Booking | Related Satellite Phone Rental Booking internal id |
Assumptions
- We will consider the Service Number in the unprocessed file as the Mobile number in NetSuite.
- The Number Called in the unprocessed file can be considered as the Destination Number in the generated invoice record in NetSuite.
- Quantity from the CSV file can be considered as the Duration of call and this value will be set in the invoice record as quantity.
- We will remove the line items with usage type “mobile terminating” or “SMS incoming” from the initial file to create the invoice record.
- The call type description on the invoice should come from the CallIdentifierID and Label Map, Units would be SMS for any sms codes and Mins for any voice services.
- The initial files in the file cabinet are Excel files in xlsx format. Hence, we can convert the files to actual data to generate invoices. Also, we can include zeros if the data in the Excel file contains preceding zeros.
- The file will not contain any special characters to avoid file corruption.
Risk
- The script will not handle any other file type other the mentioned xlsx. We need to update the script if it is any other file type.
- If the item record for the items specified in the file are not active or present in the NetSuite, we will throw an error while invoice creation.
- If there will be any errors occur during the process, the user needs to correct the file and reupload the file.
- 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.
Queries
From the initial file, we are getting “Usage Type” like SMS incoming, SMS outcoming, and the file doesn’t contain the CallIdentifierID(as highlighted below) to identify the “CallIdentifierID and Label Map” custom record in NetSuite. So could you please share the script and implementation details of how we are currently creating the new file from the initial file to include the CallIdentifierID, OriginatorCountry, and DestinationCountry?
Future scope
We have different options for automating the file uploading to the folder in NetSuite.
- Email plugin: The user can send the file directly to a constant email address in NetSuite and files will be automatically saved to the file cabinet.
- SFTP server: If any third party is providing the details in the file and they can send it to SFTP servers, we can automate the file downloading and save in the file cabinet.
- Custom page/custom record for uploading the file if the user has no access to the file cabinet.