Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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**.  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+