Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_plug_all_journal_out_of_balance_entries [2019/01/27 11:29]
127.0.0.1 external edit
control_sql_-_plug_all_journal_out_of_balance_entries [2019/02/05 11:21] (current)
admin
Line 1: Line 1:
-====== ​ ====== 
- 
- 
  
 ===== Explanation of SQL ===== ===== Explanation of SQL =====
Line 32: Line 29:
 <code sql> <code sql>
 -- GL Out of Balance Repair -- GL Out of Balance Repair
 + 
 -- User Defined Values -- User Defined Values
 DECLARE @StartDate DateTime; DECLARE @StartDate DateTime;
Line 37: Line 35:
 DECLARE @UseGLDate bit; DECLARE @UseGLDate bit;
 DECLARE @AltGLDate DateTime; DECLARE @AltGLDate DateTime;
-DECLARE @PlugGLAccountID ​int;+DECLARE @PlugGLAccountID ​INT; 
 + 
 SET @PlugGLAccountID = ____;  -- Enter the plug GLAccount.ID SET @PlugGLAccountID = ____;  -- Enter the plug GLAccount.ID
 SET @StartDate = '​1/​1/​1997';​ SET @StartDate = '​1/​1/​1997';​
Line 43: Line 42:
 SET @UseGLDate = 1;          -- Set to 1 to use same date as Journal. ​ Set to 0 to use AltGLDate instead 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 SET @AltGLDate = GETDATE(); ​ -- Alternate GL Date to use if UseGLDate = 0
 + 
 -- Declare and Initialize some automatic values -- Declare and Initialize some automatic values
-DECLARE @PlugGLClassificationType ​int+DECLARE @PlugGLClassificationType ​INT
-DECLARE @PlugGLClassTypeName ​varchar(50); +DECLARE @PlugGLClassTypeName ​VARCHAR(50); 
-DECLARE @MaxGLID ​int;+DECLARE @MaxGLID ​INT; 
 + 
 SELECT @PlugGLClassificationType = GLClassificationType,​ SELECT @PlugGLClassificationType = GLClassificationType,​
         @PlugGLClassTypeName ​     = GLClassTypeName         @PlugGLClassTypeName ​     = GLClassTypeName
Line 52: Line 53:
 WHERE ID = @PlugGLAccountID WHERE ID = @PlugGLAccountID
 ; ;
-SET @MaxGLID = (select max(ID) from Ledger);+SET @MaxGLID = (SELECT MAX(ID) FROM Ledger); 
 +  
 + 
 -- now pull All journals that are out of balance into a table -- now pull All journals that are out of balance into a table
 DECLARE @OOBs TABLE( DECLARE @OOBs TABLE(
-    ID int identity(1,1),+    ID INT IDENTITY(1,1),
     EntryDateTime DateTime,     EntryDateTime DateTime,
-    Amount ​decimal(18,4), +    Amount ​DECIMAL(18,4), 
-    GroupID ​int+    GroupID ​INT
-    AccountID ​int+    AccountID ​INT
-    TransactionID ​int+    TransactionID ​INT
-    DivisionID ​int+    DivisionID ​INT
-    JournalID ​int+    JournalID ​INT
     );     );
 + 
 INSERT INTO @OOBs INSERT INTO @OOBs
-SELECT ​max(EntryDateTime) ​as EntryDateTime,​ +SELECT ​MAX(EntryDateTime) ​AS EntryDateTime,​ 
-       Sum(Amount) ​as Amount, +       SUM(Amount) ​AS Amount, 
-       Max(GroupID) ​as GroupID, +       MAX(GroupID) ​AS GroupID, 
-       Max(AccountID) ​as AccountID,​ +       MAX(AccountID) ​AS AccountID,​ 
-       Max(TransactionID) ​as TransactionID,​ +       MAX(TransactionID) ​AS TransactionID,​ 
-       Max(DivisionID) ​as DivisionID,+       MAX(DivisionID) ​AS DivisionID,
        ​JournalID        ​JournalID
 + 
 FROM GL FROM GL
