USP_DATAFORMTEMPLATE_EDIT_REVENUE_SPLIT_2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUE_SPLIT_2
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @REVENUEAMOUNT money,
                        @SPLITS xml,
                        @CONSTITUENTISINDIVIDUAL bit
                    )
                    as 
                        set nocount on;

                        declare @TRANSACTIONTYPECODE tinyint;
                        declare @CHANGEDATE datetime;
                        set @CHANGEDATE = getdate();

                        --Set currency parameters for backwards compatibility

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

                        select @TRANSACTIONTYPECODE = REVENUE.TYPECODE from dbo.FINANCIALTRANSACTION REVENUE where REVENUE.ID = @ID and REVENUE.DELETEDON is null;

                        if @TRANSACTIONTYPECODE = 1
                            raiserror('NOTVALIDFORPLEDGE',13,1)

                        if @TRANSACTIONTYPECODE = 3
                            raiserror('NOTVALIDFORMGPLEDGE',13,1)

                        declare @SPLITSCHANGED bit
                        --verify if splits have changed. Ignore any null designation splits from membership transactions, for consistency with the load split logic.

                        set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED_EXCLUDEMEMBERSHIP(@ID, @SPLITS)

                        -- track changes for RG in the RG amendment table

                        if @TRANSACTIONTYPECODE = 2 and @SPLITSCHANGED = 1
                        begin
                           declare @OLDSPLITS xml;
                           declare @DESIGNATIONS xml;

                           --load the OLDSPLITS excluding empty designations in order to keep them consistent with the load

                           set @OLDSPLITS = (select * from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLIT
                                                      where SPLIT.DESIGNATIONID is not null
                                             for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64);

                           --process the designations

                           exec dbo.USP_REVENUE_GETSPLITS_MERGEDESIGNATIONSTOXML @SPLITS, @OLDSPLITS, @DESIGNATIONS output;

                           insert into dbo.RECURRINGGIFTAMENDMENT(ID,FINANCIALTRANSACTIONID,AMENDMENTTYPECODE,DATE,DESIGNATIONS,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                           values (newid(),@ID,2,@CHANGEDATE,@DESIGNATIONS,@CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE)
                        end

                        -- if the designations have changed, clear any user-defined gl distributions for this revenue record


                            -- check to see if designations have changed

                            if @SPLITSCHANGED = 1
                            -- clear the user-defined gl distributions

                            begin
                                --Cache CONTEXT INFO

                                declare @contextCache varbinary(128);
                                set @contextCache = CONTEXT_INFO();

                                if not @CHANGEAGENTID is null
                                    set CONTEXT_INFO @CHANGEAGENTID;

                                    --delete from REVENUEGLDISTRIBUTION

                                    delete JE from  dbo.JOURNALENTRY JE
                                    inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID  
                                    inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                                    and JEX.TABLENAMECODE = 1 and FTLI.FINANCIALTRANSACTIONID = @ID;

                                if @TRANSACTIONTYPECODE = 1
                                    --delete from WRITEOFFGLDISTRIBUTION

                                    delete JE from  dbo.JOURNALENTRY JE
                                    inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID  
                                    inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                                    and JEX.TABLENAMECODE = 12 and JEX.WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @ID);
                                else
                                begin    
                                    --delete from STOCKSALEGLDISTRIBUTION

                                    delete JE from dbo.JOURNALENTRY JE  
                                    inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                                    inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID  
                                    and JEX.TABLENAMECODE = 11 and JEX.LOGICALREVENUEID = @ID;

                                    --delete from PROPERTYDETAILGLDISTRIBUTION

                                    delete JE from  dbo.JOURNALENTRY JE
                                    inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                                    inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                                    inner join  dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
                                    where JEX.TABLENAMECODE = 10 
                                    and (JEX.PROPERTYDETAILID = @ID or (JEX.PROPERTYDETAILID is null and FT.ID = @ID))
                                end

                                --Restore CONTEXT_INFO

                                if not @contextCache is null
                                    set CONTEXT_INFO @contextCache;
                            end

                        -- check to see if the revenue record needs to be re-acknowledged                            

                        if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
                        begin

                            -- if designations have changed, mark the revenue letters for this record out of date, if necessary

                            if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
                                exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID;                     

                        end

                        declare @ISSPONSORSHIPRECURRINGADDITIONALGIFT bit = 0;
                        set @ISSPONSORSHIPRECURRINGADDITIONALGIFT =  dbo.UFN_RECURRINGGIFT_ISSPONSORSHIPRECURRINGADDITIONALGIFT(@ID); 

                        -- Set the IDs for any blank entries in the splits XML so that it can associate the declines with gift aid

                        -- flag with the generated splits.  Also, pull in the existing value for declines gift aid if it wasn't passed

                        -- in the xml.

                        set @SPLITS = (    select 
                                            case when SPLITS.[ID] is null or SPLITS.[ID] = '00000000-0000-0000-0000-000000000000' then newid() else SPLITS.[ID] end [ID],
                             SPLITS.[AMOUNT],
                                            SPLITS.[APPLICATIONCODE],
                                            SPLITS.[DESIGNATIONID],
                                            case @ISSPONSORSHIPRECURRINGADDITIONALGIFT 
                                                when 0 then SPLITS.[TYPECODE]
                                            else 17
                                            end as TYPECODE,
                                            case when SPLITS.[DECLINESGIFTAID] is null then REVENUESPLITGIFTAID.DECLINESGIFTAID else SPLITS.DECLINESGIFTAID end DECLINESGIFTAID,

                                            --Set currency parameters for backwards compatibility

                                            case
                                                when SPLITS.[TRANSACTIONCURRENCYID] is null then
                                                    @ORGANIZATIONCURRENCYID
                                                else
                                                    SPLITS.[TRANSACTIONCURRENCYID]
                                            end [TRANSACTIONCURRENCYID]
                                        from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS
                                        left join dbo.REVENUESPLITGIFTAID on SPLITS.ID = REVENUESPLITGIFTAID.ID
                                        for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64);

                        declare @SPLITSAMOUNTMODIFIED table
                        (
                            ID uniqueidentifier
                        );

                        declare @REVENUECATEGORYCOUNT int = 0;
                        declare @REVENUECATEGORYID uniqueidentifier = null; 

                        -- @SPLITSAMOUNTMODIFIED is used to see which splits need to have their recognition credits reset.  That only occurs

                        -- for pledges and recurring gifts.

                        -- JamesWill WI176241 Also check to see if the revenue category should be copied around 

                        if @TRANSACTIONTYPECODE in (1, 2)
                        begin
                            insert into @SPLITSAMOUNTMODIFIED (ID)
                            select
                                SPLITS.ID
                            from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS
                            where
                                not exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT where REVENUESPLIT.ID = SPLITS.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE <> 1) or
                                SPLITS.AMOUNT <> (select REVENUESPLIT.BASEAMOUNT from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT where REVENUESPLIT.ID = SPLITS.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE <> 1);

                            select top 1 @REVENUECATEGORYID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
                            from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                            inner loop join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
                            where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID
                                and REVENUESPLIT.DELETEDON is null
                                and REVENUESPLIT.TYPECODE <> 1;

                            select @REVENUECATEGORYCOUNT = count(distinct REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID)
                            from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                            inner loop join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
                            where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID
                                and REVENUESPLIT.DELETEDON is null
                                and REVENUESPLIT.TYPECODE <> 1

                            --Only set the revenue category on the splits if we can correctly infer what that should be 

                            --(i.e., all the existing splits have the same category). So there should only be 1 distinct category

                            --(If there are 0, that means that there shouldn't be a revenue category but also that there aren't currently any. 

                            --so we shouldn't need to delete anything).

                            if @REVENUECATEGORYCOUNT <> 1 
                                set @REVENUECATEGORYID = null;
                        end
                        exec dbo.USP_REVENUE_SPLIT_EDIT_SAVE_2 @ID, @CHANGEAGENTID, @CHANGEDATE, @REVENUEAMOUNT, @SPLITS;

                        -- On Pledges and Recurring Gifts, reset recognition credits for splits whose amounts changed

                        if @TRANSACTIONTYPECODE in (1, 2)
                        begin
                            -- Reset recognition credits for splits whose amount changed

                            declare @SPLITID uniqueidentifier
                            declare SPLITSCURSOR cursor local fast_forward for
                                select ID from @SPLITSAMOUNTMODIFIED;

                            open SPLITSCURSOR;
                            fetch next from SPLITSCURSOR into @SPLITID;

                            while (@@FETCH_STATUS = 0)
                            begin
                                --Cache CONTEXT INFO and remove old recognition credits

                                declare @contextCacheRecognitionCreditClear varbinary(128);
                                set @contextCacheRecognitionCreditClear = CONTEXT_INFO();
                                set CONTEXT_INFO @CHANGEAGENTID;

                                delete from dbo.REVENUERECOGNITION
                                where REVENUESPLITID = @SPLITID;

                                if not @contextCacheRecognitionCreditClear is null
                                    set CONTEXT_INFO @contextCacheRecognitionCreditClear;

                                exec dbo.[USP_REVENUEDETAIL_CREATERECOGNITIONS] @SPLITID, @CHANGEAGENTID, @CHANGEDATE;
                                fetch next from SPLITSCURSOR into @SPLITID;
                            end

                            close SPLITSCURSOR;
                            deallocate SPLITSCURSOR;                            

                            --JamesWill WI176241 Check to see if the revenue category should be copied to newly created splits 

                            if not @REVENUECATEGORYID is null
                            begin
                                insert into dbo.REVENUECATEGORY(ID, GLREVENUECATEGORYMAPPINGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                select 
                                    REVENUESPLIT.ID,
                                    @REVENUECATEGORYID,
                                    @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE 
                                from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT 
                                left join dbo.REVENUECATEGORY [CATEGORY] on [CATEGORY].ID = REVENUESPLIT.ID
                                where [CATEGORY].ID is null and REVENUESPLIT.FINANCIALTRANSACTIONID = @ID
                                    and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE <> 1
                            end
                        end

                        if @TRANSACTIONTYPECODE = 1 
                            exec dbo.USP_PLEDGE_FIXDEPENDENTSPLITS @ID, @CHANGEAGENTID, @CHANGEDATE;

                        if @SPLITSCHANGED = 1
                        begin
                            exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID, 
                                @CHANGEAGENTID = @CHANGEAGENTID,
                                @CHANGEDATE = @CHANGEDATE
                        end

                        --Gift Aid is for UK only

                        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
                        begin
                            declare @DATE datetime, @APPEALID uniqueidentifier, @PAYMENTMETHODCODE tinyint;
                            declare @CREDITTYPECODEID uniqueidentifier;
                            select
                                @DATE = cast(REVENUE.DATE as datetime),
                                @APPEALID = REVENUE_EXT.APPEALID,
                                @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                            from dbo.FINANCIALTRANSACTION REVENUE
                            inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                            inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                            where REVENUE.ID = @ID and REVENUE.DELETEDON is null;

                            if @PAYMENTMETHODCODE = 2
                                select @CREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID 
                                from dbo.REVENUE
                                inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                                left join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                                left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
                                where REVENUE.ID = @ID;

                            declare @SPLITSDECLININGGIFTAID xml
                            set @SPLITSDECLININGGIFTAID = (    select
                                                                ID as REVENUESPLITID
                                                            from dbo.UFN_REVENUE_GETSPLITS_FROMITEMLISTXML(@SPLITS)
                                                            where DECLINESGIFTAID = 1
                                                            for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64)

                            exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, @TRANSACTIONTYPECODE, @SPLITSDECLININGGIFTAID
                        end

                        return 0;