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.

None. This is a selection query and no data is modified in the running of it.

-- 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
  • Entered : 9/2010
  • Version : 4.4+
You could leave a comment if you were logged in.