Proposal summary
JCurve Solutions would like to create a new report to retrieve data for data from Invoices, Credit Note documents, Debit Note documents. The report can recall backdate and need to show the amount based on as at date selected. And print reports in Excel format.
Requirement
New report ‘Invoice Uncollected’ to retrieve data for data from Invoices, Credit Note documents, and Debit Note documents.
Assumptions:
- This report will retrieve data for data from the Invoice, Credit Note document, Debit Note document
- The report can recall backdate and need to show the amount based on as at date selected
- Print report as Excel format
Report Details – Criteria:
| Criteria: | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | As of Date | Select as of Date to filter transaction | Input as at Date to recall report compare with date in invoice and 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 Uncollected | Report Name | Fixed |
| 3 | As at | Show as at date to recall report | Based on as of date select in the criteria |
| Report Column: | |||
| # | Field | Description | Details |
| 1 | Seq# | Running No | Running No |
| 2 | Contract# | Project No. | Project Order |
| 3 | Sale Type | Sale Type | Sale Type from invoice, credit note |
| 4 | Customer PO | Reference to customer PO | Other references No in Invoice screen and credit Note screen |
| 5 | Customer Name | Customer Name | Customer in Invoice screen and credit Note screen |
| 6 | Sector Type | Sector type | Sector Type from Customer Master |
| 7 | Project Name | Project Full Name | Project Full Name |
| 8 | Sales Name | Sales Man | Sale Reps |
| 9 | Invoice | Invoice document No | Invoice # |
| 10 | Inv. Date | Date | Date |
| 11 | Amount | Total | Total => Include VAT |
| 12 | Credit Term | Term | Term payment from customer master |
| 13 | Aging | No. of invoice outstanding compare with as at a date in the report | Show no. of the day by comparing invoice due date with as at date in the report selection criteria |
| 14 | Not yet due | Amount of invoice which is not due yet | Amount from invoice which Date Diff (as at date in report selection criteria – Invoice Due Date) < = 0 |
| 15 | Up to 30 | Amount from invoice which is no. of outstanding day between 1-30 | Amount from invoice which 1<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 30 |
| 16 | 31 – 60 | Amount from invoice which is no. of outstanding day between 31-60 | Amount from invoice which 31<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 60 |
| 17 | 61 – 90 | Amount from invoice which is no. of outstanding day between 61-90 | Amount from invoice which 61<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 90 |
| 18 | 91 – 120 | Amount from invoice which is no. of outstanding day between 91-120 | Amount from invoice which 91<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 120 |
| 19 | 121 – 150 | Amount from invoice which is no. of outstanding day between 121-150 | Amount from invoice which 121<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 150 |
| 20 | 151 – 180 | Amount from invoice which is no. of outstanding day between 151-180 | Amount from invoice which 151<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 180 |
| 21 | 181 – 210 | Amount from invoice which is no. of outstanding day between 181-210 | Amount from invoice which 181<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 210 |
| 22 | 211 – 240 | Amount from invoice which is no. of outstanding day between 211-240 | Amount from invoice which 211<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 240 |
| 23 | 241 – 270 | Amount from invoice which is no. of outstanding day between 241-270 | Amount from invoice which 241<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 270 |
| 24 | 271 – 300 | Amount from invoice which is no. of outstanding day between 271-300 | Amount from invoice which 271<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 300 |
| 25 | 301 – 330 | Amount from invoice which is no. of outstanding day between 301-330 | Amount from invoice which 301<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 330 |
| 26 | 331 – 360 | Amount from invoice which is no. of outstanding day between 331-360 | Amount from invoice which 331<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 36 |
| 27 | 361 – 390 | Amount from invoice which is no. of outstanding day between 361-390 | Amount from invoice which 361<= Date Diff (as at date in report selection criteria – Invoice Due Date) <= 390 |
| 28 | >>> 390 | Amount from invoice which is no. of outstanding day greater than 390 | Amount from invoice which Date Diff (as at date in report selection criteria – Invoice Due Date) > 390 |
| 29 | Appointment Date | Appointment date | Link from Customer Billing note |
| 30 | Remark | Blank Column | |
| Report Summary: | |||
| # | Field | Description | NS field |
| 1 | Summary Amount | Summary Amount | Sum Amount |
| 2 | Not yet due | Summary Not yet due | Sum Amount in Column |
| 3 | Up to 30 | Summary up to 30 | Sum Amount in Column |
| 4 | 31 – 60 | Summary 31 – 60 | Sum Amount in Column |
| 5 | 61 – 90 | Summary 61 – 90 | Sum Amount in Column |
| 6 | 91 – 120 | Summary 91 – 120 | Sum Amount in Column |
| 7 | 121 – 150 | Summary 121 – 150 | Sum Amount in Column |
| 8 | 151 – 180 | Summary 151 – 180 | Sum Amount in Column |
| 9 | 181 – 210 | Summary 181 – 210 | Sum Amount in Column |
| 10 | 211 – 240 | Summary 211 – 240 | Sum Amount in Column |
| 11 | 241 – 270 | Summary 241 – 270 | Sum Amount in Column |
| 12 | 271 – 300 | Summary 271 – 300 | Sum Amount in Column |
| 13 | 301 – 330 | Summary 301 – 330 | Sum Amount in Column |
| 14 | 331 – 360 | Summary 331 – 360 | Sum Amount in Column |
| 15 | 361 – 390 | Summary 361 – 390 | Sum Amount in Column |
| 16 | >>>390 | Summary >>>390 | Sum Amount in Column |
Our Solution
This requirement can be achieved by creating a new report using a script by fetching the data using transaction search. And this report will retrieve data for details from the Invoice transactions.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Invoice Uncollected. Then upon clicking the “Invoice Uncollected”, 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 As of Date 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.
| Filters/ Criteria tabs Shown in Report Page: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | As of Date | Date(trandate). The transactions will be displayed on or before the date selected in this filter. |
| Backend Criteria for Report: | ||
| 1 | Transaction Type | Invoice records |
| 2 | Status | Open |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Invoice Uncollected | Fixed |
| 3 | As at | Based on as of date select in the criteria |
Please see the table below for the report column details for the detailed ‘Invoice Uncollected’ report.
| Report Columns: | ||
| # | Field | Field Name from NS records |
| 1 | Seq# | Running no(Not from NS) |
| 2 | Contract# | Project Order(cseg_project_order) from project record |
| 3 | Sale Type | Sale Type(class) from Invoice record |
| 4 | Customer PO | PO#(otherrefnum) from Invoice Record |
| 5 | Customer Name | Customer(entity) from invoice |
| 6 | Sector Type | Sector Type(custentity_sector_type) from customer record |
| 7 | Project Name | Project Full Name(custbody_prj_name) from invoice record |
| 8 | Sales Name | Sale1 from Invoice (salerep) |
| 9 | Invoice | Invoice #(tranid) from invoice record |
| 10 | Inv. Date | Date(trandate) from invoice record |
| 11 | Amount | Total(total) from the summary box on invoice record |
| 12 | Credit Term | Terms(terms) under financial subtab from the customer record |
| 13 | Aging | Number of Days between invoice due date and selected date in the filter |
| 14 | Not yet due | Amount(total) from invoice which Date diff. b/w selected date in filter and due date(duedate) of inv. <= 0 or Aging <=0 |
| 15 | Up to 30 | Amount(total) from invoice which 1 <= Date diff. b/w selected date and due date(duedate) of inv. <=30 or 1<= Aging <=30 |
| 16 | 31 – 60 | Amount(total) from invoice which 31 <= Date diff. b/w selected date and due date(duedate) of inv. <=60 |
| 17 | 61 – 90 | Amount(total) from invoice which 61 <= Date diff. b/w selected date and due date(duedate) of inv. <=90 |
| 18 | 91 – 120 | Amount(total) from invoice which 91 <= Date diff. b/w selected date and due date(duedate) of inv. <=120 |
| 19 | 121 – 150 | Amount(total) from invoice which 121 <= Date diff. b/w selected date and due date(duedate) of inv. <=150 |
| 20 | 151 – 180 | Amount(total) from invoice which 151 <= Date diff. b/w selected date and due date(duedate) of inv. <=180 |
| 21 | 181 – 210 | Amount(total) from invoice which 181 <= Date diff. b/w selected date and due date(duedate) of inv. <=210 |
| 22 | 211 – 240 | Amount(total) from invoice which 211 <= Date diff. b/w selected date and due date(duedate) of inv. <=240 |
| 23 | 241 – 270 | Amount(total) from invoice which 241 <= Date diff. b/w selected date and due date(duedate) of inv. <=270 |
| 24 | 271 – 300 | Amount(total) from invoice which 271 <= Date diff. b/w selected date and due date(duedate) of inv. <=300 |
| 25 | 301 – 330 | Amount(total) from invoice which 301 <= Date diff. b/w selected date and due date(duedate) of inv. <=330 |
| 26 | 331 – 360 | Amount(total) from invoice which 331 <= Date diff. b/w selected date and due date(duedate) of inv. <=360 |
| 27 | 361 – 390 | Amount(total) from invoice which 361 <= Date diff. b/w selected date and due date(duedate) of inv. <=390 |
| 28 | >>> 390 | Amount(total) from invoice which Date diff. b/w selected date and due date(duedate) of inv. >390 |
| 29 | Appointment Date | Appointment Date(CBN Customer Billing Note) from CBN Customer Billing List subtab under custom subtab on invoice record. |
| 30 | Remark | Blank column |
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:
- If the file size is less than 10Mb (based on predefined line count), the excel file will be downloaded directly from Netsuite.
- 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
- 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.
- 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
- Each report page can include 1000 lines, with the 1001st line will display the 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.