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)
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
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, you can modify them with this query.
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
Version Information
- Entered : 7/2012
- Version : 4.6+