V_QUERY_EVENTPRICE

This provides the ability to query for registration options.

Fields

Field Field Type Null Description
ID uniqueidentifier System record ID
EVENTID uniqueidentifier Event ID
EVENTREGISTRATIONTYPE nvarchar(100) yes Registration type
EVENTREGISTRATIONTYPEID uniqueidentifier Event registration type ID
AMOUNT money Registration fee
RECEIPTAMOUNT money Tax deductable amount
COST money Cost
REGISTRATIONCOUNT int Registration count
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
NAME nvarchar(100) Name
ORGANIZATIONAMOUNT money Registration fee (organization currency)
ORGANIZATIONCOST money Cost (organization currency)
ORGANIZATIONEXCHANGERATEID uniqueidentifier yes Organization exchange rate
BASECURRENCYID uniqueidentifier yes Base currency ID
DESCRIPTION nvarchar(1000) yes Description
ISNOTAVAILABLEONLINEREG bit Not available online
FUNDRAISINGGROUPS varchar(95) yes Registration option available for the following groups
EARLYREGISTRATIONDATE datetime yes Early discount date
EARLYREGISTRATIONDISCOUNT money yes Early discount amount
LATEFEEDATE datetime yes Late fee date
LATEFEE money yes Late fee amount
ALLOWPARTICIPANTSWAIVEBENEFITS bit yes Allow participant to waive benefit

Definition

Copy 
/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:27:15 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTPRICE AS



            with [option_cte] 
      as
      (
          select EVENTPRICEID, 
          (case ISREGTYPEAVAILABLEINDIVIDUAL when 1 then 'Individual; ' else '' end 
          + case ISREGTYPEAVAILABLEFAMILYMEMBER when 1 then 'Household members; ' else '' end  
          + case ISREGTYPEAVAILABLEHEADHOUSEHOLD when 1 then 'Household leaders; ' else '' end
          + case ISREGTYPEAVAILABLETEAMMEMBER when 1 then 'Team members; ' else '' end
          + case ISREGTYPEAVAILABLETEAMLEADER when 1 then 'Team leaders; ' else '' end
          + case ISREGTYPEAVAILABLECOMPANYLEADER when 1 then 'Company leaders; ' else '' end) as RegistrantOption
          from dbo.EVENTPRICE EP (nolock) 
          inner join dbo.FAFREGISTRATIONTYPE FAF on FAF.EVENTPRICEID = EP.ID
      )

select
                    EVENTPRICE.ID,
                    EVENTPRICE.EVENTID,
                    EVENTREGISTRATIONTYPE.DESCRIPTION [EVENTREGISTRATIONTYPE],
                    EVENTPRICE.EVENTREGISTRATIONTYPEID,
                    EVENTPRICE.AMOUNT,
                    EVENTPRICE.RECEIPTAMOUNT,
                    --dbo.UFN_EVENTPRICEDESIGNATION_GETTOTALDESIGNATIONAMOUNT(EVENTPRICE.ID) [DESIGNATIONAMOUNT],

                    EVENTPRICE.COST,
                    EVENTPRICE.REGISTRATIONCOUNT,
                    --dbo.UDA_BUILDLIST(BENEFIT.NAME) [BENEFITLIST],

                    EVENTPRICE.DATEADDED,
                    EVENTPRICE.DATECHANGED,
                    EVENTPRICE.TSLONG,
                    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
                    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
                    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
                    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
                    EVENTPRICE.NAME,
                    EVENTPRICE.ORGANIZATIONAMOUNT,
                    EVENTPRICE.ORGANIZATIONCOST,
                    EVENTPRICE.ORGANIZATIONEXCHANGERATEID,
                    EVENT.BASECURRENCYID,

                    FRT.DESCRIPTION,
                    ISNULL(FRT.ISNOTAVAILABLEONLINEREG,0) ISNOTAVAILABLEONLINEREG, --Not available online

                    case when isnull(O.RegistrantOption,'')<>'' then  
                            (case when RIGHT(O.RegistrantOption,2)='; ' then LEFT(O.RegistrantOption,len(O.RegistrantOption)-1) else O.RegistrantOption end)
                    else
                        O.RegistrantOption END as FUNDRAISINGGROUPS --Registration option available for the following groups

                    ,FRT.EARLYREGISTRATIONDATE --Early discount date

                    ,FRT.EARLYREGISTRATIONDISCOUNT --Early discount amount

                    ,FRT.LATEFEEDATE --Late fee date

                    ,FRT.LATEFEE --Late fee amount

                    ,FRT.ALLOWPARTICIPANTSWAIVEBENEFITS --Allow participant to waive benefit

                    /*#EXTENSION*/ 
                from 
                    dbo.EVENTPRICE
                    left join dbo.EVENT on EVENTPRICE.EVENTID = EVENT.ID
                    left join dbo.EVENTREGISTRATIONTYPE on EVENTREGISTRATIONTYPE.ID = EVENTPRICE.EVENTREGISTRATIONTYPEID
                    left join dbo.EVENTPRICEBENEFIT on EVENTPRICE.ID = EVENTPRICEBENEFIT.EVENTPRICEID
                    left join dbo.BENEFIT on EVENTPRICEBENEFIT.BENEFITID = BENEFIT.ID
                    left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = EVENTPRICE.ADDEDBYID
                    left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = EVENTPRICE.CHANGEDBYID
                    left join dbo.FAFREGISTRATIONTYPE as FRT ON FRT.EVENTPRICEID=EVENTPRICE.ID 
                    left join [option_cte] O on O.EVENTPRICEID = EVENTPRICE.ID  
                --group by

                --    EVENTPRICE.ID,EVENTPRICE.EVENTID,EVENTREGISTRATIONTYPE.DESCRIPTION,EVENTPRICE.AMOUNT,EVENTPRICE.EVENTREGISTRATIONTYPEID,

                --    EVENTPRICE.RECEIPTAMOUNT,EVENTPRICE.COST,EVENTPRICE.REGISTRATIONCOUNT,EVENTPRICE.DATEADDED,EVENTPRICE.DATECHANGED,EVENTPRICE.TSLONG,

                --    ADDEDBY.APPLICATIONNAME,ADDEDBY.USERNAME,CHANGEDBY.APPLICATIONNAME,CHANGEDBY.USERNAME,EVENTPRICE.NAME,

                --    EVENTPRICE.ORGANIZATIONAMOUNT,EVENTPRICE.ORGANIZATIONCOST,EVENTPRICE.ORGANIZATIONEXCHANGERATEID,EVENT.BASECURRENCYID,

                --    FRT.DESCRIPTION,ISNOTAVAILABLEONLINEREG;