{$page}

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.

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

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

• Backlinks include_pagepage_componentbacklinks