On Dec 2, 2007 11:15 PM, Mark Clarke <
..hidden..> wrote:
Hi all,
I don't know about others but the various bugs in ledger-smb have caused
the system to go out of balance. Often the total per a chart account does
not agree to the detail underlying the account. i.e
if one looks at the
balance on the TB and then drills down to the detail the account amounts
are different.
There were some bugs early in the 1.2.x series which could cause this because part of a transaction would roll back. To my knowledge these have all been fixed for some time, but if you are still seeing new instances, we need to track them down as any such bugs are considered serious.
In my case this was caused by the often reported problem with AP Vendor
Invoice deletions or editing and reposting of AP invoices. It seems this
is still not fixed. Here is what I do to get things back in balance. (I
hope I am not making things worse!)
Please backup your database and test this first. Let me know if I am
missing something.
1) First you need to find the transactions in acc_trans which are supposed
to be AP transaction but have no corresponding entries in the ap table. I
don't have a copy of the query I ran now but it should be something like
Select tran_id from acc_trans
left join ap on acc_trans.trans_id = ap.trans_id
where chart_id = (id for purchase account from chart table) where
invoiceid is not null and
ap.id is null
This should give you all AP entries in acc_trans not in ap.
I would do it differently:
SELECT trans_id FROM acc_trans WHERE trans_id NOT IN
(SELECT id FROM ap
UNION
SELECT id FROM ar
UNION
SELECT id FROM gl
)
GROUP BY trans_id;
Your way may cause issues if GL entries hit the AP account.
Also you can try the same with invoice (with just the ap and ar tables)