Proposal summary
JCurve Solutions would like to create a new report to retrieve data for data from the AP Bill document. This report will show Current status of the document within the selected period based on the payment date. And print reports in Excel format.
Requirement
New report ‘รายงานแสดงรายละเอียดการจ่าย’ to retrieve data for data from the AP Bill document. This report will show the Current status of the document within the selected period based on the payment date. And print reports in Excel format.
Assumptions:
- This report will retrieve data for data from AP Bill document
- This report will show Current status of document within the selected period based on payment date
- Print report as Excel format
Sample:
Report Details – Criteria:
| Criteria: | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | Pay date from … to … | Select date range for pay date from … to | Input range date to recall |
| 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 | รายงานแสดงรายละเอียดการจ่าย | Report Name | Fixed |
| 3 | Pay date from … to… | Show date range for pay date from … to | Based on range date select in the criteria ดึงข้อมูล pay date มาจากไหน |
| Report Column: | |||
| # | Field | Description | Details |
| 1 | Document # | Vendor Bill no | Show No.If it has vendor billing note reference, show vendor billing note no elseshow vendor reference no.1. ผ่านการรับวางบิล => ใช้เลขที เอกสารใบรับวางบิล2. ไม่ผ่านการรับวางบิล => ใช้เลขที Finance Request* Currently we put Reference no. from E-Biz System in the reference field.Please see details from integration specification as below. |
| 2 | Vendor Name | Vendor Name | Vendor Name from AP Bill (Entity) |
| 3 | Amount | Bill Amount | Total Amount include VAT |
| 4 | WHT Amount | WHT amount need to deduct from AP Bill | WHT reference in AP Bill document (custbody_wht_amount) |
| 5 | Net Pay | Net Amount Paid | Total Amount. – WHT amount |
| 6 | Pay Date | Estimate payment date | Estimate payment date (custbody_estpaydate) ดึงข้อมูล pay date มาจากไหน |
| 7 | Pending Original Invoice | Original Invoice | Show if it is no check box in field REQUIRED DOCUMENT (เอกสารใบส่งสินค้า/ใบแจ้งหนี ) (custbody_requiredoc) |
| 8 | Pending Original Tax Invoice | Original Tax Invoice | Show if it is no check box in field REQUIRED DOCUMENT (ใบกํากับภาษี ) custbody_requiredoc_taxdoc |
| 9 | Pending Receipt voucher | Receipt voucher | Show if it is no check box in field REQUIRED DOCUMENT(ใบเสร็จรับเงิน) custbody_requiredoc_receipt |
| 10 | Pending Copy ID Card | Copy ID Card | Show if it is no check box in field REQUIRED DOCUMENT (สําเนาบัตรประชาชน) custbody_require_id |
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 AP Bill document. This report will show the current status of the document within the selected period based on the payment date. And print reports in Excel format.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> รายงานแสดงรายละเอียดการจ่าย. Then upon clicking the “รายงานแสดงรายละเอียดการจ่าย”, 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., LTDI, From date as 1st Jan of the current year and the 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.
| Filters/ Criteria tabs Shown on Report Page: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | Pay date from … to … | Date(trandate) from the bill payment record |
| Backend Criteria for Report: | ||
| 1 | Type | Bill |
| 2 | Applying Transaction Type | Bill Payment |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | รายงานแสดงรายละเอียดการจ่าย | Fixed |
| 3 | Pay date from … to… | Based on the range date selected in the criteria |
| Report Column: | ||
| # | Field | Field Name from NS records |
| 1 | Document # | Reference No(tranid) from Bill record or vendor reference no from VBN Vendor Billing Note List if ‘tranid’ is empty. |
| 2 | Vendor Name | Vendor Name from AP Bill (Entity) |
| 3 | Amount | Total(total) Amount include VAT from AP Bill |
| 4 | WHT Amount | WHT reference in AP Bill document (custbody_wht_amount) |
| 5 | Net Pay | Total Amount – WHT amount |
| 6 | Pay Date | Estimate payment date (custbody_estpaydate) ดึงข้อมูล pay date มาจากไหน |
| 7 | Pending Original Invoice | Will show ‘Yes’ or ‘No’ value if the field REQUIRED DOCUMENT (เอกสารใบส่งสินค้า/ใบแจ้งหนี ) (custbody_vbn_requiredoc) on the bill is checked or unchecked. |
| 8 | Pending Original Tax Invoice | Will show ‘Yes’ or ‘No’ value if the field REQUIRED DOCUMENT (ใบกํากับภาษี ) (custbody_vbn_requiredoc_taxdoc) on the bill is checked or unchecked. |
| 9 | Pending Receipt voucher | Will show ‘Yes’ or ‘No’ value if the field REQUIRED DOCUMENT(ใบเสร็จรับเงิน) (custbody_vbn_requiredoc_receipt) on the bill is checked or unchecked. |
| 10 | Pending Copy ID Card | Will show ‘Yes’ or ‘No’ value if the field REQUIRED DOCUMENT (สําเนาบัตรประชาชน) (custbody_vbn_require_id) on the bill is checked or unchecked. |
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 layout will be based on the Normal Search Layout in the UI.
- The date filter is based on the transaction date from the bill payment record.
- Pending Document columns will show ‘Yes’ or ‘No’ values if the fields REQUIRED DOCUMENT on the bill are checked or unchecked.
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.