Proposal summary
JCurve Solutions would like to create a new report to retrieve data for data from the AP Bill document Reference to Payment with status Paid which is related to PP36 and PND54. This report will show the current status of the document when recalling the report. And print reports in Excel format.
Requirement
New report ‘รายงานการยื่นแบบ ภงด. 54 และ ภพ. 36’ to retrieve data for data from the AP Bill document Reference to Payment with status Paid which has related to PP36 and PND54.
Assumptions:
- This report will retrieve data for data from AP Bill document Reference to Payment with status Paid which has relate to PP36 and PND54
- To show the details in report, it needs to group by Type of report first and then show details in report
- Report will be grouped by Vendor and Payment No.
- 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 Year | Select as of Year to filter transaction | Input as at Year to recall report |
| 3 | As of Month | Select as of Month to filter transaction | Input as at Month to recall report |
| 4 | Type of Report | Select type of report | Can select for: · All · Ordinary filling · Additional Filling Check Type of transaction from WHT CONDITION (THAI TAX) field |
| 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 | รายงานการยื่นแบบ ภงด.54 และ ภพ. 36 | Report Name | Fixed |
| 3 | ประจำเดือน … ปี … | Show Month and Year recall the report | Based on month and year select in the criteria |
| 4 | ประเภทภาษีหัก ณ ที่จ่าย | Select types of report | Type of Withholding tax report |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Page No | Show the Page no | Running no. based on transaction (page no / Total Page) |
| 2 | Supplier Name | Show Supplier Name | Vendor Name of Bill Payment |
| 3 | Payment Date | Show Date of Payment | Payment Date |
| 4 | Doc No | Vendor Bill No | Transaction No of AP Bill |
| 5 | PV No | Bill Payment No | Transaction No of Bill payment |
| 6 | PO No | PO Reference with AP Bill | Relate record of AP Bill link to PO / custbody_ref_pono in case of interface data |
| 7 | Contract | Reference Project No | cseg_project_order |
| 8 | ประเภทเงินได้ | WHT Description | WHT Description relate to WHT Code |
| 9 | อัตราภาษี | WHT Rate | WHT Rate |
| 10 | INV No | AP Reference No | AP Reference No |
| 11 | INV Amt (FC) | Amount (FC) | Amount (FC) |
| 12 | Tax Based Amt (FC) | Amount (FC) | Amount (FC) |
| 13 | Exc. Rate | Exchange rate | Exchange rate |
| 14 | INV Amt (THB) | Amount | Amount |
| 15 | WHT Tax (THB) | WHT Amount | WHT Amount /Exchange Rate |
| 16 | VAT (THB) | Tax Amount | Tax Amount |
| Report Summary | |||
| # | Field | Description | NS field |
| 1 | INV Amt (FC) | Sum Amount (FC) | Sum Amount (FC) |
| 2 | Tax Based Amt (FC) | Sum Amount (FC) | Sum Amount (FC) |
| 3 | INV Amt (THB) | Sum Amount | Sum Amount |
| 4 | WHT Tax (THB) | Sum WHT Amount | Sum WHT Amount |
| 5 | VAT (THB) | Sum Tax Amount | Sum Tax Amount |
| รวม | Summary Part : Group By Vendor Name and Payment Date |
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 Reference to Payment with status Paid which is related to PP36 and PND54.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> รายงานการยื่นแบบ ภงด. 54 และ ภพ. 36. Then upon clicking the “รายงานการยื่นแบบ ภงด. 54 และ ภพ. 36”, 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, Type of Report as All, As of Year as current Year, and the As of Month as the current month, 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 Year | Select as of Year to filter transaction |
| 3 | As of Month | Select as of Month to filter transaction |
| 4 | Type of Report | Select type of Report based on values from WHT FILING STATUS (THAI TAX) field on the transaction record. AllOrdinary FilingAdditional Filing 1Additional Filing 2 |
| Backend Criteria for Report: | ||
| 1 | Type | Bill |
| 2 | Status | Paid in Full |
| 3 | Related Transaction | Bill Payment |
| 4 | Tax Item | PP36 and PND54 |
| Report Details: | ||
| # | Field | Description |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | รายงานการยื่นแบบ ภงด.54 และ ภพ. 36 | Fixed |
| 3 | ประจำเดือน … ปี … | Show Month and Year in the filter of the report |
| 4 | ประเภทภาษีหัก ณ ที่จ่าย | Selected type of report based on the field WHT FILING STATUS (THAI TAX){custbody_rapid_tht_whtfilingstatus} will be shown in the header part of the report. |
| Report Column: | ||
| # | Field | Description |
| 1 | Page No | The (page no of Total Page) will be displayed on the top-right side of the page. |
| 2 | Supplier Name | Vendor’s Name(entity) from the bill payment transaction record |
| 3 | Payment Date | Date from related bill payment transaction record |
| 4 | Doc No | Document number from Vendor Bill |
| 5 | PV No | Document number(tranid) from related Bill Payment record |
| 6 | PO No | REF. PO NO(custbody_ref_pono) field under integration tab from Bill record |
| 7 | Contract | Project Order(cseg_project_order) from the reference Sales Orders on item line |
| 8 | ประเภทเงินได้ | WHT Description from related WH tax code record in Bill transaction |
| 9 | อัตราภาษี | WHT Rate from related WH tax code record in Bill transaction |
| 10 | INV No | Reference No(tranid) from Vendor Bill |
| 11 | INV Amt (FC) | The sum of Amount of line that bill line have WHT code reference |
| 12 | Tax Based Amt (FC) | The sum of WH Tax Based Amount of line that bill line have WHT code reference |
| 13 | Exc. Rate | Exchange rate(exchangerate) from the bill transaction record |
| 14 | INV Amt (THB) | The sum of the Amount of line that bill line have WHT code reference * Exc. Rate |
| 15 | WHT Tax (THB) | The sum of the WH Tax Amount of line that bill line have WHT code reference * Exc. Rate |
| 16 | VAT (THB) | The sum of Tax Amount * Exc. Rate |
| Report Summary: | ||
| # | Field | Description |
| 1 | INV Amt (FC) | Sum Amount (FC) |
| 2 | Tax Based Amt (FC) | Sum Amount (FC) |
| 3 | INV Amt (THB) | Sum Amount |
| 4 | WHT Tax (THB) | Sum WHT Amount |
| 5 | VAT (THB) | Sum Tax Amount |
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.
- The report layout will be based on the Normal Search Layout in the UI.
- In the Type of report filter, it will display all values under the Type of Report field in the system as standard.
- When you select the type of report filter, it will display a list of related bill documents.
- Assume that the tax items PP36 and PND54 are available in the production account.
- The As of month date filter will include dates up to the last date of the month selected in the filter.
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.
- The tax items PP36 and PND54 are currently not available in the Sandbox account.