USP_DATAFORMTEMPLATE_VIEW_REGISTRANTPROFILE_2

The load procedure used by the view dataform template "Registrant Summary View Form 2"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@PICTURE	varbinary	INOUT	PICTURE
@REGISTRANTGUESTCOUNT	int	INOUT	No. of guests
@REGISTRANTSTATUS	nvarchar(28)	INOUT	Status
@GROUPNAME	nvarchar(100)	INOUT	Group name
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ATTENDED	bit	INOUT	ATTENDED
@WILLNOTATTEND	bit	INOUT	WILLNOTATTEND
@ONLINEREGISTRANT	bit	INOUT	Online registrant
@REGISTRANTSEAT	nvarchar(310)	INOUT	Seat
@TOTALFEES	money	INOUT	Total registration fees
@TOTALPAID	money	INOUT	Total paid
@FEEBALANCE	money	INOUT	Balance
@TOTALRECEIPTAMOUNT	money	INOUT	Total receipt amount
@PAYMENT1ID	uniqueidentifier	INOUT	PAYMENT1ID
@PAYMENT2ID	uniqueidentifier	INOUT	PAYMENT2ID
@PAYMENT3ID	uniqueidentifier	INOUT	PAYMENT3ID
@PAYMENT4ID	uniqueidentifier	INOUT	PAYMENT4ID
@PAYMENT1AMOUNT	money	INOUT	PAYMENT1AMOUNT
@PAYMENT2AMOUNT	money	INOUT	PAYMENT2AMOUNT
@PAYMENT3AMOUNT	money	INOUT	PAYMENT3AMOUNT
@PAYMENT4AMOUNT	money	INOUT	PAYMENT4AMOUNT
@PAYMENT1DATE	datetime	INOUT	PAYMENT1DATE
@PAYMENT2DATE	datetime	INOUT	PAYMENT2DATE
@PAYMENT3DATE	datetime	INOUT	PAYMENT3DATE
@PAYMENT4DATE	datetime	INOUT	PAYMENT4DATE
@PAYMENTCOUNT	int	INOUT	PAYMENTCOUNT
@PAYMENT1ISCREDIT	bit	INOUT	PAYMENT1ISCREDIT
@PAYMENT2ISCREDIT	bit	INOUT	PAYMENT2ISCREDIT
@PAYMENT3ISCREDIT	bit	INOUT	PAYMENT3ISCREDIT
@PAYMENT4ISCREDIT	bit	INOUT	PAYMENT4ISCREDIT
@REGISTRANTLOOKUPID	nvarchar(36)	INOUT	Registrant ID
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@EVENTBASECURRENCYID	uniqueidentifier	INOUT	EVENTBASECURRENCYID
@PAYMENT1TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	PAYMENT1TRANSACTIONCURRENCYID
@PAYMENT2TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	PAYMENT2TRANSACTIONCURRENCYID
@PAYMENT3TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	PAYMENT3TRANSACTIONCURRENCYID
@PAYMENT4TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	PAYMENT4TRANSACTIONCURRENCYID
@LOGINASPARTICIPANTLINK	UDT_WEBADDRESS	INOUT	Login as participant
@ALLOW	bit	INOUT	Allow event administrator access to their fundraising headquarters?
@URLFORMATSTRING	varchar(512)	INOUT	URLFORMATSTRING
@SHAREDKEY	varchar(512)	INOUT	SHAREDKEY
@BBNCUSERNAME	varchar(512)	INOUT	BBNCUSERNAME
@HAVEHQPAGE	bit	INOUT	The event website have a personal page defined to login as the participant?
@HAVELOGINPAGE	bit	INOUT	The event website have a login page defined to login as the participant?
@WEBSITESTATUS	bit	INOUT	The event website has been disabled?
@SENDPASSWORDURL	nvarchar(512)	INOUT	SENDPASSWORDURL
@ISFAFEVENT	bit	INOUT	ISFAFEVENT
@PARTICIPANTURL	nvarchar(max)	INOUT	PARTICIPANTURL
@DATEREGISTERED	datetime	INOUT	Date registered
@TOTALFUNDRAISING	money	INOUT	Total fundraising
@TOTALFUNDRAISINGGOAL	money	INOUT	Total fundraising goal
@TARGETFUNDRAISINGGOAL	money	INOUT	Target fundraising goal
@GROUPTYPE	tinyint	INOUT	GROUPTYPE
@ISUSER	bit	INOUT	ISUSER
@ALLOWSSO	bit	INOUT	ALLOWSSO
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@AUTHENTICATEDSOCAILMEDIA	nvarchar(512)	INOUT	Authenticated with: Fackbook, Google, Yahoo
@REGISTRATIONTYPE	nvarchar(16)	INOUT	Type
@GROUPID	uniqueidentifier	INOUT
@EVENTREGISTRANTCONSTITUENCYTEXT	nvarchar(100)	INOUT
@OPPORTUNITYID	uniqueidentifier	INOUT
@OPPORTUNITYNAME	nvarchar(500)	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_REGISTRANTPROFILE_2
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ADDRESS nvarchar(300) = null output,
                    @PHONENUMBER nvarchar(100) = null output,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                    @PICTURE varbinary(max) = null output,
                    @REGISTRANTGUESTCOUNT int = null output,
                    @REGISTRANTSTATUS nvarchar(28) = null output,
                    @GROUPNAME nvarchar(100) = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @ATTENDED bit = null output,
                    @WILLNOTATTEND bit = null output,
                    @ONLINEREGISTRANT bit = null output,
                    @REGISTRANTSEAT nvarchar(310) = null output,
                    @TOTALFEES money = null output,
                    @TOTALPAID money = null output,
                    @FEEBALANCE money = null output,
                    @TOTALRECEIPTAMOUNT money = null output,
                    @PAYMENT1ID uniqueidentifier = null output,
                    @PAYMENT2ID uniqueidentifier = null output,
                    @PAYMENT3ID uniqueidentifier = null output,
                    @PAYMENT4ID uniqueidentifier = null output,
                    @PAYMENT1AMOUNT money = null output,
                    @PAYMENT2AMOUNT money = null output,
                    @PAYMENT3AMOUNT money = null output,
                    @PAYMENT4AMOUNT money = null output,
                    @PAYMENT1DATE datetime = null output,
                    @PAYMENT2DATE datetime = null output,
                    @PAYMENT3DATE datetime = null output,
                    @PAYMENT4DATE datetime = null output,
                    @PAYMENTCOUNT int = null output,
                    @PAYMENT1ISCREDIT bit = null output,
                    @PAYMENT2ISCREDIT bit = null output,
                    @PAYMENT3ISCREDIT bit = null output,
                    @PAYMENT4ISCREDIT bit = null output,
                    @REGISTRANTLOOKUPID nvarchar(36) = null output,
                    @ISORGANIZATION bit = null output,
                    @EVENTBASECURRENCYID uniqueidentifier = null output,
                    @PAYMENT1TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @PAYMENT2TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @PAYMENT3TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @PAYMENT4TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @LOGINASPARTICIPANTLINK dbo.UDT_WEBADDRESS = null output,
                    @ALLOW bit = null output,
                    @URLFORMATSTRING varchar(512) = null output,
                    @SHAREDKEY varchar(512) = null output,
                    @BBNCUSERNAME varchar(512) =null output,
                    @HAVEHQPAGE bit = null output,
                    @HAVELOGINPAGE bit = null output,
                    @WEBSITESTATUS bit = null output,
                    @SENDPASSWORDURL nvarchar(512) = null output,
                    @ISFAFEVENT bit = null output,
                    @PARTICIPANTURL nvarchar(max) = null output,
                    @DATEREGISTERED datetime = null output,
                    @TOTALFUNDRAISING money = null output,
                    @TOTALFUNDRAISINGGOAL money = null output,
                    @TARGETFUNDRAISINGGOAL money = null output,
                    @GROUPTYPE tinyint = null output,
                    @ISUSER bit = null output,
                    @ALLOWSSO bit = null output,
                    @DONOTMAIL bit = null output,
                    @DONOTEMAIL bit = null output,
                    @DONOTPHONE bit = null output,
                    @PHONEISCONFIDENTIAL bit = null output,
                    @ADDRESSISCONFIDENTIAL bit = null output,
                    @ADDRESSID uniqueidentifier = null output,
                    @PHONENUMBERID uniqueidentifier = null output,
                    @EMAILADDRESSID uniqueidentifier = null output,
                    @AUTHENTICATEDSOCAILMEDIA nvarchar(512) = null output,
                    @REGISTRATIONTYPE nvarchar(16) = null output,
                    @GROUPID uniqueidentifier = null output,
                    @EVENTREGISTRANTCONSTITUENCYTEXT nvarchar(100) = null output,
                    @OPPORTUNITYID uniqueidentifier = null output,
                    @OPPORTUNITYNAME nvarchar(500) = null output
                )
                as
                set nocount on;

                set @DATALOADED = 0;

                declare @EVENTID uniqueidentifier = null;
                declare @CONSTITUENTSEQUENCEID int;

                select
                    @DATALOADED = 1,
                    @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
                    @DONOTMAIL = ADDRESS.DONOTMAIL,
                    @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
                    @ADDRESSID = ADDRESS.ID,
                    @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
                    @DONOTPHONE = PHONE.DONOTCALL,
                    @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
                    @PHONENUMBERID = PHONE.ID,
                    @EMAILADDRESS = EMAILADDRESS.EMAILADDRESS,
                    @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
                    @EMAILADDRESSID = EMAILADDRESS.ID,
                    @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                    @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
                    @REGISTRANTGUESTCOUNT = coalesce((select count(GUESTS.ID) from dbo.REGISTRANT as GUESTS where GUESTS.GUESTOFREGISTRANTID = REGISTRANT.ID),0),
                    @REGISTRATIONTYPE =
                        case
                            when REGISTRANT.ISWALKIN = 1 then 'Walk-in'
                            when exists(select 'x' from dbo.INVITEE where INVITEE.EVENTID = EVENT.ID and INVITEE.CONSTITUENTID = case when REGISTRANT.GUESTOFREGISTRANTID is not null then (select HOST.CONSTITUENTID from dbo.REGISTRANT as HOST where HOST.ID = REGISTRANT.GUESTOFREGISTRANTID) else REGISTRANT.CONSTITUENTID end and INVITEE.INVITATIONSENTON is not null) then 'Invitation'
                            else 'Preregistration'
                        end,
                    @REGISTRANTSTATUS =
                        case
                            when dbo.UFN_REGISTRANT_ISCANCELLED(REGISTRANT.ID) = 1 then 'Canceled'
                            when REGISTRANT.ATTENDED = 0 and REGISTRANT.WILLNOTATTEND = 1 then 'Will not attend'
                            when REGISTRANT.ATTENDED = 0 and REGISTRANT.WILLNOTATTEND = 0 then 'Registered'
                            else 'Attended'
                        end,
                    @CONSTITUENTID = REGISTRANT.CONSTITUENTID,
                    @ATTENDED = REGISTRANT.ATTENDED,
                    @WILLNOTATTEND = REGISTRANT.WILLNOTATTEND,
                    @ONLINEREGISTRANT = REGISTRANT.ONLINEREGISTRANT,
                    @REGISTRANTSEAT = dbo.UFN_EVENTSEATING_GETREGISTRANTSEATPATH(REGISTRANT.ID),
                    @REGISTRANTLOOKUPID = REGISTRANT.LOOKUPID,
                    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
                    @EVENTBASECURRENCYID = EVENT.BASECURRENCYID,
                    @EVENTID = EVENT.ID,
                    @CONSTITUENTSEQUENCEID = CONSTITUENT.SEQUENCEID
                from dbo.REGISTRANT
                    left join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
                    left join dbo.CONSTITUENT on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
                    left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                    left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY = 1
                    left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY = 1
                where REGISTRANT.ID = @ID;

                select
                    @OPPORTUNITYID = EVENTREGISTRATIONOPPORTUNITY.OPPORTUNITYID,
                    @OPPORTUNITYNAME = dbo.UFN_OPPORTUNITY_GETDESCRIPTION(EVENTREGISTRATIONOPPORTUNITY.OPPORTUNITYID)
                from dbo.EVENTREGISTRATIONOPPORTUNITY
                where EVENTREGISTRATIONOPPORTUNITY.ID = @ID;

                --Simpler to do the financial calculations here so we don't have to recompute TOTALFEES and TOTALPAID again to figure out the balance

                with [EVENTFEEINFO] as 
                (
                    select
                        coalesce((select sum(REGISTRANTREGISTRATION.AMOUNT) from dbo.REGISTRANTREGISTRATION where REGISTRANTREGISTRATION.REGISTRANTID = @ID),0) as [TOTALFEES],
                        coalesce((select sum(REGISTRANTREGISTRATION.RECEIPTAMOUNT) from dbo.REGISTRANTREGISTRATION where REGISTRANTREGISTRATION.REGISTRANTID = @ID),0) as [TOTALRECEIPTAMOUNT],
                        coalesce((select sum(EVENTREGISTRANTPAYMENT.AMOUNT) from dbo.EVENTREGISTRANTPAYMENT
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                            where FINANCIALTRANSACTIONLINEITEM.DELETEDON IS NULL AND EVENTREGISTRANTPAYMENT.REGISTRANTID = @ID),0) as [TOTALPAID],
                        coalesce((select sum(CREDITITEMS.TOTAL) from dbo.[UFN_REGISTRANT_GETCREDITITEMS](@ID) CREDITITEMS),0) as [TOTALREFUNDED]
                )
                select
                    @TOTALFEES = [EVENTFEEINFO].TOTALFEES,
                    @TOTALPAID = [EVENTFEEINFO].TOTALPAID - [EVENTFEEINFO].TOTALREFUNDED,
                    @FEEBALANCE = [EVENTFEEINFO].TOTALFEES - [EVENTFEEINFO].TOTALPAID + [EVENTFEEINFO].[TOTALREFUNDED],
                    @TOTALRECEIPTAMOUNT = [EVENTFEEINFO].TOTALRECEIPTAMOUNT
                from [EVENTFEEINFO]

                declare @PAYMENTS table
                (
                    REVENUEID uniqueidentifier,
                    AMOUNT money,
                    DATE datetime,
                    DATEADDED datetime,
                    ISCREDIT bit,
                    TRANSACTIONCURRENCYID uniqueidentifier
                );

                insert into @PAYMENTS(REVENUEID, AMOUNT, DATE, DATEADDED, ISCREDIT, TRANSACTIONCURRENCYID)
                    select
                        REVENUE.ID as [ID],
                        sum(REVENUESPLIT.TRANSACTIONAMOUNT) as [AMOUNT],
                        max(REVENUE.DATE) as [DATE],
                        max(REVENUE.DATEADDED) as [DATEADDED],
                        cast(0 as bit) as [ISCREDIT],
                        REVENUESPLIT.TRANSACTIONCURRENCYID
                    from dbo.EVENTREGISTRANTPAYMENT
                            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                            inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
                    where EVENTREGISTRANTPAYMENT.REGISTRANTID = @ID
                    group by
                        REVENUE.ID,     -- group by payment; some splits may exist for event designations now

                        REVENUESPLIT.TRANSACTIONCURRENCYID

                    union all

                    select
                        [CREDIT].[ID] as [ID],
                        sum([CREDITITEMS].[TOTAL]) as [AMOUNT],
                        [CREDIT].[TRANSACTIONDATE] as [DATE],
                        null as [DATEADDED],
                        cast(1 as bit) as [ISCREDIT],
                        null as TRANSACTIONCURRENCYID
                    from dbo.[UFN_REGISTRANT_GETCREDITITEMS](@ID) as [CREDITITEMS]
                    inner join dbo.[CREDIT]
                        on [CREDIT].[ID] = [CREDITITEMS].[CREDITID]
                    group by [CREDIT].[ID], [CREDIT].[TRANSACTIONDATE]
                    order by [DATE] desc;

                select @PAYMENTCOUNT = count(*) from @PAYMENTS;

                if @PAYMENTCOUNT >= 1
                    select top 1 
                        @PAYMENT1ID = REVENUEID,
                        @PAYMENT1AMOUNT = AMOUNT,
                        @PAYMENT1DATE = DATE,
                        @PAYMENT1ISCREDIT = ISCREDIT,
                        @PAYMENT1TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                    from @PAYMENTS
                    order by DATE desc, DATEADDED desc, AMOUNT desc;

                if @PAYMENTCOUNT >= 2
                    select top 1
                        @PAYMENT2ID = REVENUEID,
                        @PAYMENT2AMOUNT = AMOUNT,
                        @PAYMENT2DATE = DATE,
                        @PAYMENT2ISCREDIT = ISCREDIT,
                        @PAYMENT2TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                    from @PAYMENTS
                    where REVENUEID <> @PAYMENT1ID
                    order by DATE desc, DATEADDED desc, AMOUNT desc;

                if @PAYMENTCOUNT >= 3
                    select top 1
                        @PAYMENT3ID = REVENUEID,
                        @PAYMENT3AMOUNT = AMOUNT,
                        @PAYMENT3DATE = DATE,
                        @PAYMENT3ISCREDIT = ISCREDIT,
                        @PAYMENT3TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                    from @PAYMENTS
                    where REVENUEID <> @PAYMENT1ID and REVENUEID <> @PAYMENT2ID
                    order by DATE desc, DATEADDED desc, AMOUNT desc;

                if @PAYMENTCOUNT >= 4
                    select top 1
                        @PAYMENT4ID = REVENUEID,
                        @PAYMENT4AMOUNT = AMOUNT,
                        @PAYMENT4DATE = DATE,
                        @PAYMENT4ISCREDIT = ISCREDIT,
                        @PAYMENT4TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
                    from @PAYMENTS
                    where REVENUEID <> @PAYMENT1ID and REVENUEID <> @PAYMENT2ID and REVENUEID <> @PAYMENT3ID
                    order by DATE desc, DATEADDED desc, AMOUNT desc;

                if @DATALOADED = 1
                    select
                        @GROUPNAME = EVENTGROUP.NAME
                    from dbo.EVENTGROUPMEMBER
                    inner join dbo.EVENTGROUP on EVENTGROUP.ID = EVENTGROUPMEMBER.EVENTGROUPID
                    where EVENTGROUPMEMBER.REGISTRANTID = @ID;    

                declare @CLIENTSITESID  int = null;
                declare @CLIENTUSERID  int = null;
                declare @HQPID int = null;
                declare @SITECONTENTID int = -1;
                declare @LOGINPAGEID int = null;

                set @ISFAFEVENT = dbo.UFN_IS_FRIENDS_ASKING_FRIENDS_EVENT(@EVENTID);

                if @ISFAFEVENT = 1
                BEGIN
                    select
                        @GROUPNAME = TFT.NAME, @GROUPTYPE = TX.TYPECODE, @GROUPID=TFT.ID,
                        @TOTALFUNDRAISING = case when TX.TYPECODE = 3 then dbo.UFN_REVENUE_GETHOUSEHOLDRAISEDTOTAL(TFT.ID, TX.EVENTID) else @TOTALFUNDRAISING end 
                    from TEAMFUNDRAISER TF
                        join TEAMFUNDRAISINGTEAMMEMBER TFTM on TF.ID = TFTM.TEAMFUNDRAISERID
                        join TEAMFUNDRAISINGTEAM TFT on TFTM.TEAMFUNDRAISINGTEAMID = TFT.ID
                        join TEAMEXTENSION TX on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
                    where CONSTITUENTID = @CONSTITUENTID;

                    select
                        @CLIENTSITESID = eex.CLIENTSITESID,
                        @ALLOW = isnull(rex.[ALLOWADMINACCESSHQ], 1),
                        @REGISTRANTSTATUS =
                        case
                            when dbo.UFN_REGISTRANT_ISCANCELLED(r.ID) = 1 then 'Canceled'
                            when r.ATTENDED = 0 and r.WILLNOTATTEND = 1 then 'Active (will not attend)'
                            when r.ATTENDED = 0 and r.WILLNOTATTEND = 0 then 'Active'
                            else 'Attended'
                        end,
                        @TOTALFUNDRAISING = dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL(@ID, @EVENTID),
                        @TOTALFUNDRAISINGGOAL = rex.FUNDRAISINGGOAL, 
                        @TARGETFUNDRAISINGGOAL = rex.TARGETFUNDRAISINGGOAL, @DATEREGISTERED = r.DATEADDED
                    from dbo.REGISTRANT r
                        left join dbo.REGISTRANTEXTENSION rex on rex.REGISTRANTID = r.ID
                        left join dbo.EVENTEXTENSION eex ON r.EVENTID = eex.EVENTID  
                    where r.ID = @ID;

                    set @EVENTREGISTRANTCONSTITUENCYTEXT = 
                        case when dbo.UFN_CONSTITUENT_ISREGISTRANT(@CONSTITUENTID) = 1 then 
                            dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C') --Event registrant

                        else null end;

                    set @WEBSITESTATUS = dbo.UFN_CLIENTSITES_GETEVENTWEBSITESTATUS(@EVENTID);
                    set @CLIENTUSERID = dbo.fnGetUserIDFromLinkedRecordID(@CONSTITUENTSEQUENCEID, 0);
                    declare @ISBLACKBAUDLOGIN bit = 1;
                    set @AUTHENTICATEDSOCAILMEDIA = '';

                    select
                        @ISBLACKBAUDLOGIN = 0,
                        @AUTHENTICATEDSOCAILMEDIA = @AUTHENTICATEDSOCAILMEDIA + PROVIDERNAME + ', '
                    from dbo.CLIENTUSERSEXTENSION
                    where CLIENTUSERID = @CLIENTUSERID;

                    if @WEBSITESTATUS = 1
                    begin
                        set @PARTICIPANTURL = dbo.UFN_VANITYURL_GETFAFPARTICIPANTURL(@ID, @EVENTID, 0)
                    end

                    select @ALLOWSSO = VALUE
                    from dbo.CMSSITESETTING
                    where ENUMID = 25 and CLIENTSITESID = @CLIENTSITESID;

                    select
                        @HQPID = HQ.VALUE,
                        @LOGINPAGEID = LP.VALUE,
                        @HAVEHQPAGE = case when HQ.VALUE is null then 0 else 1 end,
                        @HAVELOGINPAGE = case when LP.VALUE is null then 0 else 1 end
                    from EVENTEXTENSION EX
                        left join dbo.CMSSITESETTING HQ on EX.CLIENTSITESID = HQ.CLIENTSITESID and HQ.ENUMID = 30
                        left join dbo.CMSSITESETTING LP on EX.CLIENTSITESID = LP.CLIENTSITESID and LP.ENUMID = 3
                    where EX.EVENTID = @EVENTID;

                    select @SITECONTENTID = P.SITECONTENTID
                    from dbo.PAGECONTENT P
                        inner join dbo.SiteContent SC on SC.ID = P.SiteContentID and SC.ContentTypesID = 33   --sign in part

                    where P.SITEPAGESID = @LOGINPAGEID;

                     if @WEBSITESTATUS = 1
                     BEGIN
                        if @ALLOWSSO = 1
                        BEGIN
                            set @ISUSER = 1
                            exec dbo.USP_GETCREATE_SSO_URLFORMATSTRING @URLFORMATSTRING = @URLFORMATSTRING output,@SHAREDKEY = @SHAREDKEY output,@SITEID = @CLIENTSITESID, @SSO_NAME='BBEC Caller 2', @includeIP=0
                            if @ISBLACKBAUDLOGIN = 1
                                select @SENDPASSWORDURL = VALUE + 'ConfirmationEmail.aspx?pid=' + Convert(varchar(100),@LOGINPAGEID) +  
                                    '&UserID=' + Convert(varchar(100),@CLIENTUSERID) +  
                                    '&SiteContentID=' + Convert(varchar(100),@SITECONTENTID)  
                                from dbo.CMSSITESETTING where ENUMID=11 and CLIENTSITESID = @CLIENTSITESID  
                            else
                                set @AUTHENTICATEDSOCAILMEDIA = 'Authenticated with: ' + SUBSTRING(@AUTHENTICATEDSOCAILMEDIA, 1, LEN(@AUTHENTICATEDSOCAILMEDIA) - 1)
                            if @ALLOW = 1
                            BEGIN
                                select @BBNCUSERNAME = USERNAME from dbo.CLIENTUSERS where ID = @CLIENTUSERID
                                if @HAVEHQPAGE = 1
                                BEGIN
                                    if @LOGINPAGEID is not null
                                            DECLARE @HOUSEHOLDPARAMETER varchar(100) = '';
                                            DECLARE @TEAMID uniqueidentifier = NULL;
                                            DECLARE @TYPECODE int;
                                            SELECT  @TEAMID = TEAMID, @TYPECODE = TEAMTYPECODE
                                            FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID);
                                            select @LOGINASPARTICIPANTLINK = VALUE + '?pid=' + Convert(varchar(100),@LOGINPAGEID) +  
                                            '&ru=page.aspx%3fpid%3d'
                                            from dbo.CMSSITESETTING
                                            where ENUMID=10 and CLIENTSITESID = @CLIENTSITESID;
                                 END
                             END
                         END
                     END
             END