On 8/3/07, Chris Travers <..hidden..> wrote:
Hi Charley;
On second view, the problem is likely to be in your acc_trans table. You
are just searching AR and AP transaction summary records (which we should
probably properly normalize in 1.4). You want to be searching the account
line items instead.
Two more queries that might help you locate the discrepency:
SELECT id from ap join acc_trans ON (ap.id = acc_trans.trans_id)
WHERE chart_id = (select id from chart where accno = [ap account number])
GROUP BY ap.id, acc_trans.trans_id, ap.amount, ap.paid
HAVING sum(acc_trans.amount) <> (ap.amount - ap.paid);
This pulls up AP entries with wrong summary info.
SELECT trans_id FROM acc_trans
WHERE trans_id NOT IN (select id FROM ap)
AND chart_id = (select id from chart where accno = [ap account number])
GROUP BY trans_id;
This pulls up any transactions hitting the AP account which are not in the
AP table.