Re: Quest for information: Accounts Receivables/Payables Values

["Chris Travers" <..hidden..>]

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.

You can modify them to hit ar instead.

Best Wishes,
Chris Travers