Proposal summary
JCurve Solutions would like to create a new report to retrieve data from Journal vouchers with journal voucher date in the selected date range. And print reports in PDF format.
Requirement
New report ‘รายงานขอคืน WHT’ to retrieve data from Journal voucher and select Journal voucher type = Bank charge with journal voucher date in the selected date range. And print reports in PDF format.
Assumptions:
- This report will retrieve data from Journal voucher and select Journal voucher type = Bank charge with journal voucher date in the selected date range
- Print report as PDF format
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | Bank account | Select Bank account based on subsidiary | Bank account filter by subsidiary |
| 3 | Date from … to | Select Date to filter transaction | Input Date range to recall report compare with Journal voucher date |
| 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 | รายงานการขอคืน WHT | Report Name | Fixed |
| 3 | ธนาคาร xxxx | Bank account | Based on Bank account select in the criteria |
| 4 | Date from … To … | Show period date | Based on the range date select in the criteria |
| 5 | Page | Show Page no with total Page | |
| Report Column | |||
| # | Field | Description | Details |
| 1 | วันท | Date | WHT Deduct Date in Journal line reference with WHT deduct account = ภาษีเงินได้ถูกหัก ณ ที จ่าย (custcol_gable_whtdeduct_date) |
| 2 | เลขที สําคัญจ่าย | Document No | Journal voucher no |
| 3 | เลขที เอกสาร | Bank Document | Bank document reference in Journal line reference with WHT deduct account = ภาษีเงินได้ถูกหัก ณ ที จ่าย (custcol_gable_bank_doc) |
| 4 | เลขที สัญญา | Project | Project reference in Journal voucher |
| 5 | จํานวนเงิน | Bank Charge Amount | Bank amount reference in Journal line reference with WHT deduct account = ภาษีเงินได ถูกหัก ณ ที จ่าย (custcol_gable_bank_charge ) |
| 6 | ภาษี ห ก ั ณ ที จ่าย | Amount | Amount in Journal line reference with WHT deduct account = ภาษีเงินได ถูกหัก ณ ที จ่าย |
| 7 | ประเภทค่าใช้จ่าย | WHT Deduct type | WHT Deduct type reference in Journal line reference with WHT deduct account = ภาษีเงินได้ถูกหัก ณ ที จ่าย (custcol_gable_wht_deduct_type ) |
| Report Summary | |||
| # | Field | Description | NS field |
| 1 | Summary | Summary | Sum Amount total |
| 2 | ลงชือผูร ้บ ั เอกสาร __________________ (ตัวบรรจง) | Name of per person sign in document | Fixed wording |
| 3 | วันท | Date | Fixed wording |
Our Solution
This requirement can be achieved by creating a new report using a transaction saved search. And this search will retrieve data from the Journal voucher and select the Bank account with the Journal voucher date in the selected date range filter. And print reports in PDF format.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> รายงานขอคืน WHT. Then upon clicking the “รายงานขอคืน WHT”, 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, Bank account as 1101-0201 เงินฝากออมทรัพย์ CITIBANK A/C 555-0482-012, From Date as First of current Year, and To 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: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | Bank account | Select Bank account based on subsidiary |
| 3 | Date from … to | Select Date to filter journal transactions |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | รายงานการขอคืน WHT | Report Name, fixed |
| 3 | ธนาคาร xxxx | Bank Account Name selected in the list |
| 4 | Date from … To … | Select date(transdate) in the From & To filter |
| 5 | Page | Show Page no with total Page on the header of the PDF |
| Report Column: | ||
| # | Field | Field Name from NS records |
| 1 | วันท | Date from journal record |
| 2 | เลขที สําคัญจ่าย | Document No from journal record |
| 3 | เลขที เอกสาร | Bank Document(WHT Deduct) line from journal record |
| 4 | เลขที สัญญา | Project full name from journal record |
| 5 | จํานวนเงิน | Bank Charge Amount(WHT Deduct) line from journal record |
| 6 | ภาษี ห ก ั ณ ที จ่าย | Amount line from journal record |
| 7 | ประเภทค่าใช้จ่าย | WHT Deduct type(WHT Deduct) line from journal record |
| Report Summary: | ||
| # | Field | Description |
| 1 | Summary | Sum of Amount |
| 2 | ลงชือผูร ้บ ั เอกสาร __________________ (ตัวบรรจง) | Add a Fixed word on PDF after summary line(Name of per person sign in the document) |
| 3 | วันที ________________ | Add a Fixed word on PDF after Name of per person sign line |
Finally, on the report page, an Export button will be added to download the detailed report in PDF format.
PDF Formatting Details:
- The A4 page size will be used in the PDF file.
- The Landscape orientation will be included in the file format.
- The content will be printed in Angsana font.
- The Report Details, Columns, Print By as Employee ID of the exported user, Print Date as export Date, and Page No values will be provided in the Header on all pages.
- The footer of PDF pages will be empty.
- Based on the content size, the report’s results will be shown on the following pages.
- The Amount Summary will display immediately after the search results.
- And the Signature and Date will be printed immediately after the total summary.
When you click the export button, the report will be downloaded in PDF 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 PDF 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 PDF 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 Bank Account filter, it will display all values under the Bank Account field in the system as standard.
- When you select the Bank Account filter, it will display a list of related journal 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.