USP_OTHER_UPDATEPAYMENT

            CREATE procedure dbo.USP_OTHER_UPDATEPAYMENT
            (
                @ID uniqueidentifier,
                @REVENUEID uniqueidentifier,
                @AMOUNT money,
                @DESIGNATIONID uniqueidentifier,
                @OTHERTYPECODEID uniqueidentifier,
                @CAMPAIGNS xml,
                @RECOGNITIONCREDITS xml,
                @SOLICITORS xml,
                @CATEGORYCODEID uniqueidentifier,
                @CHANGEDATE datetime = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @BASEAPPLIEDAMOUNT money = null,
                @ORGANIZATIONAPPLIEDAMOUNT money = null
            )
            as
                set nocount on;

                if ((select DESIGNATION.ISACTIVE 
                     from dbo.REVENUESPLIT_EXT REVENUESPLIT
                     inner join dbo.DESIGNATION on DESIGNATION.ID = @DESIGNATIONID
                     where REVENUESPLIT.ID = @ID
                     and REVENUESPLIT.DESIGNATIONID <> @DESIGNATIONID)) = 0
                   raiserror('Revenue cannot be added to inactive designations.', 13, 1);

                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASECURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @DATE datetime;
                declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;                 
                declare @ORGANIZATIONCURRENCYID uniqueidentifier;

                select
                    @DATE = cast(REVENUE.DATE as datetime),
                    @BASECURRENCYID = CS.BASECURRENCYID,
                    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGEXCHANGERATEID
                from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
                inner join dbo.PDACCOUNTSYSTEM PD on PD.ID = REVENUE.PDACCOUNTSYSTEMID
                inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
                where REVENUESPLIT.ID = @ID;

                -- Convert the applied amount into base and organization amounts if it is not provided by the caller

                if @BASEAPPLIEDAMOUNT is null or @ORGANIZATIONAPPLIEDAMOUNT is null
                    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEAPPLIEDAMOUNT output, null, @ORGANIZATIONAPPLIEDAMOUNT output, @ORGANIZATIONEXCHANGERATEID, 0, @BASETOORGANIZATIONEXCHANGERATEID output;
                else
                    set @BASETOORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTBASETOORGANIZATIONRATE(@BASECURRENCYID, @DATE, null, @ORGANIZATIONEXCHANGERATEID);

                update dbo.FINANCIALTRANSACTIONLINEITEM set
                    TRANSACTIONAMOUNT = @AMOUNT
                    ,BASEAMOUNT = @BASEAPPLIEDAMOUNT
                    ,ORGAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                where ID = @ID
                    and (
                        TRANSACTIONAMOUNT != @AMOUNT or
                        BASEAMOUNT != @BASEAPPLIEDAMOUNT or
                        ORGAMOUNT != @ORGANIZATIONAPPLIEDAMOUNT);

                -- adding applicationcode and typecode in the update since the Revenue Update batch can change the application type code without

                -- deleting the split record, so the applicationcode and typecode doesn't get set correctly if say, a donation is changed into

                -- an other application.

                update dbo.REVENUESPLIT_EXT set
                    DESIGNATIONID = @DESIGNATIONID
                    ,APPLICATIONCODE = 4
                    ,TYPECODE = 4
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CHANGEDATE
                where ID = @ID
                    and (
                        DESIGNATIONID != @DESIGNATIONID or
                        TYPECODE != 4 or
                        APPLICATIONCODE != 4);

                update dbo.REVENUESPLITOTHER
                set OTHERTYPECODEID = @OTHERTYPECODEID, 
                    CHANGEDBYID = @CHANGEAGENTID, 
                    DATECHANGED = @CHANGEDATE
                where ID = @ID and OTHERTYPECODEID <> @OTHERTYPECODEID;

                -- AnkushGu - 12/18/2012 - WI 248767

                -- From RUB when we change the payment type record is updated rather than adding new. So when we change payment transaction from Donation to Other Type

                -- REVENUESPLITOTHER isn't getting updated. So adding an insert to fix the issue.

                if @@ROWCOUNT = 0
                begin
                    --Check if no other revenue split record exist with the same ID before adding

                    if not exists(select 1 from dbo.REVENUESPLITOTHER where ID = @ID)
                        insert into dbo.REVENUESPLITOTHER(ID, OTHERTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values(@ID, @OTHERTYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                end

                exec dbo.USP_REVENUESPLIT_GETCAMPAIGNS_UPDATEFROMXML @ID, @CAMPAIGNS, @CHANGEAGENTID;

                if dbo.UFN_REVENUESPLIT_MATCHEDBYDONORCHALLENGE(@ID) = 1
                begin
                    --If the new rows do not have RECOGNITIONCREDITFKID or ID they won't be added (e.g. if credits were reset)

                    set @RECOGNITIONCREDITS = (select [AMOUNT], [BASECURRENCYID], [CONSTITUENT], [CONSTITUENTID], [DESIGNATION], [DESIGNATIONID], [DONOR], [EFFECTIVEDATE], [GROSSAMOUNT], 
                                                                            case when RECOGNITIONCREDITFKID is null and ID is null then newid() else ID end [ID], 
                                                                            [RECOGNITIONTYPE], [REVENUERECOGNITIONTYPECODEID],
                                                                            case when RECOGNITIONCREDITFKID is null then @ID else RECOGNITIONCREDITFKID end [RECOGNITIONCREDITFKID],
                                                                            case when RECOGNITIONCREDITFKID is null then 0 else DONORCHALLENGERECOGNITIONTYPECODE end [DONORCHALLENGERECOGNITIONTYPECODE] 
                                                                        from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONCREDITS)
                                                                        order by EFFECTIVEDATE asc, CONSTITUENT asc
                                                                        for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),BINARY BASE64)
                    set @RECOGNITIONCREDITS = dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_CONVERTAMOUNTSINXML(@RECOGNITIONCREDITS, @BASECURRENCYID, @BASETOORGANIZATIONEXCHANGERATEID);
                    exec dbo.USP_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_CUSTOMUPDATEFROMXML @ID, @RECOGNITIONCREDITS, @CHANGEAGENTID;
                end
                else
                begin
                    set @RECOGNITIONCREDITS = dbo.UFN_REVENUERECOGNITION_CONVERTAMOUNTSINXML(@RECOGNITIONCREDITS, @BASECURRENCYID, @BASETOORGANIZATIONEXCHANGERATEID);
                    exec dbo.USP_REVENUE_GETRECOGNITIONS_2_UPDATEFROMXML @ID, @RECOGNITIONCREDITS, @CHANGEAGENTID;
                end

                set @SOLICITORS = dbo.UFN_REVENUESOLICITOR_CONVERTAMOUNTSINXML(@SOLICITORS, @BASECURRENCYID, @BASETOORGANIZATIONEXCHANGERATEID);
                exec dbo.USP_REVENUE_GETSOLICITORS_2_UPDATEFROMXML @ID, @SOLICITORS, @CHANGEAGENTID;

                exec dbo.USP_REVENUECATEGORY_ADDEDIT @ID, @CATEGORYCODEID, @CHANGEAGENTID;

                --If this split was at one point a donation and is now becoming an other split (most likely through RUB), then it is possible that there is an opportunity associated

                --  with this splits. If there is, delete it, since other splits are not allowed to have opportunities associated with them.

                if exists(select 1 from dbo.REVENUEOPPORTUNITY where ID = @ID)
                    delete from dbo.REVENUEOPPORTUNITY where ID = @ID