This query looks at each order and each journal entry and makes sure each is in balance according to the rules of the balance sheet. Any orders or journal entries that are not are listed.

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

-- Find any journal entry that is our of balance by the financial summary (even if sums to zero)
 
DECLARE @StartDate datetime;
DECLARE @EndDate datetime;
 
SET @StartDate = '12/1/2009';
SET @EndDate = '12/31/2009 23:59:59';
 
SELECT SUM(AssetAmount) AS AssetAmount,
       SUM(LiabilityAmount) AS LiabilityAmount,
       SUM(EquityAmount) AS EquityAmount,
       SUM(PandLAmount) AS PandLAmount,
       (SUM(AssetAmount) + SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount)) AS Delta,
       JournalID,
       (SELECT StartDateTime FROM Journal WHERE ID = JournalID) AS JournalDate,
       NULL AS TransactionID,
       NULL AS OrderNumber
 
FROM
(
  SELECT 
     TransactionID,
     JournalID,
     (CASE WHEN GLClassificationType BETWEEN 1000 AND 1999 THEN 'Asset'
           WHEN GLClassificationType BETWEEN 2000 AND 2999 THEN 'Liability'
           WHEN GLClassificationType BETWEEN 3000 AND 3999 THEN 'EQuity'
      ELSE 'P&L'
      END
     ) AS AccountType,
     CASE WHEN GLClassificationType BETWEEN 1000 AND 1999 THEN Amount ELSE 0 END AS AssetAmount,
     CASE WHEN GLClassificationType BETWEEN 2000 AND 2999 THEN Amount ELSE 0 END AS LiabilityAmount,
     CASE WHEN GLClassificationType BETWEEN 3000 AND 3999 THEN Amount ELSE 0 END AS EquityAmount,
     CASE WHEN GLClassificationType BETWEEN 4000 AND 7999 THEN Amount ELSE 0 END AS PandLAmount
  FROM GL
  WHERE EntryDateTime BETWEEN @StartDate AND @EndDate
    AND TransactionID IS NULL
) AS BalanceSheet
 
GROUP BY JournalID, TransactionID
HAVING SUM(AssetAmount) <> -(SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount))
 
 
UNION ALL
 
-- Find any order that is our of balance by the financial summary (even if sums to zero)
 
SELECT SUM(AssetAmount) AS AssetAmount,
       SUM(LiabilityAmount) AS LiabilityAmount,
       SUM(EquityAmount) AS EquityAmount,
       SUM(PandLAmount) AS PandLAmount,
       (SUM(AssetAmount) + SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount)) AS Delta,
       NULL AS JournalID,
       NULL AS JournalDate,
       TransactionID,
       (SELECT COALESCE(OrderNumber, BillNumber) FROM TransHeader WHERE ID = TransactionID) AS TransHeaderNumber
FROM
(
  SELECT 
     TransactionID,
     JournalID,
     (CASE WHEN GLClassificationType BETWEEN 1000 AND 1999 THEN 'Asset'
           WHEN GLClassificationType BETWEEN 2000 AND 2999 THEN 'Liability'
           WHEN GLClassificationType BETWEEN 3000 AND 3999 THEN 'EQuity'
      ELSE 'P&L'
      END
     ) AS AccountType,
     CASE WHEN GLClassificationType BETWEEN 1000 AND 1999 THEN Amount ELSE 0 END AS AssetAmount,
     CASE WHEN GLClassificationType BETWEEN 2000 AND 2999 THEN Amount ELSE 0 END AS LiabilityAmount,
     CASE WHEN GLClassificationType BETWEEN 3000 AND 3999 THEN Amount ELSE 0 END AS EquityAmount,
     CASE WHEN GLClassificationType BETWEEN 4000 AND 7999 THEN Amount ELSE 0 END AS PandLAmount
  FROM GL
  WHERE EntryDateTime BETWEEN @StartDate AND @EndDate
        AND TransactionID IS NOT NULL
) AS BalanceSheet
 
GROUP BY TransactionID, JournalID
HAVING SUM(AssetAmount) <> -(SUM(LiabilityAmount) + SUM(EquityAmount) + SUM(PandLAmount))
  • Entered : 2/2010
  • Version : Control 4.0+
You could leave a comment if you were logged in.