USP_BBNC_COMMITFUNDRAISERTEAMFUNDRAISERUPDATE

Updates a team fundraiser's information from a fundraiser Blackbaud Internet Solutions transaction to the system from a given batch.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@GOAL	money	IN
@CONSTITUENCYCODEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@ISOCODE	nvarchar(3)	IN
Definition

Copy


            CREATE procedure dbo.USP_BBNC_COMMITFUNDRAISERTEAMFUNDRAISERUPDATE
            (
                @ID uniqueidentifier,
                @GOAL money,
                @CONSTITUENCYCODEID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
        @ISOCODE nvarchar(3) = null
            )
            as
                set nocount on;

                declare @CURRENTDATE datetime;

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

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

                set @CURRENTDATE = getdate();


        --Multicurrency Info Grab

        declare @TRANSACTIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETCURRENCYFROMISO(@ISOCODE)
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),@CHANGEDATE,1,null)
        declare @ORGANIZATIONAMOUNT  money = dbo.UFN_CURRENCY_CONVERT(@GOAL,@ORGANIZATIONEXCHANGERATEID)

        if dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() <> @TRANSACTIONCURRENCYID
        begin
          set @GOAL = dbo.UFN_CURRENCY_CONVERT(@GOAL,@ORGANIZATIONEXCHANGERATEID)
        end

                begin try
                    update dbo.TEAMFUNDRAISER set
                        [GOAL] = @GOAL,
                        [CHANGEDBYID] = @CHANGEAGENTID,
                        [DATECHANGED] = @CHANGEDATE,
            [ORGANIZATIONGOAL] = @ORGANIZATIONAMOUNT,
            [ORGANIZATIONEXCHANGERATEID] = @ORGANIZATIONEXCHANGERATEID
                    where
                        [ID] = @ID;

                    if @CONSTITUENCYCODEID is not null
                        begin
                            declare @CONSTITUENTID uniqueidentifier;

                            select
                                @CONSTITUENTID = [TEAMFUNDRAISER].[CONSTITUENTID]
                            from
                                dbo.TEAMFUNDRAISER
                            where
                                [TEAMFUNDRAISER].[ID] = @ID;

                            if not exists
                            (
                                select
                                    1
                                from
                                    dbo.CONSTITUENCY
                                where
                                    [CONSTITUENTID] = @CONSTITUENTID
                                    and [CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
                                    and 
                                    (
                                        ([DATEFROM] is null and [DATETO] is null)
                                        or ([DATEFROM] <= dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) and [DATETO] is null)
                                        or ([DATEFROM] is null and dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) <= [DATETO])
                                        or (dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE) between [DATEFROM] and [DATETO])
                                    )
                            )
                                begin
                                    declare @CONSTITUENCYDATETO datetime;

                                    select top (1)
                                        @CONSTITUENCYDATETO = dateadd(day, -1, [CONSTITUENCY].[DATEFROM])
                                    from
                                        dbo.CONSTITUENCY
                                    where
                                        [CONSTITUENCY].[CONSTITUENTID] = @CONSTITUENTID
                                        and [CONSTITUENCY].[CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
                                        and [CONSTITUENCY].[DATEFROM] > dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
                                    order by
                                        [DATETO];

                                    insert into dbo.CONSTITUENCY
                                    (
                                        [CONSTITUENTID],
                                        [CONSTITUENCYCODEID],
                                        [DATEFROM],
                                        [DATETO],
                                        [ADDEDBYID],
                                        [CHANGEDBYID],
                                        [DATEADDED],
                                        [DATECHANGED]
                                    )
                                    values
                                    (
                                        @CONSTITUENTID,
                                        @CONSTITUENCYCODEID,
                                        dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE),
                                        dbo.UFN_DATE_GETEARLIESTTIME(@CONSTITUENCYDATETO),
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID,
                                        @CHANGEDATE,
                                        @CHANGEDATE
                                    );
                                end
                        end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;