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.

  1. 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.
  2. If the customer is using Avalara, the order total will only be sent once to Avalara so those totals will be 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:

  1. Identify the orders with the SQL below.
  2. Edit each order and save it.
  3. [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.

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
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)
)
  • Entered : 10.2010
  • Version : 4.5 Only
You could leave a comment if you were logged in.