Table of Contents

{$page}

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.

code_formatsql

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

code

code_formatsql

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

code

Version Information