User Tools

Site Tools


find_arap_outofbalance

In earlier RMA versions of SQL-Ledger sometimes exchange rate differences were not booked correctly on the debitor and creditor control accounts, but ended up on the exchange rate difference account instead. This would lead to different results in the P+L versus the results in the Balance Sheet.

The following query will help you identify such AR transactions:

  SELECT transdate, (SELECT MIN(transdate) FROM acc_trans WHERE trans_id=ar.id AND chart_id IN (SELECT 
  id FROM chart WHERE link LIKE '%_paid')) paydate,  invnumber, (SELECT SUM(amount) FROM acc_trans WHERE
  trans_id=ar.id AND chart_id IN (SELECT id FROM chart WHERE category='E')) amount, (SELECT SUM(amount)
  FROM acc_trans WHERE trans_id = ar.id AND chart_id IN (SELECT id FROM chart WHERE category='E')) 
  payment FROM ar WHERE transdate BETWEEN '2015-01-01' and '2015-12-31' AND id IN (SELECT trans_id FROM
  acc_trans WHERE ABS(amount) = 0.01 AND chart_id IN (SELECT id FROM chart WHERE category='E'));

And the following query will help you identify such AP transactions:

  SELECT transdate, (SELECT MIN(transdate) FROM acc_trans WHERE trans_id=ap.id AND chart_id IN (SELECT id
  FROM chart WHERE link LIKE '%_paid')) paydate,  invnumber, (SELECT SUM(amount) FROM acc_trans WHERE
  trans_id=ap.id AND chart_id IN (SELECT id FROM chart WHERE category='E')) amount, (SELECT SUM(amount)
  FROM acc_trans WHERE trans_id = ap.id AND chart_id IN (SELECT id FROM chart WHERE category='E')) 
  payment FROM ap WHERE transdate BETWEEN '2015-01-01' and '2015-12-31' AND id IN (SELECT trans_id FROM
  acc_trans WHERE ABS(amount) = 0.01 AND chart_id IN (SELECT id FROM chart WHERE category='E'));
  

You may need to adjust the dates and amounts to fit your individual search.

find_arap_outofbalance.txt · Last modified: 2016/04/20 11:23 by sweitmann