Differences
This shows you the differences between two versions of the page.
control_sql_-_plug_all_ar_balances_for_orders_not_in_sale [2019/01/27 11:29] 127.0.0.1 external edit |
control_sql_-_plug_all_ar_balances_for_orders_not_in_sale [2019/02/05 11:27] (current) admin |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== | ||
- | |||
- | |||
- | |||
===== Explanation of SQL ===== | ===== Explanation of SQL ===== | ||
Line 32: | Line 28: | ||
<code sql> | <code sql> | ||
-- Invalid A/R Records Repair | -- Invalid A/R Records Repair | ||
+ | |||
-- User Defined Values | -- User Defined Values | ||
DECLARE @StartDate DateTime; | DECLARE @StartDate DateTime; | ||
Line 38: | Line 35: | ||
DECLARE @AltGLDate DateTime; | DECLARE @AltGLDate DateTime; | ||
DECLARE @PlugGLAccountID INT; | DECLARE @PlugGLAccountID INT; | ||
+ | |||
SET @PlugGLAccountID = ___; -- Update the Plug Account | SET @PlugGLAccountID = ___; -- Update the Plug Account | ||
SET @StartDate = ' | SET @StartDate = ' | ||
Line 43: | Line 41: | ||
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 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 | @PlugGLClassTypeName | ||
Line 53: | Line 53: | ||
; | ; | ||
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( | ||
Line 64: | Line 66: | ||
JournalID INT | JournalID INT | ||
); | ); | ||
+ | |||
INSERT INTO @OOBs | 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. | -- 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 | SELECT | ||
- | StartDateTime | + | StartDateTime |
, T.Amount | , T.Amount | ||
- | , J.StartGLGroupID | + | , J.StartGLGroupID |
, J.AccountID | , J.AccountID | ||
- | , ISNULL(T.TransActionID, | + | , ISNULL(T.TransActionID, |
, J.DivisionID | , J.DivisionID | ||
, T.JournalID | , T.JournalID | ||
Line 77: | Line 80: | ||
-- We are pulling the last JournalID that has a AR entry on it and also the sum of the AR balance for that transaction. | -- 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 | SELECT | ||
- | MAX(JournalID) | + | MAX(JournalID) |
- | , SUM(Amount) | + | , SUM(Amount) |
, TransActionID | , TransActionID | ||
FROM Ledger GL WITH(NOLOCK) | FROM Ledger GL WITH(NOLOCK) | ||
Line 88: | Line 91: | ||
FROM Ledger GL WITH(NOLOCK) | FROM Ledger GL WITH(NOLOCK) | ||
LEFT JOIN TransHeader T WITH(NOLOCK) ON T.ID = GL.TransactionID | LEFT JOIN TransHeader T WITH(NOLOCK) ON T.ID = GL.TransactionID | ||
- | WHERE GLAccountID = 14 AND T.StatusID | + | WHERE GLAccountID = 14 AND T.StatusID |
AND ISNULL(OrderCreatedDate, | AND ISNULL(OrderCreatedDate, | ||
GROUP BY TransActionID | GROUP BY TransActionID | ||
- | HAVING SUM(Amount) | + | HAVING SUM(Amount) |
) | ) | ||
AND GLAccountID = 14 | AND GLAccountID = 14 | ||
Line 99: | Line 102: | ||
ORDER BY EntryDateTime | ORDER BY EntryDateTime | ||
; | ; | ||
+ | |||
-- Now create the balancing A/R record | -- Now create the balancing A/R record | ||
-- and insert it into the GL | -- and insert it into the GL | ||
+ | |||
INSERT INTO Ledger | INSERT INTO Ledger | ||
([ID] | ([ID] | ||
Line 213: | Line 218: | ||
FROM @OOBs T | FROM @OOBs T | ||
; | ; | ||
+ | |||
-- Now create the plug record (so we don't make it off balance) | -- 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 | -- and insert it into the GL. But first we have to adjust the GLID | ||
+ | |||
DECLARE @Offset INT; | DECLARE @Offset INT; | ||
SET @Offset = (SELECT COUNT(*) FROM @OOBs); | SET @Offset = (SELECT COUNT(*) FROM @OOBs); | ||
+ | |||
INSERT INTO Ledger | INSERT INTO Ledger | ||
([ID] | ([ID] | ||
Line 329: | Line 337: | ||
FROM @OOBs T | FROM @OOBs T | ||
; | ; | ||
+ | |||
-- and show the output | -- and show the output | ||
SELECT @MaxGLID + ID AS GLIDAR, @MaxGLID + @Offset + ID AS GLIDPlug, * | SELECT @MaxGLID + ID AS GLIDAR, @MaxGLID + @Offset + ID AS GLIDPlug, * |