====== {$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.//
  * Open Control in IDE mode.
  * Open Order explorer and go to Advanced Mode.
  * Set the query to...
    * All Orders where...
    *   Paintbooth Due Date is equal to 10/31/1975
  * Refresh the explorer, making sure all of the orders for included and selected in the explorer grid.
  * Go to the Action Menu and select "Refresh GL On These Transactions"



===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 =====
  * Entered : 03/18/2010
  * Version :



===== Related SQLs =====
  * Backlinks [[include_pagepage_componentbacklinks]]