Differences

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

--- find_arap_outofbalance [2016/04/20 09:49] – created sweitmann
+++ find_arap_outofbalance [2024/09/25 06:21] (current) – external edit 127.0.0.1
@@ 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.
-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.