Explanation of SQL
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.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- 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))
Version Information
- Entered : 2/2010
- Version : Control 4.0+
You could leave a comment if you were logged in.