USP_RECOGNITIONCREDIT_ADDTOGROUPMEMBERS

            CREATE procedure dbo.USP_RECOGNITIONCREDIT_ADDTOGROUPMEMBERS
             (
                @SPLITS xml,
                @DONORID uniqueidentifier,
                @DONATIONDATE datetime,
                @GIFTID uniqueidentifier,
                @RECOGNITIONCREDITTYPEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @USERECOGNITIONCREDITTYPEIDFROMRECOGNITION bit = 0
            )
            as

                 set nocount on;

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

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

                begin try

                    declare @GIFTSPLITS xml;
                    set @GIFTSPLITS = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@GIFTID)

                    --KevinKoe WI 131183 - Using a singular split ID and amount does not work if the matching gift has more than one split

                    --declare @SPLITID uniqueidentifier;

                    --declare @AMOUNT money;


                    --select @SPLITID=ID, @AMOUNT=AMOUNT

                    --from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)


                    --Set currency parameters for backwards compatibility

                    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                    with [CTE] as 
                    (
                        select 
                            newid() as ID,
                            MGPSPLITS.ID as SPLITID,
                            CONSTITUENT.ID as CONSTITUENTID,
                            --JamesWill WI109642 Pro-rate the recognition according to the recognition on the original revenue

                            (
                                case
                                    when RECOGNITIONWITHGIFTSPLIT.AGGREGATEGIFTSPLITAMOUNT = 0 then 0  --Ensure we don't divide by 0

                                    else MGPSPLITS.AMOUNT * (RECOGNITIONWITHGIFTSPLIT.RECOGNITIONAMOUNT / RECOGNITIONWITHGIFTSPLIT.AGGREGATEGIFTSPLITAMOUNT)
                                end
                            ) as AMOUNT,
                            RECOGNITIONWITHGIFTSPLIT.BASECURRENCYID,
                            RECOGNITIONWITHGIFTSPLIT.ORGANIZATIONEXCHANGERATEID,
                            RECOGNITIONWITHGIFTSPLIT.TRANSACTIONCURRENCYID,
                            RECOGNITIONWITHGIFTSPLIT.BASEEXCHANGERATEID,
                            RECOGNITIONWITHGIFTSPLIT.REVENUERECOGNITIONTYPECODEID
                        from
                        -- Aggregate the original gift splits joined with recognition credits.  Without the aggregation, if the claim is generated from a payment 

                        -- with multiple splits and the same designation, the constituent will be recognized multiple times because it joins to the MG splits 

                        -- using designation ID.  The derived table GIFTSPLITAMOUNTBYDESIGNATION is needed to handle the scenario where one split is paid by recognition 

                        -- type and another split is paid by a different type.  Otherwise, it could show the recognition percentage as 100%

                        -- for both splits and when it multiples by MGPSPLITS.AMOUNT above, would result in an incorrect recognition amount.  Just GIFTSPLITAMOUNTBYDESIGNATION

                        -- can't be used because it could result in identical recognition credits being created.

                        (
                            select
                                GIFTSPLITAMOUNTBYDESIGNATION.AMOUNT as AGGREGATEGIFTSPLITAMOUNT,
                                GIFTSPLIT.DESIGNATIONID,
                                sum(REVENUERECOGNITION.AMOUNT) as RECOGNITIONAMOUNT,
                                REVENUERECOGNITION.CONSTITUENTID,
                                REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID,
                                GIFTSPLIT.BASECURRENCYID,
                                GIFTSPLIT.ORGANIZATIONEXCHANGERATEID,
                                GIFTSPLIT.TRANSACTIONCURRENCYID,
                                GIFTSPLIT.BASEEXCHANGERATEID
                            from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@GIFTSPLITS) as GIFTSPLIT
                            inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = GIFTSPLIT.ID
                            inner join
                            (
                                select
                                    DESIGNATIONID,
                                    sum(AMOUNT) as AMOUNT
                                from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@GIFTSPLITS)
                                group by
                                    DESIGNATIONID
                            ) as GIFTSPLITAMOUNTBYDESIGNATION on GIFTSPLIT.DESIGNATIONID = GIFTSPLITAMOUNTBYDESIGNATION.DESIGNATIONID
                            group by
                                REVENUERECOGNITION.CONSTITUENTID,
                                GIFTSPLIT.DESIGNATIONID,
                                GIFTSPLITAMOUNTBYDESIGNATION.AMOUNT,
                                REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID,
                                GIFTSPLIT.BASECURRENCYID,
                                GIFTSPLIT.ORGANIZATIONEXCHANGERATEID,
                                GIFTSPLIT.TRANSACTIONCURRENCYID,
                                GIFTSPLIT.BASEEXCHANGERATEID
                        ) as RECOGNITIONWITHGIFTSPLIT
                        inner join dbo.CONSTITUENT on CONSTITUENT.ID = RECOGNITIONWITHGIFTSPLIT.CONSTITUENTID
                        inner join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) as MGPSPLITS on MGPSPLITS.DESIGNATIONID = RECOGNITIONWITHGIFTSPLIT.DESIGNATIONID
                    )
                    insert into REVENUERECOGNITION
                    (
                        ID, 
                        REVENUESPLITID,
                        CONSTITUENTID,
                        AMOUNT,
                        EFFECTIVEDATE,
                        REVENUERECOGNITIONTYPECODEID, 
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED,
                        DATECHANGED,
                        BASECURRENCYID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID
                    )
                    select
                        newid(),
                        SPLITID,
                        CONSTITUENTID,
                        CTE.AMOUNT,
                        @DONATIONDATE,
                        case when @USERECOGNITIONCREDITTYPEIDFROMRECOGNITION = 1
                            then [CTE].REVENUERECOGNITIONTYPECODEID
                            else @RECOGNITIONCREDITTYPEID
                        end,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,

                        --Set currency parameters for backwards compatibility

                        case
                            when CTE.BASECURRENCYID is null then
                                @ORGANIZATIONCURRENCYID
                            else
                                CTE.BASECURRENCYID
                        end,

                        case
                            when CTE.BASECURRENCYID is null then
                                CTE.AMOUNT
                            else
                                dbo.UFN_CURRENCY_CONVERTTOORGANIZATIONCURRENCY(CTE.AMOUNT, CTE.ORGANIZATIONEXCHANGERATEID, null)
                        end,

                        case
                            when CTE.BASECURRENCYID is null then
                                null
                            else
                                CTE.ORGANIZATIONEXCHANGERATEID
                        end
                    from [CTE]
                end try

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

                return 0;