Explanation of SQL
This query creates offsetting entries for any order not in Sale that have an A/R balance in the GL. Two entries are created, one to reverse the A/R balance and another one to PLUG the difference so the GL is not out of balance. You must set the plug account before using the query. This SQL creates a PLUG entry to balance every GL journal that is out of balance. A plug entry does not actually identify which GL entries were correct/incorrect, it merely locates any transactions that are out of balance and creates an adjusting entry using the PLUG account (i.e. An account you create, such as Suspense) which will bring the transaction back in balance.
Notes:
- It is recommended that you only use this query for historical years. If you are in the current year it is best to find correct the GL using the proper accounts.
- It must be run with all users out and the SSLIP shut down.
- CHAPI must be restarted (if using 5.1) when complete.
- The GL Account used as a plug must be a real GL Account. Be sure to set this before running.
- If you aren't sure what this is or does, YOU SHOULD NOT BE USING IT.
Risk of Data Corruption if Run Improperly
High. The GL Table is modified and new entries created 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
-- Invalid A/R Records Repair -- User Defined Values DECLARE @StartDate DateTime; DECLARE @EndDate DateTime; DECLARE @UseGLDate bit; DECLARE @AltGLDate DateTime; DECLARE @PlugGLAccountID INT; SET @PlugGLAccountID = ___; -- Update the Plug Account SET @StartDate = '1/1/2000'; -- Adjust dates to set specific timeframe of when orders were created. SET @EndDate = '1/1/2020'; -- Adjust dates to set specific timeframe of when orders were created. SET @UseGLDate = 1; -- Set to 1 to use same date as Journal. Set to 0 to use AltGLDate instead SET @AltGLDate = GETDATE(); -- Alternate GL Date to use if UseGLDate = 0 -- Declare and Initialize some automatic values DECLARE @PlugGLClassificationType INT; DECLARE @PlugGLClassTypeName VARCHAR(50); DECLARE @MaxGLID INT; SELECT @PlugGLClassificationType = GLClassificationType, @PlugGLClassTypeName = GLClassTypeName FROM GLAccount WHERE ID = @PlugGLAccountID ; SET @MaxGLID = (SELECT MAX(ID) FROM Ledger); -- now pull All journals that are out of balance into a table DECLARE @OOBs TABLE( ID INT IDENTITY(1,1), EntryDateTime DateTime, Amount DECIMAL(18,4), GroupID INT, AccountID INT, TransactionID INT, DivisionID INT, JournalID INT ); INSERT INTO @OOBs -- We are then using the last JournalID to pull the other information for that one specific Journal so we can do our inserts later. SELECT StartDateTime AS EntryDateTime , T.Amount , J.StartGLGroupID AS GroupID , J.AccountID , ISNULL(T.TransActionID,99) AS TransActionID , J.DivisionID , T.JournalID FROM ( -- We are pulling the last JournalID that has a AR entry on it and also the sum of the AR balance for that transaction. SELECT MAX(JournalID) AS JournalID , SUM(Amount) AS Amount , TransActionID FROM Ledger GL WITH(NOLOCK) WHERE TransActionID IN ( -- We are looking for any transactions that are out of balance. SELECT TransActionID FROM Ledger GL WITH(NOLOCK) LEFT JOIN TransHeader T WITH(NOLOCK) ON T.ID = GL.TransactionID WHERE GLAccountID = 14 AND T.StatusID <> 3 AND ISNULL(OrderCreatedDate,@StartDate) BETWEEN @StartDate AND @EndDate GROUP BY TransActionID HAVING SUM(Amount) <> 0 ) AND GLAccountID = 14 GROUP BY TransActionID ) T LEFT JOIN Journal J WITH(NOLOCK) ON J.ID = T.JournalID ORDER BY EntryDateTime ; -- Now create the balancing A/R record -- and insert it into the GL INSERT INTO Ledger ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EntryDateTime] ,[Amount] ,[Classification] ,[IsTaxable] ,[GroupID] ,[GLAccountID] ,[GLAccountClassTypeID] ,[AccountID] ,[AccountClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[TransDetailID] ,[TransDetailClassTypeID] ,[GoodsItemID] ,[GoodsItemClassTypeID] ,[Description] ,[DivisionID] ,[Notes] ,[IsModified] ,[IsUser] ,[TaxClassID] ,[Quantity] ,[PartID] ,[PartClassTypeID] ,[JournalID] ,[JournalClassTypeID] ,[Reconciled] ,[ReconciliationDateTime] ,[ReconciliationID] ,[ReconciliationClassTypeID] ,[ProcessedDivisionID] ,[GLClassificationType] ,[GLClassTypeName] ,[TransPartID] ,[TransPartClassTypeID] ,[StationID] ,[PayrollID] ,[PayrollClassTypeID] ,[DepositJournalID] ,[EntryType] ,[EmployeeID] ,[OffBalanceSheet] ,[WarehouseID] ,[InventoryID] ) SELECT @MaxGLID + ID AS ID, -1 AS StoreID, 8900 AS ClassTypeID, 'CyriousTech' AS ModifiedByUser, 'TechSQL' AS ModifiedByComputer, GetDate() AS ModifiedByDate, 0 AS SeqID, 0 AS IsSystem, CAST(1 AS bit) AS IsActive, CASE WHEN @UseGLDate = 1 THEN EntryDateTime ELSE @AltGLDate END AS EntryDateTime, -Amount AS Amount, 0 AS Classification, CAST(0 AS bit) AS IsTaxable, GroupID, 14 AS GLAccountID, -- A/R Account 8001 AS GLAccountClassTypeID, AccountID, 2000 AS AccountClassTypeID, TransactionID, 10000 AS TransactionClassTypeID, CAST(NULL AS INT) AS TransDetailID, CAST(NULL AS INT) AS TransDetailClassTypeID, CAST(NULL AS INT) AS GoodsItemID, CAST(NULL AS INT) AS GoodItemClassTypeID, 'Tech A/R Fix '+CAST(GetDate() AS VARCHAR(16)) AS Description, DivisionID, CAST(NULL AS VARCHAR(1)) AS Notes, CAST(0 AS bit) AS IsModified, CAST(0 AS bit) AS IsUser, CAST(NULL AS INT) AS TaxClassID, 0 AS Quantity, CAST(NULL AS INT) AS PartID, CAST(NULL AS INT) AS PartClassTypeID, JournalID, 20500 AS JournalClassTypeID, CAST(0 AS bit) AS Reconciled, CAST(NULL AS datetime) AS ReconciliationDateTime, CAST(NULL AS INT) AS ReconciliationID, CAST(NULL AS INT) AS ReconciliationClassTypeID, DivisionID AS ProcessedDivisionID, @PlugGLClassificationType AS GLClassificationType, @PlugGLClassTypeName AS GLClassTypeName, -- cast(NULL as int) as GLDepartmentID, -- This field is not in most databases since it is no longer used. CAST(NULL AS INT) AS TransPartID, CAST(NULL AS INT) AS TransPartClassTypeID, CAST(NULL AS INT) AS StationID, CAST(NULL AS INT) AS PayrollID, CAST(NULL AS INT) AS PayrollClassTypeID, CAST(NULL AS INT) AS DepositJournalID, 2 AS EntryType, CAST(NULL AS INT) AS EmployeeID, CAST(0 AS bit) AS OffBalanceSheet, CAST(NULL AS INT) AS WarehouseID, CAST(NULL AS INT) AS InventoryID FROM @OOBs T ; -- Now create the plug record (so we don't make it off balance) -- and insert it into the GL. But first we have to adjust the GLID DECLARE @Offset INT; SET @Offset = (SELECT COUNT(*) FROM @OOBs); INSERT INTO Ledger ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EntryDateTime] ,[Amount] ,[Classification] ,[IsTaxable] ,[GroupID] ,[GLAccountID] ,[GLAccountClassTypeID] ,[AccountID] ,[AccountClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[TransDetailID] ,[TransDetailClassTypeID] ,[GoodsItemID] ,[GoodsItemClassTypeID] ,[Description] ,[DivisionID] ,[Notes] ,[IsModified] ,[IsUser] ,[TaxClassID] ,[Quantity] ,[PartID] ,[PartClassTypeID] ,[JournalID] ,[JournalClassTypeID] ,[Reconciled] ,[ReconciliationDateTime] ,[ReconciliationID] ,[ReconciliationClassTypeID] ,[ProcessedDivisionID] ,[GLClassificationType] ,[GLClassTypeName] ,[TransPartID] ,[TransPartClassTypeID] ,[StationID] ,[PayrollID] ,[PayrollClassTypeID] ,[DepositJournalID] ,[EntryType] ,[EmployeeID] ,[OffBalanceSheet] ,[WarehouseID] ,[InventoryID] ) SELECT @MaxGLID + @Offset + ID AS ID, -1 AS StoreID, 8900 AS ClassTypeID, 'CyriousTech' AS ModifiedByUser, 'TechSQL' AS ModifiedByComputer, GetDate() AS ModifiedByDate, 0 AS SeqID, 0 AS IsSystem, CAST(1 AS bit) AS IsActive, CASE WHEN @UseGLDate = 1 THEN EntryDateTime ELSE @AltGLDate END AS EntryDateTime, Amount AS Amount, 0 AS Classification, CAST(0 AS bit) AS IsTaxable, GroupID, @PlugGLAccountID AS GLAccountID, 8001 AS GLAccountClassTypeID, AccountID, 2000 AS AccountClassTypeID, TransactionID, 10000 AS TransactionClassTypeID, CAST(NULL AS INT) AS TransDetailID, CAST(NULL AS INT) AS TransDetailClassTypeID, CAST(NULL AS INT) AS GoodsItemID, CAST(NULL AS INT) AS GoodItemClassTypeID, 'Tech A/R Fix '+CAST(GetDate() AS VARCHAR(16)) AS Description, DivisionID, CAST(NULL AS VARCHAR(1)) AS Notes, CAST(0 AS bit) AS IsModified, CAST(0 AS bit) AS IsUser, CAST(NULL AS INT) AS TaxClassID, 0 AS Quantity, CAST(NULL AS INT) AS PartID, CAST(NULL AS INT) AS PartClassTypeID, JournalID, 20500 AS JournalClassTypeID, CAST(0 AS bit) AS Reconciled, CAST(NULL AS datetime) AS ReconciliationDateTime, CAST(NULL AS INT) AS ReconciliationID, CAST(NULL AS INT) AS ReconciliationClassTypeID, DivisionID AS ProcessedDivisionID, @PlugGLClassificationType AS GLClassificationType, @PlugGLClassTypeName AS GLClassTypeName, -- cast(NULL as int) as GLDepartmentID, -- This field is not in most databases since it is no longer used. CAST(NULL AS INT) AS TransPartID, CAST(NULL AS INT) AS TransPartClassTypeID, CAST(NULL AS INT) AS StationID, CAST(NULL AS INT) AS PayrollID, CAST(NULL AS INT) AS PayrollClassTypeID, CAST(NULL AS INT) AS DepositJournalID, 2 AS EntryType, CAST(NULL AS INT) AS EmployeeID, CAST(0 AS bit) AS OffBalanceSheet, CAST(NULL AS INT) AS WarehouseID, CAST(NULL AS INT) AS InventoryID FROM @OOBs T ; -- and show the output SELECT @MaxGLID + ID AS GLIDAR, @MaxGLID + @Offset + ID AS GLIDPlug, * FROM @OOBs
Version Information
- Entered : 7/2013
- Version : Control 4.6+