USP_REVENUE_MATCHINGGIFT_CHANGECONSTITUENT

            CREATE procedure dbo.USP_REVENUE_MATCHINGGIFT_CHANGECONSTITUENT
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,
                @CURRENTAPPUSERID uniqueidentifier
            )
            as 
                set nocount on;

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

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

                -- Update rules for matching gifts

                -- 1) Cannot update a matching gift claim with a payment against it.


                declare @MATCHINGGIFTID uniqueidentifier;
                declare MGCURSOR cursor local fast_forward for
                    select 
                        FINANCIALTRANSACTION.ID
                    from dbo.FINANCIALTRANSACTION
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join REVENUEMATCHINGGIFT on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
                    where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @REVENUEID
                    and dbo.UFN_PLEDGE_PAYMENTSEXIST(FINANCIALTRANSACTION.ID) = 0
                    and FINANCIALTRANSACTION.DELETEDON is null

                open MGCURSOR;
                fetch next from MGCURSOR into @MATCHINGGIFTID;
                while @@fetch_status = 0
                begin
                    exec dbo.USP_MATCHINGGIFTPLEDGE_DELETE @MATCHINGGIFTID, @CHANGEAGENTID;

                    fetch next from MGCURSOR into @MATCHINGGIFTID;
                end

                close MGCURSOR;
                deallocate MGCURSOR;

                declare @SPLITS xml;
                declare @APPLIEDAMOUNT money;
                declare @RECEIPTAMOUNT money;
                declare @CONSTITUENTID uniqueidentifier
                select @SPLITS = (select sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                    from dbo.FINANCIALTRANSACTIONLINEITEM
                                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and REVENUESPLIT_EXT.TYPECODE = 0
                                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                                    group by REVENUESPLIT_EXT.DESIGNATIONID
                                    for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);

                if (select count(*)
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.DESIGNATION on REVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and (REVENUESPLIT_EXT.TYPECODE = 0)
                    and DESIGNATION.ISACTIVE = 0 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) > 0
                raiserror('Revenue cannot be added to inactive designations.', 13, 2);

                select @APPLIEDAMOUNT = sum(TRANSACTIONAMOUNT)
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and REVENUESPLIT_EXT.TYPECODE = 0
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                select
                    @RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT,
                    @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
                from dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                where FINANCIALTRANSACTION.ID = @REVENUEID
                    and FINANCIALTRANSACTION.DELETEDON is null;

                if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                    exec USP_MATCHINGGIFTPLEDGE_AUTOADD @CONSTITUENTID, @CHANGEAGENTID, @REVENUEID, @CHANGEDATE, @APPLIEDAMOUNT, @RECEIPTAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                    exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @CONSTITUENTID, @CHANGEAGENTID, @REVENUEID, @CHANGEDATE, @APPLIEDAMOUNT, @RECEIPTAMOUNT, @SPLITS, @CURRENTAPPUSERID;