Proposal For Intercompany transaction for Consolidation Report V2

Proposal summary 

JCurve Solutions would like to create new 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 IF, Invoice, Credit Memo, IR, Bill, Bill Credit, Journal and Rec – Cost Period records. And need to print reports in Excel format. 

Requirement 

JCurve Solutions would like to create a new report to retrieve 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 IF, Invoice, Credit Memo, IR, Bill, Bill Credit, Journal, Rec – Cost Period records and date selected in the filter. 

Assumptions: 

  • Report will show intercompany amount effect to each account but separate column between AP and AR record as per the GL impact of IF, Invoice, Credit Memo, IR, Bill, Bill Credit, Journal, Rec – Cost Period transactions. 
  • This report will retrieve data from Account balance based on chart of account that set to show in report. 
  • 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 Related Company AR  Select Another Subsidiary for report and will check transaction record as AR side (Act as Vendor of selected Subsidiary and record transaction 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 for consolidation 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 to check for AR side (Act as Vendor of selected Subsidiary) List of subsidiaries 
Report Column 
# Field Description Details 
1 Related Company AR Company selects AR Vendor Name link with Subsidiary that select 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 to transaction 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 with Subsidiary master from subsidiary select in criteria as Related Company 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 category OB1 OB2 OB3 Group amount by Item category from transaction record. Information as below only account start with 1 – 3, amount will put in OB1 else will group based on item category on transaction  
 Group for AP   
8 Number Account Code Accounts Code relate to transaction for Purchase that has GL impact from Subsidiary selected which reference to Contract no. of Subsidiary and Vendor from transaction. =. Vendor Reference in Related Company AR 
9 Name Account Name Accounts Name relate to transaction for Purchase that has GL impact from Subsidiary selected which reference to Contract no. Subsidiary and Vendor from transaction. =. Vendor Reference in Related Company AR 
 Group for AR   
10 Number Account Code Accounts Code relate to transaction for Sale that has GL impact from Subsidiary select in Related Company AR which reference to Contract no. from Related Company AR subsidiary and Customer from transaction. =. Customer Reference in Subsidiary 
11 Name Account Name Accounts Name relate to transaction for Sale that has GL impact from Subsidiary select in Related Company AR which reference to Contract no. from Related Company AR subsidiary and Customer from transaction. =. 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 by column from Subsidiary selected which reference to Contract no. Subsidiary and Vendor from transaction. =. Vendor Reference in Related Company AR 
 Group for AR   
1 Amount for each column Summary amount based on each column for AR side Summary Debit – Credit based on account per line and group by column from Related Company AR selected which reference to Contract no. Related Company AR and com conso = Y and Customer from 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. 

First, we will try to add the GL impact of IF, Invoice, Credit Memo, IR, Bill, Bill Credit, Payment, Bill Payment transactions to the existing intercompany report based on the PO# value of PO and SO transactions. 

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 V2. Then upon clicking the “Intercompany transaction for consolidation V2”, the user will be redirected to a report page. 

For other transactions (Ex: Inventory Adj, Journals, etc.,) and Rec-Cost Period records, we need to create a new intercompany version 2 report. And the filter will be applied depending on the subsidiary, item category, and com conso short name in the GL impacts of transactions and Rec-Cost Period record values. Will consider the sales order, purchase order, and journal’s GL accounts that are related to the Rec-Cost Period record. 

The report will first display a detailed report based on the default filters, such as the subsidiary as G-ABLE PUBLIC COMPANY LIMITED, As of Month as Current Month, As of Year as  Current Year, Related Subsidiary 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 Subsidiary Subsidiary(subsidiary) list 
Report Details: 
# Field Field Name from NS records 
1 Company Name Subsidiary(subsidiary) name selected in filter 
2 Intercompany transaction for Consolidation V2 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 ‘Intercompany transaction for consolidation V2’ report based on the Rec – Cost Period record. 

Report Column 
# Field Field Name from NS records 
1 Company AR Reference Vendor Name(custrecord_ref_vendor_comconso) link with Related Subsidiary that select in criteria for AR side records. 
2 Contract No AP Project Order(cseg_project_order) from the PO transaction is given in the IR, Bill, Bill Credit, Bill Payments, Vendor Returns, IF and Doc No. Field on Rec – Cost Period record 
3 Sale Type AP Sale Type(custentity_saletype) from the PO transaction is given in the IR, Bill, Bill Credit, Bill Payments, Vendor Returns, IF and Doc No. Field on Rec – Cost Period record 
4 Contract No Related Company AR Project Code from sales order is given in the Reference SO No field on Rec – Cost Period, IF, Invoice, Credit Memo, Payment, Return Authorization, IR records 
5 PO No Purchase order No is given in the IR, Bill, Bill Credit, Bill Payments, Vendor Returns, IF and Doc No. Field on Rec – Cost Period record 
6 Sale Type AR Sale Type(custentity_saletype) from the PO transaction is given in the IF, Invoice, Credit Memo, Payment, Return Authorization, IR and Reference SO No. Field on Rec – Cost Period record 
7 OB Shows the Separate rows for 3 OB line based on item category of items in the filtered transactions. OB1 
OB2 
OB3 
None 
 
Note: 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 ‘None’ value in the same row. 
 Group for AP Fixed 
8 Number Account Number from the GL impact of AP and Journal transactions.  
 Group for AR Fixed 
9 Number Account Number from the GL impact of AR and journal transactions. 
10 Diff AP – AR Formula calculates different AP Total and AR Total per line level. 
Summary 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 and Journal.  The 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 and Journal.  The total amount will be displayed in the last row. 

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: 

  1. 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.  
  1. 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 
  1. 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.  
  1. 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. 
  • First, we will add the GL impact of IF, Invoice, Credit Memo, Payment, Returns, IR, Bill, Bill Credit, Bill Payments, Vendor Returns transactions to the existing intercompany report. 
  • If we consider the IF transactions from the vendor returns, we should include those accounts in the AP part. Also, if we take the IF transactions from the sales orders, we will include the same accounts in the AR part. And the same method will be applied to IR transactions(Return Authorizations). 
  • Will take the Sale Type directly from the SO and PO records. 
  • In terms of journal transactions, we will add the accounts and amounts to the AP and AR sides based on the subsidiary, item category and com conso short name (customer and vendor) values. 
  • For Inventory Adjustment and Journal(Other Transactions…) records, we will get the Subsidiary, Item Category and Com conso short name values from the GL impact of each transaction. Based on these values, we will input the account and amounts into the respective AP and AR sides. 
  • If the com conso short name field is empty, we will exclude those transactions from the report. 
  • To avoid the duplicate in Journal, the Journal that create link to the Rec –  Cost period will stamp value in this field. So, we will avoid the duplicate data using the custom field in the journal record.  
  • The report can be included for IF, Invoice, Credit Memo, Payment, Returns, IR, Bill, Bill Credit, Bill Payments, Vendor Returns transaction’s GL impact on the reference searches: 
  • For Journals and Inventory Adjustment records, we will create new searches. 
  • For Rec-Cost Period records, we will consider the sales order, purchase order, and journal’s GL accounts that are related to the Rec-Cost Period record. And will consider the date filter as journal’s date for filtering the custom record details. 
    Reference searches 
  • 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. The total amount of respective columns will be displayed in the last row. 
  • OB rows: 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 last line with the ‘None’ value in the same row. 
  • We are now planning to update the existing intercompany report (Version 1) by including all IF, Inv, Credit memo, Payment, IR, Bill, Bill Credit, and Bill payment transactions if the related SO and PO have a relationship. 
  • The AP and AR accounts will be categorized based on the Subsidiary, Item Category, Com Conso Short Name value, and transaction types. In terms of returns, there is a possibility of repeating the same accounts on the AP and AR sides. 
  • All accounts in the transactions will be added as columns to the intercompany report as a static. 

Risks 

  • On the UI, the user will have only the option to choose the filters and submit button for generating the report. The report details will be downloaded as an excel file. The report details will not be displayed on UI 
  • 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. 
  • First, we will add the GL impact of IF, Invoice, Credit Memo, Payment, Returns, IR, Bill, Bill Credit, Bill Payments, Vendor Returns transactions to the existing intercompany report. 
  • If the existing intercompany V1 report update is not possible, then we will combine all new saved searches for IF, Invoice, Credit Memo, Payment, Returns, IR, Bill, Bill Credit, Bill Payments, Vendor Returns, Journal, and Rec-Cost period records into new suitelet page. 
  • Not sure how this will work in the suitelet page when all of the conditions are taken into account. As a result, we will not guarantee for the accuracy of the report results. 

Leave a comment

Your email address will not be published. Required fields are marked *