INTERNET APPLICATION DEVELOPMENT
MID MARKET ERP DEVELOPMENT
by Sheila Zhang
Exporting data in Sage 300 ERP to some formatted files is a common request in Sage 300 ERP development. The export program can be very simple -- just a couple of hours work -- or be a highly functional program with multiple extra features.
Recently, we finished a project with some value-added functions. The client decided to implement the SAP system for their accounting modules, but leave the operational transactions like sales activities, inventory activities and purchase activities in the Sage 300 ERP system.
The project went through our regular four-phase workflow:
Discovery and Design phase (Business Design, Functional Design)
User test phase
Go live and support phase
During the Discovery and Design phase, their outsourced SAP consultant initially suggested we prepare the Sage accounting data directly from the operational data. I pointed out three concerns:
1. It was risky to cover all scenarios within a tight go-live schedule, which essentially recreated Sage logic
2. They already have Sage GL, AR and AP and it is more economical for the client to leverage their existing system
3. Sage GL reports helps reconcile the balances in both systems in case any transaction was missing.
Everyone agreed to the solution we proposed, which leverages Sage logic and automates the accounting process in Sage and finally exports accounting data to feed into the SAP accounting module. CodePartners prepared a functional design document and the client signed off, so we knew everyone was on the same page.
Based upon the output of the Discovery and Design phase, the functional design document, the development phase was quiet and smooth. We confirmed some assumptions and got a prompt response from the clients.
The delivered package included six standalone programs and one report. The scheduled export program plays the core function of this package. It is triggered by a Windows scheduled task. The task runs at 1:00 a.m. every day. First, it runs the Day End Processing and Batch Posting functions in Sage. So, the operational data is able to translate to the accounting data in Sage. It grabs two types of data in Sage. One is master files of customers and vendors. The other is the accounting data of sales activities, inventory activities and purchase activities in Sage. Then five text files with required formats are uploaded to the specified folder. The SAP system scans the folder every five minutes and takes the files in the folder away.
Extra functions were added to the package:
If any errors happen during the process, the users receive an email with the details of the error.
The program also writes the processing results to a log table in case the user needs to reproduce any previous export files for some reason.
Code Mapping tables
The configuration program is designed to keep the database connection credential, email settings and several paths being used by the export process.
The control tables program was designed to maintain the default variables for the export program, like next SAP customer sequence number.
The customers, vendors and accounts are customized Sage user interfaces to maintain the mapping values between the two systems. For example, when a new customer is created in Sage, the program automatically assigns the SAP sequence number defined in the control tables.
User Triggered Export Program
The scheduled export program only takes care of the date that was exported. If the user needs to reproduce any previous export files, they can use the user triggered program to do it.
Use Case Document
Besides the program package, we delivered one additional use case document as well. It is a kind of a user guide, but structured by use cases we collected during the testing phases and end user training.
During the user test phase, we worked with the end user and the SAP consultant to test two months of data. Some modifications were discovered. But, because we built the architecture of the program flexible enough, it minimized the time to make any modification during the test phase. Our client was surprised and delighted.
At end of the go-live support, the client signed on the project with this comment, “Thanks to everyone for getting this project done on time and so far successful.”
And, we would like to thank our client for a successful collaboration on this project.