-Where JournalID ​in +WHERE JournalID ​IN 
-    (select ​JournalID ​from GL +    (SELECT ​JournalID ​FROM GL 
-     where EntryDateTime ​between ​@StartDate ​and @EndDate +     WHERE EntryDateTime ​BETWEEN ​@StartDate ​AND @EndDate 
-     group by JournalID, DivisionID, AccountID, TransactionID +     GROUP BY JournalID, DivisionID, AccountID, TransactionID 
-     having sum(amount) ​not between ​-0.005 ​and 0.005+     HAVING SUM(amount) ​NOT BETWEEN ​-0.005 ​AND 0.005
      )      )
-and EntryDateTime ​between ​@StartDate ​and @EndDate +AND EntryDateTime ​BETWEEN ​@StartDate ​AND @EndDate 
-Group by JournalID +GROUP BY JournalID 
-order by EntryDateTime+ORDER BY EntryDateTime
 ; ;
 + 
 -- Now create the missing records -- Now create the missing records
 -- and insert them into the GL -- and insert them into the GL
-insert into Ledger +INSERT INTO Ledger 
-Select +SELECT 
-   ​@MaxGLID + ID as ID, +   ​@MaxGLID + ID AS ID, 
-   ​-1 ​as StoreID, +   ​-1 ​AS StoreID, 
-   ​8900 ​as ClassTypeID,​ +   ​8900 ​AS ClassTypeID,​ 
-   '​CyriousTech' ​as ModifiedByUser,​ +   '​CyriousTech' ​AS ModifiedByUser,​ 
-   '​TechSQL' ​as ModifiedByComputer,​ +   '​TechSQL' ​AS ModifiedByComputer,​ 
-   ​GetDate() ​as ModifiedByDate,​ +   ​GetDate() ​AS ModifiedByDate,​ 
-   ​0 ​as SeqID, +   ​0 ​AS SeqID, 
-   ​0 ​as IsSystem, +   ​0 ​AS IsSystem, 
-   cast(1 as bit) as IsActive, +   CAST(1 AS bit) AS IsActive, 
-   case when @UseGLDate = 1 then EntryDateTime ​else @AltGLDate ​end as EntryDateTime,​ +   CASE WHEN @UseGLDate = 1 THEN EntryDateTime ​ELSE @AltGLDate ​END AS EntryDateTime,​ 
-   ​-Amount ​as Amount, +   ​-Amount ​AS Amount, 
-   ​0 ​as Classification,​ +   ​0 ​AS Classification,​ 
-   cast(0 as bit) as IsTaxable,+   CAST(0 AS bit) AS IsTaxable,
    ​GroupID,​    ​GroupID,​
-   ​@PlugGLAccountID ​as GLAccountID,​ +   ​@PlugGLAccountID ​AS GLAccountID,​ 
-   ​8001 ​as GLAccountClassTypeID,​+   ​8001 ​AS GLAccountClassTypeID,​
    ​AccountID,​    ​AccountID,​
-   ​2000 ​as AccountClassTypeID,​+   ​2000 ​AS AccountClassTypeID,​
    ​TransactionID,​    ​TransactionID,​
