...
The deferred revenue will report the following with the date as of shown below:
Date | Deferred Revenue Value |
1/1/2008 | $120 |
2/1/2008 | $110 (1 month service rendered, 11 months deferred) |
6/1/2008 | $60 (6 months service rendered and recognized, 6 months service deferred) |
7/1/2008 | $50 |
1/1/2009 | $0 (12 months of service have been 100% rendered, 100% of the value of the service is recognized) |
The deferred revenue report as of April 1, 2008, should show as below:
Owner | Account | User Package | Start Charge Date | Next Charge Date | Charged Amount | Total Revenue Recognized to date | Revenue Recognized Current period | Yet to be recognized AKA Deferred Revenue |
Acme | bsmith | Wifi | 1/1/2008 | 1/1/2009 | 120 | 30 | 10 | 90 |
Scenario 2) Customer BSmith signs up for a yearly package at $120 ($10 a month) and decides to cancel the service as of 6/1/2008 (Prorated Refund is provided)
Date | Deferred Revenue Value |
| $120 |
2/1/2008 | $110 ( 1 month service rendered, 11 months deferred) |
6/1/2008 | $0 (6 months service rendered and recognized, 6 months of service has been refunded back, no more deferred revenue) |
7/1/2008 | $0 |
1/1/2009 | $0 |
The deferred revenue report as of June 1, 2008, should show as below:
Owner | Account | User Package | Start Charge Date | Next Charge Date | Charged Amount | Total Revenue Recognized to date | Revenue Recognized Current period | Yet to be recognized AKA Deferred Revenue |
Acme | bsmith | Wifi | 1/1/2008 | 1/1/2009 | 120 | 60 | 0 | 0 |
Scenario 3) Customer BSmith signs up for a yearly package at $120 ($10 a month) and decides to cancel the service as of 6/1/2008 (No Refund is provided)
Date | Deferred Revenue Value |
1/1/2008 | $120 |
2/1/2008 | $110 ( 1 month service rendered, 11 months deferred) |
6/1/2008 | $60 (6 months service rendered and recognized, 6 months service deferred) |
7/1/2008 | $0 (No services are to be rendered, service is cancelled, deferred revenue is $0) |
1/1/2009 | $0 |
The deferred revenue report as of June 1, 2008, should show as below:
Owner | Account | User Package | Start Charge Date | Next Charge Date | Charged Amount | Total Revenue Recognized to date | Revenue Recognized Current period | Yet to be recognized AKA Deferred Revenue |
Acme | bsmith | Wifi | 1/1/2008 | 1/1/2009 | 120 | 60 | 60 | 0 |
Second report:
Monthly Billing
Amount Deferred Current period | Deferred Amount recognized current period | Balance Remaining to be recognized
Automated Delivery Config Example:
...
Create SQL job
Paste code below
Update code as needed
Ensure Database mail is setup and referenced in the below code as profile name
schedule as needed, on first day of month for example prior to the delivery job below
Code Block language sql DECLARE @return_value int DECLARE @DATE datetime; SET @DATE = CONVERT(VARCHAR,DATEADD(MONTH,DATEDIFF(MONTH,-1,getdate()),0),101)--'2017-01-01' EXEC @return_value = [dbo].[Logisense_Boss_DeferredRevenue] @OwnerID = 1, @AsOfDate = @DATE SELECT 'Return Value' = @return_value GO
...
Create SQL job
Paste code below
Update code as needed
Ensure Database mail is setup and referenced in the below code as profile name
schedule as needed, at least 30 minutes after the job that actually generates the report to give it time to generate
Code Block language sql --Deferred Revenue Report USE msdb EXECUTE AS USER = 'dbo' EXEC msdb.dbo.sp_send_dbmail @recipients=N'test@test.com', @body='See text attachment for deferred revenue report', @subject ='Deferred Revenue report', @profile_name ='SMTP', @query ='select * from ViewDeferredRevenueReport order by id desc', @attach_query_result_as_file = 1, @query_attachment_filename ='deferredrevenuereport.txt'