Differences

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

Link to this comparison view

control_sql_-_identify_imbalances_in_make_deposit_entries [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== 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 =====
 +
 +
 +
 +<code 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
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 9/2010
 +  * Version : 4.4+
 +
 +
 +