Proposal summary
JCurve Solutions would like to create new a report to retrieve data for data from the Invoice, Credit Note document, and link some details from the sale order. The report can recall backdate and need to show documents based on as at date selected. And print reports in Excel format.
Requirement
New report ‘Contract Un-Invoice’ to retrieve data for data from the Invoice, Credit Note document, and link some details from the sale order.
Assumptions:
- This report will retrieve data for data from the Invoice, Credit Note document and link some details from the sale order
- As at date of report will effect to all documents that will show in this report (Sale order date, invoice date and credit note date)
- Print report as Excel format
Sample Report:
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 | Contract Un-Invoice Report | 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 | Sale Type from SO |
| 3 | Customer Name | Customer Name | Customer in Invoice screen and credit Note screen |
| 4 | End-User Name | End customer of this project | End Customer from SO (custbody_end_custome) |
| 5 | Project Name | Project Name | Project Full Name from project Master(custentity_gable_rec_csh_fullname) |
| 6 | Sale Name | Sales Representative | Sale1 from SO (salerep) |
| 7 | PM Name | Project Manager | PM from SO (custbody_pm) ระบบ SO มีส่งข้อมูลนี ไปให้ใช่หรือไม |
| 8 | POC | POC % | % of completion from project master where Accounting period = month as of date in selection criteriaproject master ส่งข้อมูลเข้า NS ตอนไหนใน project master มีการ update %POC อย่างไร |
| 9 | SO Amount (Include VAT) | SO Total Amount | Total Amount from SO include vat |
| 10 | Invoice Amount (Include VAT) | Invoice Total Amount | Total Invoice Relate to SO include vat based on as at the date of report |
| 11 | Uninvoice Amount (Include VAT) | Different between SO and Invoice | Different amount between SO and Invoice based on as at date of report |
| 12 | Aging Authorization apply | SO Date compare to as of date recall report | Date diff from SO and as at date recall report |
| 13 | Remark | Remark for project | Remark in project master (comments) |
| Report Summary: | |||
| # | Field | Description | NS field |
| 1 | Summary SO amount | Summary SO amount | Sum SO Amt |
| 2 | Summary Invoice amount | Summary Invoice amount | Sum Inv Amt |
| 3 | Summary Un Inv Amt | Summary Un Inv Amt | Sum Un Inv Amt |
| 4 | Summary Plan Amt | Summary Plan Amt | Sum Plan Amt |
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 the Invoice, Credit Note document, and link some details from the sale order.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Contract Un-Invoice. Then upon clicking the “Contract Un-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 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.
| Criteria/ Available Filter: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | As of Date | Date(trandate) 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 | Contract Un-Invoice Report | Fixed |
| 3 | As at | Based on as of date selected in the filter |
Please see the table below for the report column details for the detailed ‘Contract Un-Invoice’ report.
| Report Column: | ||
| # | Field | Field Name from NS records |
| 1 | Contract# | Job Id(entityid) from the related project record |
| 2 | Sale Type | Sale Type(class) from the related Sales Order record |
| 3 | Customer Name | Customer Name(entity) from the related Sales Order record |
| 4 | End-User Name | End Customer(custbody_end_customer) from the related Sales Order record |
| 5 | Project Name | Project Full Name(custentity_gable_rec_csh_fullname) from the related project record |
| 6 | Sale Name | Sale1(salesrep) from the related Sales Order record |
| 7 | PM Name | PM(custbody_pm) from SO |
| 8 | POC | Percent complete override values under Financial subtab from project master where Accounting period = month as of a date in selection criteria [Invoice Search + Project Search = Common field is project id] |
| 9 | SO Amount (Include VAT) | Total Amount(total) from the related sales order record – The amount should show based on the Base Currency. |
| 10 | Invoice Amount (Include VAT) | The total(total) amount from the invoice record. The amount should show based on the Base Currency. |
| 11 | Uninvoice Amount (Include VAT) | Total Amount from the related sales order record – The total amount from the invoice record. The amount should show based on the Base Currency. |
| 12 | Aging Authorization apply | Date diff from Sales Order and as at date recall report |
| 13 | Remark | Remark(comments) from related project master record |
| Report Summary: | ||
| # | Field | NS Field |
| 1 | Summary SO amount | Sum SO Amt |
| 2 | Summary Invoice amount | Sum Inv Amt |
| 3 | Summary Un Inv Amt | Sum Un Inv Amt |
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. 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.
- 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, 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.
- 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 the 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.
- As of now, currency of all subsidiary is THB. So all the amount will be in THB. If a new subsidiary is created with a different currency in the future, the amount will be displayed in the subsidiary’s base currency and there might be mismatch on the summary.
- The report does not include the ‘Summary Plan Amt’ since there is no plan amount column in the report columns.
- The report on the UI will be displayed as normal Netsite page without any styles. It will follow the standard behaviour