Explanation of SQL
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.
Risk of Data Corruption if Run Improperly
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.
SQL
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
Version Information
- Entered : //2009
- Version :
You could leave a comment if you were logged in.