Differences

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

Link to this comparison view

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]
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 = '1/1/2000'; -- Adjust dates to set specific timeframe of when orders were created. SET @StartDate = '1/1/2000'; -- Adjust dates to set specific timeframe of when orders were created.
Line 43: Line 41:
 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 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 as EntryDateTime+StartDateTime AS EntryDateTime
 , T.Amount , T.Amount
-, J.StartGLGroupID as GroupID+, J.StartGLGroupID AS GroupID
 , J.AccountID , J.AccountID
-, ISNULL(T.TransActionID,99) as TransActionID+, ISNULL(T.TransActionID,99) AS 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) as JournalID +    MAX(JournalID) AS JournalID 
-    , SUM(Amount) as Amount+    , SUM(Amount) AS 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  3+        WHERE GLAccountID = 14 AND T.StatusID <> 3
         AND ISNULL(OrderCreatedDate,@StartDate) BETWEEN @StartDate AND @EndDate         AND ISNULL(OrderCreatedDate,@StartDate) BETWEEN @StartDate AND @EndDate
         GROUP BY TransActionID         GROUP BY TransActionID
-        HAVING SUM(Amount)  0+        HAVING SUM(Amount) <> 0
         )         )
     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, *