Table of Contents

Explanation of SQL

This query will help you locate discrepancies between the finance charge amount specified on the order vs. what is actually posted to the general ledger.

Risk of Data Corruption if Run Improperly

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

SQL

This sql will pull the finance charge account automatically specified under setup | accounting setup | default accounts setup. 

DECLARE @FinanceChargeAccountID INT;
SET @FinanceChargeAccountID = (SELECT TOP 1 FinanceChargeAccountID FROM Store WITH(NOLOCK) WHERE ID > 0);
-- This section locates every order and compares the amount in finance charges to the amount in the GL.
SELECT     
OrderNumber
, T.ID AS TransHeaderID
, CompanyName
, SaleDate
, FinanceChargeAmount
, ISNULL(
	(SELECT -SUM(GL.Amount) FROM GL WITH(NOLOCK) WHERE TransActionID = T.ID	AND GL.GLAccountID = @FinanceChargeAccountID
		),0) AS GLFinanceChargeAmt
, FinanceChargeAmount + 
	ISNULL(
	(SELECT -SUM(GL.Amount) FROM GL WITH(NOLOCK) WHERE TransActionID = T.ID	AND GL.GLAccountID = @FinanceChargeAccountID
		),0) AS Delta
FROM TransHeader T WITH(NOLOCK)
LEFT JOIN Account A WITH(NOLOCK) ON A.ID = T.AccountID
WHERE TransActionType IN (1,6)
GROUP BY T.OrderNumber, T.ID, T.SaleDate, T.FinanceChargeAmount, CompanyName
HAVING ISNULL(
	(SELECT -SUM(GL.Amount) FROM GL WITH(NOLOCK) WHERE TransActionID = T.ID	AND GL.GLAccountID = @FinanceChargeAccountID
		),0)  ISNULL(T.FinanceChargeAmount,9999999)
UNION 
-- This section locates any gl entries that do not pertain to an order. 
SELECT
NULL AS OrderNumber
, NULL AS TransHeaderID
, CompanyName
, NULL AS SaleDate
, NULL AS FinanceChargeAmount
, -SUM(GL.Amount) AS GLFinanceChargeAmt
, -SUM(GL.Amount) AS Delta
FROM GL WITH(NOLOCK)
LEFT JOIN Account A WITH(NOLOCK) ON A.ID = GL.AccountID
WHERE GL.GLAccountID = @FinanceChargeAccountID AND TransActionID IS NULL
GROUP BY CompanyName
ORDER BY T.OrderNumber
DECLARE @FinanceChargeAccountID INT;
SET @FinanceChargeAccountID = (SELECT TOP 1 FinanceChargeAccountID FROM Store WITH(NOLOCK) WHERE ID > 0);
--Query to identify all finance charges not linked to orders in a more detailed view so you can see each individual entry.
SELECT
CompanyName
, TransactionID
, Description
, GL.Amount AS GLFinanceChargeAmt
FROM GL WITH(NOLOCK)
LEFT JOIN Account A WITH(NOLOCK) ON A.ID = GL.AccountID
WHERE GL.GLAccountID = 41 AND TransActionID IS NULL

Version Information