Explanation of SQL
When funds are transferred from Undeposited Funds into a bank account via Make Deposits, Control posts the transfer and tags all the deposited funds with the DepositJournalID of the resulting transfer.
This query compares the GL records of funds deposited with the SummaryAmount in the Journal and the GL record of the transfer and identifiesa any problems.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- identify unbalanced Make Deposits (New) DECLARE @StartDate SmallDateTime = '1/1/2015'; DECLARE @EndDate SmallDateTime = '1/1/2016'; ; DECLARE @DepositIDs TABLE (DepositID INT PRIMARY KEY, GLDifference money, GLPaymentsSum money, GLLastPaymentDate DateTime, GLDepositAmount money, GLDepositEntryDate DateTime, GLDepositModifiedDate DateTime, GLDepositReconciled INT, GLDepositGLAccountID INT, GLDepositGLAccountName VARCHAR(50), GLDepositClassificationType INT, JournalDepositAmount money, JournalDepositDate DateTime, JournalModifiedDate DateTime ) INSERT INTO @DepositIDs (DepositID) SELECT DepositJournalID -- , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM ( SELECT DISTINCT DepositJournalID FROM GL WHERE DepositJournalID IS NOT NULL AND GLClassificationType=1007 AND EntryDateTime BETWEEN @StartDate AND @EndDate UNION SELECT DISTINCT DepositJournalID FROM Payment WHERE DepositJournalID IS NOT NULL AND ID IN (SELECT ID FROM Journal WHERE CompletedDateTime BETWEEN @StartDate AND @EndDate) UNION SELECT ID FROM Journal WHERE ClassTypeID = 20036 AND CompletedDateTime BETWEEN @StartDate AND @EndDate ) Tempa ; -- -- Compute the amount of each deposit by summing up the payments in each deposit -- UPDATE d SET GLPaymentsSum = GLSum.GLPaymentSum, GLLastPaymentDate = GLSum.GLLastPaymentDate FROM @DepositIDs d JOIN (SELECT DepositJournalID, SUM(Amount) AS GLPaymentSum, MAX(EntryDateTime) AS GLLastPaymentDate FROM GL WHERE GL.GLClassificationType = 1007 AND EntryDateTime BETWEEN @StartDate AND @EndDate GROUP BY DepositJournalID) AS GLSum ON GLSum.DepositJournalID = d.DepositID ; -- -- Retrieve the amount made in each deposit from the GL -- UPDATE d SET d.GLDepositAmount = Deposit.GLDepositAmount, d.GLDepositEntryDate = Deposit.GLDepositEntryDate, d.GLDepositModifiedDate = Deposit.GLDepositModifiedDate, d.GLDepositGLAccountID = Deposit.GLDepositGLAccountID, d.GLDepositGLAccountName = (SELECT AccountName FROM GLAccount WHERE ID = Deposit.GLDepositGLAccountID), d.GLDepositClassificationType = Deposit.GLDepositClassificationType FROM @DepositIDs d JOIN (SELECT JournalID, SUM(-Amount) AS GLDepositAmount, MAX(EntryDateTime) AS GLDepositEntryDate, MAX(ModifiedDate) AS GLDepositModifiedDate, MAX(GLAccountID) AS GLDepositGLAccountID, MAX(GLClassificationType) AS GLDepositClassificationType FROM GL WHERE GL.GLClassificationType = 1007 AND EntryDateTime BETWEEN @StartDate AND @EndDate GROUP BY JournalID) AS Deposit ON Deposit.JournalID = d.DepositID ; -- -- Retrieve the amount made in each deposit from the Journal table -- UPDATE d SET d.JournalDepositAmount = J.JournalDepositAmount, d.JournalDepositDate = J.JournalDepositDate, d.JournalModifiedDate = J.JournalModifiedDate FROM @DepositIDs d JOIN (SELECT ID, SummaryAmount AS JournalDepositAmount, CompletedDateTime AS JournalDepositDate, ModifiedDate AS JournalModifiedDate FROM Journal WHERE CompletedDateTime BETWEEN @StartDate AND @EndDate ) AS J ON J.ID = d.DepositID ; -- -- Fill in some more values -- UPDATE @DepositIDs SET GLDifference = COALESCE(GLPaymentsSum,0) - COALESCE(GLDepositAmount,0) ; UPDATE d SET GLDepositReconciled = (SELECT MAX(COALESCE(CAST(Reconciled AS INT),0)) FROM GL WHERE JournalID = d.DepositID) FROM @DepositIDs d ; -- -- Now identify any problems -- SELECT * FROM @DepositIDs WHERE GLDifference 0 OR (COALESCE(GLPaymentsSum,0) COALESCE(JournalDepositAmount,0)) ORDER BY GLDepositGLAccountName, DepositID DESC
Version Information
- Entered : 9/2010
- Version : 4.4+
You could leave a comment if you were logged in.