Differences

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

Link to this comparison view

activity_groupids_with_multiple_orders [2019/01/27 11:28] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== Issue =====
 +
 +
 +
 +Viewing an activity on the Actvities Tab of an order shows GL entries that should not be associated with the order. There are multiple activities / GL entries with the same GroupID but different orders.
 +
 +
 +
 +===== Explanation of Issue =====
 +
 +
 +
 +When using Production Terminal, the GroupID field in the Ledger Table and the StartGLGroupID and EndGLGroupID fields in the Journal table are duplicated between the activities Control creates and the activities Production Terminal creates.
 +
 +
 +
 +===== Severity =====
 +
 +**Low**. User is not down.
 +
 +
 +
 +===== Resolution =====
 +
 +
 +
 +1. Run the following query to veriy this issue: If there are any results, continue to Step 2.
 +
 +
 +
 +[[code_formattsql|code_formattsql|code format="​tsql"​]]
 +
 +
 +
 +-- Identify duplicate GroupIDs
 +
 +
 +
 +-- Ignore Payments, Bill Payments, and Closeouts
 +
 +
 +
 +  SELECT StartGLGroupID
 +
 +
 +
 +  FROM
 +
 +
 +
 +   ( SELECT StartGLGroupID
 +
 +
 +
 +          , transactionid
 +
 +
 +
 +     FROM journal j WITH(NOLOCK)
 +
 +
 +
 +     WHERE StartGLGroupID IS NOT NULL AND JournalActivityType NOT IN (2,43,13)
 +
 +
 +
 +     GROUP BY StartGLGroupID,​ transactionid
 +
 +
 +
 +   ) GroupIDs
 +
 +
 +
 +  GROUP BY StartGLGroupID
 +
 +
 +
 +  HAVING COUNT(StartGLGroupID) > 1
 +
 +
 +
 +<​code>​
 +2. Update Control to version 04.50.1102.0401 or later.
 +3. Close all copies of Control and shut down the SSLIP.
 +4. Run the following query to properly set the GroupIDs:
 +[[code_formattsql|code_formattsql|code format="​tsql"​]]
 +
 +
 +
 +-- Correct re-used GroupIDs
 +
 +
 +
 +-- Will only run in SQL Server 2005, 2008, and later versions ...
 +
 +
 +
 +DECLARE @NewGlGroupIDTable Table (TransactionID int, StartGLGroupID int, NewStartGLGroupID int);
 +
 +
 +
 +DECLARE @LastGLGroupID int;
 +
 +
 +
 +DECLARE @ModifiedDate datetime;
 +
 +
 +
 +DECLARE @ModifiedByUser nvarchar(25);​
 +
 +
 +
 +SET @LastGLGroupID = (SELECT Max(StartGlGroupID) FROM Journal) + 1;
 +
 +
 +
 +SET @ModifiedDate = GETDATE();
 +
 +
 +
 +SET @ModifiedByUser = '​CyrGroupIDFix';​
 +
 +
 +
 +-- 1.  Create a temp table with the new GroupIDs
 +
 +
 +
 +INSERT INTO @NewGlGroupIDTable
 +
 +
 +
 +SELECT
 +
 +
 +
 +       ​TransactionID
 +
 +
 +
 +     , StartGLGroupid
 +
 +
 +
 +     , @LastGLGroupID +
 +
 +
 +
 +       ​ROW_NUMBER()
 +
 +
 +
 +         OVER( ORDER BY StartGLGroupID,​ TransactionID )
 +
 +
 +
 +FROM Journal
 +
 +
 +
 +WHERE StartGLGroupID ​ in (
 +
 +
 +
 +  SELECT StartGLGroupID
 +
 +
 +
 +  FROM
 +
 +
 +
 +   ( SELECT StartGLGroupID
 +
 +
 +
 +          , Transactionid
 +
 +
 +
 +     FROM Journal J WITH(NOLOCK)
 +
 +
 +
 +     WHERE StartGLGroupID IS NOT NULL AND JournalActivityType NOT IN (2,43,13)
 +
 +
 +
 +     GROUP BY StartGLGroupID,​ TransactionID
 +
 +
 +
 +   ) GroupIDs
 +
 +
 +
 +  GROUP BY StartGLGroupID
 +
 +
 +
 +  HAVING COUNT(StartGLGroupID) > 1
 +
 +
 +
 +)
 +
 +
 +
 +GROUP BY StartGLGroupID,​ TransactionID
 +
 +
 +
 +-- 2.  UPDATE The Journal Table
 +
 +
 +
 +UPDATE Journal
 +
 +
 +
 +SET   ​StartGLGroupID = Temp.NewStartGLGroupID
 +
 +
 +
 +    , EndGLGroupID = Temp.NewStartGLGroupID + ( J.EndGLGroupID - J.StartGLGroupID )
 +
 +
 +
 +    , ModifiedByUser = @ModifiedByUser
 +
 +
 +
 +    , ModifiedDate = @ModifiedDate
 +
 +
 +
 +FROM Journal J
 +
 +
 +
 +  LEFT OUTER JOIN @NewGlGroupIDTable Temp
 +
 +
 +
 +     ON J.StartGLGroupID = Temp.StartGLGroupID AND
 +
 +
 +
 +        J.TransactionID = Temp.TransactionID
 +
 +
 +
 +WHERE Temp.StartGLGroupID IS NOT NULL
 +
 +
 +
 +-- 3.  UPDATE The Ledger Table using the Journal
 +
 +
 +
 +UPDATE LEDGER
 +
 +
 +
 +SET   ​GroupID = J.StartGLGroupID
 +
 +
 +
 +    , ModifiedByUser = @ModifiedByUser
 +
 +
 +
 +    , ModifiedDate = @ModifiedDate
 +
 +
 +
 +FROM Ledger GL
 +
 +
 +
 +  LEFT OUTER JOIN Journal J ON J.ID = GL.JournalID
 +
 +
 +
 +WHERE J.ModifiedByUser = @ModifiedByUser
 +
 +
 +
 +  AND GL.ModifiedByUser ​ @ModifiedByUser
 +
 +
 +
 +<​code>​
 +5. Start the SSLIP.
 +===== Version Information =====
 +  * Reported : 1__/​21__/​2011
 +  * Version(s): 04.50.1101.2101
 +  * Fixed in : 04.50.1102.0401