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 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.
Version Information
- Reported : 1/21/2011
- Version(s): 04.50.1101.2101
- Fixed in : 04.50.1102.0401