Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ===== 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**. If you use the fix queries below, data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL **are permanent and unreversable**. ===== SQL ===== This SQL is used to identify //POSSIBLE// entries that should belong to a reconciliation but do not have the ReconcilliationID set. It uses the following logic to search for them: * The SeqID will be 2 or greater (created, reconcile edit, reconciliation lost edit) * The ModifiedDate will be almost the same as the reconciliation journal's modified date * 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 </code> ===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 </code> ===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, you can modify them with this query. <code sql> UPDATE Ledger SET ReconciliationID = 247376, -- PUT the ReconcilliationID here ReconciliationDateTime = '2012-07-12 17:47:39.000', -- PUT the Recon Time here 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 '7/12/2012 17:47' AND '7/12/2012 17:48' -- PUT The Range here </code> ===== Version Information ===== * Entered : 7/2012 * Version : 4.6+