USP_PLEDGE_ADDPAYMENT

            CREATE procedure dbo.USP_PLEDGE_ADDPAYMENT
            (
                @REVENUEID uniqueidentifier,
                @APPLICATIONID uniqueidentifier,
                @APPLIEDAMOUNT money,
                @CONSTITUENTID uniqueidentifier,
                @DATE datetime,
                @UNAPPLIEDMATCHINGGIFTSPLITS xml,
                @APPLICATIONTYPE tinyint,
                @AMOUNTPAID money = null output,
                @CREATIONDATE datetime = null,
                @CHANGEAGENTID uniqueidentifier = null,
                @CREATEDSPLITS xml = null output,
                @OVERPAYMENTAPPLICATIONTYPECODE tinyint = null,
                @BUSINESSUNITSAPPLIED bit = 0,
                @APPLIEDBASEAMOUNT money = null,
                @APPLIEDORGANIZATIONAMOUNT money = null,
                @APPLICATIONSPLITS xml = null
            )
            as

                set nocount on

                declare @EMPTYGUID uniqueidentifier;
                set @EMPTYGUID = '00000000-0000-0000-0000-000000000000';

                declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier;
                declare @PAYMENTBASECURRENCYID uniqueidentifier;
                declare @PAYMENTBASEEXCHANGERATEID uniqueidentifier;
                declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @PAYMENTBASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                select
                    @PAYMENTTRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @PAYMENTBASECURRENCYID = CURRENCYSET.BASECURRENCYID,
                    @PAYMENTBASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                    @PAYMENTORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID
                from
                    dbo.FINANCIALTRANSACTION inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where
                    FINANCIALTRANSACTION.ID = @REVENUEID
                                        and FINANCIALTRANSACTION.DELETEDON is null;

                set @PAYMENTBASETOORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTBASETOORGANIZATIONRATE(@PAYMENTBASECURRENCYID, @DATE, null, @PAYMENTORGANIZATIONEXCHANGERATEID);

                if @APPLICATIONID <> @EMPTYGUID 
                begin
                    --Error if an exchange rate isn't entered, but the transaction and base currencies are different,

                    --    and the payment is for anything other than a donation, other, or unapplied MG.

                    if @PAYMENTBASEEXCHANGERATEID is null and @PAYMENTTRANSACTIONCURRENCYID <> @PAYMENTBASECURRENCYID
                    begin
                        raiserror('BBERR_INVALIDAPPLICATIONSWITHNORATE : Payments without an exchange rate can only be applied to the donation, other, or unapplied matching gift application types.', 13, 1);
                        return 1;
                    end

                    declare @INSTALLMENTSUM money
                    set @INSTALLMENTSUM = @APPLIEDAMOUNT;

                    exec dbo.USP_PLEDGE_PAYINSTALLMENTS
                        @APPLICATIONID,
                        @REVENUEID,
                        @INSTALLMENTSUM output,
                        @CHANGEAGENTID,
                        @CREATIONDATE,
                        @CREATEDSPLITS output,
                        @OVERPAYMENTAPPLICATIONTYPECODE,
                        @APPLIEDBASEAMOUNT,
                        @APPLIEDORGANIZATIONAMOUNT,
                        @APPLICATIONSPLITS;

                    set @AMOUNTPAID = @INSTALLMENTSUM;

                    declare @PLEDGESPLIT table
                    (
                        ID uniqueidentifier,
                        SOURCEREVENUESPLITID uniqueidentifier,
                        DESIGNATIONID uniqueidentifier,
                        AMOUNT money
                    )

                    insert into @PLEDGESPLIT 
                    (
                        ID, 
                        SOURCEREVENUESPLITID, 
                        DESIGNATIONID, 
                        AMOUNT -- this is the base amount of the revenue split

                    )
                    select
                        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                        T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') AS 'SOURCEREVENUESPLITID',
                        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'DESIGNATIONID',
                        T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT'
                    from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)

                    -- Generate campaigns for payment splits from pledges

                    --Delete previous values

                    delete REVENUESPLITCAMPAIGN
                    from dbo.REVENUESPLITCAMPAIGN inner join @PLEDGESPLIT t1 on REVENUESPLITCAMPAIGN.REVENUESPLITID = t1.ID

                    insert into dbo.REVENUESPLITCAMPAIGN 
                    (
                        REVENUESPLITID,
                        CAMPAIGNID, 
                        CAMPAIGNSUBPRIORITYID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        PLEDGESPLIT.ID,
                        REVENUESPLITCAMPAIGN.CAMPAIGNID,
                        REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                    from @PLEDGESPLIT as PLEDGESPLIT
                    --inner join dbo.INSTALLMENTSPLITPAYMENT on PLEDGESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID

                    --inner join dbo.FINANCIALTRANSACTIONLINEITEM on PLEDGESPLIT.SOURCEREVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID

                    inner join dbo.REVENUESPLITCAMPAIGN on PLEDGESPLIT.SOURCEREVENUESPLITID = REVENUESPLITCAMPAIGN.REVENUESPLITID
                                        inner join dbo.CAMPAIGN on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGN.ID
                                where CAMPAIGN.ISACTIVE=1; 

                    /* update appeal id with source appealid if appealid isn't supplied during payment */
                    update REX set
                        APPEALID = coalesce(REX.APPEALID, R.APPEALID),
                        MAILINGID = coalesce(REX.MAILINGID, R.MAILINGID),
                        SOURCECODE = coalesce(nullif(REX.SOURCECODE, ''), R.SOURCECODE),
                        CHANNELCODEID = coalesce(REX.CHANNELCODEID, R.CHANNELCODEID)
                    from dbo.REVENUE_EXT REX             
                    join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on REX.ID = FTLI.FINANCIALTRANSACTIONID
                    join dbo.INSTALLMENTSPLITPAYMENT ISP on FTLI.ID = ISP.PAYMENTID 
                    join dbo.REVENUE_EXT R on ISP.PLEDGEID = R.ID 
                    where REX.ID = @REVENUEID          

                    -- Create recognitions unless the application is a matching gift claim OR pending gift

                    if @APPLICATIONTYPE NOT IN (7,17)
                    begin
                        declare @REVENUEGIVENANONYMOUSLY bit,
                                                @SOURCEREVENUESPLITID  uniqueidentifier = null
                        select @REVENUEGIVENANONYMOUSLY = R.GIVENANONYMOUSLY
                        from dbo.REVENUE_EXT R
                        where R.ID = @REVENUEID

                        /*Friends Asking Friends pledge should credit the original recognition */
                        if exists(select R.ID from dbo.REVENUE_EXT R inner join dbo.EVENT E on R.APPEALID = E.APPEALID inner join dbo.EVENTEXTENSION EX on E.ID = EX.EVENTID where R.ID = @APPLICATIONID)
                        begin 
                            select @SOURCEREVENUESPLITID = SOURCEREVENUESPLITID from  @PLEDGESPLIT               
                        end

            delete REVENUERECOGNITION
            from dbo.REVENUERECOGNITION inner join @PLEDGESPLIT t1 on REVENUERECOGNITION.REVENUESPLITID = t1.ID

                        insert into dbo.REVENUERECOGNITION
                        (
                            REVENUESPLITID, 
                            CONSTITUENTID, 
                            AMOUNT,
                            EFFECTIVEDATE,
                            REVENUERECOGNITIONTYPECODEID,
                            BASECURRENCYID,
                            ORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        select
                            PLEDGESPLIT.ID,
                            RECOGNITIONS.CONSTITUENTID,
                            RECOGNITIONS.AMOUNT,
                            @DATE,
                            RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
                            @PAYMENTBASECURRENCYID,
                            case when @PAYMENTBASECURRENCYID = @ORGANIZATIONCURRENCYID 
                                then RECOGNITIONS.AMOUNT
                                else dbo.UFN_CURRENCY_CONVERT(RECOGNITIONS.AMOUNT, @PAYMENTBASETOORGANIZATIONEXCHANGERATEID)
                            end,
                            @PAYMENTBASETOORGANIZATIONEXCHANGERATEID,
                            @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                        from @PLEDGESPLIT as PLEDGESPLIT
                        cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(@REVENUEGIVENANONYMOUSLY, @CONSTITUENTID, AMOUNT, @DATE, @SOURCEREVENUESPLITID) as RECOGNITIONS  /*@SOURCEREVENUESPLITID is null most of cases except FAF pledge payment*/
                                        end        

            delete REVENUESOLICITOR
            from dbo.REVENUESOLICITOR inner join @PLEDGESPLIT t1 on REVENUESOLICITOR.REVENUESPLITID = t1.ID

                    insert into dbo.REVENUESOLICITOR
                    (
                        REVENUESPLITID, 
                        CONSTITUENTID, 
                        AMOUNT, 
                        SEQUENCE,
                        BASECURRENCYID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        PLEDGESPLIT.ID,
                        REVENUESOLICITOR.CONSTITUENTID,
                        --JamesWill 04/03/2006 CR239791-033106 According to documentation, @APPLIEDAMOUNT will

                        --implicitly cast as a decimal. Explicitly performing the cast rounds it, however, and

                        --gives an incorrect result. 

                        case when cast(SOURCESPLIT.BASEAMOUNT as decimal(30, 5)) <> 0 
                        then cast(REVENUESOLICITOR.AMOUNT * (cast(PLEDGESPLIT.AMOUNT as decimal(30, 5))/ cast(SOURCESPLIT.BASEAMOUNT as decimal(30, 5))) as money)
                        else 0.0
                        end,
                        REVENUESOLICITOR.SEQUENCE,
                        @PAYMENTBASECURRENCYID,
                        case when cast(SOURCESPLIT.BASEAMOUNT as decimal(30, 5)) <> 0
                        then
                                case when @PAYMENTBASECURRENCYID = @ORGANIZATIONCURRENCYID 
                                    then cast(REVENUESOLICITOR.AMOUNT * (cast(PLEDGESPLIT.AMOUNT as decimal(30, 5))/ cast(SOURCESPLIT.BASEAMOUNT as decimal(30, 5))) as money)
                                    else dbo.UFN_CURRENCY_CONVERT((cast(REVENUESOLICITOR.AMOUNT * (cast(PLEDGESPLIT.AMOUNT as decimal(30, 5))/ cast(SOURCESPLIT.BASEAMOUNT as decimal(30, 5))) as money)), @PAYMENTBASETOORGANIZATIONEXCHANGERATEID)
                                end
                        else 
                                0.0
                        end,
                        @PAYMENTBASETOORGANIZATIONEXCHANGERATEID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                    from @PLEDGESPLIT as PLEDGESPLIT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as SOURCESPLIT on PLEDGESPLIT.SOURCEREVENUESPLITID = SOURCESPLIT.ID
                                        inner join dbo.REVENUESPLIT_EXT on SOURCESPLIT.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.REVENUESOLICITOR on SOURCESPLIT.ID = REVENUESOLICITOR.REVENUESPLITID
                                        where SOURCESPLIT.DELETEDON is null
                                                and SOURCESPLIT.TYPECODE <> 1

                    -- Copy category codes from the source splits

                    delete REVENUECATEGORY
                    from dbo.REVENUECATEGORY inner join @PLEDGESPLIT t1 on REVENUECATEGORY.ID = t1.ID

                    insert into dbo.REVENUECATEGORY
                    (
                        ID,
                        GLREVENUECATEGORYMAPPINGID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        PLEDGESPLIT.ID,
                        REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                    from @PLEDGESPLIT as PLEDGESPLIT
                    inner join dbo.REVENUECATEGORY on PLEDGESPLIT.SOURCEREVENUESPLITID = REVENUECATEGORY.ID

                    -- Copy opportunity from source

                    delete REVENUEOPPORTUNITY
                    from dbo.REVENUEOPPORTUNITY inner join @PLEDGESPLIT t1 on REVENUEOPPORTUNITY.ID = t1.ID

                    insert into dbo.REVENUEOPPORTUNITY
                    (
                        ID,
                        OPPORTUNITYID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        PLEDGESPLIT.ID,
                        REVENUEOPPORTUNITY.OPPORTUNITYID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                    from @PLEDGESPLIT as PLEDGESPLIT
                    inner join dbo.REVENUEOPPORTUNITY on PLEDGESPLIT.SOURCEREVENUESPLITID = REVENUEOPPORTUNITY.ID

                end
                else
                begin
                    declare @UNAPPLIEDMGSPLITS table
                    (
                        ID uniqueidentifier,
                        AMOUNT money,
                        DESIGNATIONID uniqueidentifier,
                        REVENUEID uniqueidentifier,
                        APPLICATIONCODE tinyint,
                        TYPECODE tinyint
                    )

                    insert into @UNAPPLIEDMGSPLITS
                    (
                        ID,
                        AMOUNT,
                        DESIGNATIONID,
                        REVENUEID,
                        APPLICATIONCODE,
                        TYPECODE
                    )
                    select
                        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                        T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
                        T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'DESIGNATIONID',
                        T.c.value('(REVENUEID)[1]','uniqueidentifier') AS 'REVENUEID',
                        7 as APPLICATIONCODE, -- Matching Gift

                        0 as TYPECODE -- Gift

                    from @UNAPPLIEDMATCHINGGIFTSPLITS.nodes('/UNAPPLIEDMATCHINGGIFTSPLITS/ITEM') T(c)                    

                    update @UNAPPLIEDMGSPLITS set ID = newid() where ID is null

                    select @CREATEDSPLITS = 
                        (
                            select
                                ID,
                                AMOUNT,
                                DESIGNATIONID,
                                REVENUEID,
                                APPLICATIONCODE,
                                TYPECODE
                            from @UNAPPLIEDMGSPLITS
                            for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                        );

                    exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @CREATEDSPLITS, @APPLIEDAMOUNT;

                    exec dbo.USP_REVENUE_GETSPLITS_ADDFROMXML @REVENUEID, @CREATEDSPLITS, @CHANGEAGENTID, @CREATIONDATE;

                    -- Generate default campaigns

                    insert into dbo.REVENUESPLITCAMPAIGN
                    (
                        REVENUESPLITID,
                        CAMPAIGNID,
                        CAMPAIGNSUBPRIORITYID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        UNAPPLIEDMGSPLITS.ID,
                        DEFAULTCAMPAIGNS.CAMPAIGNID,
                        DEFAULTCAMPAIGNS.CAMPAIGNSUBPRIORITYID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                    from @UNAPPLIEDMGSPLITS UNAPPLIEDMGSPLITS
                    cross apply dbo.UFN_DESIGNATION_GETDEFAULTCAMPAIGNS(UNAPPLIEDMGSPLITS.DESIGNATIONID, @DATE) as DEFAULTCAMPAIGNS

                    -- Generate default recognition credits

                    insert into dbo.REVENUERECOGNITION
                    (
                        REVENUESPLITID,
                        CONSTITUENTID,
                        REVENUERECOGNITIONTYPECODEID,
                        AMOUNT,
                        EFFECTIVEDATE,
                        BASECURRENCYID,
                        ORGANIZATIONAMOUNT,
                        ORGANIZATIONEXCHANGERATEID,
                        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                    )
                    select
                        UNAPPLIEDMGSPLITS.ID,
                        RECOGNITIONS.CONSTITUENTID,
                        RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
                        RECOGNITIONS.AMOUNT,
                        FINANCIALTRANSACTION.DATE,
                        @PAYMENTBASECURRENCYID,
                        case when @PAYMENTBASECURRENCYID = @ORGANIZATIONCURRENCYID 
                            then RECOGNITIONS.AMOUNT
                            else dbo.UFN_CURRENCY_CONVERT(RECOGNITIONS.AMOUNT, @PAYMENTBASETOORGANIZATIONEXCHANGERATEID)
                        end,
                        @PAYMENTBASETOORGANIZATIONEXCHANGERATEID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CREATIONDATE, @CREATIONDATE
                    from @UNAPPLIEDMGSPLITS UNAPPLIEDMGSPLITS
                    inner join dbo.FINANCIALTRANSACTION on @REVENUEID = FINANCIALTRANSACTION.ID
                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(REVENUE_EXT.GIVENANONYMOUSLY, @CONSTITUENTID, UNAPPLIEDMGSPLITS.AMOUNT, @DATE, null) as RECOGNITIONS

                    set @AMOUNTPAID = @APPLIEDAMOUNT;
                end

                if @APPLICATIONTYPE = 7
                    begin

                        select @REVENUEGIVENANONYMOUSLY = R.GIVENANONYMOUSLY
                        from dbo.REVENUE_EXT R
                        where R.ID = @REVENUEID

                        set @CREATEDSPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@CREATEDSPLITS, @PAYMENTBASECURRENCYID, @PAYMENTORGANIZATIONEXCHANGERATEID, @PAYMENTBASECURRENCYID, null)
                        -- (Lhunt 12/14/2010: moved code that creates default recognition credits based on MGC preferences to a separate stored procedure so other areas of the apps can use it) 

                        exec dbo.USP_RECOGNITIONCREDITS_ADDBASEDONMGCPREFERENCES
                            @SPLITS = @CREATEDSPLITS,
                            @APPLICATIONID = @APPLICATIONID,
                            @CHANGEAGENTID = @CHANGEAGENTID,
                            @CURRENTDATE = @CREATIONDATE,
                            @REVENUEGIVENANONYMOUSLY = @REVENUEGIVENANONYMOUSLY

                    end

                if @APPLICATIONTYPE = 17
                begin
                    declare @RECOGNITIONCONSTITUENTID uniqueidentifier,
                                    @DATEENTERED datetime,
                                    @REVENUESPLITID uniqueidentifier

                    select 
                        @RECOGNITIONCONSTITUENTID=RR.CONSTITUENTID,
                        @DATEENTERED = REV.DATE
                    from dbo.FINANCIALTRANSACTION REV 
                    join dbo.FINANCIALTRANSACTIONLINEITEM RS ON REV.ID = RS.FINANCIALTRANSACTIONID
                    join dbo.REVENUERECOGNITION RR on RS.ID = RR.REVENUESPLITID
                    where REV.ID = @APPLICATIONID

                    if @RECOGNITIONCONSTITUENTID is not null
                    begin
                        set @CREATEDSPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@CREATEDSPLITS, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), null, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), null)
                        exec dbo.USP_RECOGNITIONCREDIT_ADDDEFAULTSFORMGSPLITS @CREATEDSPLITS, @RECOGNITIONCONSTITUENTID, @DATEENTERED, null, @CHANGEAGENTID, @CREATIONDATE;
                    end

                end

                --Copy revenue attributes of the recurring gift to the payment

                exec dbo.USP_REVENUEATTRIBUTES_COPYTOREVENUE @APPLICATIONID, @REVENUEID, @CHANGEAGENTID

                 /* Apply business units */  
                 if @BUSINESSUNITSAPPLIED = 0
                        exec dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS @REVENUEID, @CHANGEAGENTID, @CREATIONDATE;