Data Migration for Project Accounting in Sage 500 ERP (MAS 500)

Implementing a powerful, user friendly and flexible ERP system is a vital task for a modern company. It will affect the operation, business processes, management and even sales & marketing. During implementation, data migration, customization and integration are three barriers for all ERP teams. Therefore, selecting the right system and right consultant Is crucial.
Mantralogix Inc. is an experienced Sage partner in implementing a full range of Sage ERP and Sage CRM products. It has proven knowledge and skills in training, deployment, data migration, and customization/integration. In this blog, we will explore the data migration for Project Accounting in Sage  500 ERP (MAS 500), a powerful ERP system.
 

Preparation

Project accounting in Sage 500 ERP is an additional module, compared with other modules, such as AR, AP, GL, SO, or PO. But it’s important for some consulting companies which need to control budgets and funds, and employee payments. This module has lots of features and some data may not be easily mapped from legacy system to Sage 500. Thus, understanding the required data is the first step for data migration for project accounting in Sage 500.
Two types of data will be migrated into Sage 500. The first type is the project data and the second one is the invoices for the projects.
The following table shows the relevant entities in Sage 500.

The following table shows the description of required data before migration.

Tips for data processing

1. Tools for migration
   • Sage 500 provides three additional modules for Sage consultants to implement the data migration;  Data Migrator, Data Porter, and Data Import Manager.
   • Data Portal is a nice tool for importing simple data into Sage 500. For example, there is an Icon for Data Portal at the menu bar of the form of Maintain Tasks. By clicking the icon, integrated Excel will pop up and it has the import/export add-on functions.
   • Data Import Manager is a useful module for importing complicated data, such as importing Invoice data.
   • Data Migrator is a function for migrating data from Sage accounting systems to Sage 500.
     
     
2. Customized fields
   • Data Portal also supports some customized fields, which may be in migration data. By defining the data portal metadata, those customized fields can be mapped for migration.
     
     
3. Process of data migration
   • The required data before migrating the project data and invoice data has to be maintained and imported in Sage 500. Most of the required data can be imported using Data Portal, such as Tasks. But others will use the import utility function to import the data. For example, customer data can be imported by using the AR import utility function in the AR module.
   • After the required data is imported, the project data can be imported including Master Project, Project and Estimate line.
   • The last step is to import the invoice data. There is an important process for this importing. The invoice line has to be linked to the project estimate line. Therefore, an additional step has to be done after the invoice importing.
     
     
4. Estimate Line migration
   • The resource data may not have the data of the project detail lines. Therefore, it’s required to extract the project estimate lines for existing invoice lines. The classification of the estimate lines can be based on phase, cost classification, vendor (or resource), task, item, unit price, unit cost, and invoice description.
   • One additional consideration are  adjustments. It could be seen as the credit memo during invoice importing. In the estimate line, the adjustment lines have to be added into the project estimate line if existing. Otherwise, the invoice lines cannot be linked to the project estimate lines during invoice importing.
   • During the migration, it only imports estimate lines for one project at one time using Data portal. But there is a way to write a store procedure to import them into Sage 500 in batch.
     
     
5. Invoice description
   • Invoice description is special data for defining the descriptions of the invoice lines. It can be printed as description for each invoice line in the invoice report.
   • The invoice descriptions for the project estimate line and invoice lines can be different. But the linked field between the invoice line and estimate line has to be defined using other fields rather than the invoice description during the importing process.
     
     
6. Total amount and Ssles tax variance
   • Using Sage 500 data import manager, the invoice amount will be calculated by Sage 500. If the decimal places are different between the imported data and Sage 500, the total amount of the invoice may be different by calculating the invoice lines. Therefore, the total amount has to be updated after the data import by running a special procedure. It will include invoice details, invoice header and invoice pending batch.
   • The Sales Tax has the same issue as the total amount, in particular for adjustment lines. The sales tax has to be updated in the relating tables in Sage 500 for invoice batch, header and details.
     
     
7. Invoice status update
   • After importing the invoice data, the invoice may be marked as ‘In Use’. It can be updated to be ‘Balanced’ in batch.
     
     
8. Invoice report modification
   • The default invoice report can be modified to support the specified format which might be used before. Sage 500 provides the standard process of how to modify the report, including modifying working table, store procedure and crystal reports.

Summary

Project accounting is an add-on module in Sage 500. It includes great information and functions for  users to easily setup and operate although the migration processes are complicated.  It’s recommended that you leverage an experienced consultant where the migration processes are concerned. We welcome your questions!