USP_BBNC_COMMITFUNDRAISERTEAMFUNDRAISERADD

            CREATE procedure dbo.USP_BBNC_COMMITFUNDRAISERTEAMFUNDRAISERADD
            (
                @ID uniqueidentifier = null output,
                @BBNCMAPID int = null output,
                @APPEALID uniqueidentifier = null,
                @TEAMFUNDRAISINGTEAMID uniqueidentifier = null,
                @CONSTITUENTID uniqueidentifier = null,
                @GOAL money = 0,
                @CONSTITUENCYCODEID uniqueidentifier = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @ISTEAMCAPTAIN bit = 0,
        @ISOCODE nvarchar(3) = null
            )
            as
                set nocount on;

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

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

                --Used for adding constituency, not for DATEADDED/DATECHANGED

                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate();

       --Multicurrency Info Grab

      declare @TRANSACTIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETCURRENCYFROMISO(@ISOCODE)
      declare @BASECURRENCYID uniqueidentifier = (select BASECURRENCYID from dbo.APPEAL where APPEAL.ID = @APPEALID)
      declare @BASEEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@CHANGEDATE,1,null)
      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 @BASECURRENCYID <> @TRANSACTIONCURRENCYID
      begin
       set @GOAL = dbo.UFN_CURRENCY_CONVERT(@GOAL,@BASEEXCHANGERATEID)
      end

                begin try
                    declare @CURRENTGOAL money;
                    select
                        @ID = TEAMFUNDRAISER.ID,
                        @CURRENTGOAL = TEAMFUNDRAISER.GOAL
                    from
                        dbo.APPEAL
                        left join dbo.TEAMFUNDRAISER on
                            APPEAL.ID = TEAMFUNDRAISER.APPEALID
                            and @CONSTITUENTID = TEAMFUNDRAISER.CONSTITUENTID
                    where
                        APPEAL.ID = @APPEALID;

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

                        insert into dbo.TEAMFUNDRAISER
                            (ID,APPEALID,CONSTITUENTID,GOAL,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,ORGANIZATIONGOAL,ORGANIZATIONEXCHANGERATEID,BASECURRENCYID)
                        values
                            (@ID,@APPEALID,@CONSTITUENTID,@GOAL,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@BASECURRENCYID);
                    end
                    else
                        if @GOAL <> @CURRENTGOAL
                            update
                                dbo.TEAMFUNDRAISER
                            set
                                GOAL = @GOAL
                            where
                                ID = @ID;

                    insert into dbo.TEAMFUNDRAISINGTEAMMEMBER
                        (TEAMFUNDRAISINGTEAMID,TEAMFUNDRAISERID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                    values
                        (@TEAMFUNDRAISINGTEAMID,@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE);

                    if @ISTEAMCAPTAIN = 1
                    and @TEAMFUNDRAISINGTEAMID is not null
                    and not exists(select 1 from dbo.TEAMFUNDRAISINGTEAMCAPTAIN where TEAMFUNDRAISINGTEAMID = @TEAMFUNDRAISINGTEAMID and CONSTITUENTID = @CONSTITUENTID)
                        insert into dbo.TEAMFUNDRAISINGTEAMCAPTAIN
                            (TEAMFUNDRAISINGTEAMID, CONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values
                            (@TEAMFUNDRAISINGTEAMID, @CONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                    if @CONSTITUENCYCODEID is not null
                    and not exists
                    (
                        select
                            1
                        from
                            dbo.CONSTITUENCY
                        where
                            [CONSTITUENTID] = @CONSTITUENTID
                            and [CONSTITUENCYCODEID] = @CONSTITUENCYCODEID
                            and 
                            (    /* JamesWill 02/22/2008 CR294342-022208 Copied this where clause from USP_BBNC_COMMITFUNDRAISERTEAMFUNDRAISERUPDATE to pick up all the existing constituencies */
                                ([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

                    select
                        @BBNCMAPID = [ID]
                    from
                        dbo.BBNCTEAMFUNDRAISERIDMAP
                    where
                        TEAMFUNDRAISERID = @ID;
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;