Table of Contents

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:

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