Proposal For Journal Entry Import Customization

Proposal Summary 

This document outlines the tailored requirements for TB data upload customization to create Journal Entries. The aim is to seamlessly integrate trial balance data from various accounting systems used by client subsidiaries into NetSuite. The solution involves importing  Trial Balance files, enabling direct use without manual data transformation. The proposal encompasses the required components, processes, and customization aligned with client specifications. 

Requirement 

The Client has several subsidiaries that are currently using different systems for accounting. The requirement is to use the Trial Balance files downloaded from these systems and use them without changes directly to import TB data into NetSuite. The trial balance can be recorded in NetSuite through Journal Entry transactions. JE can be created either manually or through CSV import. For CSV import, data needs to be converted into NS CSV template as of now. This customization will enable users to use the TB files as it is without transforming the data into an NS template. 

Deliverable 

Through requirement analysis, it emerged that there are a total of 4 different Excel formats in which TB data can be downloaded from different legacy accounting systems. Users need to convert these files into CSV format without making any changes to the data except by adding a column at the end for Cost Centre (Department) and converting the Amount column to Number format. Users will be provided with designated sub-parent folders in the NS File Cabinet to upload the TB CSV files. Based on the file structure specific to each folder, data validation, error handling and upload will be executed using a script to create a TB Journal entry. 

File & File Cabinet Structure 

Each subsidiary group will have a dedicated folder structure, and TB CSV files will be uploaded into these folders while adhering to a standardized naming convention: 

{TB}_{subsidiary Name}_{name of existing Accounting System}_{MonthYYYY} 

For e.g., ‘TB_COG Aggregation Pty Ltd_Xero_July2023’. 

  • The abbreviation ‘TB’ will denote the file type. 
  • The subsidiary name should be similar in the system. 
  • The existing accounting system will be indicated to clarify the source:  
    • Xero  
    • MYOB  
    • ASC  
    • Quicken 
  • The month mentioned in the file name will signify the relevant TB Accounting period. 

The file cabinet structure will be as follows, 

  • Seven Primary Folders, each aligned with a specific subsidiary group. These folders will be named according to the parent subsidiary’s identity. 

Subfolder Structure within Primary Folders 

  • Within each primary folder, a subfolder framework will be established to facilitate a systematic approach to data processing: 

Subfolder 1: Processed Files 

  • Files that successfully pass data validation will be processed to create corresponding journal entries. 
  • Processed files will be seamlessly moved from the primary folder to this ‘Processed Files’ subfolder, ensuring clear progress tracking. 

Subfolder 2: Failed Files 

  •  If a common validation error occurs, then an error will be added once in the file. In cases where specific lines failed during the validation check, an ‘Error Reason’ column will detail the failure reasons on a per-line basis. The file will be seamlessly moved from the primary folder to this ‘Failed Files’ subfolder, ensuring clear progress tracking. 
  • If data validation fails for a single line, the JE will not be created and a designated user will promptly receive an email notification containing the failed file as an attachment, along with explicit failure reasons. 

User Guidelines for File Upload 

To maintain the integrity of data imports, users are advised to adhere to the following file upload guidelines: 

  • Upload TB files directly into the relevant subsidiary’s primary folder within the NetSuite file cabinet. 
  • Avoid uploading files directly into the subfolders dedicated to successfully processed or failed files. 
  • Ensure that the uploaded files are placed parallel to these subfolders, and aligned with the appropriate primary folder. 
  • This meticulously planned file and folder structure enhances data management efficiency, optimizes validation and processing procedures, and facilitates seamless communication between users and the system. 

Script Execution 

An automated script will be created to execute the seamless TB data uploading process within NetSuite. This script will operate on a scheduled basis, with two automated runs daily at 12 AM and 12 PM. Additionally, a manual execution option will be incorporated, allowing users to ‘Save and Execute’ scripts. This manual trigger provides a controlled method for processing available TB files as needed. 

The Following validations will be performed during the execution, 

  • If a Journal Entry with the same Subsidiary and Date combination already exists in NetSuite, marked with the Journal Type as ‘TB migration’, will not process the file further. Fail it as duplicate highlighting the reason: “TB Journal already exists for Subsidiary & Date combination.” 
  • In cases where a TB Journal with the Subsidiary & Date combination is not found within NetSuite, marked with the Journal Type as ‘TB migration’, the script will progress to subsequent processing steps. This ensures that only unique data is integrated, minimizing redundancy and enhancing the accuracy of the data within the system. 

