This query compares the the Summary Journal for each payment to the GL record of it. Note that not all records indicated an error, as manual GL entries may show up in this report (and are not incorrect).

Note: This report can take a long time to run (1-5 minutes).

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

Set the @StartDate variable to the begining time you want to run the comparison. For speed reasons, it is not recommended that you run this more than 2 years in one query.

DECLARE @StartDate DateTime;
SET @StartDate='1/1/2008';
 
SELECT  
    (SELECT PrintAsName AS Division 
        FROM EmployeeGroup 
        WHERE A.DivisionID = EmployeeGroup.ID) AS Division,
    TransHeader.OrderNumber AS OrderNumber,
     Account.CompanyName AS CompanyName,
    B.JournalDate,
    A.GLDate,
    A.GLAmount AS GLPayment,
    B.JournalAmount AS JournalPayment,
    A.GLAmount - B.JournalAmount AS Difference,
    A.GLGroupID,
    Account.ID AS AccountID
 
FROM
(
    SELECT    COALESCE(DivisionID, 10) AS DivisionID, 
        AccountID GLAccountID, 
        GroupID AS GLGroupID,
        TransactionID AS GLTHID,
        GL.EntryDateTime AS GLDate,
        SUM(Amount) GLAmount
    FROM GL
    WHERE GLClassificationType IN (1000, 1007, 2001)
      AND GL.EntryDateTime >= @StartDate
    GROUP BY COALESCE(DivisionID, 10), AccountID, GroupID, TransactionID, EntryDateTime
    HAVING SUM(Amount) <> 0 AND (AccountID IS NOT NULL)
) AS A
FULL JOIN
(
    SELECT AccountID AS JAccountID, 
        StartDateTime AS JournalDate,
        DetailAmount AS JournalAmount,
        StartGLGroupID,
        EndGLGroupID,
        TransactionID AS JTHID
    FROM Journal
    WHERE ClassTypeID = 20001 -- AND DetailAmount <> 0
      AND Journal.StartDateTime >= @StartDate
) AS B ON A.GLGroupID BETWEEN B.StartGLGroupID AND B.EndGLGroupID
 
LEFT JOIN Account ON Account.ID = COALESCE(JAccountID, GLAccountID)
 
LEFT JOIN TransHeader ON TransHeader.ID = COALESCE(JTHID, GLTHID)
 
WHERE COALESCE(B.JournalAmount, 0) <> COALESCE(A.GLAmount, 0)
    AND COALESCE(B.JournalAmount, 0) <> COALESCE(-A.GLAmount, 0)
 
ORDER BY Division, JournalDate, CompanyName, GLAccountID
  • Entered : 6/25/2009
  • Version : Control 4.3
You could leave a comment if you were logged in.