Activity GroupIDs With Multiple Orders

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.


-- 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

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:


-- 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

5. Start the SSLIP.

Version Information

  • Reported : 1/21/2011
  • Version(s): 04.50.1101.2101
  • Fixed in : 04.50.1102.0401

See Also