The AMIEXE (Database mass update) screen looks so simple, so innocuous. Don’t be fooled! Along with its friends, SQL Update/Delete/Insert and GMAINT (Table maintenance) Data Changes, they can lead you down a road that requires spending massive amounts of time troubleshooting followed by cleaning up the changes or even restoring from backup. Yes, even restoring from backup.

Why should you avoid AMIEXE, GMAINT and SQL data changes? Time to hop on my soap box.

Database mass update (AMIEXE)

We will look at Database mass update (AMIEXE) first. Here is how the Online help describes it: “It is possible to create, using the system transactions management, a transaction code that is used to carry out a mass update (modification, deletion of information in the database), after an optional phase of associated setup entry.” This function comes with its own legal notice:

There is no automatic validation of data: “the coherence rules linked with the application are not controlled: it is a maintenance tool.” There is no undo feature with AMIEXE. Once the changes are made, they are final. This function is for development purposes only. Even the developers should be vetting this in a test environment before doing in production, ensuring there is a usable-current backup of the production system and tracking these changes along with all others made.

On to GMAINT and SQL data changes

When you enter transactions though the front end of X3, the transactions must pass business rule tests to ensure accurate, valid data. Examples:

If you enter a BP customer invoice

The program checks to see if the site is valid. If not, you receive the following error:

BP customer must be valid, or you receive the following error:

The invoice function is looking at those plus all the other fields to ensure you have the data required for successful completion of the transaction. SQL and GMAINT don’t do any validations. If you use those to enter the non-existent customer XRT001 directly into the tables, those functions will accept it without question. They won’t even require you to complete all the fields.

SQL and GMAINT don’t pass the data on to other related tables. If I delete the invoice header, what happens to the associated detail records? Nothing, they are left stranded! Let’s not forget payments that were applied against the invoice. If I delete a journal entry, SQL and GMAINT don’t update the Balance table. If I delete an inventory transaction, SQL and GMAINT don’t delete all the related table entries, update statistical fields, recalculate average cost, etc.

If I use SQL or GMAINT to just change the accounting date on an invoice header, SQL and GMAINT don’t automatically update the Fiscal Year and Period value in the record. They don’t check the currency rate table and recalculate amounts in the details or jump to the GL and redo all the transactions there. They won't.stop you from modifying amounts on a journal in final status or stop you from entering amounts where debits don't equal credits

Based on real life situations

Situation 1

A controller was preparing for an audit, when he noticed that the general ledger balances were about $5 million off from the detail. It made no sense. What caused this? It turned out that a purchasing supervisor had made a mistake on an item price and decided it would be easier to “fix” by going into GMAINT. I’d tell you what specific changes the purchasing supervisor did, but he didn’t make notes of his changes. Their business partner spent numerous hours going through purchasing and the general ledger to track down and correct the data. Cost? Panic with the investors’ auditors days away. Hours spent investigating by the controller, his accountants and Support plus the billable engagement for the business partner’s work. Correcting through the front end may have been a little time consuming, but nowhere near the time spent for the troubleshooting and cleanup involved because of “fixing” through GMAINT.

Situation 2

Inventory was out of synch on an intermittent basis. There were transactions missing and numbers that didn’t make sense. What caused this? Someone was going in via SQL and deleting inventory transactions to “correct” inventory. Cost? Frustration dealing with reports, cost and inquires that didn’t match or make sense. People losing confidence in the system. Hours spent troubleshooting X3 and their 3^rd party products for conflicts. Time spent poring over logs and establishing and reviewing audits. Efforts to correct data. A much simpler solution would have been to just ask how to correct the inventory issue followed by creating processes to deal with the situation going forward.

Lessons learned

Before I hop down from my soap box, here are some items to help keep your system safe.

• Database mass update (AMIEXE) is development tool and only developers should have access to it.
• Be extremely cautious when granting access to SQL and GMAINT. SQL queries are great for viewing multiple data records and GMAINT is good for viewing single records. However, with GMAINT, it is easy to accidently change, delete or add records. Keep in mind that SQL queries if not written correctly can bring your system to its knees.
• SQL and GMAINT aren’t the only areas where you should exercise caution granting access. Did I tell you about the newbie who decided to redo the auto journals?
• Keep a detailed list of all changes made to the system and when they were made. if something goes wrong, it is easier to trace back.
• Provide a mechanism for users to report unexpected results, error messages or other “weird stuff.” Better to find these things out sooner while it is fresh in everyone’s memory and you can limit damage.
• Be sure to regularly back up your system and check to make sure that the backup is usable. Question: How often should you back up? Answer: How much data are you willing to lose?
• Have a test environment available for vetting changes and doing training. Make sure it mirrors your production environment so that your testing will reveal how changes will behave in production. Training with production-like data can be so much more meaningful for newbies and definitely safer than doing it in production.
• Think really hard before you say “I’m just going to update one record…” Your motto should be "If I can fix it through the front end, I will. Otherwise, I will contact my business partner for assistance."

Hopping down from my soap box!

Until next time my Sage X3 friends. Stay safe!