Proposal Summary
This proposal is to generate the budget report with updating amount in the budget report with following condition such as;
- If the accounting period is closed we need to fetch amount value from the report
- If the accounting period is open, we need to fetch budget amount from the report
Description
Convert Budget Vs Actual custom report into a budget import csv file.
The columns in the custom report are Accounts, Actual and budget amounts in each account per month.
So, each month will have two columns in the custom report- one for budget amount and one for actual amount. Please refer the image below;
Client required to rearrange the data in the custom report into a budget import csv file.
Budget import template is attached below;
The budget import csv file should have only one value for each month.
- The actual amount should be shown under the corresponding accounting period column(Month column) in the import csv file if the corresponding accounting period is closed.
- The budget amount should be shown under the corresponding accounting period column(Month column) in the import csv file if the corresponding accounting period is open.
This budget import csv file is required for the purpose of updating the amounts in the budget which is already being created.
Solution
We will create a custom record in Netsuite called “Budget Vs Actual report into a budget import“. This custom record will have the following fileds.
- Budget Vs Actual custom report “ → This field is for adding the initial file that needs to be processed
- Status → This will show the file conversion status like Todo, processing and completed.
- Generated budget file→ Field to store the new file that is created
Following, describes the steps required to create the budget file as per the requirement
1.As a first step, user have to create a custom record entry in the Netsuite using the custome record “Budget Vs Actual report into a budget import“. In that entry, user need to choose the file that needs to be processed (“Budget Vs Actual custom report”) and saves the custom record entry.
2.A script will run in background (can scheduled in specified intervels like mothly, weekly etc) and it will fetch the custom record entries for processing. Once it completed the processing the Generated budget file will be attched to the corresponding custom reord entry. And an email notification will also sent to the user once the file is generated.
Note: Real time generation of the file can have limitation based on the size of the data to be processed. That is the reason generating the file on a scheduled basis.