Proposal summary

JCurve Solutions would like to create a new report to retrieve data for data from invoices and credit notes that will be received based on the due date of the document. This report will show the current status of documents with open status only. And print reports in Excel format.

Requirement

New report ‘รายงานการรับเงินจาก Invoice’ to retrieve data for data from invoice and credit note that will be received based on the due date of the document.

Assumptions:

• This report will retrieve data for data from invoice and credit note that will be received based on due date of document
• This report will show current status of document with open status only
• Print report as Excel format

Sample Report:

Report Details – Criteria:

Criteria:
#	Field	Description	Details
1	Subsidiary	Select Subsidiary for report	name of Subsidiary
2	Due Date from … to	Select as of Date to filter transaction	Input Date range to recall report compare with due date from invoiceand date from CN , DN
Report Details:
#	Field	Description	Details
1	Company Name	Show Subsidiary name that select for report	name of Subsidiary from custrecord_rapid_tht_company_legalname
2	รายงานการรับเงินจาก invoice	Report Name	Fixed
3	Due Date From … to	Show date range to recall report	Based on as of date select in the criteria
Report Column:
#	Field	Description	Details
1	Sale Name	Sale Person	Group Data by Sale person (Order By)
2	Invoice No	Invoice No	Document No from invoice/ credit note/debit note
3	Invoice date	Invoice date	Date from invoice/ credit note
4	Contract	Project No	Project No
5	Sale Type	POC Type of contract	Sale Type
6	Customer Name	Customer name of project	Customer name from Invoice / Credit Note (entity)
7	Project Name	Project Name	Project Full name from project Master(ccustentity_gable_rec_csh_fullname)
8	PM	Project Manager	PM from Invoice/ Credit Note (custbody_pm)
9	Amount	Invoice Total Amount	Total Invoice (Include VAT)
10	Age	Due Date compare to current date recall report	Date diff from due date and current date recall report
11	Due Date	Due date on document	Due date from invoice/ Date from CN
12	Remark	Remark	Remark from document (comments)
Report Summary:
#	Field	Description	NS field
1	Summary	Summary	Sum Amount Total

Our Solution

This requirement can be achieved by creating a new report using a transaction saved search. And this search will retrieve data for data from invoices that will be received based on the due date of the document.

The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> รายงานการรับเงินจาก Invoice. Then upon clicking the “รายงานการรับเงินจาก Invoice”, the user will be redirected to a report page.

The report will first display a detailed report based on the default filters, such as the subsidiary as G-ABLE CO., LTD and the Due Date From as 1st Jan of the current year and To as today, and then show a summary of the total amount in the report’s last row.

The fields given below will be indicated on the header part of the report.

Criteria/ Available Filter:
#	Field	Field Name from NS records
1	Subsidiary	Subsidiary(subsidiary) list
2	Due Date from … to	Due Date(duedate) from invoice record
Backend Criteria for Report:
1	Type	Invoice
2	Status	Open
Report Details::
#	Field	Field Name from NS records
1	Company Name	Subsidiary(subsidiary) name selected in filter
2	รายงานการรับเงินจาก invoice	Fixed
3	Due Date from … to	Based on the due date(duedate) select in the criteria

Please see the table below for the report column details for the detailed ‘รายงานการรับเงินจาก invoice’ report.

Report Column:
#	Field	Field Name from NS records
1	Order By(Sale Name)	Sale1(salerep) from Sales Order
2	Invoice No	Invoice#(tranid) from invoice record
3	Invoice date	Date(trandate) from the invoice record
4	Contract	Job Id(entityid) from the related project record
5	Sale Type	Sale Type(class) from the related Sales Order record
6	Customer Name	Customer Name(entity) from the related Sales Order record
7	Project Name	Project Full Name(custentity_gable_rec_csh_fullname) from the related project record
8	PM	PM(custbody_pm) from Sales Order
9	Amount	The total amount(total) from the invoice record
10	Age	Date Difference between the due date and date selected in the filter
11	Due Date	Due date(duedate) from the invoice record
12	Remark	Remark(comments) from related project master record

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. And in real-time, the excel file will be exported by splited files based on the line counts that can be included in the single file.
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 if a custom record to track the requests
   1. If the file size is greater than 10Mb, then 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 if the file count is smaller than or equal to the specific count that we can attach in the email.
   1. If the size of files is greater than the specific count, 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

Note: Exported multiple files will be attached to the email is a very exceptional case. i.e. Most of the export function occurs in real-time or the files attached to the custom record.  

Assumptions

• Each report page can include 1000 lines, with the 1001st line displaying a summary of the Total Amount of all results in the report.
• The report will cover all open invoices without considering custom forms.

Risks

• 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.
• The report will not include a color format or row-wise sales rep names in the report layout due to standard suitlet page limitations. The report on the UI will be displayed as normal Netsite page without any styles. It will follow the standard behaviour
• As per the standard suitelet elements, we can arrange the Order By(Sales Name) row as a separate column (first column), then display the associated transactions in the following columns. In the last row of the PM name column, the summary row of the amount column for the appropriate sales rep will be displayed. And the name of the next sales rep and the transaction details will be added to the following lines.
  

We are running all reports as a suitlet page, and the details will be displayed on multiple pages based on the results loading capacity. As a result of the lack of script loading, the sorting functionality will not work properly.