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]
 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 :
 +
 +
 +