Table of Contents

{$page}

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…

code_formatsql

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 )

code

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….

code_formatsql

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'

code

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…

code_formatsql

UPDATE Ledger

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

WHERE GroupID = 17811

code

Step 5

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

Run this query…

code_formatsql

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
           )

code

Version Information