Differences

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

Link to this comparison view

control_sql_-_identify_doubled_gl_from_bug_with_posting_payment_and_sale_simultaneously [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +In Control version 4.5 (release in July - September of 2010) a specific bug existed whereby when an order was paid and marked sale simultaneously (from the enter payment screens), the sales and A/R balance would be posted twice one on the payment and once in the "​marked sale".
 +
 +
 +
 +This query identifies these orders by listing the order where the GL income is double the orders'​ subtotal.
 +
 +
 +
 +===== Notes =====
 +  - This will find all those where the problem still exists. Some might have initially had the problem, but Control'​s auto-correcting GL will fix the problem if the order is edited or another payment applied. Hence, in many cases this won't be an issue.
 +  - If the customer is using Avalara, the order total will //only be sent once// to Avalara so those totals will be correct.
 +
 +
 +
 +===== Approach to Correct =====
 +
 +
 +
 +Contact Cyrious Technical Support and refer to this link. Our technical staff can correct the problem for you.
 +
 +
 +
 +Control'​s auto-correcting mechanism is the easiest way to fix this problem. The process for this is simple:
 +  - Identify the orders with the SQL below.
 +  - Edit each order and save it.
 +  - [If required], shift the GL entries to the appropriate timeframe. A sample query for this is also below, but be sure to confirm this is the desired results before executing it.
 +
 +
 +
 +Another slight modification of the above is to use the query below to set a UDF, then pull up all these orders in Explorer in IDE mode and run the recalculate GL on them all at once. This approach, though more efficient, should only be carried out under the supervision of a senior tech.
 +
 +
 +
 +===== SQL to Identify Doubled Orders Remaining =====
 +
 +
 +
 +<code sql>
 +declare @startdate datetime;
 +declare @enddate datetime;
 +set @startdate = '​9/​1/​2010';​
 +set @enddate = '​10/​1/​2010';​
 +select
 +    (select OrderNumber from TransHeader where ID = GL.TransactionID) as OrderNumber,​
 +    -SUM(amount)/​2 as AmountDoubled,​
 +    TransactionID
 +from GL
 +where GLClassificationType = 4000
 +and TransactionID in (select ID from TransHeader where SaleDate between @startdate and @enddate)
 +group by TransactionID
 +having SUM(amount) ​ 0 and
 +       ​-SUM(amount) = 2 * (Select SubTotalPrice from TransHeader where ID = GL.TransactionID)
 +order by OrderNumber
 +</​code>​
 +
 +
 +
 +===== SQL to Shift GL Entries =====
 +
 +
 +
 +<code sql>
 +declare @startrundate datetime;
 +declare @endrundate datetime;
 +SET @startrundate = '​9/​26/​2010 18:​00';​
 +SET @endrundate = '​9/​26/​2010 22:​00';​
 +update GL
 +set EntryDateTime =
 +    (select SaleDate from TransHeader where ID = GL.TransactionID),​
 +    IsSystem = 1  -- flag these for future reference
 +where EntryDateTime between @startrundate and @endrundate
 +and TransActionID in (
 + select TransactionID from GL
 + where GLClassificationType = 4000
 + and TransactionID in (select ID from TransHeader where SaleDate between @startdate and @enddate)
 + group by TransactionID
 + having SUM(amount) ​ 0 and
 +    ​-SUM(amount) = 2 * (Select SubTotalPrice from TransHeader where ID = GL.TransactionID)
 +)
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 10.2010
 +  * Version : **4.5 Only**
 +
 +
 +