Differences
This shows you the differences between two versions of the page.
— |
control_sql_-_queries_to_search_for_reconciliation_problems [2019/02/05 11:39] (current) admin created |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ===== Explanation of SQL ===== | ||
+ | These queries are used to identify payments that are not included in the Reconciliation but should be. | ||
+ | |||
+ | |||
+ | ===== Risk of Data Corruption if Run Improperly ===== | ||
+ | |||
+ | **High**. | ||
+ | |||
+ | |||
+ | |||
+ | ===== SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | This SQL is used to identify // | ||
+ | * The SeqID will be 2 or greater (created, reconcile edit, reconciliation lost edit) | ||
+ | * The ModifiedDate will be almost the same as the reconciliation journal' | ||
+ | * These will be bill payment entries. | ||
+ | |||
+ | |||
+ | |||
+ | ===SQL to Pull Reconciliations (for reference)=== | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | SELECT ID AS ReconciliationID, | ||
+ | LinkID AS GLAccountID, | ||
+ | (SELECT AccountName FROM GLAccount WHERE ID = LinkID) AS AccountName, | ||
+ | ModifiedDate, | ||
+ | EndDateTime, | ||
+ | MONTH(StartDateTime) AS StartMonth, DAY(StartDateTime) AS StartDay, YEAR(StartDateTime) AS StartYear, | ||
+ | MONTH(EndDateTime) AS EndMonth, DAY(EndDateTime) AS EndDay, YEAR(EndDateTime) AS EndYear, | ||
+ | SummaryAmount AS StartingBalance, | ||
+ | DetailAmount AS EndingBalance, | ||
+ | SummaryAmount - DetailAmount AS ChangeInBalance, | ||
+ | UseActualTime AS InProgress, | ||
+ | CAST(BillingNotes AS XML) AS OtherCharges | ||
+ | FROM Journal | ||
+ | WHERE ClassTypeID = 9750 | ||
+ | -- AND LinkID = 6214 -- PUT the GLAccountID of the Bank Account Here | ||
+ | ORDER BY ModifiedDate DESC | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===SQL to Pull Potential Mis-Reconciled GL Entries=== | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | SELECT top 1000 * | ||
+ | FROM GL | ||
+ | WHERE GLAccountID = 6214 -- PUT the GLAccountID of the Bank Account Here | ||
+ | AND SeqID > 1 AND ReconciliationID IS NULL | ||
+ | AND Description LIKE 'Bill Pay%' | ||
+ | ORDER BY ModifiedDate DESC | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===SQL to Fix Mis-Reconciled GL Entries=== | ||
+ | |||
+ | |||
+ | |||
+ | Once you have clearly identified that a certain date range of GL Entries identified above should belong to a specific Reconcilliation, | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | UPDATE Ledger | ||
+ | SET ReconciliationID = 247376, | ||
+ | ReconciliationDateTime = ' | ||
+ | ReconciliationClassTypeID = 9750, | ||
+ | Reconciled = 1 | ||
+ | WHERE GLAccountID = 6214 -- PUT the GLAccountID of the Bank Account Here | ||
+ | AND SeqID > 1 AND ReconciliationID IS NULL | ||
+ | AND Description LIKE 'Bill Pay%' | ||
+ | AND ModifiedDate BETWEEN ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 7/2012 | ||
+ | * Version : 4.6+ |