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.

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

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

• Entered : 7/31/2004, Brandon Readlinger (Cyrious)
• Version : All versions of Control