Proposal summary
Beans and Brews currently imports the Point of Sale data into NetSuite manually each business day. They would like to automate this process so that the csv file is picked up automatically from an FTP site and then imported into NS as a “pending” Journal Entry.
Requirement
Create a solution for automatically importing sales data as a Pending Journal entry in the Netsuite account automatically every business day.
Our Solution
We can automatically fetch the csv file from the FTP site and import the CSV file into the Netsuite account everyday at 12 am. We can use SFTP to fetch the file from the FTP site. The file should be in a separate folder and the name should be unique in order to identify the CSV file to fetch. The status of import can be sent to the particular employee. The status of the journal entry imported will always be “Pending Approval”.
Assumptions
- The file name will be the same for the CSV file and we are referring to the file name for fetching.
- The CSV file will be a formatted file which is ready to import
SAMPLE CSV CONTENT
Sample columns needed
- Entry No
- Date
- Posting period
- Memo
- Currency
- Account
- Debit
- Credit
- Line memo
- Department
- Country
- Name
Note: Date should be in the same Date Format of Netsuite. As well, all the list field values should be in an exact match with the Netsuite values or we need the Netsuite internal id instead of text.
- The file is always a CSV file. The maximum file size should be 10MB & there will be only one CSV file per day.
- The file will be containing a maximum of 25000 lines as the import supports 25000 lines import at a time.
- We don’t have to remove the file after fetching from the FTP site.
- We have to create the journal entries with status “Pending Approval”
- We assume the Journal will have the status field (After enabling approval workflow the status field will be shown in the JE)