A bug existed in an earlier version of Control 4.5 (fixed around March 2011) where the DepositJournalID was not recorded in the GL on overpayments. However, since the Payment record is known (the JournalID), this information can be retrieved from there.

This query is needed before running control_sql_-_identify_imbalances_in_make_deposit_entries.

Minor. This query, as written, does alter data. However, it should not affect any data that is not wrong and therefore can be run repeatedly on good data with no affect.

The SQL to view the erroneous GL entries is:

SELECT * -- GL.ID as GLID, GL.Amount as GLAmount, GL.EntryDateTime as GLDate, GL.MakeDepositID
FROM GL 
JOIN Payment P ON P.ID = GL.JournalID
WHERE GLClassificationType = 1007 AND GL.DepositJournalID IS NULL AND P.DepositJournalID IS NOT NULL
ORDER BY GL.id DESC

The SQL to fix the erroneous GL entries is:

UPDATE GL
SET DepositJournalID = (SELECT P.DepositJournalID FROM Payment P WHERE P.ID = GL.JournalID)
WHERE DepositJournalID IS NULL AND JournalID IN (SELECT ID FROM Payment WHERE DepositJournalID IS NOT NULL)
  AND GLClassificationType = 1007

• Entered : //2009
• Version :