Deferred Revenue Reporting and Functionality

Summary

This document describes how deferred revenue is reported based on several scenarios.

Definition of Terms

• Deferred Revenue – This is a fractional amount of a monetary value paid for a service which is yet to be rendered. Deferred Revenue itself refers specifically to the portion of a payment of which a service has not been fully rendered.

Report Columns Required

(package and user status not shown in examples)

•  Owner

•  Account

•  Account Status

•  User Package

•  User Package Status

•  Start Charge Date

•  Next Charge Date (as of running of report)

•  Charged Amount (to date)

•  Total Revenue Recognized (to date)

• Revenue Recognized (current period)

• Revenue Yet To Be Recognized – Deferred Revenue

Scenarios

Scenario 1) Customer BSmith signs up for a yearly package at $120 ($10 a month)

The deferred revenue will report the following with the date as of shown below:

The deferred revenue report as of April 1, 2008, should show as below:

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)

The deferred revenue report as of June 1, 2008, should show as below:

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)

The deferred revenue report as of June 1, 2008, should show as below:

Second report:

Monthly Billing

Amount Deferred Current period | Deferred Amount recognized current period | Balance Remaining to be recognized

Automated Delivery Config Example:

Auto population of the report based on a date:

1. Create SQL job

2. Paste code below

3. Update code as needed

4. Ensure Database mail is setup and referenced in the below code as profile name

5. schedule as needed, on first day of month for example prior to the delivery job below
   

   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 

Scheduled delivery of report POST generation by above script:

1. Create SQL job

2. Paste code below

3. Update code as needed

4. Ensure Database mail is setup and referenced in the below code as profile name

5. schedule as needed, at least 30 minutes after the job that actually generates the report to give it time to generate
   

   --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'[cce]