Differences

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

Link to this comparison view

control_sql_-_find_negative_balance_due_orders [2019/01/27 11:28]
 control_sql_-_find_negative_balance_due_orders [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +Use this query to find orders with negative balance due.  This is usually due to a bug whereby payments could be doubled (really entered twice without refreshing) or even tripled.  This query finds those affected orders.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**. This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +select OrderNumber, TotalPrice, PaymentTotal, 
 +   CreditMemoAmount,
 +   BalanceDue, StatusText, OrderCreatedDate, SaleDate,
 +  (Select SUM(Amount) from GL where TransactionID = TransHeader.ID and GLAccountID = 14) as ARBalance,
 +from TransHeader 
 +where BalanceDue < 0
 +and TransactionType = 1
 +order by OrderNumber desc
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 3/2012
 +  * Version : 4.6+
 +
 +
 +