[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Quest for information: Accounts Receivables/Payables Values



Chris Travers wrote:
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.

Thank you, Chris, for giving me a place to start.  Much appreciated!

Charley