===== 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+