Open Forum

Expand all | Collapse all

Identify Deferred Revenue Missing Deferral Profile

  • 1.  Identify Deferred Revenue Missing Deferral Profile

    Posted Apr 13, 2020 07:00 PM
    Edited by Matthew Swanson Apr 13, 2020 07:03 PM
    Does anyone have an easy way to identify sales transactions that were billed to a deferred revenue (balance sheet) account that doesn't have a deferral profile associated with the transaction?

    While we were auditing our deferred revenue accounts, we identified some sales transactions that didn't have a deferral associated with it.  Normally, these sales transactions would move into the balance sheet account, and would move out into the income statement revenue accounts over the course of the contract/agreement (usually 3, 6 or 12 months).  In these cases, the revenue moves onto the balance sheet and never moves off to the income statement.

    I'm thinking a SQL view/SmartList that would identify sales transactions based on GL account (00-12345-00) where there isn't a deferral associated with it, but I'm not finding how to identify the what deferral is/is not associated with the transactions.  Any suggestions (or examples of a similar use case) would be helpful.  It will help us unwind what is already there and then get added to our monthly audits.

    Version 2018 R2

    #Technical#BIandReporting #SmartList #Finance​​​
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Identify Deferred Revenue Missing Deferral Profile

    TOP CONTRIBUTOR
    Posted Apr 14, 2020 09:05 AM
    Matthew,

    I don't have a simple solution for this.  Maybe someone else has run into this.

    Maybe this might help...   Your journal entries contain the source transaction ID from the subledger.  You should be able to dump your journal entries for that account to excel using Smartlist (be sure to include the Originating Document Number).  Then, use vlookup in Excel to find the entries that don't match to your deferral entries.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Identify Deferred Revenue Missing Deferral Profile

    TOP CONTRIBUTOR
    Posted Apr 14, 2020 11:02 AM
    Hi @Matthew Swanson,

    I would suggest the following SQL tables to be of interest as it relates to Deferrals.
    1.) PP000100 - Deferral Header Work -this will include the subledger document number to tie back to GL.  Also includes the deferral account index.
    2.) PP000101 - Deferral Line Work -this will have the recognition schedule
    3.) PP100100 -Deferral Header History -this will include the subledger document number to tie back to the GL. Also includes the deferral account index
    4.) PP100101 - Deferrral Line History - this will have the recognition schedule
    5.) GL00105 - Account Index Master - can cross reference deferral account index against this table to get the account number as a string (i.e. 1-1-1000).

    From there it's a matter of how you want to cross reference.write your SQL script/view. Some options include:
    1.) You can start at the GL as suggested by others and identify documents that exist in the GL but do not have a corresponding deferral profile/deferral account that matches as expected.

    2.) You could work in the Sales Transactions subledger and related tables and identify similar information using the transaction and distribution tables compared to the deferral tables above.

    Does that make sense?  If you have questions, please let me know.

    Thanks,

    Samantha

    ------------------------------
    Samantha Higdon ,CPA,CGMA
    Consultant
    Lagom, LLC
    Carmel IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Identify Deferred Revenue Missing Deferral Profile

    GPUG ALL STAR
    Posted Apr 14, 2020 11:03 AM
    Edited by Jo deRuiter Apr 14, 2020 11:04 AM
    Hi @Matthew Swanson

    I do not have time to write the query now, but here are the related tables.

    If it was deferred it will show in the PP100101 AND PP100100 tables.

    The PP_Module will be 3 for Sales Transactions
    The PP_Document_Number will be the SOP or RM Doc Number​

    In PP10100 you can link to the PP10101 table to find the Customer Number and other info including the account indexes associated with the transaction.
    The PP10101 table has the identifying Journal Entry number that will correspond to what has hit the GL20000 or GL30000 tables.

    Here are the tables in each (gotten with a select top 1000)
    /****** Script for SelectTopNRows command from SSMS ******/
    SELECT TOP (1000) [PP_Module]
    ,[PP_Record_Type]
    ,[PP_Document_Number]
    ,[PP_Sequencer]
    ,[PPOFFSEQ]
    ,[VCHRNMBR]
    ,[CNTRLTYP]
    ,[DSTSQNUM]
    ,[DSCRIPTN]
    ,[GLPOSTDT]
    ,[TRXAMNT]
    ,[JRNENTRY]
    ,[DEX_ROW_ID]
    FROM [dbo].[PP100101]

    SELECT TOP (1000) [PP_Module]
    ,[PP_Record_Type]
    ,[PP_Document_Number]
    ,[PP_Sequencer]
    ,[PPOFFSEQ]
    ,[VCHRNMBR]
    ,[CNTRLTYP]
    ,[DSTSQNUM]
    ,[STRTNGDT]
    ,[ENDINGDT]
    ,[OFFACCT]
    ,[ACTINDX]
    ,[PP_Deferred_BC_Index]
    ,[PP_Deferrals_Transfer_In]
    ,[PP_Recognition_Index]
    ,[TRXAMNT]
    ,[USERID]
    ,[REFRENCE]
    ,[IntegerValue]
    ,[PP_Calculation_Method]
    ,[PP_Miscellaneous_Periods]
    ,[VOIDSTTS]
    ,[CUSTVNDR]
    ,[DOCDATE]
    ,[NOTEINDX]
    ,[JRNENTRY]
    ,[DEX_ROW_ID]
    FROM [dbo].[PP100100]

    Best of luck and if I find time I will try and write a script

    ------------------------------

    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Identify Deferred Revenue Missing Deferral Profile

    Posted Apr 14, 2020 01:12 PM
    Edited by Matthew Swanson Apr 14, 2020 01:12 PM
    Thank you  - this should be enough to get me started on the query.  I'll see what I can come up with and post back.

    ------------------------------
    Matthew Swanson
    NCBP
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role