Topic: How to correct wrong sales account for an item - completed transaction

I just discovered that a sales item was set up using the wrong sales account.  There are now about 30 completed transactions (sales invoice - payment received and applied) with this item posting to the wrong sales account.

What is the best way to fix this?
I have already corrected the wrong sales account in the item.

I could make a bunch of journal entries to correct them, but I think that would still leave the errors in any sales reports, etc.

Can I directly edit the 0_gl_trans table to correct the posted account #?  Are there other tables that would have to be manually corrected?  Is this a can of worms?

Thank you
Bruce

Re: How to correct wrong sales account for an item - completed transaction

I would give it a shot, it might just work.

Make a backup first.  Then form a mysql query of the gl_trans table to select all the wrong postings.  Change the query to an update of the right account.

Run through all the reports and see if anything broke.  If so, you can just restore from backup.

Let us know how it turns out.

You can also experiment with making the journal entries and see if that breaks any report.  In theory, it should result in the totals you expect.

Re: How to correct wrong sales account for an item - completed transaction

In a separate new company, make 1 sample transaction to simulate the wrong entries you mad. Study the before and after sql backups Now appropriately edit the actual company's DB with these updations, possibly having the sqls created from a spreadsheet.

Re: How to correct wrong sales account for an item - completed transaction

apmuthu wrote:

In a separate new company, make 1 sample transaction to simulate the wrong entries you mad. Study the before and after sql backups Now appropriately edit the actual company's DB with these updations, possibly having the sqls created from a spreadsheet.

Hi, this is the hard and to-the-core kind of solution and this will require considerable IT knowledge to do it properly

Is there somewhat easier and proper way to do this directly in FA itself ? I mean, how would one ( from an Accountant's perspective ) correct all those 30 mistaken entries ? Does void all those 30 and re-enter suffices ?

Re: How to correct wrong sales account for an item - completed transaction

Voiding the invoices, re-entering them, and then reapplying the payments may be the only way to fix the wrong sales account using vanilla FA.

However, the sales orders from the voided invoices will live on forever because they cannot be canceled due to this bug: http://mantis.frontaccounting.com/view.php?id=4155, although the only ill effect is seeing 0 amount sales orders appear during searches or reports.   Sales orders also cannot be voided.

Using a journal entry would be the fastest and easiest vanilla FA way to fix the problem, but I do not think it would work because it does not support counterparty on sales accounts (a feature I added to my fork).

The inability to edit invoiced sales orders is a continual irritant to my staff who need to change the content of sales orders for various reasons.  FA's philosophy is that the invoice is a contract with the customer and should not be changed.  FA only allows a few changes such as date.

Re: How to correct wrong sales account for an item - completed transaction

I looked at the modify code today and it voids out the old G/L transactions.  So now I'm thinking that perhaps all you need to do is modify the invoice and reprocess it.

Re: How to correct wrong sales account for an item - completed transaction

Hey Braath, thanks for that


I have looked at how other Accounting Software and thinking how, if similar issues were encountered. It really does seem very hard to 'clean up' old mistakes like wrong invoices / wrong sales order ... sometime even if you void the said wrong transaction, there are cases where the voided sales order still lives on, as you mentioned

I wonder if all these are by design, as to make sure all things are traced ? ... or simply, oh well it's  a bug ?