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

High. This query creates new GL entries. 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-reversible.

-- GL Out of Balance Repair
 
-- User Defined Values
DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
DECLARE @UseGLDate bit;
DECLARE @AltGLDate DateTime;
DECLARE @PlugGLAccountID INT;
 
SET @PlugGLAccountID = ____;  -- Enter the plug GLAccount.ID
SET @StartDate = '1/1/1997';
SET @EndDate   = '1/1/2020';
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
SELECT MAX(EntryDateTime) AS EntryDateTime,
       SUM(Amount) AS Amount,
       MAX(GroupID) AS GroupID,
       MAX(AccountID) AS AccountID,
       MAX(TransactionID) AS TransactionID,
       MAX(DivisionID) AS DivisionID,
       JournalID
 
FROM GL
WHERE JournalID IN
    (SELECT JournalID FROM GL
     WHERE EntryDateTime BETWEEN @StartDate AND @EndDate
     GROUP BY JournalID, DivisionID, AccountID, TransactionID
     HAVING SUM(amount) NOT BETWEEN -0.005 AND 0.005
     )
AND EntryDateTime BETWEEN @StartDate AND @EndDate
GROUP BY JournalID
ORDER BY EntryDateTime
;
 
-- Now create the missing records
-- and insert them into the GL
INSERT INTO Ledger
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,
   @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 SQL 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,
   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 GLID, *
FROM @OOBs

• Entered : 7/2013
• Version : 4.6+