Proposal Summary
Airport Home Appliance would like to create a new report to retrieve data from sales orders by getting details from invoices, Customer deposits, and RMA’s. And the reports print will be in Excel format.
Requirement
A new report to retrieve data from sales orders by extracting details from invoices, customer deposits and RMAs. Also reports should be printed in excel format.
Report Column
# Field
1 Date of SO
2 Sales Order #
3 Sales Order $ Amount
4 Balance Due
5 Date of CD
6 Customer Deposit #
7 Type of Payment (CD)
8 CD $ Amount
9 Date of Invoice
10 Corresponding Invoice
11 Total Invoice Amount
12 Payment Transaction
13 Payment Amount
14 Balance Due ( Invoice)
15 Type of Payment
16 Date Of RMA#
17 RMA#
18 RMA $ Amount
19 Date of CM
Our Solution
This requirement can be achieved by creating a new report to retrieve data from the Sales orders with details from invoices, Customer deposits, and RMA’s.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Sales Report. Then upon clicking the “Sales Report”, the user will be redirected to a report page.
The report will first display a detailed sales report of the latest 200 sales orders with related transactions based on the default filter, such as the month filter as Current Month, and location, Customer, Sales person filter as “-All-”.
Filters/ Criteria tabs Shown in Report Page
# Field Field Name from NS records
1 Sales Person Sales Rep
2 Location Location
3 Customer Customer
4 As of Month Month ( Custom List)
The fields given below will be indicated on the header part of the report.
Report Column
# Field Description
1 Sales Order # Sales order Number
2 Related Transaction Transaction Name
3 Document Number Document Number
4 Date trandate
5 Amount Amount from transactions
6 Amount Due Amount Remaining
Finally, on the report page, an Export button will be added to download the detailed report in Excel format. When you click the export button, the report will be downloaded in Excel format based on the report’s size in the following cases:
1. If the file size is less than 10Mb (based on predefined line count), the excel file will be downloaded directly from Netsuite.
2. Otherwise, the Report Download will take place through a scheduled process where there is a delay in the process based on the availability of the Netsuite Queue. For that will make use of a custom record to track the requests.
a. If the file size is greater than 10Mb & the total email size is less than 19Mb, the download process will take a long time. So that we will split the file into multiple files with predefined line counts in a single file due to the size of each individual attachment cannot exceed 10Mb. And the generated multiple files will be emailed to the initiator’s email. According to the limitations of Netsuite, the total message size (including attachments) in the email must be 20MB or less.
b. If the file size is greater than 19Mb, the download will take a long time, and all of the results will not load in a single file and email. To overcome this, we will generate multiple files with predefined line counts in a single file. And all generated files will be saved in Netsuite’s file cabinet and attached to the corresponding custom record entry which tracks that Report generation process.
Once all export functions have been completed in NetSuite, a single email with the custom record link will be sent to the download initiator. The initiator can export files by login to the NetSuite and by navigating to the custom record entry with the link from the email.
The summary line on the Excel file will be calculated based on the result lines included in the corresponding file in the case of splitted file download.
Assumptions
· Pagination will be used to list all the sales order with corresponding related transactions.
· Depending on the month filter, each report page can only have a maximum of 200 sales order details, beginning with the 201st sales order, the details will be presented on the next page.
Exclusion
· The line counts in the single file to progress with the scheduled process can be confirmed during the development phase.
· If the file is more than 19Mb, the user should go to NetSuite and manually download the file from the account.
· The file cabinet will be stored with many files in the future. This will have an impact on the Netsuite account’s file cabinet. So, users have to remove these files from a file cabinet in the future to free up the storage.