Data Validations 

  • The script will rigorously evaluate the precision of file names against the required format. Should there be any deviations or spelling errors, an error will be triggered to prevent processing inconsistencies. 
  • The legacy accounting system’s name must be correctly mapped in the file name. 
  • For each file, the script will verify that the accounting period mentioned aligns with a valid period in NetSuite – excluding closed or future periods. Any mismatch will prompt an error to maintain accurate period selection. 
  • The script will cross-reference the availability of Mapping Accounting records with corresponding Legacy codes and subsidiary combinations. Should any discrepancies arise, an error will be triggered to uphold data consistency. 
  • The script will enforce a ‘Number’ format for all amount columns, requiring decimal number presentation (e.g., 100.25). Deviations from this format will trigger an error. 
  • All amounts are assumed to be in AUD. A currency symbol is not required in the TB file. 
  • The script will rigorously examine the balance between Debit and Credit amounts. If an imbalance occurs, an error will be triggered to maintain accurate financial data representation. 
  • The script will accept lines with 0.00 amounts, recognizing that NetSuite will automatically remove these lines. 

Journal Entry Record 

The Journal Entry record will be created only with the following fields. 

Body Fields: 

  • Subsidiary: Extracted directly from the file name. 
  • Date: The script will populate the Date field with the last date of the posting period. This posting period will be extracted from the file name itself. 
  • Currency: The default currency for all Journal Entries will be set as AUD. 
  • Reversal Date: The Reversal Date will be configured as the first day of the next posting period. 
  • Journal Type – The default value is ‘TB migration’. 

Line Fields: 

  • Account: Determined from the Mapping custom record based on the legacy account code and subsidiary. 
  • Amount: Debit/Credit sourced from the file. If available, the script will also consider Year to Date (YTD) debit or credit figures. 
  • Department: The Department field value is fetched from the file itself. If the value is not available, the default value ‘Cost Centre Missing’ will be used. 
  • Class: By default, the Class field will be populated with the value “-All-“. 
  • Location: Retrieved from the subsidiary record’s default location (Custom Field). 

Changes to Journal Entries: 

Should any modifications be required to a Journal Entry following its initial upload, users will follow a specific process. They will first need to delete the relevant Journal Entry record. Once the necessary changes have been made to the data, the updated file can be re-uploaded to ensure accurate representation within the system. 

By adhering to these updates and adjustments, the Journal Entry records will maintain accuracy, consistency, and seamless integration of TB data into the NetSuite environment. 

Error Handling 

  • In cases where specific lines were failed during the validation check, an ‘Error Reason’ column will detail the failure reasons on a per-line basis. And the file will be seamlessly moved from the primary folder to this ‘Failed Files’ subfolder. 
  • If data validation fails for entire file, A designated user will promptly receive an email notification containing the failed file as an attachment, along with explicit failure reasons 
  • The email will be sent according to the Parent subsidiary, and it will be directed to employees with specific assigned roles. 

Assumptions 

The proposed solution is based on the following assumptions: 

  • The solution is designed assuming that all files will follow the structure of the provided sample files. Any changes to the file format or structure could lead to failed data imports.  
  • All files are expected to be in CSV format. All lines and column details will remain unaltered, except for the addition of a Cost Centre (Department) column at the end 
  • Users are required to adhere to the correct folder structure for file uploads. This is essential to ensure a successful data import process. 
  • The solution assumes that the default class option “-All-” will be available within NetSuite. 
  • It is assumed that all required components, such as the Mapping custom record, Default location field in subsidiary records, the folder structure in the File cabinet and other necessary fields were available in the account. 
  • We can use the standard option of Journal entry with the Reversal date to achieve the reversal Journal. So, the reverse journal will be automatically created as per the NetSuite standard procedure with the reversal date. 

Risks 

  • Static File Structure Dependency: The script will work based on the initially shared sample format. If the file’s format changes, the process could break and cause errors. 
  • Netsuite account imposes a limit for Transaction lines. We assume that a single TB file will contain only lines that are permitted on JE as per the customer account. If a file has more lines, the script might not be able to create the Journal entry and it will be also conisered as a Failed entry 
  • Duplicate TB validation will be done only based on the file names and the available TB Journals. The Data within the file will not be validated to confirm the uniqueness 
  • According to NetSuite’s standard, we can only send emails to a maximum of 10 recipients. If there are more than 10 recipients, the script will still send only one email for each file, even if there are multiple recipients beyond that limit. 
  • File Cabinet restoration is not considered in this scope. 

Leave a comment

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