Proposal summary
JCurve Solutions would like to create new a report to retrieve data for data from Account balance based on the chart of accounts that are set to show in the report. And the Report will show the intercompany amount effect on each account in the separate column between AP and AR records based on the date selected in the filter. And need to print reports in Excel format.
Requirement
JCurve Solutions would like to create new a report to retrieve data for data from Account balance based on the chart of accounts that are set to show in the report. And the Report will show the intercompany amount effect on each account in the separate column between AP and AR records based on the date selected in the filter.
Assumptions:
- Report will show intercompany amount effect to each account but separate column between AP and AR record.
- For selected Subsidiary will represent transaction for AP (mean has purchase transaction from another subsidiary) and select another subsidiary which act as Vendor to show relate record as AR (mean selected subsidiary act as customer).
- This report will retrieve data from Account balance based on chart of account that set to show in report.
- This report show data relate to document for transaction that select as com conso = Y and has data in field com conso short name.
- Need to have field to reference com conso code (com conso short name) to summary transaction in report.
- Print report as excel format.
Sample Report:
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report and will checktransaction record for AP side (Purchasefrom another company and record transaction inAP transaction) | 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 | Related Company AR | Select Another Subsidiary for report and willcheck transaction record as AR side (Act asVendor of selected Subsidiary and recordtransaction for Subsidiary as AR transaction) | name of Subsidiary |
| Report Details | |||
| # | Field | Description | Details |
| 1 | Subsidiary Name | Show Subsidiary name that select for report | name of Subsidiary |
| 2 | Intercompany transaction forconsolidation | Report Name | Fixed |
| 3 | As at Month… Year… | Show Month and Year recall the report | Based on month and year select in the criteria |
| 4 | Related Company AR | Show Another Subsidiary name that select tocheck for AR side (Act as Vendor ofselected Subsidiary) | List of subsidiaries |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Related Company AR | Company selects AR | Vendor Name link with Subsidiary thatselect in criteria as Related Company AR |
| 2 | Contract No Subsidiary AP | Project No from Subsidiary relate to transaction | Project Code of selected subsidiary ex. Project code in transaction |
| 3 | Sale Type AP | Sale Type from contract No. of Subsidiary | Sale Type relate to Project order no (reference No,2) |
| 4 | Contract No Related Company AR | Project No from Related Company AR relate totransaction | Project Code in Related Company AR which PO# in Sale order =Purchase order No in Subsidiary |
| 5 | PO No | PO No from transaction of Subsidiary | Purchase order no that Subsidiary issue for Vendor Name link withSubsidiary master from subsidiary select in criteria as RelatedCompany AR (link with the Contract no of Subsidiary in No. 2) |
| 6 | Sale Type AR | Sale Type from contract Company AR | Sale Type relate to Project order no (reference No,4) |
| 7 | OB | Separate as 3 OB line based on item categoryOB1OB2OB3 | Group amount by Item category from transaction record. Informationas below only account start with 1 – 3, amount will put in OB1 else willgroup based on item category on transaction |
| Group for AP | |||
| 8 | Number | Account Code | Accounts Code relate to transaction for Purchase that has GL impactfrom Subsidiary selected which reference to Contract no. of Subsidiaryand comso = Y and Vendor from transaction. =. Vendor Reference inRelated Company AR |
| 9 | Name | Account Name | Accounts Name relate to transaction for Purchase that has GL impactfrom Subsidiary selected which reference to Contract no. Subsidiaryand comso = Y and Vendor from transaction. =. Vendor Reference inRelated Company AR |
| Group for AR | |||
| 10 | Number | Account Code | Accounts Code relate to transaction for Sale that has GL impact fromSubsidiary select in Related Company AR which reference to Contractno. from Related Company AR subsidiary and Customer fromtransaction. =. Customer Reference in Subsidiary |
| 11 | Name | Account Name | Accounts Name relate to transaction for Sale that has GL impact fromSubsidiary select in Related Company AR which reference to Contractno. from Related Company AR subsidiary and Customer fromtransaction. =. Customer Reference in Subsidiary |
| 12 | Diff AP – AR | Show different amount between AP and AR | Formula calculates different AP and AR per line level |
| Report Row | |||
| # | Field | Description | Details |
| Group for AP | |||
| 1 | Amount for each column | Summary amount based on each column for AP Side | Summary Debit – Credit based on account per line and group bycolumn from Subsidiary selected which reference to Contract no.Subsidiary and com conso = Y and Vendor from transaction. =. VendorReference in Related Company AR |
| Group for AR | |||
| 1 | Amount for each column | Summary amount based on each column for ARside | Summary Debit – Credit based on account per line and group bycolumn from Related Company AR selected which reference toContract no. Related Company AR and com conso = Y and Customerfrom transaction. =. Customer Reference in Subsidiary |
Our Solution
This requirement can be achieved by creating a new report using a transaction saved search. And this search will retrieve data from all transactions based on chart of account that set to show in report. The data shown in the report is related to reference customer/vendor and subsidiary of all transaction records.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Intercompany transaction for consolidation. Then upon clicking the “Intercompany transaction for consolidation”, 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, As of Month as Current Month and the As of Year as Current Year, Related Company AR as FIRST LOGIC CO., LTD and then show a sum of the all account column 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 Month | Month consider from Date(trandate) on transactions. |
| 3 | As of Year | Year consider from Date(trandate) on transactions. |
| 4 | Related Company AR | Subsidiary(subsidiary) list |
| Backend Criteria for Report | ||
| 1 | Com Conso | True |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Intercompany transaction forconsolidation | Fixed |
| 3 | AS at Month … Year … | Based on the date(trandate) of transactions |
| 4 | Related Company AR | Subsidiary(subsidiary) name selected in filter |
Please see the table below for the report column details for the detailed ‘R Intercompany transaction for consolidation’ report.
| Report Column | ||
| # | Field | Field Name from NS records |
| 1 | Related Company AR | Reference Vendor Name(custrecord_ref_vendor_comconso) link with Subsidiary that select in criteria as Related Company AR |
| 2 | Contract No Subsidiary AP | Project Order(cseg_project_order) from PO transactions filtered under selected in the Subsidiary filter |
| 3 | Sale Type AP | Sale Type(custentity_saletype) from the PO related project record |
| 4 | Contract No Related Company AR | Project Code from SO transaction in the selected Related Company AR filter. |
| 5 | PO No | Purchase order No of AP transaction in the selected Subsidiary filter. Note: Only the details from PO and SO transactions are shown in the report. If a PO has multiple SOs, the PO details will be displayed in the first line with the first SO, and the remaining SO details will be displayed in the following lines without the PO details being repeated. |
| 6 | Sale Type AR | Sale Type relate to Project order no in Related Company AR |
| 7 | OB | Shows the Separate rows for 3 OB line based on item category of items in the filtered transactions. OB1 OB2 OB3 Note: We only consider the mentioned 3 OB lines based on the G-Able obligation values of item categories. If any accounts from all transactions have a GL impact but do not have the items with mentioned 3 OB lines, those account’s total will be reported in the first line with the ‘Sales Type’ or ‘None’ value in the same row. |
| Group for AP | Fixed | |
| 8 | Number | Account Number from the GL impact of AP transactions. In the transaction list includes the all transactions in subsidiary with vendor name = Reference vendor name in the Related Company AR record. Note: The AP account columns in Excel are static. If no transactions have been generated for any accounts, the data will be displayed as zero/empty. And, if a new account is created in the system in future, the script must be updated to include the new account column in the excel file. |
| 9 | AP Total | Sum of all AP transactions in subsidiary with vendor name = Reference vendor name in the Related Company AR record. |
| Group for AR | Fixed | |
| 10 | Number | Account Number from the GL impact of AR transactions. In the transaction list includes the all transactions in Related Company AR with vendor name = Reference customer name in the subsidiary record. Note: The AR account columns in Excel are static. If no transactions have been generated for any accounts, the data will be displayed as zero/empty. And, if a new account is created in the system in future, the script must be updated to include the new account column in the excel file. |
| 11 | AR Total | Sum of all AR transactions in Related Company AR with vendor name = Reference customer name in the subsidiary record. |
| 12 | Diff AP – AR | Formula calculates different AP Total and AR Total per line level. |
| Report Row | ||
| # | Field | Description |
| Group for AP | ||
| 1 | Amount for each column in AP section | The amount automatically displays the –ve and +ve values based on the GL impact of transactions AP. As a result, the amount will appear in the appropriate account column with the following condition:Com Conso = YVendor from transaction in Subsidiary = Vendor Reference in Related Company ARThe total amount will be displayed in the last row. |
| Group for AR | ||
| 1 | Amount for each column in AR section | The amount automatically displays the –ve and +ve values based on the GL impact of transactions AR. As a result, the amount will appear in the appropriate account column with the following condition:Com Conso = YCustomer from transaction in Related Company AR = Customer Reference in SubsidiaryThe total amount will be displayed in the last row. |
Finally, on the Suitelet page, an Export button, Subsidiary, Related Company AR, As of Month, and As of Year filters 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 split 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 of 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 split 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 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 can be created based the reference searches JJ Intercompany Transaction JS-526 V1 ,JJ Intercompany Transaction JS-526 V2 and Account Search.
- In this report, we consider all transactions that have a GL impact. The amount automatically displays the –ve and +ve values based on the GL impact of all transactions. As a result, the amount will appear in the appropriate account column with the following condition:
- Com Conso = Y
- Vendor from the transaction in Subsidiary = Vendor Reference in Related Company AR/ Customer from the transaction in Related Company AR = Customer Reference in Subsidiary
- The total amount of respective columns will be displayed in the last row.
- Only the details from PO and SO transactions are shown in the report. If a PO has multiple SOs, the PO details will be displayed in the first line with the first SO, and the remaining SO details will be displayed in the following lines without the PO details being repeated.
- Will only consider the mentioned 3 OB lines based on the G-Able obligation values of item categories. If any accounts from all transactions have a GL impact but do not have the items with mentioned 3 OB lines, those account’s total will be reported in the first line with the ‘Sales Type’ or ‘None’ value in the same row.
Risks
- No UI view for the report results on the Suitelet page. Only the option of exporting the report in excel format based on the filters selected in the suitelet page will be available.
- Only the details from PO and SO transactions are shown in the report.
- If one PO has multiple SOs, the PO details will be displayed in the first line with the first SO, and the remaining SO details will be displayed in the following lines without the PO details being repeated.
- Will only consider the mentioned 3 OB lines based on the G-Able obligation values of item categories. If any accounts from all transactions have a GL impact but do not have the items with mentioned 3 OB lines, those account’s total will be reported in the first line with the ‘Sales Type’ or ‘None’ value in the same row.
- The amount automatically displays the –ve and +ve values based on the GL impact of all transactions.
- The AP/ AR account columns in Excel are static. If no transactions have been generated for any accounts, the data will be displayed as zero/empty. And, if a new account is created in the system in future, the script must be updated to include the new account column in the excel file.
- 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.
- There will be another risk of combining 3 searches based on the grouping of all item lines, transactions, and GL impact accounts. Because there will be a chance to reach the script execution limit/time limit. So the script may end up with an error without providing the result. The occurrence of this scenario can be confirmed only at the time of development. So we couldn’t guarantee the precision of the report.