This is an old revision of the document!


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.

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.

-- 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
  • Entered : 7/2013
  • Version : Control 4.6+
You could leave a comment if you were logged in.