Proposal summary
JCurve Solutions would like to create a new report to retrieve data for data from Sale order, Requisition, and project master data. This report will show the current status of the document when recalling the report. And print reports in Excel format.
Requirement
New report ‘Preload & Preapprove’ to retrieve data for data from Sale order, Requisition, and project master data.
Assumptions:
- This report will retrieve data for data from Sale order, Requisition and project master data
- This report will show the current status of the document when recall the report
- Print report as Excel format
Sample
| Report Details – 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 |
| 3 | Approve Status | Select SO Approve Status (E-Biz Status) | Can select as – Preload – Preapprove – All |
| 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 | Preload and Preapprove | 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 | Contract# | Project No. | Project No |
| 2 | Sale Type | Sale Type | Get Sale Type from Sale order |
| 3 | Approval Status | Sale Order Status | E-Biz Status in Sale order document |
| 4 | Project | Project Name | Project name from project Master (companyname) |
| 5 | Sales Name | Sale Representative | Sale1 from SO (salerep) |
| 6 | Supervisor Name | Supervisor Name of Sale Rep | Supervisor from employee master link with sale name |
| 7 | Customer | Customer name of project | Customer name from SO (entity) |
| 8 | SO Amount (Include VAT) | SO Total Amount | Total Amount from SO include VAT |
| 9 | Cost Amount (Include VAT) | Project Cost | Total Cost from project master (custentity_gable_rec_csh_totalcost) |
| 10 | Preload Amount (Include VAT) | Total PR Cost | Total PR cost relate to Project No |
| 11 | Approve Date | SO Approve date from E-Biz | Approve date from Sale order (custbody_tosw_date) |
| 12 | Age | Aging date from SO Date until date recall report | Datediff compare SO Date and as at date recall report |
| 13 | Remark | Remark for project | Remark in project master (comments) |
| Report Summary | |||
| # | Field | Description | NS field |
| 1 | Summary Project Amount | Summary Project Amount | Summary Project Amount |
| 2 | Summary Project Cost | Summary Project Cost | Summary Project Cost |
| 3 | Summary Pre-load Cost | Summary Pre-load Cost | Summary Pre-load Cost |
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 details for data from Sale order, Requisition, and project master data. This report will show the current status of the document when recalling the report.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Preload and Preapprove. Then upon clicking the “Preload and Preapprove”, 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, Approve Status as All, 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) |
| 3 | Approve Status | E-Biz Status(custbody_ebizstat) under integration subtab from SO – Preload – Preapprove – Approve – All |
| Backend Criteria for Report | ||
| 1 | Type | Sales Order & Requisition |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Preload and Preapprove | Fixed |
| 3 | As at | Based on as of date select in the criteria |
Please see the table below for the report columns for the detailed ‘Preload & Preapprove’ report.
| Report Column | ||
| # | Field | Field Name from NS records |
| 1 | Contract# | Project Order |
| 2 | Sale Type | Sale Type(class) from SO |
| 3 | Approval Status | E-Biz Status(custbody_ebizstat) under integration subtab from SO |
| 4 | Project | Project name from project Master (companyname) |
| 5 | Sales Name | Sales Rep from SO (salerep) |
| 6 | Supervisor Name | Supervisor(supervisor) from sales rep employee record |
| 7 | Customer | Customer name from SO (entity) |
| 8 | SO Amount (Include VAT) | Total(total) Amount from SO |
| 9 | Cost Amount (Include VAT) | Total Cost under cost sheet subtab from project record (custentity_gable_rec_csh_totalcost) |
| 10 | Preload Amount (Include VAT) | Estimated Total Amount from the related requisition record |
| 11 | Approve Date | Approve date under integration subtab from Sale order (custbody_tosw_date) |
| 12 | Age | Datediff compare SO Date and selected date in the filter |
| 13 | Remark | Project Note (Remark) field(comments) in the project master record related to the sales order |
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 displaying a summary of the Total Amount of all results in the report.
- In the status filter, it will display all values under the E-Biz Status field in the system as standard.
- When you select the report’s status filter, it will display a list of related sales order documents.
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.