...
Payments report will provide a list of all payments in the system, as well the Credits report will provide any credits on accounts.
| Info |
|---|
Note: Make sure to exclude deposits from this report when reconciling against Aged reports. |
How Much Money Is Owed?
There are multiple balance reports that can be used. Aged A/R, Deferred Revenue, and Taxes Payable will provide details.
The reports below will have specific equal values when they are generated with the same date range:
Reconciliation Equations
| Info |
|---|
Note: The content below assumes no grouping and is filtered by date only using the same AS OF dates for each. |
Format:
Capitalized words = Report name
Uncapitilized Uncapitalized words = column names
TAXES PAYABLE total tax = SALES total tax
BALANCE total = SALES charge + total tax - PAYMENTS total (with Deposit = false filter)
CHARGE total (Sales report) + TAX total (sales report) AMOUNT total (Payments report, posting date to be used - with Deposits removed) = AR total (as of same date)
An SQL query that can be run as well here is this:
| Code Block |
|---|
DECLARE @periodstart datetime DECLARE @periodend datetime DECLARE @agedstartbalance DECIMAL DECLARE @agedendbalance DECIMAL DECLARE @sales DECIMAL DECLARE @taxes DECIMAL DECLARE @payments DECIMAL DECLARE @difference nvarchar(50)SET @periodstart = '2009-2-1' SET @periodend = '2010-1-1' EXEC [boss].[dbo].logisense_boss_aged @periodstart, 30, 30, 30, 30 SELECT @agedstartbalance = SUM(balance) FROM [boss].[dbo].viewagedreport EXEC [boss].[dbo].logisense_boss_aged @periodend, 30, 30, 30, 30 SELECT @agedendbalance = SUM(balance) FROM [boss].[dbo].viewagedreport SELECT @sales = SUM(charge) FROM [boss].[dbo].viewreportsalebyservice WHERE DATE >= @periodstart AND DATE < @periodend SELECT @taxes = SUM(tax) FROM [boss].[dbo].viewreportsalebyservice WHERE DATE >= @periodstart AND DATE < @periodend SELECT @payments = SUM(amount) FROM [boss].[dbo].viewpayment WHERE deposit = 0 AND DATE >= @periodstart AND DATE < @periodend SET @difference = @agedstartbalance + @sales + @taxes - @payments - @agedendbalance SELECT @difference |
...
Below is the SQL query that can be setup as a job and emailed for monthly reconciliation.
| Info |
|---|
Note: that this below query is fully based on POSTINGDATE as opposed to just DATE. You need to ensure your Aged Report view is updated to use posting date here |
| Code Block | ||
|---|---|---|
| ||
DECLARE @periodstart datetime DECLARE @periodend datetime DECLARE @agedstartbalance money DECLARE @agedendbalance money DECLARE @sales money DECLARE @taxes money DECLARE @payments money DECLARE @difference nvarchar(50) SET @periodstart = '2013-6-1' SET @periodend = '2013-7-1' EXEC [engageip].[dbo].logisense_boss_aged @periodstart, 30, 30, 30, 30 SELECT @agedstartbalance = SUM(balance) FROM [engageip].[dbo].viewagedreport EXEC [engageip].[dbo].logisense_boss_aged @periodend, 30, 30, 30, 30 SELECT @agedendbalance = SUM(balance) FROM [engageip].[dbo].viewagedreport SELECT @sales = SUM(charge) FROM [engageip].[dbo].viewreportsalebyservice WHERE PostingDate >= @periodstart AND postingdate < @periodend SELECT @taxes = SUM(tax) FROM [engageip].[dbo].viewreportsalebyservice WHERE postingdate >= @periodstart AND postingdate < @periodend SELECT @payments = SUM(amount) FROM [engageip].[dbo].viewpayment WHERE deposit = 0 AND PostingDate >= @periodstart AND postingdate < @periodend SET @difference = @agedstartbalance + @sales + @taxes - @payments - @agedendbalance SELECT @agedstartbalance AS 'AgedStartBalance plus' , @sales AS 'Sales plus' , @taxes AS 'Taxes minus' , @payments AS 'Payments minus' , @agedendbalance AS 'AgedEndBalance equals', @difference AS AgedDifference |
Code for the automated job looks like this (the below code does not include the posting date change). To use it, create an SQL job, setup your SQL mail configuration and use this to send it out weekly, or monthly as required.
| Code Block | ||
|---|---|---|
| ||
EXEC msdb.dbo.sp_send_dbmail @recipients=N'test@logisense.com;test1@logisense.com',@body='This is the monthly reconciliation report >>>', @subject ='Scheduled Report Reconciliation',@profile_name ='Mail Sendout',@query = "USE engageip USE engageip declare @periodstart datetime declare @periodend datetime DECLARE @Today datetime SELECT @Today = getdate() SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,-1,@Today)) SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today)) --set @periodstart = SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today)) -- first date of period --set @periodend = '2012-12-1' -- first day of next monthh select @periodstart = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today)) -- first date of period select @periodend = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today)) exec [engageip].[dbo].logisense_boss_aged @periodstart, 30, 30, 30, 30 declare @agedstartbalance money select @agedstartbalance = sum(balance) from [engageip].[dbo].viewagedreport exec [engageip].[dbo].logisense_boss_aged @periodend, 30, 30, 30, 30 declare @agedendbalance money select @agedendbalance = sum(balance) from [engageip].[dbo].viewagedreport declare @sales money select @sales = sum(charge) from viewreportsalebyservice where date >= @periodstart and date < @periodend declare @taxes money select @taxes = sum(tax) from viewreportsalebyservice where date >= @periodstart and date < @periodend declare @payments money select @payments = sum(amount) from viewpayment where deposit = 0 and date >= @periodstart and date < @periodend declare @difference smallmoney set @difference = @agedstartbalance + @sales + @taxes - @payments - @agedendbalance select @agedstartbalance as AgedStart,@sales as Sales,@taxes as Taxes,@payments as Payments,@agedendbalance as AgedEnd,@Difference as [Difference] ", @attach_query_result_as_file = 0 |
...