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.

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.

Low. User is not down.

1. Run the following query to veriy this issue: If there are any results, continue to Step 2.

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

  • Reported : 1/21/2011
  • Version(s): 04.50.1101.2101
  • Fixed in : 04.50.1102.0401
You could leave a comment if you were logged in.