USP_BBNC_COMMITEVENTREGISTRATIONREGISTRANTREGISTRATION

Adds an event registration for a registrant from a Blackbaud Internet Solutions event registration transaction to the system from a given batch.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@REGISTRANTID	uniqueidentifier	IN
@EVENTPRICEID	uniqueidentifier	IN
@AMOUNT	money	IN
@RECEIPTAMOUNT	money	IN
@QUANTITY	int	IN
@DATEPURCHASED	datetime	IN
@ORIGINPAGENAME	nvarchar(1000)	IN
@ORIGINPAGEID	int	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@ISOCODE	nvarchar(3)	IN
Definition

Copy


            CREATE procedure dbo.USP_BBNC_COMMITEVENTREGISTRATIONREGISTRANTREGISTRATION
            (
                @ID uniqueidentifier = null output,
                @REGISTRANTID uniqueidentifier = null,
                @EVENTPRICEID uniqueidentifier = null,
                @AMOUNT money = null,
                @RECEIPTAMOUNT money = null,
                @QUANTITY int = null,
                @DATEPURCHASED datetime = null,
                @ORIGINPAGENAME nvarchar(1000) = null,
                @ORIGINPAGEID int = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @ISOCODE nvarchar(3) = null
            )
            as
                set nocount on;

                if @ID is null
                    set @ID = newid();

                if @CHANGEAGENTID is null            
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CHANGEDATE is null
                    set @CHANGEDATE = getdate();

        declare @TRANSACTIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETCURRENCYFROMISO(@ISOCODE);
        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@ORGANIZATIONCURRENCYID,@CHANGEDATE,1,null);   
        declare @ORGANIZATIONAMOUNT money = dbo.UFN_CURRENCY_CONVERT(@AMOUNT,@ORGANIZATIONEXCHANGERATEID);

                begin try
                    --HACK: BBNC does not provide a receipt amount, use the current value for the selected event price

                    if @RECEIPTAMOUNT is null
                        select
                            @RECEIPTAMOUNT =
                            case
                                when (@QUANTITY * [EVENTPRICE].[RECEIPTAMOUNT]) > @AMOUNT
                                    then @AMOUNT
                                else (@QUANTITY * [EVENTPRICE].[RECEIPTAMOUNT])
                            end
                        from
                            dbo.[EVENTPRICE]
                        where
                            [EVENTPRICE].[ID] = @EVENTPRICEID;

                    insert into dbo.[REGISTRANTREGISTRATION]
                    (
                        [ID],
                        [REGISTRANTID],
                        [EVENTPRICEID],
                        [AMOUNT],
                        [RECEIPTAMOUNT],
                        [QUANTITY],
                        [DATEPURCHASED],
                        [NETCOMMUNITYPAGENAME],
                        [NETCOMMUNITYPAGEID],
                        [ADDEDBYID],
                        [CHANGEDBYID],
                        [DATEADDED],
                        [DATECHANGED],
                        [ORGANIZATIONAMOUNT],
                        [ORGANIZATIONEXCHANGERATEID]
                    )
                    values
                    (
                        @ID,
                        @REGISTRANTID,
                        @EVENTPRICEID,
                        @AMOUNT,
                        @RECEIPTAMOUNT,
                        @QUANTITY,
                        @DATEPURCHASED,
                        @ORIGINPAGENAME,
                        @ORIGINPAGEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        @ORGANIZATIONAMOUNT,
                        @ORGANIZATIONEXCHANGERATEID
                    )

                    --Add the default designation for the added registrations,

                    declare @DEFAULTDESIGNATIONID uniqueidentifier = 
                        (select 
                            DESIGNATIONID
                        from dbo.REGISTRANT
                            join dbo.EVENT on EVENT.ID = REGISTRANT.EVENTID
                            join dbo.EVENTDESIGNATION on EVENTDESIGNATION.EVENTID = EVENT.ID
                        where 
                            REGISTRANT.ID = @REGISTRANTID
                            and EVENT.DESIGNATIONSONFEES = 1
                            and EVENTDESIGNATION.[DEFAULT] = 1)

                    declare @RECIEPTAMOUNT money = 
                        coalesce((select sum(REGISTRANTREGISTRATION.RECEIPTAMOUNT) 
                                    from dbo.REGISTRANTREGISTRATION 
                                    where REGISTRANTREGISTRATION.REGISTRANTID = @REGISTRANTID),0);


                    --default designation and receipt amount on main registrant; update if necessary

                    if @DEFAULTDESIGNATIONID is not null 
                    begin

                        if not exists(select * from dbo.REGISTRANTDESIGNATION where REGISTRANTID = @REGISTRANTID)
       insert into dbo.REGISTRANTDESIGNATION(REGISTRANTID,DESIGNATIONID,AMOUNT,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                            values(@REGISTRANTID, @DEFAULTDESIGNATIONID, @RECIEPTAMOUNT,
                                    @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE); 

                        -- if there is single registrant designation, update it

                        if 1 = (select count(*) from dbo.REGISTRANTDESIGNATION where REGISTRANTID = @REGISTRANTID)
                            update dbo.REGISTRANTDESIGNATION
                            set 
                                AMOUNT = @RECIEPTAMOUNT,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                            where 
                                REGISTRANTID = @REGISTRANTID
                                and DESIGNATIONID = @DEFAULTDESIGNATIONID
                    end

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;