Sage 100

Sage Intelligence pulls out the account types, main accounts, and full accounts, among other things. If you are running a multi-company consolidation it checks these values and the company code for duplicates. What I am finding out is that sometimes there is bad data (last time it was in the Main account, this time it's in the Full Account) which causes Sage Intelligence to error out. Anyway, there is a company code field stored in those tables and I have a client that has 2,000 bad records in the GL_Account table. By bad records I mean it has the wrong company code tied to the record. I created a UDF (Delete) tied to the GL_Account table, imported all of the bad records with a value of Y, and ran the delete utility (which shows my UDF as a selection option). It has changed the status to Delete on all of the accounts but they still exist. Finally for the question - without waiting for a year-end processing, is there any way to get rid of them? Sage Intelligence doesn't care that the status is ""deleted"" - it still pulls them in. Maybe I can change the SQL query on that particular container - but I was hoping to just get the accounts of MAS.

I am sorry that I can not answer the question as I believe Sage and Intelligence should rarely be included in close proximity to one and other...

haha, we changed the sql query and it works fine now, but I'd love to know how a gl account in company abc has a company code value of xyz. maybe an issue that happened when copying chart of accounts from another company. Never caused an issue inside of mas so maybe they just never created a fix...

I have seen this before. Most likely cause from manually copying company files (from dos prompt) and not the copy company routine. For example. Let say you set up the sub accounts on one company and then someone just copied the data file over into another. The company code field will have the wrong company at that point.

that would make sense Jim - they would have used that method when they originally set up these companies. What Sage Intelligence is doing makes perfect sense - check and make sure we don't have duplicate accounts with the same company code. This isn't a Sage Intelligence flaw - there is bad data in mas90. I wish we could get it out of there without manually deleting 2000 records through DFDM. But for now modifying the SI SQL query (my UDF Delete field <>Y) is a good work around.

A script could be written to fix the field (company code) and rewrite the record.

we have to delete the record, not change the company code. We could write some custom programming to do that as well, but I'm not sure the client will want ot pay for it. Especially if it doesn't cause issues anymore with our work around.

What are your parameters to delete the records. The delete record script is super simple

inside of the GL_Account table is a UDF field (Y/N) named ""Delete"" - if the value is set to ""Y"" then we want to delete the record