====== {$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 =====
  * Entered : __7/31/2004, Brandon Readlinger (Cyrious)__
  * Version : All versions of Control



===== Related SQLs =====
  * Backlinks [[include_pagepage_componentbacklinks]]