Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_query_to_fix_gl_entries_without_depositjournalid [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== 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:
 +
 +
 +
 +<code sql>
 +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
 +</​code>​
 +
 +
 +
 +The SQL to fix the erroneous GL entries is:
 +
 +
 +
 +<code sql>
 +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
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : __/__/2009
 +  * Version :
 +
 +
 +