Table of Contents

Explanation of SQL

We discovered, on Company 64845's system, a number of instances of orders that have Cost of Built balance in the GL, even though the order has been marked sold.

Risk of Data Corruption if Run Improperly

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

SQL

Step 1

Flag the orders with that are Sales, but have a Cost of Built balance.

Run this query… 

UPDATE TransHeaderUserField
SET    Paintbooth_Due_Date = '10/31/1975'
WHERE  ID IN ( SELECT   GL.TransactionID
               FROM     Ledger GL
                        LEFT JOIN TransHeader TH ON ( TH.ID = GL.TransactionID )
               WHERE    ( GL.GLAccountID = 34 ) AND ( TH.StatusID > 2 )
               GROUP BY GL.TransactionID
               HAVING   SUM(GL.Amount) NOT BETWEEN -0.0001 AND 0.0001 )

Step 2

Refresh the GL of the orders from Step 1.

Step 3

Adjust the times of the new GL Entries.

It looks like there was a bug that caused Cost of Built entries to be added on edit after the order is marked sale. This query will adjust the fix to the same date and time as the bad entries.

Run these queries…. 

DECLARE @TempTable TABLE(TransHeaderID INT PRIMARY KEY, 
                         LastGroupID INT, PriorGroupID INT, 
                         NewEntryDateTime DATETIME, OldEntryDateTime DATETIME)
;
INSERT INTO @TempTable (TransHeaderID)
SELECT   ID
FROM     TransHeaderUserField
WHERE    Paintbooth_Due_Date = '10/31/1975'
;
UPDATE @TempTable
SET    LastGroupID = ( SELECT MAX(GroupID) FROM Ledger WHERE TransactionID = TransHeaderID )
;
UPDATE @TempTable
SET    PriorGroupID = ( SELECT MAX(GroupID) FROM Ledger WHERE TransactionID = TransHeaderID AND GroupID < LastGroupID AND GLAccountID = 34 )
;
UPDATE @TempTable
SET    NewEntryDateTime = ( SELECT MIN(EntryDateTime) FROM Ledger WHERE GroupID = PriorGroupID )
;
UPDATE @TempTable
SET    OldEntryDateTime = ( SELECT MAX(EntryDateTime) FROM Ledger WHERE GroupID = LastGroupID )
;
UPDATE Ledger
SET    EntryDateTime = T.NewEntryDateTime
FROM   Ledger GL
       LEFT JOIN @TempTable T ON (T.TransHeaderID = GL.TransactionID)
WHERE  GL.GroupID IN (SELECT LastGroupID FROM @TempTable)
;
UPDATE TransHeaderUserField
SET    Paintbooth_Due_Date = NULL
WHERE  Paintbooth_Due_Date = '10/31/1975'

Step 4

Repair GL Group 17811

This GL Group appears to be a correction entries. In order for the up coming queries to work properly, these entries need to be shifted to the sale date.

Run this query… 

UPDATE Ledger
SET EntryDateTime = (SELECT SaleDate FROM TransHeader WHERE ID = Ledger.TransactionID)
WHERE GroupID = 17811

Step 5

Repair Cost of Built entries dated after the order sale date.

Run this query… 

UPDATE Ledger
SET    EntryDateTime = (SELECT SaleDate FROM TransHeader WHERE TransHeader.ID = GL.TransactionID)
WHERE  ID IN ( SELECT GLID
               FROM   ( SELECT   (SELECT OrderNumber FROM TransHeader WHERE ID = TransactionID) AS OrderNumber,
                                 (SELECT SaleDate FROM TransHeader WHERE ID = TransactionID) AS SaleDate,
                                 EntryDateTime,
                                 (SELECT AccountName FROM GLAccount WHERE ID = GLAccountID) AS AccountName,
                                 Amount, GLClassificationType, GLClassTypeName,
                                 ID AS GLID,  
                                 GLAccountID, 
                                 GroupID,
                                 TransactionID AS TransHeaderID,
                                 JournalID
                        FROM     Ledger GL
                        WHERE    GroupID IN ( SELECT   GroupID 
                                              FROM     Ledger GL
                                              WHERE    GLAccountID = 34
                                                       AND (EntryDateTime > (SELECT SaleDate FROM TransHeader WHERE ID = TransactionID))
                                              GROUP BY GroupID 
                                              HAVING   SUM(Amount) NOT BETWEEN -0.01 AND 0.01 )
                                 AND GLAccountID  14 -- Accounts Receivable 
                                 AND GLClassificationType  1007 -- Undeposited funds
                                 AND GLClassificationType  4000 -- Income
                      ) TempA
             )

Version Information