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 int) as TransDetailID, +   CAST(NULL AS INT) AS TransDetailID, 
-   cast(NULL as int) as TransDetailClassTypeID, +   CAST(NULL AS INT) AS TransDetailClassTypeID, 
-   cast(NULL as int) as GoodsItemID, +   CAST(NULL AS INT) AS GoodsItemID, 
-   cast(NULL as int) as GoodItemClassTypeID, +   CAST(NULL AS INT) AS 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 int) as TaxClassID, +   CAST(NULL AS INT) AS TaxClassID, 
-   0 as Quantity, +   0 AS Quantity, 
-   cast(NULL as int) as PartID, +   CAST(NULL AS INT) AS PartID, 
-   cast(NULL as int) as PartClassTypeID,+   CAST(NULL AS INT) AS 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 int) as ReconciliationID, +   CAST(NULL AS INT) AS ReconciliationID, 
-   cast(NULL as int) as ReconciliationClassTypeID, +   CAST(NULL AS INT) AS ReconciliationClassTypeID, 
-   DivisionID as ProcessedDivisionID, +   DivisionID AS ProcessedDivisionID, 
-   @PlugGLClassificationType as GLClassificationType, +   @PlugGLClassificationType AS GLClassificationType, 
-   @PlugGLClassTypeName as GLClassTypeName, +   @PlugGLClassTypeName AS GLClassTypeName, 
-   cast(NULL as int) as GLDepartmentID, +   CAST(NULL AS INT) AS GLDepartmentID, 
-   cast(NULL as int) as TransPartID, +   CAST(NULL AS INT) AS TransPartID, 
-   cast(NULL as int) as TransPartClassTypeID, +   CAST(NULL AS INT) AS TransPartClassTypeID, 
-   cast(NULL as int) as StationID, +   CAST(NULL AS INT) AS StationID, 
-   cast(NULL as int) as PayrollID, +   CAST(NULL AS INT) AS PayrollID, 
-   cast(NULL as int) as PayrollClassTypeID, +   CAST(NULL AS INT) AS PayrollClassTypeID, 
-   cast(NULL as int) as DepositJournalID, +   CAST(NULL AS INT) AS DepositJournalID, 
-   2 as EntryType, +   2 AS EntryType, 
-   cast(NULL as int) as EmployeeID, +   CAST(NULL AS INT) AS EmployeeID, 
-   cast(0 as bit) as OffBalanceSheet, +   CAST(0 AS bit) AS OffBalanceSheet, 
-   cast(NULL as int) as WarehouseID, +   CAST(NULL AS INT) AS WarehouseID, 
-   cast(NULL as int) as InventoryID +   CAST(NULL AS INT) AS 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>