Differences
This shows you the differences between two versions of the page.
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] 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 | + | DECLARE @PlugGLAccountID |
+ | |||
SET @PlugGLAccountID = ____; -- Enter the plug GLAccount.ID | SET @PlugGLAccountID = ____; -- Enter the plug GLAccount.ID | ||
SET @StartDate = ' | SET @StartDate = ' | ||
Line 43: | Line 42: | ||
SET @UseGLDate = 1; -- Set to 1 to use same date as Journal. | SET @UseGLDate = 1; -- Set to 1 to use same date as Journal. | ||
SET @AltGLDate = GETDATE(); | SET @AltGLDate = GETDATE(); | ||
+ | |||
-- Declare and Initialize some automatic values | -- Declare and Initialize some automatic values | ||
- | DECLARE @PlugGLClassificationType | + | DECLARE @PlugGLClassificationType |
- | DECLARE @PlugGLClassTypeName | + | DECLARE @PlugGLClassTypeName |
- | DECLARE @MaxGLID | + | DECLARE @MaxGLID |
+ | |||
SELECT @PlugGLClassificationType = GLClassificationType, | SELECT @PlugGLClassificationType = GLClassificationType, | ||
@PlugGLClassTypeName | @PlugGLClassTypeName | ||
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 | + | Amount |
- | GroupID | + | GroupID |
- | AccountID | + | AccountID |
- | TransactionID | + | TransactionID |
- | DivisionID | + | DivisionID |
- | JournalID | + | JournalID |
); | ); | ||
+ | |||
INSERT INTO @OOBs | INSERT INTO @OOBs | ||
- | SELECT | + | SELECT |
- | Sum(Amount) | + | SUM(Amount) |
- | Max(GroupID) | + | MAX(GroupID) |
- | Max(AccountID) | + | MAX(AccountID) |
- | Max(TransactionID) | + | MAX(TransactionID) |
- | Max(DivisionID) | + | MAX(DivisionID) |
| | ||
+ | |||
FROM GL | FROM GL | ||
- | Where JournalID | + | WHERE JournalID |
- | (select | + | (SELECT |
- | where EntryDateTime | + | WHERE EntryDateTime |
- | group by JournalID, DivisionID, AccountID, TransactionID | + | GROUP BY JournalID, DivisionID, AccountID, TransactionID |
- | having sum(amount) | + | HAVING SUM(amount) |
) | ) | ||
- | and EntryDateTime | + | AND EntryDateTime |
- | 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 |
- | | + | |
- | | + | |
- | | + | |
- | ' | + | ' |
- | ' | + | ' |
- | | + | |
- | | + | |
- | | + | |
- | cast(1 as bit) as IsActive, | + | CAST(1 AS bit) AS IsActive, |
- | case when @UseGLDate = 1 then EntryDateTime | + | CASE WHEN @UseGLDate = 1 THEN EntryDateTime |
- | | + | |
- | | + | |
- | cast(0 as bit) as IsTaxable, | + | CAST(0 AS bit) AS IsTaxable, |
| | ||
- | | + | |
- | | + | |
| | ||
- | | + | |
| | ||
- | | + | |
- | 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, |
- | ' | + | ' |
| | ||
- | 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, |
- | | + | |
- | cast(NULL as int) as PartID, | + | CAST(NULL AS INT) AS PartID, |
- | cast(NULL as int) as PartClassTypeID, | + | CAST(NULL AS INT) AS PartClassTypeID, |
| | ||
- | | + | |
- | cast(0 as bit) as Reconciled, | + | CAST(0 AS bit) AS Reconciled, |
- | cast(NULL as datetime) | + | CAST(NULL AS datetime) |
- | cast(NULL as int) as ReconciliationID, | + | CAST(NULL AS INT) AS ReconciliationID, |
- | cast(NULL as int) as ReconciliationClassTypeID, | + | CAST(NULL AS INT) AS ReconciliationClassTypeID, |
- | | + | |
- | | + | |
- | | + | |
- | 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, |
- | | + | |
- | 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 | + | SELECT |
- | from @OOBs | + | FROM @OOBs |
</ | </ | ||