V_QUERY_REVENUESPLIT

Revenue application information

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
DESIGNATIONID	uniqueidentifier	yes	Designation system record ID
AMOUNT	money
TYPE	nvarchar(27)	yes	Type
APPLICATION	nvarchar(27)	yes	Application
REVENUEID	uniqueidentifier
DESIGNATIONNAME	nvarchar(512)	yes	Designation name
ADDEDBY_APPLICATION	nvarchar(200)	yes	Added by application
ADDEDBY_USERNAME	nvarchar(128)	yes	Added by user name
CHANGEDBY_APPLICATION	nvarchar(200)	yes	Changed by application
CHANGEDBY_USERNAME	nvarchar(128)	yes	Changed by user name
DATEADDED	datetime		Date added
DATECHANGED	datetime		Date changed
TSLONG	bigint	yes	Timestamp value
CATEGORY	nvarchar(100)	yes	Revenue category
BASECURRENCYID	uniqueidentifier	yes	Base currency ID
BASEEXCHANGERATEID	uniqueidentifier	yes	Base exchange rate
ORGANIZATIONAMOUNT	money
ORGANIZATIONEXCHANGERATEID	uniqueidentifier	yes
TRANSACTIONAMOUNT	money		Amount (transaction currency)
TRANSACTIONCURRENCYID	uniqueidentifier		Transaction currency ID
PROGRAMID	uniqueidentifier	yes	Program
EVENTID	uniqueidentifier	yes	Event
EVENTLOCATIONID	uniqueidentifier	yes	Event location
FEEID	uniqueidentifier	yes	Fee
TAXID	uniqueidentifier	yes	Tax
RESOURCEID	uniqueidentifier	yes	Resource
MERCHANDISEPRODUCTINSTANCEID	uniqueidentifier	yes	Merchandise
VOLUNTEERTYPE	nvarchar(50)	yes	Staff resource
APPLICATIONCODE	tinyint
GLREVENUECATEGORYMAPPINGID	uniqueidentifier	yes
VOLUNTEERTYPEID	uniqueidentifier	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  9/30/2015 1:01:52 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUESPLIT AS



                select
                    SPLIT.ID,
                    REVENUESPLIT_EXT.DESIGNATIONID,
                    SPLIT.BASEAMOUNT as AMOUNT,
                    REVENUESPLIT_EXT.TYPE,
                    REVENUESPLIT_EXT.APPLICATION,
                    SPLIT.FINANCIALTRANSACTIONID as REVENUEID,
                    DESIGNATION.NAME as [DESIGNATIONNAME],
                    [ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
                    [ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
                    [CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
                    [CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
                    SPLIT.DATEADDED,
                    SPLIT.DATECHANGED,
                    SPLIT.TSLONG,
                    GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME as [CATEGORY],
                    case
                        when FINANCIALTRANSACTION.TYPECODE in (2, 3, 8)
                            then (select RX.NONPOSTABLEBASECURRENCYID from dbo.REVENUE_EXT RX where RX.ID = FINANCIALTRANSACTION.ID)
                        else
                            CURRENCYSET.BASECURRENCYID
                    end as [BASECURRENCYID],
                    FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                    SPLIT.ORGAMOUNT as ORGANIZATIONAMOUNT,
                    FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
                    SPLIT.TRANSACTIONAMOUNT,
                    FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    REVENUESPLITORDER.PROGRAMID,
                    REVENUESPLITORDER.EVENTID,
                    REVENUESPLITORDER.EVENTLOCATIONID,
                    REVENUESPLITORDER.FEEID,
                    REVENUESPLITORDER.TAXID,
                    REVENUESPLITORDER.RESOURCEID,
                    REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID,
                    VOLUNTEERTYPE.NAME VOLUNTEERTYPE,
                    REVENUESPLIT_EXT.APPLICATIONCODE,
                    REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID,
                    VOLUNTEERTYPE.ID VOLUNTEERTYPEID
                    /*#EXTENSION*/                
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM SPLIT
                inner join 
                    dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = SPLIT.ID
                inner join 
                    dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = SPLIT.FINANCIALTRANSACTIONID and FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) 
                left join 
                    dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                left join dbo.CURRENCYSET 
                    on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                    
                left outer join
                    dbo.REVENUESPLITORDER on REVENUESPLITORDER.ID = SPLIT.ID
                left outer join
                    dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = REVENUESPLITORDER.VOLUNTEERTYPEID
                left outer join
                    dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
                left join
                    dbo.REVENUECATEGORY on SPLIT.ID = REVENUECATEGORY.ID
                left join 
                    dbo.GLREVENUECATEGORYMAPPING on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
                left join 
                    dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = SPLIT.ADDEDBYID
                left join 
                    dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = SPLIT.CHANGEDBYID
                where
                    SPLIT.DELETEDON is null and FINANCIALTRANSACTION.DELETEDON is null;