V_QUERY_REVENUEREGISTRANT

This provides the ability to query for payments applied to event registrants.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
PAYMENTID	uniqueidentifier		Revenue system ID
REGISTRANTID	uniqueidentifier		Registrant system ID
REGISTRATIONSTOTALAMOUNT	money	yes	Registrations amount
DATEADDED	datetime		Date added
DATECHANGED	datetime		Date changed
TSLONG	bigint	yes	Timestamp value
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
BASECURRENCYID	uniqueidentifier	yes	Base currency ID

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  8/17/2011 2:38:24 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_REVENUEREGISTRANT AS



                select
                    EVENTREGISTRANTPAYMENT.ID,
                    EVENTREGISTRANTPAYMENT.PAYMENTID,
                    EVENTREGISTRANTPAYMENT.REGISTRANTID,
                    (
                        select
                            sum(REGISTRANTREGISTRATION.AMOUNT)
                        from
                            dbo.REGISTRANTREGISTRATION
                        where
                            REGISTRANTREGISTRATION.REGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                    ) [REGISTRATIONSTOTALAMOUNT],
                    EVENTREGISTRANTPAYMENT.DATEADDED,
                    EVENTREGISTRANTPAYMENT.DATECHANGED,
                    EVENTREGISTRANTPAYMENT.TSLONG,
                    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
                    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
                    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
                    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
                    (
                        select
                            EVENT.BASECURRENCYID
                        from
                            dbo.REGISTRANT
                            inner join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                        where
                            REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                    ) [BASECURRENCYID]

                    /*#EXTENSION*/ 
                from 
                    dbo.EVENTREGISTRANTPAYMENT
                    left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = EVENTREGISTRANTPAYMENT.ADDEDBYID
                    left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = EVENTREGISTRANTPAYMENT.CHANGEDBYID