Proposal summary

This proposal describes the solution and estimated effort for completing the customization.

Requirement

Pleo is an expense management app, and they want to do a custom import of records registered in the app into NS.

We need to read a .xlsx file containing any information to be loaded in NetSuite and save it in the back up folder.

Based on the information in the file we can create the following:

Requirement

Pleo is an expense management app, and they want to do a custom import of records registered in the app into NS.

We need to read a .xlsx file containing any information to be loaded in NetSuite and save it in the back up folder.

Based on the information in the file we can create the following:

• Journal
• Bill + Bill Payment
• Expense Report
• Expense Payment

Deliverables

We will be creating a folder for storing the file attached by the user and to identify whether it is a Pleo bank account. The customization will only be working for all files uploading in this folder and there will be a backup folder for saving all the processed files.

Following points to be noted:

• To identify if the account is a Pleo bank account client has created an account checkbox field Pleo {custrecord_nbpleo_check}
• Receipt PLEO URL field {custbody_nbpleo_url } will be already created in records
• The file is loaded into the Pleo folder by the NetSuite user.
• A menu where the user can set up the internal id of the folders (it should be different for each customer so we cannot hardcode it) needs to be added.
• In NetSuite the journals need two lines, but we have all the information in one line in the file.

 

Reading the file                

We will be adding a scheduled script for fetching the files from the player folder and processing the data.

To identify which transaction, we need to create we will be referring to the following conditions:

• Check the “Contra Account” field (column W in the sample file)
• If the column for the custom field {custrecord_nbpleo_check} is true, then it is a Pleo account.
  • If the “CIF” file field (column AB) is empty, then it is considered as a journal else we will create first the bill and after paying the total amount of the bill use the field “Contra Account “as a bank account.
• If the column for the custom field {custrecord_nbpleo_check} is false.
  • If the “Receipt“file field (column B ) is not empty then Expense Report
  • If none of the above conditions, it will be an Expense Payment

Note: It is the responsibility of the file creator to add the field values accurately

 

Mapping file fields with NetSuite

The following table displays the field mapping of the fields in the file that is processing and the corresponding fields in NetSuite records.

Column in the File	Field in Netsuite	Example	Comments
Date	{date} of each transaction	20/11/2020
Receipt	Journal: header memo Bill: Reference number {transid} Expense Report: N/A	2000014	If a transaction has more than one line, the receipt number is the same. We need to check this value to know if there are more lines in the file of the same transaction. We can use this field as external id of the transaction.
Text	line {memo}	Mileage – 2 km | Kilometraje | Estefania stefania Gambin | visita cliente
Amount	Journal: {Debit amount} Bill: {expenses amount} (when currency = orig currency, if not ignore it) Expense Report: {amount} (when currency = orig currency, if not ignore it) Expense Payment: {amount} (when currency = orig currency, if not ignore it)	1,50	Numbers with the Spanish format. The comma is the decimal delimiter. The bill amount is the same amount of the bill payment too Expense payment are negative in the file but needs to be positive in Netsuite.
Currency	{currency}	EUR	When is different than Orig. currency, ignore
Orig. amount	Journal: N/A Bill: {expenses amount} Expense Report: {Foreign amount} Expense Payment: {amount}
Orig. currency	{currency}
Source description	Journal: N/A Bill: Vendor {entity} Expense Report: N/A Expense Payment: N/A	Ernesto	If we cannot find it in NetSuite, show an error message “Entity not found”. If it is a journal, create the journal without this field.
Category	Journal: N/A Bill: Expense category Expense Report: Category Expense Payment: N/A	Meals
Account number	Journal: Account Bill: Account (only if category is empty) Expense Report: N/A Expense Payment: N/A	43000000
Owner	Journal: N/A Bill: N/A Expense Report: Empleado Expense Payment: Empleado	Ernesto
Note	N/A
Current Balance	N/A
Department	Department	Finance
Department Code	N/A
Receipt urls	{custbody_nbpleo_url}	https://app.pleo.io:443/receipts/6a24d0c2-1d34-4d3f-81da-5d55519e77d8/file
Employee Code	N/A
Review Status	N/A
Review Note	N/A
Tax Code	Journal: N/A Bill: {tax item} Expense Report: {tax item} Expense Payment: N/A	S-ES
Tax Rate	N/A
Tax Amount	N/A
Contra Account	Journal: Second line of the journal account Bill: is the bank account for the payment. Expense Report: N/A Expense Payment: N/A	57200000
Bill Number	Journal: N/A Bill: {tranid} Expense Report: N/A Expense Payment: N/A	fac1234
Bill Date	Journal: N/A Bill: {custbody_nbs236_sii_bill_date} Expense Report: N/A Expense Payment: N/A	03/04/2023
Due Date	N/A
Date Paid	Date of the bill payment	03/04/2023
CIF	N/A
Document Number	N/A
Document Type	N/A
Field 1			this is is custom field, it can be Cost center, project
Field 2			this is is custom field, it can be Cost center, project
Field 3			this is is custom field, it can be Cost center, project

Error Handling 

We will be creating a custom record for error handling if any error happened while file processing and attaching it with the custom page subtab for showing the user if any error occurred during the process. 

We can notify the user about the error by email to specified employee records in NetSuite

Assumptions

• We assume that the Netsuite user will upload files in the NetSuite folder
• File headers’ name will not be altered in the   future and the user will always define the headers properly in the xlsx file.
• File format will be .xlsx always and always follow the sample format.
• We assume that the standard NetSuite import option is not used to reduce the time for file preparation based on each transaction.
• Field 1, field 2, and field 3 will be buffer custom fields available in the records.
• We will be creating standalone records for Bill with only values on the expense sub-list.
• Journal payment will not be considered.
• We will only consider the necessary field values (account details, entity details, etc) are valid in the NetSuite account to create the records. 
• Customization will not accept any files which are corrupted or missing headers.
• We are assuming that the quantity provided by each vendor is accurate. 
• The file size will not exceed 10 MB. 
• Not considered any subsidiary or location restrictions 
• No restrictions related to Subsidiaries or locations or even roles. 

Risk

• We will not process the files if the file size is more than 10 MB.
• We will not be validating the field values provided in the file and it is the responsibility of the user to add a file that is not corrupted and in correct format.

• not process the files if the file size is more than 10 MB.

Time

64 hours