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+