User Tools

Site Tools


find_arap_outofbalance

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

find_arap_outofbalance [2016/04/20 09:49]
sweitmann created
find_arap_outofbalance [2016/04/20 11:23]
sweitmann
Line 1: Line 1:
 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. 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 transactions:​+The following query will help you identify such AR transactions:​
  
-    SELECT transdate, invnumber, ​ABS(amount) FROM ar WHERE transdate BETWEEN '2014-01-01'​ and '​2015-12-31'​ +    SELECT transdate, ​(SELECT MIN(transdate) FROM acc_trans WHERE trans_id=ar.id AND chart_id IN (SELECT  
-    ​AND id IN (SELECT trans_id FROM acc_trans WHERE ABS(amount) = 0.01 AND chart_id IN (SELECT id FROM  +    id FROM chart WHERE link LIKE '​%_paid'​)) paydate,  ​invnumber, ​(SELECT SUM(amount) ​FROM acc_trans WHERE 
-    chart WHERE category='​E'​));​+    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. 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