-   ​10000 ​as TransactionClassTypeID,​ +   ​10000 ​AS TransactionClassTypeID,​ 
-   cast(NULL as intas TransDetailID,​ +   CAST(NULL AS INTAS TransDetailID,​ 
-   cast(NULL as intas TransDetailClassTypeID,​ +   CAST(NULL AS INTAS TransDetailClassTypeID,​ 
-   cast(NULL as intas GoodsItemID,​ +   CAST(NULL AS INTAS GoodsItemID,​ 
-   cast(NULL as intas GoodItemClassTypeID,​ +   CAST(NULL AS INTAS GoodItemClassTypeID,​ 
-   '​Tech SQL Fix '+cast(GetDate() ​as varchar(16)) as Description,​+   '​Tech SQL Fix '+CAST(GetDate() ​AS VARCHAR(16)) AS Description,​
    ​DivisionID,​    ​DivisionID,​
-   cast(NULL as varchar(1)) as Notes, +   CAST(NULL AS VARCHAR(1)) AS Notes, 
-   cast(0 as bit) as IsModified,​ +   CAST(0 AS bit) AS IsModified,​ 
-   cast(0 as bit) as IsUser, +   CAST(0 AS bit) AS IsUser, 
-   cast(NULL as intas TaxClassID,​ +   CAST(NULL AS INTAS TaxClassID,​ 
-   ​0 ​as Quantity, +   ​0 ​AS Quantity, 
-   cast(NULL as intas PartID, +   CAST(NULL AS INTAS PartID, 
-   cast(NULL as intas PartClassTypeID,​+   CAST(NULL AS INTAS PartClassTypeID,​
    ​JournalID,​    ​JournalID,​
-   ​20500 ​as JournalClassTypeID,​ +   ​20500 ​AS JournalClassTypeID,​ 
-   cast(0 as bit) as Reconciled,​ +   CAST(0 AS bit) AS Reconciled,​ 
-   cast(NULL as datetime) ​as ReconciliationDateTime,​ +   CAST(NULL AS datetime) ​AS ReconciliationDateTime,​ 
-   cast(NULL as intas ReconciliationID,​ +   CAST(NULL AS INTAS ReconciliationID,​ 
-   cast(NULL as intas ReconciliationClassTypeID,​ +   CAST(NULL AS INTAS ReconciliationClassTypeID,​ 
-   ​DivisionID ​as ProcessedDivisionID,​ +   ​DivisionID ​AS ProcessedDivisionID,​ 
-   ​@PlugGLClassificationType ​as GLClassificationType,​ +   ​@PlugGLClassificationType ​AS GLClassificationType,​ 
-   ​@PlugGLClassTypeName ​as GLClassTypeName,​ +   ​@PlugGLClassTypeName ​AS GLClassTypeName,​ 
-   cast(NULL as intas GLDepartmentID,​ +   CAST(NULL AS INTAS GLDepartmentID,​ 
-   cast(NULL as intas TransPartID,​ +   CAST(NULL AS INTAS TransPartID,​ 
-   cast(NULL as intas TransPartClassTypeID,​ +   CAST(NULL AS INTAS TransPartClassTypeID,​ 
-   cast(NULL as intas StationID,​ +   CAST(NULL AS INTAS StationID,​ 
-   cast(NULL as intas PayrollID,​ +   CAST(NULL AS INTAS PayrollID,​ 
-   cast(NULL as intas PayrollClassTypeID,​ +   CAST(NULL AS INTAS PayrollClassTypeID,​ 
-   cast(NULL as intas DepositJournalID,​ +   CAST(NULL AS INTAS DepositJournalID,​ 
-   ​2 ​as EntryType,​ +   ​2 ​AS EntryType,​ 
-   cast(NULL as intas EmployeeID,​ +   CAST(NULL AS INTAS EmployeeID,​ 
-   cast(0 as bit) as OffBalanceSheet,​ +   CAST(0 AS bit) AS OffBalanceSheet,​ 
-   cast(NULL as intas WarehouseID,​ +   CAST(NULL AS INTAS WarehouseID,​ 
-   cast(NULL as intas InventoryID +   CAST(NULL AS INTAS InventoryID 
-from @OOBs T+FROM @OOBs T
 ; ;
 -- and show the output -- and show the output
-select ​@MaxGLID + ID as GLID, * +SELECT ​@MaxGLID + ID AS GLID, * 
-from @OOBs+FROM @OOBs
 </​code>​ </​code>​