USP_MATCHINGGIFTPAYMENT_UPDATEMATCHINGGIFTCLAIMS

            CREATE procedure dbo.USP_MATCHINGGIFTPAYMENT_UPDATEMATCHINGGIFTCLAIMS
            (
                @ID uniqueidentifier,
                @MATCHINGGIFTCLAIMSPLITS xml,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @MATCHINGGIFTCLAIMLINEITEMMAP xml = null output
            )
            as
                set nocount on;

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

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

                --Determine which claims were edited and grab information about other splits on

                -- that claim so we can avoid creating claim splits with duplicate designations.

                declare @MODIFIEDMATCHINGGIFTCLAIM table
                (
                    FINANCIALTRANSACTIONID uniqueidentifier,
                    FINANCIALTRANSACTIONLINEITEMID uniqueidentifier,
                    REPLACEMENTFINANCIALTRANSACTIONLINEITEMID uniqueidentifier,
                    AMOUNT money,
                    DESIGNATIONID uniqueidentifier,
                    DESIGNATIONIDCHANGED bit,
                    ROWNUMBER int
                );

                --Record edited claim splits

                insert into @MODIFIEDMATCHINGGIFTCLAIM
                (
                    FINANCIALTRANSACTIONLINEITEMID,
                    DESIGNATIONID,
                    DESIGNATIONIDCHANGED
                )
                select distinct
                    MATCHINGGIFTCLAIMSPLITS.ITEM.value('(REVENUESPLITID)[1]', 'uniqueidentifier'),
                    MATCHINGGIFTCLAIMSPLITS.ITEM.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as DESIGNATIONID,
                    1 as DESIGNATIONIDCHANGED
                from
                    @MATCHINGGIFTCLAIMSPLITS.nodes('/MATCHINGGIFTCLAIMSPLITS/ITEM') as MATCHINGGIFTCLAIMSPLITS(ITEM);

                if exists
                (
                    select
                        1
                    from
                        @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                    group by
                        MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID,
                        MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID
                    having
                        count(*) > 1
                )
                begin
                    raiserror('BBERR_MATCHINGGIFTCLAIMSPLITASSIGNEDMULTIPLEDESIGNATIONS: When changing a designation on a matching gift claim, the designations must match for all portions of the payment that were originally applied the same designation.',13,1);
                    return 1;
                end

                update @MODIFIEDMATCHINGGIFTCLAIM set
                    FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                    AMOUNT = FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                from
                    @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID;

                --Record non-edited claim splits that match the edited claims

                insert into @MODIFIEDMATCHINGGIFTCLAIM
                (
                    FINANCIALTRANSACTIONID,
                    FINANCIALTRANSACTIONLINEITEMID,
                    AMOUNT,
                    DESIGNATIONID,
                    DESIGNATIONIDCHANGED
                )
                select
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                    FINANCIALTRANSACTIONLINEITEM.ID,
                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                    REVENUESPLIT_EXT.DESIGNATIONID,
                    0 as DESIGNATIONIDCHANGED
                from
                    (
                        select distinct
                            MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID
                        from
                        @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                    ) as MODIFIEDMATCHINGGIFTCLAIM
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    left join @MODIFIEDMATCHINGGIFTCLAIM as ALREADYACCOUNTEDFORCLAIM on FINANCIALTRANSACTIONLINEITEM.ID = ALREADYACCOUNTEDFORCLAIM.FINANCIALTRANSACTIONLINEITEMID
                where
                    ALREADYACCOUNTEDFORCLAIM.FINANCIALTRANSACTIONLINEITEMID is null;

                with
                ORDEREDMODIFIEDMATCHINGGIFTCLAIM as
                (
                    select
                        MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID,
                        row_number() over
                        (
                            partition by
                                MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID,
                                MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID
                            order by
                                MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONIDCHANGED
                        ) as ROWNUMBER
                from
                    @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                )
                update @MODIFIEDMATCHINGGIFTCLAIM set
                    ROWNUMBER = ORDEREDMODIFIEDMATCHINGGIFTCLAIM.ROWNUMBER
                from
                    @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                    inner join ORDEREDMODIFIEDMATCHINGGIFTCLAIM on MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID = ORDEREDMODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID;

                update @MODIFIEDMATCHINGGIFTCLAIM set
                    REPLACEMENTFINANCIALTRANSACTIONLINEITEMID =
                        (
                            select
                                SELECTEDMODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID
                            from
                                @MODIFIEDMATCHINGGIFTCLAIM as SELECTEDMODIFIEDMATCHINGGIFTCLAIM
                            where
                                SELECTEDMODIFIEDMATCHINGGIFTCLAIM.ROWNUMBER = 1
                                and MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID = SELECTEDMODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID
                                and MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID = SELECTEDMODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID
                        )
                from
                    @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM;

                set @MATCHINGGIFTCLAIMLINEITEMMAP = 
                    (
                        select
                            MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID,
                            MODIFIEDMATCHINGGIFTCLAIM.REPLACEMENTFINANCIALTRANSACTIONLINEITEMID
                        from
                            @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                        where
                            MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID <> MODIFIEDMATCHINGGIFTCLAIM.REPLACEMENTFINANCIALTRANSACTIONLINEITEMID
                        for xml raw('ITEM'),type,elements,root('MATCHINGGIFTCLAIMLINEITEMMAP'),BINARY BASE64
                    );

                --Clear references from this payment to matching gift claim line items we

                -- are going to delete. This avoids foreign key violations. We will

                -- restore the link when editing the payment.

                update dbo.FINANCIALTRANSACTIONLINEITEM set
                    SOURCELINEITEMID = REPLACEMENTFINANCIALTRANSACTIONLINEITEMID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM on FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID
                where
                    MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID <> MODIFIEDMATCHINGGIFTCLAIM.REPLACEMENTFINANCIALTRANSACTIONLINEITEMID
                    and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID;

                delete from dbo.INSTALLMENTSPLITPAYMENT
                from
                    dbo.INSTALLMENTSPLITPAYMENT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
                    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                    inner join @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM on INSTALLMENTSPLIT.REVENUESPLITID = MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID
                where
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
                    and MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONIDCHANGED = 1;

                --Multiple splits may be edited, group them by matching gift claim revenue record

                declare MODIFIEDMATCHINGGIFTCLAIMCURSOR cursor local fast_forward for
                    select
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                        CURRENCYSET.BASECURRENCYID,
                        FINANCIALTRANSACTION.ORGEXCHANGERATEID,
                        FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                        FINANCIALTRANSACTION.BASEEXCHANGERATEID
                    from
                        @MATCHINGGIFTCLAIMSPLITS.nodes('/MATCHINGGIFTCLAIMSPLITS/ITEM') as MATCHINGGIFTCLAIMSPLITS(ITEM)
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on MATCHINGGIFTCLAIMSPLITS.ITEM.value('REVENUESPLITID[1]', 'uniqueidentifier') = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    group by
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                        CURRENCYSET.BASECURRENCYID,
                        FINANCIALTRANSACTION.ORGEXCHANGERATEID,
                        FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                        FINANCIALTRANSACTION.BASEEXCHANGERATEID;

                declare @MODIFIEDMATCHINGGIFTCLAIMREVENUEID uniqueidentifier;
                declare @MATCHINGGIFTCLAIMBASECURRENCYID uniqueidentifier;
                declare @MATCHINGGIFTCLAIMORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @MATCHINGGIFTCLAIMTRANSACTIONCURRENCYID uniqueidentifier;
                declare @MATCHINGGIFTCLAIMBASEEXCHANGERATEID uniqueidentifier;

                open MODIFIEDMATCHINGGIFTCLAIMCURSOR;
                fetch next from MODIFIEDMATCHINGGIFTCLAIMCURSOR into
                    @MODIFIEDMATCHINGGIFTCLAIMREVENUEID,
                    @MATCHINGGIFTCLAIMBASECURRENCYID,
                    @MATCHINGGIFTCLAIMORGANIZATIONEXCHANGERATEID,
                    @MATCHINGGIFTCLAIMTRANSACTIONCURRENCYID,
                    @MATCHINGGIFTCLAIMBASEEXCHANGERATEID;

                while @@FETCH_STATUS = 0 
                begin
                    -- Cache current values for recognition updates.

                    declare @OLDSPLITS xml = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@MODIFIEDMATCHINGGIFTCLAIMREVENUEID);
                    declare @OLDRECOGNITIONS xml = dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE_TOITEMLISTXML(@MODIFIEDMATCHINGGIFTCLAIMREVENUEID);

                    declare @SPLITS xml;

                    with
                    AGGREGATESPLITSITEM as
                    (
                        select
                            MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONLINEITEMID,
                            MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID,
                            (
                                select
                                    sum(AMOUNT)
                                from
                                    @MODIFIEDMATCHINGGIFTCLAIM as SAMEDESIGNATIONMODIFIEDMATCHINGGIFTCLAIM
                                where
                                    SAMEDESIGNATIONMODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID = MODIFIEDMATCHINGGIFTCLAIM.DESIGNATIONID
                                    and SAMEDESIGNATIONMODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID = @MODIFIEDMATCHINGGIFTCLAIMREVENUEID
                            ) as AMOUNT
                        from
                            @MODIFIEDMATCHINGGIFTCLAIM as MODIFIEDMATCHINGGIFTCLAIM
                        where
                            MODIFIEDMATCHINGGIFTCLAIM.FINANCIALTRANSACTIONID = @MODIFIEDMATCHINGGIFTCLAIMREVENUEID
                            and MODIFIEDMATCHINGGIFTCLAIM.ROWNUMBER = 1
                    )
                    select @SPLITS = 
                        (
                            select
                                OLDSPLITS.ITEM.query('./*[local-name() != "DESIGNATIONID" and local-name() != "AMOUNT"]'),
                                AGGREGATESPLITSITEM.DESIGNATIONID,
                                AGGREGATESPLITSITEM.AMOUNT
                            from
                                @OLDSPLITS.nodes('/SPLITS/ITEM') as OLDSPLITS(ITEM)
                                inner join AGGREGATESPLITSITEM on OLDSPLITS.ITEM.value('(ID)[1]', 'uniqueidentifier') = AGGREGATESPLITSITEM.FINANCIALTRANSACTIONLINEITEMID
                            order by
                                OLDSPLITS.ITEM.value('(ID)[1]', 'uniqueidentifier')
                            for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                        );

                    set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS,@MATCHINGGIFTCLAIMBASECURRENCYID,@MATCHINGGIFTCLAIMORGANIZATIONEXCHANGERATEID,@MATCHINGGIFTCLAIMTRANSACTIONCURRENCYID,@MATCHINGGIFTCLAIMBASEEXCHANGERATEID);
                    exec dbo.USP_REVENUE_GETSPLITS_2_UPDATEFROMXML @MODIFIEDMATCHINGGIFTCLAIMREVENUEID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

                    exec dbo.USP_REVENUE_UPDATERECOGNITION
                        @MODIFIEDMATCHINGGIFTCLAIMREVENUEID,
                        @OLDSPLITS,
                        0, --@UPDATERECOGNITIONOPTION, --0 = no update

                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @OLDRECOGNITIONS,
                        null,
                        null,
                        3;

                    exec dbo.USP_REVENUE_UPDATESOLICITORS @MODIFIEDMATCHINGGIFTCLAIMREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

                    --assume one installment

                    declare @INSTALLMENTSPLITS xml;
                    set @INSTALLMENTSPLITS = (
                        select distinct
                        INSTALLMENTSPLIT.ID, 
                        INSTALLMENT.ID 
                        INSTALLMENTID, 
                        REVENUESPLIT.DESIGNATIONID, 
                        REVENUESPLIT.AMOUNT,
                        REVENUESPLIT.BASECURRENCYID,
                        REVENUESPLIT.ORGANIZATIONAMOUNT,
                        REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
                        REVENUESPLIT.TRANSACTIONAMOUNT,
                        REVENUESPLIT.TRANSACTIONCURRENCYID,
                        REVENUESPLIT.BASEEXCHANGERATEID,
                        REVENUESPLIT.ID as REVENUESPLITID
                        from REVENUESPLIT
                        inner join INSTALLMENT on INSTALLMENT.REVENUEID = REVENUESPLIT.REVENUEID
                        left outer join INSTALLMENTSPLIT 
                            on REVENUESPLIT.ID = INSTALLMENTSPLIT.REVENUESPLITID
                            and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                        where REVENUESPLIT.REVENUEID = @MODIFIEDMATCHINGGIFTCLAIMREVENUEID
                        for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64
                    );

                    exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_2_UPDATEFROMXML @MODIFIEDMATCHINGGIFTCLAIMREVENUEID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CURRENTDATE;

                    exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @MODIFIEDMATCHINGGIFTCLAIMREVENUEID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CHANGEDATE = @CURRENTDATE;

                    fetch next from MODIFIEDMATCHINGGIFTCLAIMCURSOR into
                        @MODIFIEDMATCHINGGIFTCLAIMREVENUEID,
                        @MATCHINGGIFTCLAIMBASECURRENCYID,
                        @MATCHINGGIFTCLAIMORGANIZATIONEXCHANGERATEID,
                        @MATCHINGGIFTCLAIMTRANSACTIONCURRENCYID,
                        @MATCHINGGIFTCLAIMBASEEXCHANGERATEID;
                end

                close MODIFIEDMATCHINGGIFTCLAIMCURSOR;
                deallocate MODIFIEDMATCHINGGIFTCLAIMCURSOR;