USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPDUES

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPDUES
(
    @ID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @TRANSACTIONCURRENCYID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier = null,
    @BASEEXCHANGERATEID uniqueidentifier = null,
    @EXCHANGERATE decimal(20,8) = null,
    @PDACCOUNTSYSTEMID uniqueidentifier = null,
    @POSTSTATUSCODE tinyint = 1,
    @POSTDATE date = null,

    -- What are you doing with this payment?

    @PAYINGORGIVINGRADIO tinyint = 0,

    -- Who is the membership being given to?

    @MEMBERSHIPRECIPIENT uniqueidentifier = null,
    @RENEWALRECIPIENT tinyint = 0,

    -- Who is paying for this?

    @BILLTOCONSTITUENTID uniqueidentifier = null,
    @EFFORTID uniqueidentifier = null,
    @FINDERNUMBER nvarchar(18) = null,
    @APPEALID uniqueidentifier = null,
    @CHANNELCODEID uniqueidentifier = null,
    @DATE date = null,

    -- What are they paying for?

    @WHATPAYINGFORVALUE tinyint = 0,    --0 = Add, 1 = Renew, 2 = Pay, 3 = Upgrade

    @RENEWALREVENUETYPE tinyint = 0,
    @PAYREVENUETYPE tinyint = 0,
    @PAYADDITIONALTONEXTINSTALLMENT bit = 0,
    @PAYADDITIONALMONEYAMOUNT money = 0,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
    @MEMBERSHIPLEVELID uniqueidentifier = null,
    @MEMBERSHIPLEVELTERMID uniqueidentifier = null,
    @MEMBERSHIPEXPIRESONDATE date = null,
    @MEMBERSHIPTRANSACTIONAMOUNT money = null,
    @MEMBERSHIPRECOGNITION xml = null,
    @CONTRIBUTORYDESIGNATIONID uniqueidentifier = null,
    @EXISTINGMEMBERSHIPID uniqueidentifier = null,
    @MEMBERSHIPPLEDGEAMOUNT money = 0,

    --Are these people still included in this membership?

    @EXISTINGMEMBERS xml = null,
    @EXISTINGCHILDREN xml = null,

    --Which members should receive a card?

    @MEMBERSHIPCARDS xml = null,

    -- Is there a discount or promotion?

    @USEDISCOUNTRADIO tinyint = 0,
    @DISCOUNTTYPE uniqueidentifier = null,
    @PROMOTIONCODE nvarchar(50) = null,
    @APPLIEDDISCOUNTID uniqueidentifier = null,
    @MEMBERSHIPPROGRAMADDON xml = null,

    -- Are they also giving a donation?

    @ADDDONATION bit = 0,
    @DONATIONAMOUNT money = 0,
    @GIVENANONYMOUSLY bit = 0,
    @DONATIONOPPORTUNITYID uniqueidentifier = null,
    @DONATIONDESIGNATIONID uniqueidentifier = null,
    @DONATIONCAMPAIGNSLIST nvarchar(100) = null,
    @DONATIONSOLICITORSLIST nvarchar(100) = null,
    @DONATIONRECOGNITIONSLIST nvarchar(100) = null,
    @CAMPAIGNS xml = null,
    @SOLICITORS xml = null,
    @DONATIONCATEGORYCODEID uniqueidentifier = null,
    @RECOGNITIONS xml = null,
    @DECLINESGIFTAID bit = 0,

    --What is the pledge schedule?

    @PLEDGEFREQUENCYCODE tinyint = 5,
    @PLEDGENUMBEROFINSTALLMENTS int = 1,
    @PLEDGESTARTDATE datetime = null,
    @INSTALLMENTS xml = null,
    @SENDPLEDGEREMINDER bit = 1,

    -- How are they paying?

    @CHECKDATE dbo.UDT_FUZZYDATE = null,
    @CHECKNUMBER nvarchar(20) = null,
    @REFERENCENUMBER nvarchar(20) = null,
    @REFERENCEDATE dbo.UDT_FUZZYDATE = null,
    @PAYMENTMETHODCODE tinyint = 1,
    @DIRECTDEBITRESULTCODE nvarchar(10) = null,
    @CONSTITUENTACCOUNTID uniqueidentifier = null,
    @REFERENCE nvarchar(255) = null,
    @AUTOMATICALLYRENEWMEMBERSHIP bit = 0,
    @CREDITCARDNUMBER nvarchar(20) = '',
    @CREDITCARDTOKEN uniqueidentifier = null,
    @CARDHOLDERNAME nvarchar(255) = '',
    @EXPIRESON dbo.UDT_FUZZYDATE = null,
    @AUTHORIZECREDITCARD bit = 0,
    @AUTHORIZATIONCODE nvarchar(20) = '',
    @CSC nvarchar(4) = '',
    @CREDITTYPECODEID uniqueidentifier = null,
    @MERCHANTACCOUNTID uniqueidentifier = null,
    @CREDITCARDTRANSACTIONID uniqueidentifier = null,
    @AUTOPAY bit = 0,

    -- Should it be acknowledged?

    @DONOTACKNOWLEDGE bit = 0,
    @TAXDEDUCTIBLEAMOUNT money = null,
    @LETTERCODEID uniqueidentifier = null,
    @TRIBUTEID uniqueidentifier = null,

    -- Are they also paying for an event registration?

    @NEWEVENTREGISTRATION bit = 0,
    @TOTALAMOUNT money = 0,

    @DONOTRECEIPT bit = 0,
    @BENEFITS xml = null,
    @PERCENTAGEBENEFITS xml = null,
    @COMMENTS nvarchar(255) = '',
    @BATCHNUMBER nvarchar(100) = '',
    @NUMBEROFCHILDREN smallint = 0,     -- Temporary workaround for children not being implemented in 2012 Q1

    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
    @SOURCECODE nvarchar(60) = '',
    @ISONEOFF bit = 0,
    @MEMBERSHIPDECLINESGIFTAID bit = 0,
    @DDISOURCECODEID uniqueidentifier = null,
    @DDISOURCEDATE date = null,
    @VENDORID nvarchar(50) = '',
    @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,

    --BBIS Fields

    @BBNCTRANID int = 0,
    @ORIGINPAGE nvarchar(100) = '', 
    @ORIGINPAGEID int = 0,

    @SEPAMANDATEID uniqueidentifier = null,
  @BATCHROWID uniqueidentifier = null,
  @REVENUECATEGORYID uniqueidentifier = null,

  @SOLICITCODES xml = null
)
as

    set nocount on;

    declare @ANNUALPROGRAMTYPECODE int = 0
    declare @RECURRINGPROGRAMTYPECODE int = 1
    declare @LIFETIMEPROGRAMTYPECODE int = 2

    --These correspond to MembershipTransactionTypeCode declared in Blackbaud.AppFx.Membership.UIModel.MembershipDuesCommon 

    declare @ADD_MEMBERSHIPTRANSACTTIONTYPECODE int = 0
    declare @RENEW_MEMBERSHIPTRANSACTTIONTYPECODE int = 1
    declare @PAY_MEMBERSHIPTRANSACTTIONTYPECODE int = 2
    declare @UPGRADE_MEMBERSHIPTRANSACTTIONTYPECODE int = 3
    -- end MembershipTransactionTypeCode


    declare @MEMBERSHIPSPLITID uniqueidentifier
    declare @RGORPLEDGEMEMBERSHIPSPLITID uniqueidentifier
    declare @PAYMENTMETHODID uniqueidentifier
    declare @TAXDEDUCTIBLETYPE tinyint
    declare @INSTALLMENTPLEDGEPOSTSTATUSCODE tinyint
    declare @SPLITS xml;

    select 
        @TAXDEDUCTIBLETYPE = DEDUCTIBILITYCODE,
        @INSTALLMENTPLEDGEPOSTSTATUSCODE = case MEMBERSHIPPROGRAM.INSTALLMENTPOSTSTATUSCODE --Converting from FTM post status to USP_PLEDGE_ADD post status

            when 3 then 2
            when 1 then 1
        end
    from dbo.MEMBERSHIPPROGRAM 
    where ID = @MEMBERSHIPPROGRAMID

    if @ID is null
        set @ID = newid()

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

    if @POSTSTATUSCODE is null
        set @POSTSTATUSCODE = 1 --Not posted


    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

    set @BENEFITS = dbo.UFN_MEMBERSHIPBENEFITS_SETIDSTONULL(@BENEFITS)
    set @PERCENTAGEBENEFITS = dbo.UFN_MEMBERSHIPPERCENTAGEBENEFITS_SETIDSTONULL(@PERCENTAGEBENEFITS)

        declare @STARTINGSTATUSCODE tinyint
        select
            @STARTINGSTATUSCODE = STATUSCODE
        from dbo.MEMBERSHIP
        where ID = @EXISTINGMEMBERSHIPID

        declare @SPLITSGIFTAIDINFOTBL table
        (
            REVENUESPLITID uniqueidentifier,
            DECLINESGIFTAID bit,
            ISCOVENANT bit,
            ISSPONSORSHIP bit
        )

        declare @SPLITSDECLININGGIFTAID xml
        declare @PAYMENTGIFTID uniqueidentifier

        if @PDACCOUNTSYSTEMID is null
            set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B';

        -- Check GL business rule for this account system and set to 'Do not post' if needed.

        if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
        begin
            set @POSTSTATUSCODE = 2; -- Do not post

            set @INSTALLMENTPLEDGEPOSTSTATUSCODE = 2; -- Do not post

            set @POSTDATE = null;
        end

        if @POSTSTATUSCODE = 2
            set @POSTDATE = null;

        if @POSTDATE is null and @POSTSTATUSCODE <> 2
            set @POSTDATE = @DATE

        --Set currency parameters for backwards compatibility

        if @TRANSACTIONCURRENCYID is null
            set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

        --Multicurrency - Retrieve base currency from the account system's currency set.

        declare @CURRENCYSETID uniqueidentifier;
        select @CURRENCYSETID = CURRENCYSETID from dbo.PDACCOUNTSYSTEM where ID = @PDACCOUNTSYSTEMID;

        if @CURRENCYSETID is null
            set @CURRENCYSETID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();

        select @BASECURRENCYID = BASECURRENCYID from dbo.CURRENCYSET where ID = @CURRENCYSETID;

        -- Ensure that we can add a payment of this transaction currency to the account system.

        if not exists
        (
            select 1
            from 
                dbo.CURRENCYSETTRANSACTIONCURRENCY
                inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSETTRANSACTIONCURRENCY.CURRENCYSETID
            where 
                PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
                and CURRENCYSETTRANSACTIONCURRENCY.CURRENCYID = @TRANSACTIONCURRENCYID
        )
        begin
            raiserror('TRANSACTIONCURRENCYINVALIDFORACCOUNTSYSTEM',13,1)
        end

        if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
        begin
            set @BASEEXCHANGERATEID = newid()

            --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

            /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
            and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
            begin
                raiserror('User does not have the right to add a new spot rate.', 13, 1);
                return 1;
            end*/

            insert into dbo.CURRENCYEXCHANGERATE
            (
                ID,
                FROMCURRENCYID,
                TOCURRENCYID,
                RATE,
                ASOFDATE,
                TYPECODE,
                SOURCECODEID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @BASEEXCHANGERATEID,
                @TRANSACTIONCURRENCYID,
                @BASECURRENCYID,
                @EXCHANGERATE,
                @DATE,
                2,
                null,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );
        end

        --Get Multicurrency values.

        declare @BASEAMOUNT money;
        declare @DONATIONBASEAMOUNT money
        declare @ORGANIZATIONAMOUNT money;
        declare @DONATIONORGANIZATIONAMOUNT money;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
        declare @ORGANIZATIONCURRENCYID uniqueidentifier;
        declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;
        declare @BASECURRENCYDECIMALDIGITS tinyint;
        declare @ORGANIZATIONCURRENCYDECIMALDIGITS tinyint;
        declare @TRANSACTIONCURRENCYDECIMALDIGITS tinyint;
        declare @NONREVENUEBASEAMOUNT money = 0;
        declare @WHEREISREVENUETRACKEDCODE tinyint;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @MEMBERSHIPTRANSACTIONAMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1, @BASETOORGANIZATIONEXCHANGERATEID output;

        select @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS
        from dbo.CURRENCY
        where ID = @BASECURRENCYID

        select @ORGANIZATIONCURRENCYDECIMALDIGITS = DECIMALDIGITS
        from dbo.CURRENCY
        where ID = @ORGANIZATIONCURRENCYID

        select @TRANSACTIONCURRENCYDECIMALDIGITS = DECIMALDIGITS
        from dbo.CURRENCY
        where ID=@TRANSACTIONCURRENCYID

        declare @LATESTBASEEXCHANGERATE uniqueidentifier
        set @LATESTBASEEXCHANGERATE =  dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@DATE,1,null)

        select @WHEREISREVENUETRACKEDCODE = WHEREISREVENUETRACKEDCODE
            from dbo.MEMBERSHIPPROGRAM
            where ID = @MEMBERSHIPPROGRAMID

        if (@WHEREISREVENUETRACKEDCODE = 1)
        begin
            if (@TRANSACTIONCURRENCYID = @BASECURRENCYID)
                SET @NONREVENUEBASEAMOUNT = @MEMBERSHIPTRANSACTIONAMOUNT
            else
                SET @NONREVENUEBASEAMOUNT = dbo.UFN_CURRENCY_CONVERT(@MEMBERSHIPTRANSACTIONAMOUNT,@BASEEXCHANGERATEID)
        end

        if exists
            (
                select * from dbo.MEMBERSHIPTRANSACTION
                where MEMBERSHIPID = @EXISTINGMEMBERSHIPID and cast(TRANSACTIONDATE as date) > @DATE
            )
            raiserror('BBERR_DATEEARLIERTHANLASTMEMBERSHIPTRANSACTION', 13,1);

        if @PAYMENTMETHODCODE = 0 or @PAYMENTMETHODCODE = 1
            set @AUTOMATICALLYRENEWMEMBERSHIP = 0

        declare @PAYMENTCREATED bit = 0;

        if @ISONEOFF = 1
        begin

            declare @ISGROUP bit = 0;

      declare @CONSTITUENTID uniqueidentifier;
      select @CONSTITUENTID = 
        case @PAYINGORGIVINGRADIO 
          when 0 then @BILLTOCONSTITUENTID 
          else @MEMBERSHIPRECIPIENT 
        end;

            select
                @ISGROUP = ISGROUP
            from dbo.CONSTITUENT
            where 
                ID = @CONSTITUENTID;

            -- If adding a new membership

            if @WHATPAYINGFORVALUE = @ADD_MEMBERSHIPTRANSACTTIONTYPECODE or @WHATPAYINGFORVALUE = @RENEW_MEMBERSHIPTRANSACTTIONTYPECODE
                begin
                    if dbo.UFN_MEMBERSHIPDUESBATCH_CANADDCONSTITUENTTOPROGRAM_3(@CONSTITUENTID, @MEMBERSHIPPROGRAMID,@WHATPAYINGFORVALUE,@EXISTINGMEMBERSHIPID) = 0
                        raiserror('BBERR_CONSTITUENTINVALIDFORPROGRAM', 13,1);
                end
            else
                begin
                    -- Do not allow individuals to renew other individuals' memberships

                    if @ISGROUP = 0
                    begin
                            if @EXISTINGMEMBERSHIPID is not null and not exists(select 1 from dbo.MEMBER where MEMBERSHIPID = @EXISTINGMEMBERSHIPID and CONSTITUENTID = @CONSTITUENTID)
                                raiserror('BBERR_INVALIDMEMBERSHIP_FORCONSTITUENT', 13,1);
                    end
                    else
                    begin

                    -- Do not allow backdate transaction unless it is for a pending membership

                    declare @CURRENTSTATUS tinyint = 0;
                    declare @JOINDATE datetime;

                    select
                        @CURRENTSTATUS = STATUSCODE,
                        @JOINDATE = JOINDATE
                    from dbo.MEMBERSHIP 
                    where ID = @EXISTINGMEMBERSHIPID;

                    if @MEMBERSHIPEXPIRESONDATE < @JOINDATE
                        raiserror('BBERR_EXPIRATIONDATEB4JOINDATE', 13, 1);
                end
            end
        end

        if @WHATPAYINGFORVALUE = @PAY_MEMBERSHIPTRANSACTTIONTYPECODE
        begin
            declare @LATESTMEMBERSHIPTRANSACTIONID uniqueidentifier = null;
            select @LATESTMEMBERSHIPTRANSACTIONID = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID(@EXISTINGMEMBERSHIPID);

            --Get the pledge or recurring gift that this payment is being applied to

            declare @EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID uniqueidentifier
            select top 1
                @EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            from dbo.MEMBERSHIPTRANSACTION
                left join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                left join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                left join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            where
                MEMBERSHIPTRANSACTION.ID = @LATESTMEMBERSHIPTRANSACTIONID
                and FINANCIALTRANSACTION.TYPECODE in (2,15)
                and FINANCIALTRANSACTION.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                and (
                            (
                                FINANCIALTRANSACTION.TYPECODE = 15 and
                                MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= coalesce(MEMBERSHIP.LASTRENEWEDON, MEMBERSHIP.JOINDATE)
                            ) or
                            (
                                FINANCIALTRANSACTION.TYPECODE = 2 and
                                MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= MEMBERSHIP.JOINDATE
                            )
                        )
            order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc;

            if @EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID is null
                        raiserror('BBERR_NOCOMMITMENTFORPAY', 13, 1)

            if (@WHEREISREVENUETRACKEDCODE = 0)
            begin
                --Create payment

                exec dbo.USP_PAYMENT_ADDBASE 
                    @ID output, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @BILLTOCONSTITUENTID,
                    @DATE, 
                    @TOTALAMOUNT, 
                    @PAYMENTMETHODCODE, 
                    @CHECKDATE, 
                    @CHECKNUMBER,
                    @REFERENCEDATE, 
                    @REFERENCENUMBER, 
                    @CARDHOLDERNAME, 
                    @CREDITCARDNUMBER,
                    @CREDITTYPECODEID, 
                    @AUTHORIZATIONCODE, 
                    @EXPIRESON, 
                    '',
                    0, 
                    '', 
                    0, 
                    null,
                    null, 
                    @TAXDEDUCTIBLEAMOUNT, 
                    @CONSTITUENTACCOUNTID,
                    @POSTSTATUSCODE, 
                    @POSTDATE, 
                    @FINDERNUMBER,
                    @SOURCECODE, 
                    @APPEALID, 
                    @BENEFITS, 
                    0, 
                    @GIVENANONYMOUSLY, 
                    @EFFORTID, 
                    @CHANNELCODEID, 
                    @DONOTACKNOWLEDGE,@DONOTRECEIPT,
                    @BATCHNUMBER, 
                    @OTHERPAYMENTMETHODCODEID, 
                    @REFERENCE, 
                    @TRIBUTEID, 
                    @LETTERCODEID, 
                    @DIRECTDEBITRESULTCODE, 
                    0, 
                    0, 
                    '', 
                    0, 
                    0, 
                    0, 
                    @PERCENTAGEBENEFITS, 
                    @TRANSACTIONCURRENCYID,
                    @BASECURRENCYID, 
                    @BASEEXCHANGERATEID, 
                    @EXCHANGERATE, 
                    @CURRENTAPPUSERID, 
                    @SEPAMANDATEID;

                exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
                        @ID = @ID,
                        @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                        @CHANGEDATE = @CURRENTDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID

                set @PAYMENTMETHODID  = (select ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID)

                if @CREDITCARDTRANSACTIONID is not null and @PAYMENTMETHODCODE = 2 -- Credit card

                begin
                    update dbo.CREDITCARDPAYMENTMETHODDETAIL set
                        TRANSACTIONID = @CREDITCARDTRANSACTIONID,
                        VENDORID = isnull(@VENDORID, ''),
                        DATECHANGED = @CURRENTDATE,
                        CHANGEDBYID = @CHANGEAGENTID
                    where ID = @PAYMENTMETHODID
                end

                declare @AMOUNTPAID money;
                declare @CREATEDSPLITS xml;
                declare @PAYMENTSPLITSDECLININGGIFTAID xml
                declare @PAYMENTRECEIPTTYPECODE tinyint;
                declare @PAYMENTAMOUNT money = @MEMBERSHIPTRANSACTIONAMOUNT + @PAYADDITIONALMONEYAMOUNT

                if (select TYPECODE from dbo.FINANCIALTRANSACTION where ID = @EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID and DELETEDON is null) = 15
                begin
                    --Add the payment to the appropriate installment plan

                    exec dbo.USP_PLEDGE_ADDPAYMENT 
                        @ID,
                        @EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID,
                        @PAYMENTAMOUNT,
                        @BILLTOCONSTITUENTID,
                        @DATE,
                        null,
                        19,
                        @AMOUNTPAID,
                        @CURRENTDATE,
                        @CHANGEAGENTID,
                        @CREATEDSPLITS output,
                        1,
                        0,
                        null,
                        null;

                    set @PAYMENTRECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@BILLTOCONSTITUENTID,2);

                    set @RGORPLEDGEMEMBERSHIPSPLITID = (
                        select T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                        from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                        where T.c.value('(TYPECODE)[1]','tinyint') = 2
                    )
                end
                else
                begin
                    declare @NEXTRECURRINGGIFTINSTALLMENTID uniqueidentifier = dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENT(@EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID, null)

                    exec dbo.USP_RECURRINGGIFT_ADDPAYMENT 
                        @REVENUEID = @ID,
                        @APPLICATIONID = @EXISTINGMEMBERSHIPFINANCIALTRANSACTIONID,
                        @APPLIEDAMOUNT = @PAYMENTAMOUNT,
                        @CONSTITUENTID = @BILLTOCONSTITUENTID,
                        @DATE = @DATE,
                        @CREATIONDATE = @CURRENTDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @CREATEDSPLITS = @CREATEDSPLITS output,
                        @BASEAPPLIEDAMOUNT = null,
                        @ORGANIZATIONAPPLIEDAMOUNT = null;

                    set @PAYMENTRECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@BILLTOCONSTITUENTID,3);

                        set @RGORPLEDGEMEMBERSHIPSPLITID = (select
                                    T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                                where T.c.value('(TYPECODE)[1]','tinyint') = 2)

                    --if the balance of the installment is now 0, then update the membership

                    if dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(@NEXTRECURRINGGIFTINSTALLMENTID) = 0
                    begin
                        update dbo.MEMBERSHIP
                        set LASTRENEWEDON = dbo.UFN_DATE_GETEARLIESTTIME(@DATE),
                            MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID, 
                            MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
                            ISGIFT = @PAYINGORGIVINGRADIO,
                            GIVENBYID = case when @PAYINGORGIVINGRADIO = 1 then @BILLTOCONSTITUENTID else null end,
                            CHANGEDBYID = @CHANGEAGENTID, 
                            DATECHANGED = @CURRENTDATE,
                            STATUSCODE = 0,
                            AUTOMATICALLYRENEWMEMBERSHIP = @AUTOMATICALLYRENEWMEMBERSHIP
                        where MEMBERSHIP.ID = @EXISTINGMEMBERSHIPID
                    end
                end

                -- create revenue category for any payments

                if @REVENUECATEGORYID is not null
                begin
                    exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @REVENUECATEGORYID, @CHANGEAGENTID, @CURRENTDATE;
                end

                set @PAYMENTSPLITSDECLININGGIFTAID =
                    (
                        select
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                        from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                            inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                        where REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
                        for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                    )

                if @ADDDONATION = 1
                begin
                    exec dbo.USP_GIFT_ADDPAYMENT
                        @REVENUEID = @ID,
                        @AMOUNT = @DONATIONAMOUNT,
                        @DESIGNATIONID = @DONATIONDESIGNATIONID,
                        @OPPORTUNITYID = @DONATIONOPPORTUNITYID,
                        @CAMPAIGNS = @CAMPAIGNS,
                        @SOLICITORS = @SOLICITORS,
                        @RECOGNITIONCREDITS = @RECOGNITIONS,
                        @CATEGORYCODEID = @DONATIONCATEGORYCODEID,
                        @CREATIONDATE =  @CURRENTDATE,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @ID = @PAYMENTGIFTID output,
                        @REVENUETYPECODE = 0

                    select @SPLITS = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                        from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                        where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0
                                        group by REVENUESPLIT_EXT.DESIGNATIONID
                                        for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);  

                    -- create matching gift records

                    if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                        exec USP_MATCHINGGIFTPLEDGE_AUTOADD @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                    if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                        exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;
                end

                exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, @PAYMENTSPLITSDECLININGGIFTAID;

                update dbo.REVENUE_EXT
                set RECEIPTTYPECODE = @PAYMENTRECEIPTTYPECODE
                where ID = @ID;

                if @PAYMENTAMOUNT + @DONATIONAMOUNT < (select sum(TRANSACTIONAMOUNT) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                    raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                if @PAYMENTAMOUNT + @DONATIONAMOUNT <> (select sum(TRANSACTIONAMOUNT) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) -- and @RENEWALREVENUETYPE <> 3

                    raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                if (select count(*) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) = 0
                    raiserror('BBERR_NOAPPLICATIONS', 13, 1);

                -- Add gift fees

                exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @BILLTOCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

                            if @BBNCTRANID > 0
                    insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values(@ID, @BBNCTRANID, @ORIGINPAGE, @ORIGINPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                        

                --If a SEPA mandate is used, mark the mandate record to show that a payment has been made towards it.

                if @SEPAMANDATEID is not null
                begin
                    if @BATCHROWID is not null
                    begin
                        exec dbo.USP_SEPAMANDATE_PAYMENTMADE @SEPAMANDATEID,@BATCHROWID,@ID, @CHANGEAGENTID;
                    end
                end

                set @PAYMENTCREATED = 1
            end
        end
        else
        begin
            declare @CANBEPAIDINFULL bit = 0;
            declare @CANBEPLEDGED bit = 0;
            declare @PLEDGECREATED bit = 0;
            declare @RECURRINGGIFTCREATED bit = 0;
            declare @PLEDGEID uniqueidentifier;
            declare @RECURRINGGIFTID uniqueidentifier;
            --declare @PAYMENTCREATED bit = 0;

            declare @MEMBERSHIPACTIONCODE tinyint = 0;
            declare @ALLOWMULTIPLEMEMBERSHIPS bit = 0;
            declare @NUMBEROFMEMBERS smallint = 0;
            declare @CHILDREN smallint = 0;
            --declare @NUMBEROFCHILDREN smallint = 0;   -- Commented out for 2012 Q1; children will be implemented later. For now @NUMBEROFCHILDREN is a parameter.

            declare @CONTRIBUTED bit = 0;

            declare @MEMBERS table(ID uniqueidentifier, ISPRIMARY bit);
            declare @PRIMARYMEMBERID uniqueidentifier;
            declare @BILLTOISGROUP bit = 0;

            declare @PROGRAMTYPE tinyint = 0;

            -- Create a Primary member field to encapsulate who should receive the membership.

            if @PAYINGORGIVINGRADIO = 1
                set @PRIMARYMEMBERID = @MEMBERSHIPRECIPIENT;
            else
                set @PRIMARYMEMBERID = @BILLTOCONSTITUENTID;

            select
                @BILLTOISGROUP = CONSTITUENT.ISGROUP
            from dbo.CONSTITUENT
            where
                CONSTITUENT.ID = @BILLTOCONSTITUENTID;

            if @BILLTOISGROUP = 1 and @PAYINGORGIVINGRADIO = 0 and @WHATPAYINGFORVALUE = @RENEW_MEMBERSHIPTRANSACTTIONTYPECODE and @EXISTINGMEMBERSHIPID is not null
            begin
                -- groups can renew a membership for a member of the group. 

                -- As such, we need to change the primary member to be the person on the existing membership

                select
                    @PRIMARYMEMBERID = MEMBER.CONSTITUENTID
                from dbo.MEMBER
                where
                    MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID
                    and MEMBER.ISPRIMARY = 1;
            end

            declare @EXISTINGPRIMARYMEMBER uniqueidentifier = null;
            select
                @EXISTINGPRIMARYMEMBER = MEMBER.CONSTITUENTID
            from dbo.MEMBER
            where
                MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID
                and MEMBER.ISPRIMARY = 1;

            insert into @MEMBERS(ID, ISPRIMARY)
            select T.members.value('(CONSTITUENTID)[1]','uniqueidentifier'), 0
            from @EXISTINGMEMBERS.nodes('/EXISTINGMEMBERS/ITEM') T(members)
            where T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') is not null
            union all
            select @PRIMARYMEMBERID, 1


            -- When we're renewing, rejoining or upgrading...we don't want to change the primary if the billto changes, we want whomever is currently primary to remain.

            if ((@WHATPAYINGFORVALUE = @RENEW_MEMBERSHIPTRANSACTTIONTYPECODE or @WHATPAYINGFORVALUE = @UPGRADE_MEMBERSHIPTRANSACTTIONTYPECODE) and @EXISTINGMEMBERSHIPID is not null)
            begin

                --If primary member is in the @EXISTINGMEMBERS collection that means they're not the recipient or the billto and   we need to swap the ISPRIMARY flag so the primary remains 

                if exists (select 1 from 
                                     @EXISTINGMEMBERS.nodes('/EXISTINGMEMBERS/ITEM') T(members) 
                                     where T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') = @EXISTINGPRIMARYMEMBER)
                begin
                    update @MEMBERS
                    set ISPRIMARY = 0
                    where ID = @PRIMARYMEMBERID;

                    update @MEMBERS
                    set ISPRIMARY = 1
                    where ID = @EXISTINGPRIMARYMEMBER;
                end
            end

            select
                @CANBEPAIDINFULL = MEMBERSHIPPROGRAM.ONEPAYMENTEACHTERM,
                @CANBEPLEDGED = MEMBERSHIPPROGRAM.MULTIPLEPAYMENTSEACHTERM,
                @ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
                @CHILDREN = MEMBERSHIPLEVEL.CHILDRENALLOWED,
                @NUMBEROFMEMBERS = MEMBERSHIPLEVEL.MEMBERSALLOWED,
                @PROGRAMTYPE = MEMBERSHIPPROGRAM.PROGRAMTYPECODE,
                @CONTRIBUTED = case when MEMBERSHIPLEVEL.OBTAINLEVELCODE = 1 then 1 else 0 end
            from
                dbo.MEMBERSHIPPROGRAM
                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
            where
                MEMBERSHIPPROGRAM.ID = @MEMBERSHIPPROGRAMID
                and MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID

            declare @RECURRINGPAYMENTOPTIONS_MAKENEXTPAYMENT tinyint = 1
            declare @RECURRINGPAYMENTOPTIONS_CREATEGIFTONLY tinyint = 2
            declare @RECURRINGPAYMENTOPTION tinyint = null

            --Use @RECURRINGPAYMENTOPTION when you want to use @RENEWALREVENUETYPE only for recurring/sustaining memberships

            set @RECURRINGPAYMENTOPTION = case when @PROGRAMTYPE = @RECURRINGPROGRAMTYPECODE
                                                                                then @RENEWALREVENUETYPE 
                                                                                else null
                                                                        end

            declare @INSTALLMENTPAYMENTOPTION tinyint = null
            --Use @INSTALLMENTPAYMENTOPTION when you want to use @RENEWALREVENUETYPE only for installment based memberships (lifetime not paid in full or annual dues w/ installment payments)

            SET @INSTALLMENTPAYMENTOPTION = case when @PROGRAMTYPE <> @RECURRINGPROGRAMTYPECODE 
                                                                                then @RENEWALREVENUETYPE
                                                                                else null
                                                                            end

            --Store Addons

            declare @ADDONS table
            (
                ID uniqueidentifier,
                APPLY bit,
                ADDONID uniqueidentifier, 
                PRICE money,
                NUMBEROFADDONS integer,
                REVENUESPLITID uniqueidentifier
            );
            declare @ADDITIONALMEMBERS integer = 0;

            insert into @ADDONS(ID,APPLY, ADDONID, PRICE, NUMBEROFADDONS)
                select
                    newid(),
                    T.c.value('APPLY [1]', 'bit'),
                    T.c.value('ADDONID [1]', 'uniqueidentifier'),
                    T.c.value('PRICE [1]', 'money'),
                    T.c.value('NUMBEROFADDONS [1]', 'int')
                from @MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM') T(c)

            select
                @ADDITIONALMEMBERS = COALESCE(sum(NA.NUMBEROFADDONS), 0)
            from @ADDONS NA
                inner join dbo.ADDON A on NA.ADDONID = A.ID
            where
                A.ADDONTYPECODE = 1 and
                NA.APPLY = 1;

            set @NUMBEROFMEMBERS = @NUMBEROFMEMBERS + @ADDITIONALMEMBERS;

            declare @ERRORMSG nvarchar(52)
            if @NUMBEROFMEMBERS > 1
            begin
                set @ERRORMSG = 'Only ' + convert(nvarchar(6), @NUMBEROFMEMBERS) + ' members are allowed for this membership.'
            end
            else
            begin
                set @ERRORMSG = 'Only ' + convert(nvarchar(6), @NUMBEROFMEMBERS) + ' member is allowed for this membership.'
            end

            if @NUMBEROFMEMBERS < (select count(ID) from @MEMBERS)
            begin
                raiserror(@ERRORMSG, 13, 1);
            end

            set @ERRORMSG = 'The number of children may not be larger than '+convert(nvarchar(6), @CHILDREN)+'.'

            --For a future release, when children exist:


            --select @NUMBEROFCHILDREN =

            --count(*) from @EXISTINGCHILDREN.nodes('/EXISTINGCHILDREN/ITEM') T(c)


            -- Number of children validation

            if @CHILDREN < @NUMBEROFCHILDREN --and @CHILDREN > 0

            begin
                raiserror(@ERRORMSG, 13, 1);
            end

            if exists
            (
                select count(ID)
                from @MEMBERS
                group by ID
                having count(*) > 1
            )
                raiserror('BBERR_DUPLICATEMEMBER', 13,1);

            declare @UPGRADEACTIONCODE int = 2
            declare @DOWNGRADEACTIONCODE int = 3

            declare @UPGRADEMETHODCODE int
            declare @NOTANUPGRADE int = 0
            declare @RENEWALUPGRADE int = 1
            declare @MIDTERMUPGRADE int = 2

            set @MEMBERSHIPACTIONCODE = 
                    case @WHATPAYINGFORVALUE
                        when @UPGRADE_MEMBERSHIPTRANSACTTIONTYPECODE then @UPGRADEACTIONCODE
                        else dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@EXISTINGMEMBERSHIPID, @MEMBERSHIPLEVELID, @DATE)
                    end
            set @UPGRADEMETHODCODE = 
                    case 
                        when @WHATPAYINGFORVALUE = @UPGRADE_MEMBERSHIPTRANSACTTIONTYPECODE then @MIDTERMUPGRADE
                        when (@WHATPAYINGFORVALUE = @RENEW_MEMBERSHIPTRANSACTTIONTYPECODE) and (@MEMBERSHIPACTIONCODE IN (@UPGRADEACTIONCODE,@DOWNGRADEACTIONCODE)) then @RENEWALUPGRADE
                        else @NOTANUPGRADE
                    end

            if (@WHEREISREVENUETRACKEDCODE = 0)
            begin
                if @PROGRAMTYPE = @RECURRINGPROGRAMTYPECODE
                begin
                    --Create a recurring gift for this membership

                    declare @RGCONTRIBUTEDAMOUNT money = 0;
                    declare @RGTERMCODE tinyint;
                    declare @RGTAXDEDUCTIBLEAMOUNT money;

                    --If were just creating the recurring gift, the taxdeductibleamount is going to be zero so look at the level instead.

                    if @RENEWALREVENUETYPE = 2
                    begin
                        if @TAXDEDUCTIBLETYPE = 0 -- "Yes, the entire amount is deductible"

                        begin
                            set @RGTAXDEDUCTIBLEAMOUNT = (select AMOUNT from dbo.MEMBERSHIPLEVELTERM where ID = @MEMBERSHIPLEVELTERMID);
                        end
                        else
                        begin
                            set @RGTAXDEDUCTIBLEAMOUNT = (select RECEIPTAMOUNT from dbo.MEMBERSHIPLEVEL where ID = @MEMBERSHIPLEVELID);
                        end

                        if @RGTAXDEDUCTIBLEAMOUNT > @MEMBERSHIPTRANSACTIONAMOUNT
                            set @RGTAXDEDUCTIBLEAMOUNT = @MEMBERSHIPTRANSACTIONAMOUNT

                    end
                    else
                    begin
                        set @RGTAXDEDUCTIBLEAMOUNT = @TAXDEDUCTIBLEAMOUNT
                    end

                    declare @RGSPLITTABLE table(ID uniqueidentifier, APPLICATIONCODE tinyint, TYPECODE tinyint, DESIGNATIONID uniqueidentifier, AMOUNT money, DECLINESGIFTAID bit, TRANSACTIONCURRENCYID uniqueidentifier)
                    insert into @RGSPLITTABLE(ID, APPLICATIONCODE, TYPECODE, DESIGNATIONID, AMOUNT, DECLINESGIFTAID, TRANSACTIONCURRENCYID)
                        select newid(), 0, 0, CONTRIBUTEDAMOUNTS.DESIGNATIONID, CONTRIBUTEDAMOUNTS.AMOUNT, @MEMBERSHIPDECLINESGIFTAID, @TRANSACTIONCURRENCYID
                        from dbo.UFN_MEMBERSHIP_GETCONTRIBUTEDAMOUNTSBYLEVELID(@MEMBERSHIPLEVELID, (@RGTAXDEDUCTIBLEAMOUNT - @DONATIONAMOUNT),@TRANSACTIONCURRENCYDECIMALDIGITS) CONTRIBUTEDAMOUNTS

                    select @RGCONTRIBUTEDAMOUNT = sum(AMOUNT) from @RGSPLITTABLE

                    declare @RGMEMBERSHIPPARENTSPLITID uniqueidentifier = newid()

                    declare @RGSPLITS xml =
                        (
                            select RGSPLITS.ID, RGSPLITS.APPLICATIONCODE, RGSPLITS.TYPECODE, RGSPLITS.DESIGNATIONID, RGSPLITS.AMOUNT, RGSPLITS.DECLINESGIFTAID, RGSPLITS.TRANSACTIONCURRENCYID
                            from
                                (
                                    select
                                        @RGMEMBERSHIPPARENTSPLITID as ID,
                                        5 as APPLICATIONCODE,
                                        2 as TYPECODE,
                                        null as DESIGNATIONID,
                                        @MEMBERSHIPTRANSACTIONAMOUNT - coalesce(@RGCONTRIBUTEDAMOUNT, 0) as AMOUNT,
                                        @MEMBERSHIPDECLINESGIFTAID as DECLINESGIFTAID,
                                        @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID

                                        union all

                                        select
                                            ID as ID,
                                            APPLICATIONCODE,
                                            TYPECODE,
                                            DESIGNATIONID,
                                            AMOUNT,
                                            DECLINESGIFTAID,
                                            TRANSACTIONCURRENCYID
                                        from @RGSPLITTABLE) as RGSPLITS
                                        for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                                );

                    select @RGTERMCODE = MEMBERSHIPLEVELTERM.RECURRINGPAYMENTOPTIONCODE
                    from dbo.MEMBERSHIPLEVELTERM
                    where MEMBERSHIPLEVELTERM.ID = @MEMBERSHIPLEVELTERMID


                    --We must get a new base exchange rate here because the base currency of the user will be used as the base currency of the recurring gift, which may

                    --  be different from the base currency specified by the account system used.

                    declare @RGBASEEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID), @DATE, 1, null)

                    exec USP_DATAFORMTEMPLATE_ADD_RECURRINGGIFT
                        @RECURRINGGIFTID output,
                        @CHANGEAGENTID,
                        @BILLTOCONSTITUENTID,
                        @DATE,
                        @MEMBERSHIPTRANSACTIONAMOUNT,
                        @PAYMENTMETHODCODE,
                        @REFERENCEDATE,
                        @REFERENCENUMBER,
                        @CARDHOLDERNAME,
                        @CREDITCARDNUMBER,
                        @CREDITTYPECODEID,
                        @EXPIRESON,
                        @CONSTITUENTACCOUNTID,
                        @RGSPLITS,
                        @RGTERMCODE,
                        null,
                        @DATE,
                        @FINDERNUMBER,
                        @SOURCECODE,
                        @APPEALID,
                        @GIVENANONYMOUSLY,
                        @EFFORTID,
                        @CHANNELCODEID,
                        @DONOTACKNOWLEDGE,
                        @AUTOPAY,
                        @REFERENCE,
            @REVENUECATEGORYID,
                        @CREDITCARDTOKEN,
                        0,   -- @STANDINGORDERSETUP

                        null,-- @STANDINGORDERSETUPDATE

                        @DDISOURCECODEID,
                        @DDISOURCEDATE,
                        1,
                        @TRANSACTIONCURRENCYID,
                        @RGBASEEXCHANGERATEID,
                        @EXCHANGERATE,
                        @CURRENTAPPUSERID,
                        0,
                        1,
                        null,
                        null,
                        null,
                        1,
                        @BATCHNUMBER,
                        @BASECURRENCYID,
                        @SEPAMANDATEID;

                    -- add benefits to recurring gift - bug 207057

                    declare @TOTALBENEFITS xml;
                    set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);

                    --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.

                    set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS,@TRANSACTIONCURRENCYID,@BASECURRENCYID);
                    exec dbo.USP_REVENUE_GETBENEFITS_3_ADDFROMXML @RECURRINGGIFTID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

                    update dbo.FINANCIALTRANSACTIONLINEITEM
                    set SOURCELINEITEMID = @RGMEMBERSHIPPARENTSPLITID
                    from dbo.FINANCIALTRANSACTIONLINEITEM
                    inner join @RGSPLITTABLE RGSPLITTABLE on RGSPLITTABLE.ID = FINANCIALTRANSACTIONLINEITEM.ID 
                    where FINANCIALTRANSACTIONLINEITEM.ID = RGSPLITTABLE.ID

                    set @RECURRINGGIFTCREATED = 1
                end
                else
                begin
                    --Decide if we need to create a pledge for this membership

                    if (@PROGRAMTYPE = @LIFETIMEPROGRAMTYPECODE and @RENEWALREVENUETYPE <> 0) or (@PROGRAMTYPE = @ANNUALPROGRAMTYPECODE and (@RENEWALREVENUETYPE <> 0 or (@CANBEPAIDINFULL = 0 and @CANBEPLEDGED = 1 and @RENEWALREVENUETYPE = 0)))
                    begin
                    declare @PLEDGECONTRIBUTEDAMOUNT money = 0
                    declare @PLEDGETOTALADDONAMOUNT money = 0
                    declare @PLEDGETAXDEDUCTIBLEAMOUNT money
                    declare @PLEDGESPLITTABLE table(ID uniqueidentifier, APPLICATIONCODE tinyint, TYPECODE tinyint, DESIGNATIONID uniqueidentifier, AMOUNT money, DECLINESGIFTAID bit, TRANSACTIONCURRENCYID uniqueidentifier, ADDONID uniqueidentifier)

                    insert into @PLEDGESPLITTABLE(ID, APPLICATIONCODE, TYPECODE, DESIGNATIONID, AMOUNT, DECLINESGIFTAID, TRANSACTIONCURRENCYID, ADDONID)
                    select
                        newid(),
                        18,
                        18,
                        null,
                        A.PRICE * A.NUMBEROFADDONS,
                        @MEMBERSHIPDECLINESGIFTAID,
                        @TRANSACTIONCURRENCYID,
                        A.ADDONID
                    from @ADDONS A
                    where A.APPLY = 1

                    --Link the revenue split to the addon so that we can display it properly

                    update ADDONS
                    set REVENUESPLITID = (select ID from @PLEDGESPLITTABLE PST where PST.ADDONID = ADDONS.ADDONID)
                    from @ADDONS ADDONS

                    select @PLEDGETOTALADDONAMOUNT = isnull(sum(AMOUNT),0) from @PLEDGESPLITTABLE where TYPECODE = 18

                    if @PROGRAMTYPE = @LIFETIMEPROGRAMTYPECODE and @TAXDEDUCTIBLETYPE <> 1
                    begin
                        -- For Lifetime programs, the value in the RECEIPTAMOUNT column may not apply to the selected term

                        -- We will trust the value coming from the UIModel in this case

                        set @PLEDGETAXDEDUCTIBLEAMOUNT = @MEMBERSHIPPLEDGEAMOUNT
                    end
                    else if @TAXDEDUCTIBLETYPE = 0
                    begin
                        --Program is set to 100% tax deductible

                        set @PLEDGETAXDEDUCTIBLEAMOUNT = @MEMBERSHIPPLEDGEAMOUNT - @PLEDGETOTALADDONAMOUNT
                    end
                    else
                    begin
                        set @PLEDGETAXDEDUCTIBLEAMOUNT = (select RECEIPTAMOUNT from dbo.MEMBERSHIPLEVEL where ID = @MEMBERSHIPLEVELID)
                    end

                    --Don't let the tax deductible amount be greater than the membership amount minus addons

                    if @PLEDGETAXDEDUCTIBLEAMOUNT > (@MEMBERSHIPPLEDGEAMOUNT - @PLEDGETOTALADDONAMOUNT)
                        set @PLEDGETAXDEDUCTIBLEAMOUNT = (@MEMBERSHIPPLEDGEAMOUNT - @PLEDGETOTALADDONAMOUNT)

                    if @CONTRIBUTED = 0
                    begin
                        insert into @PLEDGESPLITTABLE
                        (
                            ID,
                            APPLICATIONCODE,
                            TYPECODE,
                            DESIGNATIONID,
                            AMOUNT,
                            DECLINESGIFTAID,
                            TRANSACTIONCURRENCYID,
                            ADDONID
                        )
                        select
                            newid(),
                            0,
                            0,
                            CONTRIBUTEDAMOUNTS.DESIGNATIONID,
                            CONTRIBUTEDAMOUNTS.AMOUNT,
                            @MEMBERSHIPDECLINESGIFTAID,
                            @TRANSACTIONCURRENCYID,
                            null
                        from dbo.UFN_MEMBERSHIP_GETCONTRIBUTEDAMOUNTSBYLEVELID(@MEMBERSHIPLEVELID, @PLEDGETAXDEDUCTIBLEAMOUNT, @TRANSACTIONCURRENCYDECIMALDIGITS) CONTRIBUTEDAMOUNTS
                    end

                    --Resetting pledge contributed amount for earned calculation below to account for level rules determined by UFN_MEMBERSHIP_GETCONTRIBUTEDAMOUNTSBYLEVELID above

                    select @PLEDGECONTRIBUTEDAMOUNT = sum(AMOUNT) from @PLEDGESPLITTABLE where TYPECODE = 0

                    declare @PLEDGEMEMBERSHIPPARENTSPLITID uniqueidentifier = newid()

                        declare @PLEDGESPLITS xml = (
                            select
                                PLEDGESPLITS.ID,
                                PLEDGESPLITS.APPLICATIONCODE,
                                PLEDGESPLITS.TYPECODE,
                                PLEDGESPLITS.DESIGNATIONID,
                                PLEDGESPLITS.AMOUNT,
                                PLEDGESPLITS.DECLINESGIFTAID,
                                PLEDGESPLITS.TRANSACTIONCURRENCYID
                            from
                                (
                                    select @PLEDGEMEMBERSHIPPARENTSPLITID as ID,
                                    5 as APPLICATIONCODE,
                                    2 as TYPECODE,
                                    case when @CONTRIBUTED = 1 then @CONTRIBUTORYDESIGNATIONID else null end as DESIGNATIONID,
                                    @MEMBERSHIPPLEDGEAMOUNT - coalesce(@PLEDGECONTRIBUTEDAMOUNT, 0) - coalesce(@PLEDGETOTALADDONAMOUNT, 0) as AMOUNT,
                                    @MEMBERSHIPDECLINESGIFTAID as DECLINESGIFTAID,
                                    @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID

                                    union all

                                    select
                                        ID as ID,
                                        APPLICATIONCODE,
                                        TYPECODE,
                                        DESIGNATIONID,
                                        AMOUNT,
                                        DECLINESGIFTAID,
                                        TRANSACTIONCURRENCYID
                                    from @PLEDGESPLITTABLE
                                ) as PLEDGESPLITS
                                    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                        );

                    declare @PLEDGESPLITSDECLININGGIFTAID xml;

                    --GL will not be generated for the installment plan in USP_PLEDGE_ADD

                    --We need to hold off on creating GL until after the membership transaction is associated with the pledge (installment plan) financial transaction. The membership transaction (and GL) creation happens later in this sproc.

                    exec dbo.USP_PLEDGE_ADD
                        @PLEDGEID output,
                        @CHANGEAGENTID,
                        @BILLTOCONSTITUENTID,
                        @DATE,
                        @MEMBERSHIPPLEDGEAMOUNT,
                        @INSTALLMENTPLEDGEPOSTSTATUSCODE,
                        @DATE,
                        @SENDPLEDGEREMINDER,
                        @PLEDGESPLITS,
                        @PLEDGEFREQUENCYCODE,
                        @PLEDGENUMBEROFINSTALLMENTS,
                        @PLEDGESTARTDATE,
                        @INSTALLMENTS,
                        @AUTOPAY,
                        @PAYMENTMETHODCODE,
                        @CARDHOLDERNAME,
                        @CREDITCARDNUMBER,
                        @CREDITTYPECODEID,
                        @EXPIRESON,
                        @REFERENCEDATE,
                        @REFERENCENUMBER,
                        @CONSTITUENTACCOUNTID,
                        @FINDERNUMBER,
                        @SOURCECODE,
                        @APPEALID,
                        @BENEFITS, --Regular and percentage??

                        0,
                        @GIVENANONYMOUSLY,
                        @EFFORTID,
                        @CHANNELCODEID,
                        @DONOTACKNOWLEDGE,
                        null,
                        @BATCHNUMBER, -- batch number

                        null,
                        @REFERENCE,
            @REVENUECATEGORYID,
                        @CREDITCARDTOKEN,
                        null, --@STANDINGORDERSETUP,

                        null, --@STANDINGORDERSETUPDATE,

                        @DDISOURCECODEID,
                        @DDISOURCEDATE,
                        @PLEDGESPLITSDECLININGGIFTAID output,
                        @PERCENTAGEBENEFITS,
                        @TRANSACTIONCURRENCYID,
                        @BASECURRENCYID,
                        @BASEEXCHANGERATEID,
                        @PDACCOUNTSYSTEMID,
                        0, -- business units applied

                        0, --@GENERATEREFERENCENUMBER,

                        null, --@STANDINGORDERREFERENCENUMBER,

                        null, --@EVENTID

                        null, --@LOCALCORPID

                        1, --@ISMEMBERSHIPPLEDGE

                        @SEPAMANDATEID;

                    exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @PLEDGEID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, @PLEDGESPLITSDECLININGGIFTAID;

                                    if @BBNCTRANID > 0
                                    insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                        values(@PLEDGEID, @BBNCTRANID, @ORIGINPAGE, @ORIGINPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                                

                    set @PLEDGECREATED = 1
                end
                end
                --Create a payment if necessary


                if @RENEWALREVENUETYPE <> 2 or (@RENEWALREVENUETYPE = 2 and @PROGRAMTYPE = @RECURRINGPROGRAMTYPECODE and @TOTALAMOUNT > 0)
                begin

                    --Temp benefit collections to send to ADDBASE. If we've created a pledge or recurring gift we don't want to create the benefits on the payment.

                    declare @TEMP_BENEFITS xml = @BENEFITS;
                    declare @TEMP_PERCENTAGEBENEFITS xml = @PERCENTAGEBENEFITS;
                    if @RECURRINGGIFTCREATED = 1 or @PLEDGECREATED = 1
                    begin
                        set @TEMP_BENEFITS = null;
                        set @TEMP_PERCENTAGEBENEFITS = null;
                    end

                    exec dbo.USP_PAYMENT_ADDBASE 
                    @ID output,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @BILLTOCONSTITUENTID,
                    @DATE,
                    @TOTALAMOUNT,
                    @PAYMENTMETHODCODE,
                    @CHECKDATE,
                    @CHECKNUMBER,
                    @REFERENCEDATE,
                    @REFERENCENUMBER,
                    @CARDHOLDERNAME,
                    @CREDITCARDNUMBER,
                    @CREDITTYPECODEID,
                    @AUTHORIZATIONCODE,
                    @EXPIRESON,
                    '',                       --@ISSUER

                    0,                        --@NUMBEROFUNITS

                    '',                       --@SYMBOL

                    0,                        --@MEDIANPRICE

                    null,                     --@GIFTINKINDSUBTYPECODEID

                    null,                     --@PROPERTYSUBTYPECODEID 

                    @TAXDEDUCTIBLEAMOUNT,
                    @CONSTITUENTACCOUNTID,
                    @POSTSTATUSCODE,
                    @POSTDATE,
                    @FINDERNUMBER,
                    @SOURCECODE,
                    @APPEALID, 
                    @TEMP_BENEFITS,
                    0,                        --@BENEFITSWAIVED

                    @GIVENANONYMOUSLY, 
                    @EFFORTID,
                    @CHANNELCODEID,
                    @DONOTACKNOWLEDGE,
                    @DONOTRECEIPT,
                    @BATCHNUMBER,
                    @OTHERPAYMENTMETHODCODEID, 
                    @REFERENCE, 
                    @TRIBUTEID,
                    @LETTERCODEID, 
                    @DIRECTDEBITRESULTCODE,
                    0,                        --@LOWPRICE

                    0,                        --@HIGHPRICE

                    '',                       --@GIFTINKINDITEMNAME

                    0,                        --@GIFTINKINDDISPOSITIONCODE

                    0,                        --@GIFTINKINDNUMBEROFUNITS

                    0,                        --@GIFTINKINDFAIRMARKETVALUE

                    @TEMP_PERCENTAGEBENEFITS, 
                    @TRANSACTIONCURRENCYID,
                    @BASECURRENCYID, 
                    @BASEEXCHANGERATEID, 
                    @EXCHANGERATE, 
                    @CURRENTAPPUSERID,
                    @SEPAMANDATEID;

                    --If a SEPA mandate is used, mark the mandate record to show that a payment has been made towards it.

                    if @SEPAMANDATEID is not null
                    begin
                        if @BATCHROWID is not null
                        begin
                            exec dbo.USP_SEPAMANDATE_PAYMENTMADE @SEPAMANDATEID,@BATCHROWID,@ID, @CHANGEAGENTID;
                        end
                    end

                    set @PAYMENTMETHODID  = (select ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID)
                    if @CREDITCARDTRANSACTIONID is not null and @PAYMENTMETHODCODE = 2 -- Credit card

                    begin
                        update dbo.CREDITCARDPAYMENTMETHODDETAIL set
                            TRANSACTIONID = @CREDITCARDTRANSACTIONID
                            ,VENDORID = isnull(@VENDORID, '')
                            , DATECHANGED = @CURRENTDATE
                            , CHANGEDBYID = @CHANGEAGENTID
                        where ID = @PAYMENTMETHODID
                    end

                    exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
                            @ID = @ID,
                            @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                            @CHANGEDATE = @CURRENTDATE,
                            @CHANGEAGENTID = @CHANGEAGENTID

                    --If this is for the first installment or the membership is being paid in full, but can only be pledged, then this is going to be a

                    --payment on a pledge. Otherwise it is a normal payment

                    if (@INSTALLMENTPAYMENTOPTION = 1) or (@CANBEPAIDINFULL = 0 and @CANBEPLEDGED = 1 and @RENEWALREVENUETYPE = 0)
                        begin
                        exec dbo.USP_PLEDGE_ADDPAYMENT @ID,
                                                @PLEDGEID,
                                                @MEMBERSHIPTRANSACTIONAMOUNT,
                                                @BILLTOCONSTITUENTID,
                                                @DATE,
                                                null,
                                                19,
                                                @AMOUNTPAID,
                                                @CURRENTDATE,
                                                @CHANGEAGENTID,
                                                @CREATEDSPLITS output,
                                                1,
                                                0,
                                                null,
                                                null

                        set @RGORPLEDGEMEMBERSHIPSPLITID = (select
                                    T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                                where T.c.value('(TYPECODE)[1]','tinyint') = 2)

                        declare @PLEDGEPAYMENTSPLITSDECLININGGIFTAID xml
                        set @PLEDGEPAYMENTSPLITSDECLININGGIFTAID =
                            (
                                select
                                    T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                                    inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                                where REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
                                for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                            )

                    -- create revenue category for any payments

                    if @REVENUECATEGORYID is not null
                    begin
                        exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @REVENUECATEGORYID, @CHANGEAGENTID, @CURRENTDATE;
                    end

                        --Additional donation added

                        if @ADDDONATION = 1
                        begin
                            exec dbo.USP_GIFT_ADDPAYMENT
                                        @REVENUEID = @ID,
                                        @AMOUNT = @DONATIONAMOUNT,
                                        @DESIGNATIONID = @DONATIONDESIGNATIONID,
                                        @OPPORTUNITYID = @DONATIONOPPORTUNITYID,
                                        @CAMPAIGNS = @CAMPAIGNS,
                                        @SOLICITORS = @SOLICITORS,
                                        @RECOGNITIONCREDITS = @RECOGNITIONS,
                                        @CATEGORYCODEID = @DONATIONCATEGORYCODEID,
                                        @CREATIONDATE =  @CURRENTDATE,
                                        @CHANGEAGENTID = @CHANGEAGENTID,
                                        @ID = @PAYMENTGIFTID output,
                                        @REVENUETYPECODE = 0

                            if @DECLINESGIFTAID = 1
                                insert into @SPLITSGIFTAIDINFOTBL (REVENUESPLITID, DECLINESGIFTAID) values (@PAYMENTGIFTID, 1)

                            set @PLEDGEPAYMENTSPLITSDECLININGGIFTAID =
                                (
                                    select
                                        DECLINEDSPLITS.REVENUESPLITID
                                    from
                                    (
                                        select
                                            REVENUESPLITID
                                        from @SPLITSGIFTAIDINFOTBL
                                        where
                                            DECLINESGIFTAID = 1

                                        union all

                                        select
                                            T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                        from @PLEDGEPAYMENTSPLITSDECLININGGIFTAID.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c)) DECLINEDSPLITS
                                        for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                                    )

                            select @SPLITS = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                                from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                                where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0
                                                group by REVENUESPLIT_EXT.DESIGNATIONID
                                                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);  

                            -- create matching gift records

                            if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                                exec USP_MATCHINGGIFTPLEDGE_AUTOADD @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                            if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                                exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;
                    end
                    exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, @PLEDGEPAYMENTSPLITSDECLININGGIFTAID;

                    declare @PLEDGEPAYMENTRECEIPTTYPECODE tinyint;
                    set @PLEDGEPAYMENTRECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@BILLTOCONSTITUENTID,2);

                    update dbo.REVENUE_EXT
                    set RECEIPTTYPECODE = @PLEDGEPAYMENTRECEIPTTYPECODE
                    where ID = @ID;

                    if @TOTALAMOUNT < (select sum(TRANSACTIONAMOUNT) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                        raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                    if @TOTALAMOUNT <> (select sum(TRANSACTIONAMOUNT) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                        raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                    if (select count(*) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) = 0
                        raiserror('BBERR_NOAPPLICATIONS', 13, 1);

                    -- Add gift fees

                    exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @BILLTOCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

                    if @BBNCTRANID > 0
                        insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values(@ID, @BBNCTRANID, @ORIGINPAGE, @ORIGINPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);  

                    set @PAYMENTCREATED = 1
                end
                    else if @RECURRINGGIFTCREATED = 1
                    begin
                        --Create a payment to the recurring gift

                        if @RENEWALREVENUETYPE <> 2
                        begin
                            exec dbo.USP_RECURRINGGIFT_ADDPAYMENT 
                                @REVENUEID = @ID,
                                @APPLICATIONID = @RECURRINGGIFTID,
                                @APPLIEDAMOUNT = @MEMBERSHIPTRANSACTIONAMOUNT,
                                @CONSTITUENTID = @BILLTOCONSTITUENTID,
                                @DATE = @DATE,
                                @CREATIONDATE = @CURRENTDATE,
                                @CHANGEAGENTID = @CHANGEAGENTID,
                                @CREATEDSPLITS = @CREATEDSPLITS output,
                                @BASEAPPLIEDAMOUNT = null,
                                @ORGANIZATIONAPPLIEDAMOUNT = null;
                        end

                                set @RGORPLEDGEMEMBERSHIPSPLITID = (select
                                    T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
                                where T.c.value('(TYPECODE)[1]','tinyint') = 2)

                        --Link the revenue split to the addon so that we can display it properly

                        update ADDONS 
                        set REVENUESPLITID = newid() 
                        from @ADDONS ADDONS
                        where ADDONS.APPLY = 1

                        declare @RGPAYMENTSPLITSDECLININGGIFTAID xml
                        set @RGPAYMENTSPLITSDECLININGGIFTAID = (select DECLINEDSPLITS.REVENUESPLITID from (


                                select ADDONS.REVENUESPLITID
                                from @ADDONS ADDONS
                                where @MEMBERSHIPDECLINESGIFTAID = 1

                                union all

                                select
                                    T.c.value('(ID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T(c) 
                                    inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
                                where
                                    REVENUESPLITGIFTAID.DECLINESGIFTAID = 1) DECLINEDSPLITS
                                for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                            )

                        declare @RGADDONBASEAMOUNT money = 0;
                        declare @RGADDONORGANIZATIONAMOUNT money = 0;
                        declare @RGADDONTRANSACTIONAMOUNT money = 0;

                        select @RGADDONTRANSACTIONAMOUNT = coalesce(sum(ADDONS.PRICE * ADDONS.NUMBEROFADDONS),0)
                        from @ADDONS ADDONS
                        where ADDONS.APPLY = 1

                        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @RGADDONTRANSACTIONAMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @RGADDONBASEAMOUNT output, null, @RGADDONORGANIZATIONAMOUNT output, null, 1, null;

                        declare @RGADDONSPLITXML xml 
                        set @RGADDONSPLITXML = (select ADDONS.REVENUESPLITID as ID, (ADDONS.PRICE * ADDONS.NUMBEROFADDONS) as AMOUNT from @ADDONS ADDONS where ADDONS.APPLY = 1 for xml raw('ITEM'),type,elements,root('ADDONSPLITS'),BINARY BASE64)

                        insert into dbo.REVENUESPLIT
                        (
                            ID,
                            REVENUEID,
                            DESIGNATIONID,
                            AMOUNT,
                            APPLICATIONCODE,
                            TYPECODE,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT,
                            TRANSACTIONCURRENCYID,
                            BASECURRENCYID,
                            BASEEXCHANGERATEID,
                            ORGANIZATIONEXCHANGERATEID,
                            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        select
                            A.REVENUESPLITID,
                            @ID,
                            null,
                            CONVERTED.BASEAMOUNT,
                            18, 
                            18, 
                            A.PRICE * A.NUMBEROFADDONS,
                            CONVERTED.ORGANIZATIONAMOUNT,
                            @TRANSACTIONCURRENCYID,
                            @BASECURRENCYID,
                            @BASEEXCHANGERATEID,
                            @ORGANIZATIONEXCHANGERATEID,
                            @CHANGEAGENTID, @CHANGEAGENTID,  @CURRENTDATE,  @CURRENTDATE
                        from @ADDONS A
                            inner join dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(@RGADDONSPLITXML, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @ORGANIZATIONCURRENCYID, @RGADDONTRANSACTIONAMOUNT, @RGADDONBASEAMOUNT, @BASECURRENCYDECIMALDIGITS, @RGADDONORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) CONVERTED
                                on CONVERTED.ITEM.value('(ITEM/ID)[1]', 'uniqueidentifier') = A.REVENUESPLITID
                        where
                            A.APPLY = 1 and
                            A.NUMBEROFADDONS > 0

                        -- create revenue category for any payments

                        if @REVENUECATEGORYID is not null
                        begin
                            exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @REVENUECATEGORYID, @CHANGEAGENTID, @CURRENTDATE;
                        end

                        --Additional donation added

                        if @ADDDONATION = 1
                        begin
                            exec dbo.USP_GIFT_ADDPAYMENT
                                @REVENUEID = @ID,
                                @AMOUNT = @DONATIONAMOUNT,
                                @DESIGNATIONID = @DONATIONDESIGNATIONID,
                                @OPPORTUNITYID = @DONATIONOPPORTUNITYID,
                                @CAMPAIGNS = @CAMPAIGNS,
                                @SOLICITORS = @SOLICITORS,
                                @RECOGNITIONCREDITS = @RECOGNITIONS,
                                @CATEGORYCODEID = @DONATIONCATEGORYCODEID,
                                @CREATIONDATE =  @CURRENTDATE,
                                @CHANGEAGENTID = @CHANGEAGENTID,
                                @ID = @PAYMENTGIFTID output,
                                @REVENUETYPECODE = 0

                            if @DECLINESGIFTAID = 1
                                insert into @SPLITSGIFTAIDINFOTBL (REVENUESPLITID, DECLINESGIFTAID) values (@PAYMENTGIFTID, 1)

                            set @RGPAYMENTSPLITSDECLININGGIFTAID =
                                (
                                    select
                                        DECLINEDSPLITS.REVENUESPLITID
                                    from
                                        (
                                            select
                                                REVENUESPLITID
                                            from
                                                @SPLITSGIFTAIDINFOTBL
                                            where
                                                DECLINESGIFTAID = 1

                                            union all

                                            select
                                                T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
                                            from @RGPAYMENTSPLITSDECLININGGIFTAID.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c)) DECLINEDSPLITS
                                            for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                                        )

                            select @SPLITS = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                                from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                                where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0
                                                group by REVENUESPLIT_EXT.DESIGNATIONID
                                                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);  

                            -- create matching gift records

                            if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                                exec USP_MATCHINGGIFTPLEDGE_AUTOADD @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                            if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                                exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;  
                        end

                        exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, @RGPAYMENTSPLITSDECLININGGIFTAID;

                        declare @RGPAYMENTRECEIPTTYPECODE tinyint = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@BILLTOCONSTITUENTID,3);

                        update dbo.REVENUE_EXT
                        set RECEIPTTYPECODE = @RGPAYMENTRECEIPTTYPECODE
                        where ID = @ID;

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

                        if @TOTALAMOUNT < @TRANSACTIONTOTAL
                            raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                        if @TOTALAMOUNT <> @TRANSACTIONTOTAL
                            raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                        if (select count(*) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) = 0
                            raiserror('BBERR_NOAPPLICATIONS', 13, 1);

                        -- Add gift fees

                        exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @BILLTOCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

                        if @BBNCTRANID > 0
                            insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@ID, @BBNCTRANID, @ORIGINPAGE, @ORIGINPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                    set @PAYMENTCREATED = 1
                end
                else
                begin
                    set @MEMBERSHIPSPLITID = newid()

                        declare @OBTAINLEVELCODE tinyint
                        select
                            @OBTAINLEVELCODE = OBTAINLEVELCODE
                        from
                            dbo.MEMBERSHIPLEVEL
                        where
                            ID = @MEMBERSHIPLEVELID

                        if @OBTAINLEVELCODE = 0
                        begin
                            declare @MEMBERSHIPREVENUESPLITTABLE table(ID uniqueidentifier, APPLICATIONCODE tinyint, TYPECODE tinyint, DESIGNATIONID uniqueidentifier, AMOUNT money, DECLINESGIFTAID bit, TRANSACTIONCURRENCYID uniqueidentifier, ADDONID uniqueidentifier)

                            insert into @MEMBERSHIPREVENUESPLITTABLE(ID, APPLICATIONCODE, TYPECODE, DESIGNATIONID, AMOUNT, DECLINESGIFTAID, TRANSACTIONCURRENCYID)
                            select newid(), 0, 0, CONTRIBUTEDAMOUNTS.DESIGNATIONID, CONTRIBUTEDAMOUNTS.AMOUNT, @MEMBERSHIPDECLINESGIFTAID, @TRANSACTIONCURRENCYID
                            from dbo.UFN_MEMBERSHIP_GETCONTRIBUTEDAMOUNTSBYLEVELID(@MEMBERSHIPLEVELID, (@TAXDEDUCTIBLEAMOUNT - @DONATIONAMOUNT), @TRANSACTIONCURRENCYDECIMALDIGITS) as CONTRIBUTEDAMOUNTS

                            declare @MEMBERSHIPSPLITAMOUNT money =
                                (
                                    select @MEMBERSHIPTRANSACTIONAMOUNT - coalesce(sum(AMOUNT), 0)
                                    from @MEMBERSHIPREVENUESPLITTABLE where TYPECODE = 0
                                )

                            insert into @MEMBERSHIPREVENUESPLITTABLE(ID, APPLICATIONCODE, TYPECODE, DESIGNATIONID, AMOUNT, DECLINESGIFTAID, TRANSACTIONCURRENCYID)
                            values(@MEMBERSHIPSPLITID, 5, 2, null, @MEMBERSHIPSPLITAMOUNT, @MEMBERSHIPDECLINESGIFTAID, @TRANSACTIONCURRENCYID)

                            insert into @MEMBERSHIPREVENUESPLITTABLE(ID, APPLICATIONCODE, TYPECODE, DESIGNATIONID, AMOUNT, DECLINESGIFTAID, TRANSACTIONCURRENCYID, ADDONID)
                            select
                                newid(),
                                18,
                                18,
                                null,
                                A.PRICE * A.NUMBEROFADDONS,
                                @MEMBERSHIPDECLINESGIFTAID,
                                @TRANSACTIONCURRENCYID, 
                                A.ADDONID
                            from @ADDONS A
                            where A.APPLY = 1

                            declare @ADDONBASEAMOUNT money;
                            declare @ADDONORGANIZATIONAMOUNT money;
                            declare @ADDONTRANSACTIONAMOUNT money;

                            select @ADDONTRANSACTIONAMOUNT = sum(AMOUNT)
                            from @MEMBERSHIPREVENUESPLITTABLE
                            where TYPECODE = 18


                            declare @REVENUESPLITXML xml;
                            set @REVENUESPLITXML =
                                (
                                    select
                                        ID,
                                        AMOUNT
                                    from
                                    (
                                        select
                                            @MEMBERSHIPSPLITID as ID,
                                            @MEMBERSHIPSPLITAMOUNT as AMOUNT

                                        union all
                                                    select ID, AMOUNT 
                                                    from @MEMBERSHIPREVENUESPLITTABLE 
                                                    where TYPECODE = 0) SPLITS
                                                    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)
                            declare @ADDONSPLITXML xml
                            set @ADDONSPLITXML = (select ID, AMOUNT from @MEMBERSHIPREVENUESPLITTABLE where TYPECODE = 18 for xml raw('ITEM'),type,elements,root('ADDONSPLITS'),BINARY BASE64)

                            --Link the revenue split to the addon so that we can display it properly

                            update ADDONS 
                            set REVENUESPLITID = (select ID from @MEMBERSHIPREVENUESPLITTABLE MRST where MRST.ADDONID = ADDONS.ADDONID) 
                            from @ADDONS ADDONS

                            insert into dbo.FINANCIALTRANSACTIONLINEITEM
                            (
                                ID,
                                FINANCIALTRANSACTIONID,
                                BASEAMOUNT,
                                TRANSACTIONAMOUNT,
                                ORGAMOUNT,
                                SOURCELINEITEMID,
                                POSTDATE,
                                POSTSTATUSCODE,
                                TYPECODE,
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                            )
                            select
                                SPLITS.ID,
                                @ID,
                                CONVERTED.BASEAMOUNT,
                                SPLITS.AMOUNT,
                                CONVERTED.ORGANIZATIONAMOUNT,
                                case SPLITS.TYPECODE when 0 then @MEMBERSHIPSPLITID else null end,
                                @POSTDATE,
                                case @POSTSTATUSCODE when 2 then 3 else 1 end,
                                0,
                                 @CHANGEAGENTID, @CHANGEAGENTID,  @CURRENTDATE,  @CURRENTDATE
                            from @MEMBERSHIPREVENUESPLITTABLE SPLITS
                                inner join dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(@REVENUESPLITXML, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @ORGANIZATIONCURRENCYID, @MEMBERSHIPTRANSACTIONAMOUNT, @BASEAMOUNT, @BASECURRENCYDECIMALDIGITS, @ORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) CONVERTED
                                    on CONVERTED.ITEM.value('(ITEM/ID)[1]', 'uniqueidentifier') = SPLITS.ID
                            where SPLITS.APPLICATIONCODE = 5 or (SPLITS.TYPECODE <> 18 and SPLITS.AMOUNT > 0)

                            insert into dbo.REVENUESPLIT_EXT
                            (
                                ID,
                                APPLICATIONCODE,
                                TYPECODE,
                                DESIGNATIONID,
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                            )
                            select
                                SPLITS.ID,
                                SPLITS.APPLICATIONCODE,
                                SPLITS.TYPECODE,
                                SPLITS.DESIGNATIONID,
                                @CHANGEAGENTID, @CHANGEAGENTID,  @CURRENTDATE,  @CURRENTDATE
                            from @MEMBERSHIPREVENUESPLITTABLE SPLITS
                            where SPLITS.APPLICATIONCODE = 5 or (SPLITS.TYPECODE <> 18 and SPLITS.AMOUNT > 0)

                            insert into dbo.REVENUESPLIT
                            (
                                ID,
                                REVENUEID,
                                AMOUNT,
                                APPLICATIONCODE,
                                TYPECODE,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                TRANSACTIONCURRENCYID,
                                BASECURRENCYID,
                                BASEEXCHANGERATEID,
                                ORGANIZATIONEXCHANGERATEID,
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                            )
                            select
                                SPLITS.ID,
                                @ID,
                                CONVERTED.BASEAMOUNT,
                                SPLITS.APPLICATIONCODE,
                                SPLITS.TYPECODE,
                                SPLITS.AMOUNT,
                                CONVERTED.ORGANIZATIONAMOUNT,
                                @TRANSACTIONCURRENCYID,
                                @BASECURRENCYID,
                                @BASEEXCHANGERATEID,
                                @ORGANIZATIONEXCHANGERATEID,
                                @CHANGEAGENTID, @CHANGEAGENTID,  @CURRENTDATE,  @CURRENTDATE
                            from @MEMBERSHIPREVENUESPLITTABLE SPLITS
                            inner join dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML(@ADDONSPLITXML, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @ORGANIZATIONCURRENCYID, @ADDONTRANSACTIONAMOUNT, @ADDONBASEAMOUNT, @BASECURRENCYDECIMALDIGITS, @ADDONORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) CONVERTED
                                on CONVERTED.ITEM.value('(ITEM/ID)[1]', 'uniqueidentifier') = SPLITS.ID


                            if exists(select 1 from @MEMBERSHIPREVENUESPLITTABLE where TYPECODE = 18)
                            begin
                                --Create recognitions for the addon splits

                                declare @MEMBERSHIPREVENUEADDONSPLITID uniqueidentifier
                                declare MEMBERSHIPREVENUEADDONCURSOR cursor local fast_forward for select ID from @MEMBERSHIPREVENUESPLITTABLE where TYPECODE = 18
                                open MEMBERSHIPREVENUEADDONCURSOR
                                fetch next from MEMBERSHIPREVENUEADDONCURSOR into @MEMBERSHIPREVENUEADDONSPLITID
                                while @@FETCH_STATUS = 0
                                begin
                                    exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @MEMBERSHIPREVENUEADDONSPLITID, @CHANGEAGENTID, @CURRENTDATE;

                                    fetch next from MEMBERSHIPREVENUEADDONCURSOR into @MEMBERSHIPREVENUEADDONSPLITID
                                end
                                close MEMBERSHIPREVENUEADDONCURSOR
                                deallocate MEMBERSHIPREVENUEADDONCURSOR
                            end
                        end
                        else
                        begin
                            insert into dbo.REVENUESPLIT
                                (
                                    ID,
                                    REVENUEID,
                                    DESIGNATIONID,
                                    AMOUNT,
                                    APPLICATIONCODE,
                                    TYPECODE,
                                    TRANSACTIONAMOUNT,
                                    ORGANIZATIONAMOUNT,
                                    TRANSACTIONCURRENCYID,
                                    BASECURRENCYID,
                                    BASEEXCHANGERATEID,
                                    ORGANIZATIONEXCHANGERATEID,
                                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                                )
                                values
                                (
                                    @MEMBERSHIPSPLITID,
                                    @ID,
                                    @CONTRIBUTORYDESIGNATIONID,
                                    @BASEAMOUNT,
                                    0,
                                    0,
                                    @MEMBERSHIPTRANSACTIONAMOUNT,
                                    @ORGANIZATIONAMOUNT,
                                    @TRANSACTIONCURRENCYID,
                                    @BASECURRENCYID,
                                    @BASEEXCHANGERATEID,
                                    @ORGANIZATIONEXCHANGERATEID,
                                    @CHANGEAGENTID, @CHANGEAGENTID,  @CURRENTDATE,  @CURRENTDATE
                                );

                            insert into dbo.REVENUESPLITCAMPAIGN
                                (
                                    ID,
                                    REVENUESPLITID,
                                    CAMPAIGNID,
                                    CAMPAIGNSUBPRIORITYID,
                                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                                )
                                select newid(),
                                        @MEMBERSHIPSPLITID,
                                        CAMPAIGNS.CAMPAIGNID,
                                        CAMPAIGNS.CAMPAIGNSUBPRIORITYID,
                                        @CHANGEAGENTID, @CHANGEAGENTID,  @CURRENTDATE,  @CURRENTDATE
                                from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT(@CONTRIBUTORYDESIGNATIONID, @DATE) CAMPAIGNS
                        end

            --Update campaign based on the contribution portion of the membership

            exec dbo.USP_REVENUE_ADDCAMPAIGNS @ID, @CHANGEAGENTID, @CURRENTDATE;

                        -- create revenue category for any payments

                        if @REVENUECATEGORYID is not null
                        begin
                            exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @REVENUECATEGORYID, @CHANGEAGENTID, @CURRENTDATE;
                        end

                        declare @GIFTID uniqueidentifier

                        --Additional donation added

                        if @ADDDONATION = 1
                        begin
                            exec dbo.USP_GIFT_ADDPAYMENT
                                        @REVENUEID = @ID,
                                        @AMOUNT = @DONATIONAMOUNT,
                                        @DESIGNATIONID = @DONATIONDESIGNATIONID,
                                        @OPPORTUNITYID = @DONATIONOPPORTUNITYID,
                                        @CAMPAIGNS = @CAMPAIGNS,
                                        @SOLICITORS = @SOLICITORS,
                                        @RECOGNITIONCREDITS = @RECOGNITIONS,
                                        @CATEGORYCODEID = @DONATIONCATEGORYCODEID,
                                        @CREATIONDATE =  @CURRENTDATE,
                                        @CHANGEAGENTID = @CHANGEAGENTID,
                                        @ID = @GIFTID output,
                                        @REVENUETYPECODE = 0

                            if @DECLINESGIFTAID = 1
                                insert into @SPLITSGIFTAIDINFOTBL (REVENUESPLITID, DECLINESGIFTAID) values (@GIFTID, 1)

                            select @SPLITS = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                                from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                                where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0
                                                group by REVENUESPLIT_EXT.DESIGNATIONID
                                                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);  

                            -- create matching gift records

                            if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                                exec USP_MATCHINGGIFTPLEDGE_AUTOADD @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                            if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                                exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;
                        end

                        insert into @SPLITSGIFTAIDINFOTBL (REVENUESPLITID, DECLINESGIFTAID)
                        select ID,DECLINESGIFTAID
                        from @MEMBERSHIPREVENUESPLITTABLE

                        set @SPLITSDECLININGGIFTAID =
                                (
                                    select
                                        REVENUESPLITID
                                    from @SPLITSGIFTAIDINFOTBL
                                    where DECLINESGIFTAID = 1
                                    for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                                )
                        exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 0, @SPLITSDECLININGGIFTAID, null, null; --revenue transaction type code for payment is 0

                        -- create recognitions for the membership part of the payment

                        exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @MEMBERSHIPSPLITID, @CHANGEAGENTID, @CURRENTDATE;

                        -- create recognitions for any contributed portion of the membership payment

                        declare @MEMBERSHIPRECOGNITIONSTABLE table(CONSTITUENTID uniqueidentifier, REVENUERECOGNITIONTYPECODEID uniqueidentifier, DESIGNATIONID uniqueidentifier, EFFECTIVEDATE date, AMOUNT money, APPLICATIONCURRENCYID uniqueidentifier)
                        insert into @MEMBERSHIPRECOGNITIONSTABLE(CONSTITUENTID, REVENUERECOGNITIONTYPECODEID, DESIGNATIONID, EFFECTIVEDATE, AMOUNT, APPLICATIONCURRENCYID)
                        select
                            T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier'),
                            T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]', 'uniqueidentifier'),
                            T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier'),
                            T.c.value('(EFFECTIVEDATE)[1]', 'date'),
                            T.c.value('(AMOUNT)[1]', 'money'),
                            T.c.value('(APPLICATIONCURRENCYID)[1]', 'uniqueidentifier')
                        from @MEMBERSHIPRECOGNITION.nodes('/MEMBERSHIPRECOGNITION/ITEM') T(c)

                        insert into dbo.REVENUERECOGNITION
                        (
                            REVENUESPLITID,
                            CONSTITUENTID,
                            AMOUNT,
                            EFFECTIVEDATE,
                            REVENUERECOGNITIONTYPECODEID,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED,
                            BASECURRENCYID,
                            ORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID
                        )
                        select
                            MEMBERSHIPSPLITS.ID,
                            MEMBERSHIPRECOGNITION.CONSTITUENTID,
                            MEMBERSHIPRECOGNITION.AMOUNT,
                            @DATE,
                            MEMBERSHIPRECOGNITION.REVENUERECOGNITIONTYPECODEID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            @BASECURRENCYID,
                            case
                                when @BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                                then dbo.UFN_CURRENCY_CONVERT(MEMBERSHIPRECOGNITION.AMOUNT, @BASETOORGANIZATIONEXCHANGERATEID)
                                else MEMBERSHIPRECOGNITION.AMOUNT
                            end,
                            @BASETOORGANIZATIONEXCHANGERATEID ORGANIZATIONEXCHANGERATEID
                        from @MEMBERSHIPREVENUESPLITTABLE MEMBERSHIPSPLITS
                            inner join @MEMBERSHIPRECOGNITIONSTABLE MEMBERSHIPRECOGNITION on MEMBERSHIPSPLITS.DESIGNATIONID = MEMBERSHIPRECOGNITION.DESIGNATIONID
                        where MEMBERSHIPSPLITS.TYPECODE = 0 and MEMBERSHIPSPLITS.AMOUNT > 0

                        exec dbo.USP_REVENUE_DEFAULTMARKETINGINFORMATION @ID, @CHANGEAGENTID, @CURRENTDATE;

                        declare @TOTALAPPLIEDAMOUNT money;

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

                        if @TOTALAMOUNT < @TOTALAPPLIEDAMOUNT
                            raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                        if @TOTALAMOUNT <> @TOTALAPPLIEDAMOUNT
                            raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                        if (select count(*) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) = 0
                            raiserror('BBERR_NOAPPLICATIONS', 13, 1);

                        -- USP_PAYMENT_ADDGIFTFEES creates GL distributions so it needs to be called after USP_PDACCOUNTSYSTEM_LINKTOREVENUE

                        exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @BILLTOCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

                        if @BBNCTRANID > 0
                            insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@ID, @BBNCTRANID, @ORIGINPAGE, @ORIGINPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                    set @PAYMENTCREATED = 1
                end
            end
            else
            begin
                declare @PLEDGEADDITIONALDONATIONGIFTID uniqueidentifier
                --Additional donation added

                if @ADDDONATION = 1
                begin
                    exec dbo.USP_PAYMENT_ADDBASE @ID output, @CHANGEAGENTID, @CURRENTDATE, @BILLTOCONSTITUENTID,
                        @DATE, @DONATIONAMOUNT, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER,
                        @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER,
                        @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, '',
                        0, '', 0, null,null, @TAXDEDUCTIBLEAMOUNT, @CONSTITUENTACCOUNTID,
                        @POSTSTATUSCODE, @POSTDATE, @FINDERNUMBER,
                        @SOURCECODE, @APPEALID, null, 0, @GIVENANONYMOUSLY, 
                        @EFFORTID, @CHANNELCODEID, @DONOTACKNOWLEDGE,@DONOTRECEIPT,
                        @BATCHNUMBER, @OTHERPAYMENTMETHODCODEID, @REFERENCE, @TRIBUTEID, @LETTERCODEID, 
                        @DIRECTDEBITRESULTCODE, 0, 0, '', 
                        0, 0, 
                        0, null, @TRANSACTIONCURRENCYID,
                        @BASECURRENCYID, @BASEEXCHANGERATEID, @EXCHANGERATE, @CURRENTAPPUSERID, @SEPAMANDATEID;

                        set @PAYMENTMETHODID = (select ID from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID)

                        if @CREDITCARDTRANSACTIONID is not null and @PAYMENTMETHODCODE = 2 -- Credit card

                        begin
                            update dbo.CREDITCARDPAYMENTMETHODDETAIL
                            set
                                TRANSACTIONID = @CREDITCARDTRANSACTIONID,
                                VENDORID = isnull(@VENDORID, ''),
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID
                            where ID = @PAYMENTMETHODID
                        end

                        exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE
                                    @ID = @ID,
                                    @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
                                    @CHANGEDATE = @CURRENTDATE,
                                    @CHANGEAGENTID = @CHANGEAGENTID

                        exec dbo.USP_GIFT_ADDPAYMENT
                                    @REVENUEID = @ID,
                                    @AMOUNT = @DONATIONAMOUNT,
                                    @DESIGNATIONID = @DONATIONDESIGNATIONID,
                                    @OPPORTUNITYID = @DONATIONOPPORTUNITYID,
                                    @CAMPAIGNS = @CAMPAIGNS,
                                    @SOLICITORS = @SOLICITORS,
                                    @RECOGNITIONCREDITS = @RECOGNITIONS,
                                    @CATEGORYCODEID = @DONATIONCATEGORYCODEID,
                                    @CREATIONDATE =  @CURRENTDATE,
                                    @CHANGEAGENTID = @CHANGEAGENTID,
                                    @ID = @PLEDGEADDITIONALDONATIONGIFTID output,
                                    @REVENUETYPECODE = 0

                        if @DECLINESGIFTAID = 1
                        begin
                            declare @PLEDGEADDITIONALDONATIONSPLITSDECLININGGIFTAID xml
                            set @PLEDGEADDITIONALDONATIONSPLITSDECLININGGIFTAID =
                                (
                                    select
                                        @PLEDGEADDITIONALDONATIONGIFTID
                                    for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64
                                );

                            exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, @PLEDGEADDITIONALDONATIONSPLITSDECLININGGIFTAID;
                        end
                        else
                            exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 1, null;

                            declare @PLEDGEADDITIONALDONATIONRECEIPTTYPECODE tinyint;
                            set @PLEDGEADDITIONALDONATIONRECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@BILLTOCONSTITUENTID,2);

                            update dbo.REVENUE_EXT
                            set RECEIPTTYPECODE = @PLEDGEADDITIONALDONATIONRECEIPTTYPECODE
                            where ID = @ID;

                            if @DONATIONAMOUNT < (select sum(TRANSACTIONAMOUNT) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                                raiserror('BBERR_AMOUNTLESSTHANAPPLIEDAMOUNT', 13, 1);

                            if @DONATIONAMOUNT <> (select sum(TRANSACTIONAMOUNT) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                                raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);

                            if (select count(*) from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1) = 0
                                raiserror('BBERR_NOAPPLICATIONS', 13, 1);

                            -- Add gift fees

                            exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @BILLTOCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

                                            if @BBNCTRANID > 0
                                        insert into dbo.REVENUEBBNC(ID, NETCOMMUNITYTRANSACTIONID, NETCOMMUNITYPAGENAME, NETCOMMUNITYPAGEID, NETCOMMUNITYAPPEALID, EMAILID, EMAILSUBJECT, EMAILNAME, ISTEAMFUNDRAISINGOFFLINEGIFT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                            values(@ID, @BBNCTRANID, @ORIGINPAGE, @ORIGINPAGEID, null, 0, '', '', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                        set @PAYMENTCREATED = 1

                        select @SPLITS = (select sum(REVENUESPLIT.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, 0 as TYPECODE
                                            from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
                                            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                                            where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 0 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 0
                                            group by REVENUESPLIT_EXT.DESIGNATIONID
                                            for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);  

                        -- create matching gift records

                        if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG() > 0
                            exec USP_MATCHINGGIFTPLEDGE_AUTOADD @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;

                        if dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG() > 0
                            exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @BILLTOCONSTITUENTID, @CHANGEAGENTID, @ID, @CURRENTDATE, @DONATIONAMOUNT, @TAXDEDUCTIBLEAMOUNT, @SPLITS, @CURRENTAPPUSERID;
                end
            end
            end

            if @SOLICITCODES is not null
            begin
                declare @SOLICITCODESTABLE table
                (
                    [CONSTITUENTSOLICITCODEID] [uniqueidentifier] NOT NULL,
                    [CONSTITUENTID] [uniqueidentifier] NOT NULL,
                    [SOLICITCODEID] [uniqueidentifier] NOT NULL,
                    [SEQUENCE] [int] NOT NULL,
                    [STARTDATE] [datetime] NULL,
                    [ENDDATE] [datetime] NULL,
                    [COMMENTS] [nvarchar](100) NOT NULL,
                    [CONSENTPREFERENCECODE] [tinyint] NOT NULL,
                    [SOURCECODEID] [uniqueidentifier] NULL,
                    [SOURCEFILEPATH] [nvarchar](260) NOT NULL,
                    [PRIVACYPOLICYFILEPATH] [nvarchar](260) NOT NULL,
                    [SUPPORTINGINFORMATION] [nvarchar](max) NOT NULL,
                    [CONSENTSTATEMENT] [nvarchar](max) NOT NULL
                );

                insert into @SOLICITCODESTABLE
                (
                    CONSTITUENTSOLICITCODEID,
                    CONSTITUENTID,
                    SOLICITCODEID,
                    STARTDATE,
                    ENDDATE,
                    COMMENTS,
                    SEQUENCE,
                    CONSENTPREFERENCECODE,
                    SOURCECODEID,
                    SOURCEFILEPATH,
                    PRIVACYPOLICYFILEPATH,
                    SUPPORTINGINFORMATION,
                    CONSENTSTATEMENT
                )
                select
                    coalesce(CONSTITUENTSOLICITCODEID,newid()),
                    @BILLTOCONSTITUENTID,
                    SOLICITCODEID,
                    STARTDATE,
                    ENDDATE,
                    coalesce(COMMENTS, ''),
                    SEQUENCE,
                    CONSENTPREFERENCECODE,
                    SOURCECODEID,
                    coalesce(SOURCEFILEPATH, ''),
                    coalesce(PRIVACYPOLICYFILEPATH, ''),
                    coalesce(SUPPORTINGINFORMATION, ''),
                    coalesce(CONSENTSTATEMENT, '')
                from dbo.UFN_MEMBERSHIPDUESBATCH_GETSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES);

                -- Validate solicit codes

                exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_SOLICITCODES @SOLICITCODES;

                -- Update existing solicit codes

                update dbo.CONSTITUENTSOLICITCODE set
                    CONSTITUENTSOLICITCODE.CONSTITUENTID = SOLICITCODES.CONSTITUENTID,
                    CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODES.SOLICITCODEID,
                    CONSTITUENTSOLICITCODE.STARTDATE = SOLICITCODES.STARTDATE,
                    CONSTITUENTSOLICITCODE.ENDDATE = SOLICITCODES.ENDDATE,
                    CONSTITUENTSOLICITCODE.COMMENTS = SOLICITCODES.COMMENTS,
                    CONSTITUENTSOLICITCODE.SEQUENCE = SOLICITCODES.SEQUENCE,
                    CONSTITUENTSOLICITCODE.CONSENTPREFERENCECODE = SOLICITCODES.CONSENTPREFERENCECODE,
                    CONSTITUENTSOLICITCODE.SOURCECODEID = SOLICITCODES.SOURCECODEID,
                    CONSTITUENTSOLICITCODE.SOURCEFILEPATH = SOLICITCODES.SOURCEFILEPATH,
                    CONSTITUENTSOLICITCODE.PRIVACYPOLICYFILEPATH = SOLICITCODES.PRIVACYPOLICYFILEPATH,
                    CONSTITUENTSOLICITCODE.SUPPORTINGINFORMATION = SOLICITCODES.SUPPORTINGINFORMATION,
                    CONSTITUENTSOLICITCODE.CONSENTSTATEMENT = SOLICITCODES.CONSENTSTATEMENT,
                    CONSTITUENTSOLICITCODE.CHANGEDBYID = @CHANGEAGENTID,
                    CONSTITUENTSOLICITCODE.DATECHANGED = @CURRENTDATE
                from @SOLICITCODESTABLE SOLICITCODES
                where CONSTITUENTSOLICITCODE.ID = SOLICITCODES.CONSTITUENTSOLICITCODEID;

                -- Remove existing rows from the table

                delete SOLICITCODES
                from @SOLICITCODESTABLE SOLICITCODES
                    inner join dbo.CONSTITUENTSOLICITCODE on SOLICITCODES.CONSTITUENTSOLICITCODEID = CONSTITUENTSOLICITCODE.ID;

                -- Add new solicit codes

                insert into CONSTITUENTSOLICITCODE
                (
                    ID,
                    CONSTITUENTID,
                    SOLICITCODEID,
                    STARTDATE,
                    ENDDATE,
                    COMMENTS,
                    SEQUENCE,
                    CONSENTPREFERENCECODE,
                    SOURCECODEID,
                    SOURCEFILEPATH,
                    PRIVACYPOLICYFILEPATH,
                    SUPPORTINGINFORMATION,
                    CONSENTSTATEMENT,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    CONSTITUENTSOLICITCODEID,
                    @BILLTOCONSTITUENTID,
                    SOLICITCODEID,
                    STARTDATE,
                    ENDDATE,
                    COMMENTS,
                    SEQUENCE,
                    CONSENTPREFERENCECODE,
                    SOURCECODEID,
                    SOURCEFILEPATH,
                    PRIVACYPOLICYFILEPATH,
                    SUPPORTINGINFORMATION,
                    CONSENTSTATEMENT,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                from @SOLICITCODESTABLE;
            end

            declare @SETEXPIRATIONDATE bit = (select case when PROGRAMTYPECODE = @ANNUALPROGRAMTYPECODE then 1 else 0 end from dbo.MEMBERSHIPPROGRAM where ID = @MEMBERSHIPPROGRAMID)

            if (@WHATPAYINGFORVALUE = @ADD_MEMBERSHIPTRANSACTTIONTYPECODE or @MEMBERSHIPACTIONCODE = 0) and 
                not exists (select 1 from dbo.MEMBERSHIP where MEMBERSHIP.ID = @EXISTINGMEMBERSHIPID)
            begin
                --New membership

                set @EXISTINGMEMBERSHIPID = newid()

                insert into dbo.MEMBERSHIP(ID, MEMBERSHIPPROGRAMID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, JOINDATE, EXPIRATIONDATE, ISGIFT, GIVENBYID, SENDRENEWALCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, NUMBEROFCHILDREN, AUTOMATICALLYRENEWMEMBERSHIP,MEMBERSHIPLEVELTYPECODEID)
                values(@EXISTINGMEMBERSHIPID, @MEMBERSHIPPROGRAMID, @MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, dbo.UFN_DATE_GETEARLIESTTIME(@DATE), case when @SETEXPIRATIONDATE = 1 then dbo.UFN_DATE_GETLATESTTIME(@MEMBERSHIPEXPIRESONDATE) else null end, @PAYINGORGIVINGRADIO, case when @PAYINGORGIVINGRADIO = 1 then @BILLTOCONSTITUENTID else null end, case when @PAYINGORGIVINGRADIO = 1 then @RENEWALRECIPIENT else 1 end, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @NUMBEROFCHILDREN, @AUTOMATICALLYRENEWMEMBERSHIP,@MEMBERSHIPLEVELTYPECODEID)

                --Save Addons

                insert into dbo.MEMBERSHIPADDON(ID, MEMBERSHIPID, ADDONID, QUANTITY, EXPIRATIONDATE, REVENUESPLITID, PURCHASEPRICE, 
                    TRANSACTIONPURCHASEPRICE, ORGANIZATIONPURCHASEPRICE, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, 
                    ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    A.ID,
                    @EXISTINGMEMBERSHIPID,
                    A.ADDONID,
                    A.NUMBEROFADDONS,
                    @MEMBERSHIPEXPIRESONDATE,
                    A.REVENUESPLITID,
                    case
                        when @BASEEXCHANGERATEID is null then A.PRICE
                        else dbo.UFN_CURRENCY_CONVERT(A.PRICE, @LATESTBASEEXCHANGERATE)
                    end,
                    A.PRICE,
                    case
                        when @ORGANIZATIONEXCHANGERATEID is null then A.PRICE
                        else dbo.UFN_CURRENCY_CONVERT(A.PRICE, @ORGANIZATIONEXCHANGERATEID)
                    end,
                    @TRANSACTIONCURRENCYID,
                    @BASECURRENCYID,
                    @BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @ADDONS A
                where A.APPLY = 1

                insert into dbo.MEMBER(ID, CONSTITUENTID, MEMBERSHIPID, ISPRIMARY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select newid(), MEMBERS.ID, @EXISTINGMEMBERSHIPID, MEMBERS.ISPRIMARY, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @MEMBERS MEMBERS
            end
            else
            begin
                if (@MEMBERSHIPEXPIRESONDATE is not null)
                begin
                    if @MEMBERSHIPEXPIRESONDATE < (select JOINDATE from dbo.MEMBERSHIP where ID = @EXISTINGMEMBERSHIPID)
                        raiserror('BBERR_INVALIDEXPIRATIONDATE',13,1);
                end

                --Renew/Upgrade/Downgrade/Re-join an existing membership

                update dbo.MEMBERSHIP
                set EXPIRATIONDATE = (case when @SETEXPIRATIONDATE = 1 then dbo.UFN_DATE_GETLATESTTIME(@MEMBERSHIPEXPIRESONDATE) else null end),
                    LASTRENEWEDON = dbo.UFN_DATE_GETEARLIESTTIME(@DATE),
                    MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
                    MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
                    NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
                    ISGIFT = @PAYINGORGIVINGRADIO,
                    GIVENBYID = case when @PAYINGORGIVINGRADIO = 1 then @BILLTOCONSTITUENTID else null end,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE,
                    STATUSCODE = 0,
                    AUTOMATICALLYRENEWMEMBERSHIP = @AUTOMATICALLYRENEWMEMBERSHIP,
          MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID
                where MEMBERSHIP.ID = @EXISTINGMEMBERSHIPID

                --Save Addons

                insert into dbo.MEMBERSHIPADDON(ID, MEMBERSHIPID, ADDONID, QUANTITY, EXPIRATIONDATE, REVENUESPLITID, PURCHASEPRICE, 
                    TRANSACTIONPURCHASEPRICE, ORGANIZATIONPURCHASEPRICE, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, 
                    ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    A.ID,
                    @EXISTINGMEMBERSHIPID,
                    A.ADDONID,
                    A.NUMBEROFADDONS,
                    @MEMBERSHIPEXPIRESONDATE,
                    A.REVENUESPLITID,
                    case 
                        when @BASEEXCHANGERATEID is null then A.PRICE
                        else dbo.UFN_CURRENCY_CONVERT(A.PRICE, @LATESTBASEEXCHANGERATE)
                    end, 
                    A.PRICE,
                    case
                        when @ORGANIZATIONEXCHANGERATEID is null then A.PRICE
                        else dbo.UFN_CURRENCY_CONVERT(A.PRICE, @ORGANIZATIONEXCHANGERATEID)
                    end,
                    @TRANSACTIONCURRENCYID,
                    @BASECURRENCYID,
                    @BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @ADDONS A
                where A.APPLY = 1

                -- Drop members that no longer exist

                update dbo.MEMBER
                    set
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE,
                        ISDROPPED = 1,
                        ISPRIMARY = 0
                where MEMBERSHIPID = @EXISTINGMEMBERSHIPID
                    and CONSTITUENTID not in (select ID from @MEMBERS)
                    and CONSTITUENTID <> @PRIMARYMEMBERID;

                -- Update members that are still active

                update MEMBER
                    set
                        MEMBER.CHANGEDBYID = @CHANGEAGENTID,
                        MEMBER.DATECHANGED = @CURRENTDATE,
                        MEMBER.ISPRIMARY = MS.ISPRIMARY
                from
                  dbo.MEMBER
                inner join @MEMBERS MS on MS.ID = MEMBER.CONSTITUENTID
                where MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID and MEMBER.ISDROPPED = 0;

                -- Insert new members

                insert into dbo.MEMBER
                    (ID, CONSTITUENTID, MEMBERSHIPID, ISPRIMARY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    newid(), MEMBERS.ID, @EXISTINGMEMBERSHIPID, MEMBERS.ISPRIMARY, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @MEMBERS MEMBERS
                where MEMBERS.ID not in (select CONSTITUENTID from dbo.MEMBER where MEMBERSHIPID = @EXISTINGMEMBERSHIPID and ISDROPPED = 0)

            end

            declare @MEMBERSHIPTRANSACTIONID uniqueidentifier = newid();

            --If we created a pledge for this payment, then link to the membership split of the pledge. Otherwise, link to the membership split of the payment

            if @PLEDGECREATED = 1
            begin
                insert into dbo.MEMBERSHIPTRANSACTION(ID, MEMBERSHIPID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, TRANSACTIONDATE, EXPIRATIONDATE, REVENUESPLITID, ISGIFT, DONORID, NUMBEROFCHILDREN, ACTIONCODE, MEMBERSHIPPROMOID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, COMMENTS,MEMBERSHIPLEVELTYPECODEID,UPGRADEMETHODCODE)
                values(@MEMBERSHIPTRANSACTIONID, @EXISTINGMEMBERSHIPID, @MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @DATE, case when @SETEXPIRATIONDATE = 1 then dbo.UFN_DATE_GETLATESTTIME(@MEMBERSHIPEXPIRESONDATE) else null end, @PLEDGEMEMBERSHIPPARENTSPLITID, @PAYINGORGIVINGRADIO, case when @PAYINGORGIVINGRADIO = 1 then @BILLTOCONSTITUENTID else null end, @NUMBEROFCHILDREN, @MEMBERSHIPACTIONCODE, @APPLIEDDISCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @COMMENTS,@MEMBERSHIPLEVELTYPECODEID,@UPGRADEMETHODCODE)

                --USP_PLEDGE_ADD does not create membership installment GL. This is intentional--we need to associate the revenue with the membership transaction here (above). This needs to be done so the correct GL account segment values will be used on the gl generation.

                if @INSTALLMENTPLEDGEPOSTSTATUSCODE = 1
                    exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @PLEDGEID, @CHANGEAGENTID, @CURRENTDATE;
            end
            else if @RECURRINGGIFTCREATED = 1
            begin 
                insert into dbo.MEMBERSHIPTRANSACTION(ID, MEMBERSHIPID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, TRANSACTIONDATE, EXPIRATIONDATE, REVENUESPLITID, ISGIFT, DONORID, NUMBEROFCHILDREN, ACTIONCODE, MEMBERSHIPPROMOID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, COMMENTS,MEMBERSHIPLEVELTYPECODEID,UPGRADEMETHODCODE)
                values(@MEMBERSHIPTRANSACTIONID, @EXISTINGMEMBERSHIPID, @MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @DATE, case when @SETEXPIRATIONDATE = 1 then dbo.UFN_DATE_GETLATESTTIME(@MEMBERSHIPEXPIRESONDATE) else null end, @RGMEMBERSHIPPARENTSPLITID, @PAYINGORGIVINGRADIO, case when @PAYINGORGIVINGRADIO = 1 then @BILLTOCONSTITUENTID else null end, @NUMBEROFCHILDREN, @MEMBERSHIPACTIONCODE, @APPLIEDDISCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @COMMENTS,@MEMBERSHIPLEVELTYPECODEID,@UPGRADEMETHODCODE)
            end
            else
            begin      
                if @PAYMENTCREATED = 1 or @WHEREISREVENUETRACKEDCODE = 1
                begin
                    if @STARTINGSTATUSCODE = 2 and exists (select ID from dbo.MEMBERSHIPTRANSACTION where MEMBERSHIPID = @EXISTINGMEMBERSHIPID)
                    begin
                        update dbo.MEMBERSHIPTRANSACTION
                        set
                            EXPIRATIONDATE = case when @SETEXPIRATIONDATE = 1 then dbo.UFN_DATE_GETLATESTTIME(@MEMBERSHIPEXPIRESONDATE) else null end,
                            REVENUESPLITID = @MEMBERSHIPSPLITID,
                            ACTIONCODE = 0, --Join

              MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID
                        where
                            MEMBERSHIPID = @EXISTINGMEMBERSHIPID
                    end
                    else
                    begin
                        insert into dbo.MEMBERSHIPTRANSACTION(ID, MEMBERSHIPID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, TRANSACTIONDATE, EXPIRATIONDATE, REVENUESPLITID, ISGIFT, DONORID, NUMBEROFCHILDREN, ACTIONCODE, MEMBERSHIPPROMOID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, COMMENTS,MEMBERSHIPLEVELTYPECODEID,BASEAMOUNT,UPGRADEMETHODCODE)
                        values(@MEMBERSHIPTRANSACTIONID, @EXISTINGMEMBERSHIPID, @MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @DATE, case when @SETEXPIRATIONDATE = 1 then dbo.UFN_DATE_GETLATESTTIME(@MEMBERSHIPEXPIRESONDATE) else null end, @MEMBERSHIPSPLITID, @PAYINGORGIVINGRADIO, case when @PAYINGORGIVINGRADIO = 1 then @BILLTOCONSTITUENTID else null end, @NUMBEROFCHILDREN, @MEMBERSHIPACTIONCODE, @APPLIEDDISCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @COMMENTS,@MEMBERSHIPLEVELTYPECODEID,@NONREVENUEBASEAMOUNT,@UPGRADEMETHODCODE)
                    end
                end
            end
        end

        update dbo.MEMBERSHIPADDON set MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID
        from dbo.MEMBERSHIPADDON
            inner join @ADDONS ADDONS on ADDONS.ID = MEMBERSHIPADDON.ID

    if @MEMBERSHIPSPLITID is not null or @RGORPLEDGEMEMBERSHIPSPLITID is not null
    begin
            insert into dbo.REVENUESPLITCAMPAIGN
            (
                    REVENUESPLITID,
                    CAMPAIGNID,
                    CAMPAIGNSUBPRIORITYID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                    coalesce(@MEMBERSHIPSPLITID,@RGORPLEDGEMEMBERSHIPSPLITID),
                    -- Check override setting on this program/level. If on, use campaign on level if not use program.

                    case ML.OVERRIDECAMPAIGNS when 1 then MLC.CAMPAIGNID else MPC.CAMPAIGNID end CAMPAIGNID,
                    case ML.OVERRIDECAMPAIGNS when 1 then MLC.CAMPAIGNSUBPRIORITYID else MPC.CAMPAIGNSUBPRIORITYID end CAMPAIGNSUBPRIORITYID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from dbo.MEMBERSHIPLEVEL ML
            left outer join dbo.MEMBERSHIPPROGRAMCAMPAIGN MPC on ML.MEMBERSHIPPROGRAMID = MPC.MEMBERSHIPPROGRAMID
            left outer join dbo.MEMBERSHIPLEVELCAMPAIGN MLC on ML.ID = MLC.MEMBERSHIPLEVELID
            where
                    ML.ID = @MEMBERSHIPLEVELID and
                    ((@DATE between MPC.DATEFROM and MPC.DATETO) or
                    (@DATE >= MPC.DATEFROM and MPC.DATETO IS NULL) or
                    (@DATE <= MPC.DATETO and MPC.DATEFROM is null) or
                    (MPC.DATEFROM IS NULL and MPC.DATETO IS NULL)) and

                    ((@DATE between MLC.DATEFROM and MLC.DATETO) or
                    (@DATE >= MLC.DATEFROM and MLC.DATETO IS NULL) or
                    (@DATE <= MLC.DATETO and MLC.DATEFROM is null) or
                    (MLC.DATEFROM IS NULL and MLC.DATETO IS NULL)) and

                    (isnull(MLC.CAMPAIGNID,MPC.CAMPAIGNID) is not null) and
                    (select CAMPAIGN.ISACTIVE from dbo.CAMPAIGN where CAMPAIGN.ID = (case ML.OVERRIDECAMPAIGNS when 1 then MLC.CAMPAIGNID else MPC.CAMPAIGNID end)) = 1;
    end

    --Update campaign based on the contribution portion of the membership

    exec dbo.USP_REVENUE_ADDCAMPAIGNS @ID, @CHANGEAGENTID, @CURRENTDATE; 

        --Enter the contribution portion into the MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE table

        if @OBTAINLEVELCODE = 1 AND @WHEREISREVENUETRACKEDCODE = 0
        begin
            insert into dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
            (
                ID,
                FINANCIALTRANSACTIONLINEITEMID,
                MEMBERSHIPID,
                CONSTITUENTID,
                MEMBERSHIPPROGRAMID,
                ORIGINALMEMBERSHIPTRANSACTIONID,
                CURRENTMEMBERSHIPTRANSACTIONID,
                AMOUNT,
                EFFECTIVEDATE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                newid(),
                @MEMBERSHIPSPLITID,
                @EXISTINGMEMBERSHIPID,
                @BILLTOCONSTITUENTID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @MEMBERSHIPTRANSACTIONID,
                case when @BASEEXCHANGERATEID is null then @MEMBERSHIPTRANSACTIONAMOUNT else dbo.UFN_CURRENCY_CONVERT(@MEMBERSHIPTRANSACTIONAMOUNT, @BASEEXCHANGERATEID) end,
                @DATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
        end
        else
        begin
            insert into dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
            (
                ID,
                FINANCIALTRANSACTIONLINEITEMID,
                MEMBERSHIPID,
                CONSTITUENTID,
                MEMBERSHIPPROGRAMID,
                ORIGINALMEMBERSHIPTRANSACTIONID,
                CURRENTMEMBERSHIPTRANSACTIONID,
                AMOUNT,
                EFFECTIVEDATE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                newid(),
                ID,
                @EXISTINGMEMBERSHIPID,
                @BILLTOCONSTITUENTID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @MEMBERSHIPTRANSACTIONID,
                case when @BASEEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @BASEEXCHANGERATEID) end,
                @DATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from
                @MEMBERSHIPREVENUESPLITTABLE
            where APPLICATIONCODE = 0 and AMOUNT > 0

            union all

            select
                newid(),
                ID,
                @EXISTINGMEMBERSHIPID,
                @BILLTOCONSTITUENTID
                ,@MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID
                ,@MEMBERSHIPTRANSACTIONID,
                case when @BASEEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @BASEEXCHANGERATEID) end,
                @DATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @RGSPLITTABLE
            where APPLICATIONCODE = 0

            union all

            select
                newid(),
                ID,
                @EXISTINGMEMBERSHIPID,
                @BILLTOCONSTITUENTID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @MEMBERSHIPTRANSACTIONID,
                case when @BASEEXCHANGERATEID is null then AMOUNT else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @BASEEXCHANGERATEID) end,
                @DATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @PLEDGESPLITTABLE
            where APPLICATIONCODE = 0

            insert into dbo.MEMBERSHIPCONTRIBUTIONPORTION
            (
                ID,
                FINANCIALTRANSACTIONLINEITEMID,
                MEMBERSHIPPROGRAMID,
                MEMBERSHIPTRANSACTIONID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select
                newid(),
                ID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @MEMBERSHIPREVENUESPLITTABLE
            where APPLICATIONCODE = 0 and AMOUNT > 0

            union all

            select
                newid(),
                ID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @RGSPLITTABLE
            where APPLICATIONCODE = 0

            union all

            select
                newid(),
                ID,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPTRANSACTIONID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from @PLEDGESPLITTABLE
            where APPLICATIONCODE = 0
        end

        if @WHATPAYINGFORVALUE <> @PAY_MEMBERSHIPTRANSACTTIONTYPECODE or @PROGRAMTYPE = @RECURRINGPROGRAMTYPECODE
        begin
            -- Membership cards

            declare @MEMBERSHIPCARDSTABLE table
            (
                ID uniqueidentifier,
                MEMBERID uniqueidentifier,
                CONSTITUENTID uniqueidentifier,
                NAMEONCARD nvarchar(100),
                EXPIRATIONDATE date
            )

            -- Populate from XML

            insert into @MEMBERSHIPCARDSTABLE (ID, MEMBERID, CONSTITUENTID, NAMEONCARD, EXPIRATIONDATE)
            select
                null,
                MEMBER.ID,
                T.c.value('(CONSTITUENTID)[1]','uniqueidentifier'),
                T.c.value('(NAMEONCARD)[1]','nvarchar(100)'),
                T.c.value('(EXPIRATIONDATE)[1]','date')
            from
                @MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(c)
                inner join dbo.MEMBER on T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') = MEMBER.CONSTITUENTID and MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID and MEMBER.ISDROPPED = 0

            -- Existing membership cards. We create this table and select distinct to avoid pulling duplicate cards that already exist. This would probably be better off being done in

            -- the UIModel and original pull.

            declare @EXISTINGCARDSTABLE table
            (
                MEMBERSHIPCARDID uniqueidentifier,
                MEMBERID uniqueidentifier,
                CONSTITUENTID uniqueidentifier,
                NAMEONCARD nvarchar(100)
            )
            insert into @EXISTINGCARDSTABLE
            select distinct MEMBERSHIPCARD.ID as MEMBERSHIPCARDID,
                                            MEMBER.ID as MEMBERID,
                                            MEMBER.CONSTITUENTID,
                                            MEMBERSHIPCARD.NAMEONCARD
            from dbo.MEMBERSHIPCARD
            inner join dbo.MEMBER on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
            inner join @MEMBERSHIPCARDS.nodes('/MEMBERSHIPCARDS/ITEM') T(c) 
            on     T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') = MEMBER.CONSTITUENTID
            where MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID  and MEMBERSHIPCARD.STATUSCODE = 0

            update @MEMBERSHIPCARDSTABLE set [@MEMBERSHIPCARDSTABLE].ID = [@EXISTINGCARDSTABLE].MEMBERSHIPCARDID, [@MEMBERSHIPCARDSTABLE].MEMBERID = [@EXISTINGCARDSTABLE].MEMBERID
            from @MEMBERSHIPCARDSTABLE
            inner join @EXISTINGCARDSTABLE on [@EXISTINGCARDSTABLE].CONSTITUENTID = [@MEMBERSHIPCARDSTABLE].CONSTITUENTID and 
            [@EXISTINGCARDSTABLE].NAMEONCARD = [@MEMBERSHIPCARDSTABLE].NAMEONCARD


            update @MEMBERSHIPCARDSTABLE set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');

            -- if existing membership card is printed and has an expiration date, cancel the old card and issue a new card

            update dbo.MEMBERSHIPCARD set STATUSCODE = 2
            from dbo.MEMBERSHIPCARD 
                inner join @MEMBERSHIPCARDSTABLE TEMPTBL on TEMPTBL.ID = MEMBERSHIPCARD.ID
            where MEMBERSHIPCARD.STATUSCODE = 1 and 
                (((MEMBERSHIPCARD.EXPIRATIONDATE is not null) and MEMBERSHIPCARD.EXPIRATIONDATE < TEMPTBL.EXPIRATIONDATE) or 
                    MEMBERSHIPCARD.NAMEONCARD <> TEMPTBL.NAMEONCARD)

            -- if a membership card already exists for this membership, but for a different member than the current card(s) being issued cancel the old card

            update dbo.MEMBERSHIPCARD set STATUSCODE = 2
            from @MEMBERSHIPCARDSTABLE TEMPTBL
                join dbo.MEMBER NEWCARDMEMBER on TEMPTBL.MEMBERID = NEWCARDMEMBER.ID
                join dbo.MEMBERSHIP MP on NEWCARDMEMBER.MEMBERSHIPID = MP.ID
                join dbo.MEMBER OLDCARDMEMBER on MP.ID = OLDCARDMEMBER.MEMBERSHIPID and OLDCARDMEMBER.ID != NEWCARDMEMBER.ID
                join dbo.MEMBERSHIPCARD OLDCARD on OLDCARDMEMBER.ID = OLDCARD.MEMBERID

            -- Cancel all cards that are issued from this existing membership for members that are dropped

            if @EXISTINGMEMBERSHIPID is not null 
            begin
                update dbo.MEMBERSHIPCARD set 
                    STATUSCODE = 2,
                    DATECHANGED = @CURRENTDATE,
                    CHANGEDBYID = @CHANGEAGENTID
                from dbo.MEMBERSHIPCARD
                    inner join dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID
                where
                    MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID
                    and MEMBER.ISDROPPED = 1
                    and MEMBERSHIPCARD.STATUSCODE <> 2
            end

            update TEMPTBL set ID = newid()
            from @MEMBERSHIPCARDSTABLE TEMPTBL
                inner join dbo.MEMBERSHIPCARD on TEMPTBL.ID = MEMBERSHIPCARD.ID
            where
                MEMBERSHIPCARD.STATUSCODE = 2 and 
                (
                    (
                        (MEMBERSHIPCARD.EXPIRATIONDATE is not null) and
                        MEMBERSHIPCARD.EXPIRATIONDATE < TEMPTBL.EXPIRATIONDATE
                    ) or
                    MEMBERSHIPCARD.NAMEONCARD <> TEMPTBL.NAMEONCARD
                )

            update dbo.[MEMBERSHIPCARD] set STATUSCODE = 2 where [MEMBERSHIPCARD].ID in 
                -- JLM 3/3/2012 : WI 199320

                /*    Using a locking hint here to alleviate contention for the MEMBERSHIPCARD table.  I'm not
                    a huge fan of adding locking hints vs refactoring, but given the nature of this subquery,
                    the hint should be ok here since we have the context of the specific membership record. */
                (select MEMBERSHIPCARD.ID from dbo.MEMBERSHIPCARD with (nolock) inner join dbo.MEMBER on MEMBER.ID = MEMBERSHIPCARD.MEMBERID where MEMBER.MEMBERSHIPID = @EXISTINGMEMBERSHIPID
                EXCEPT select ID from @MEMBERSHIPCARDSTABLE)

            -- If existing membership card doesn't have an expiration date, do not update expiration date

            update TEMPTBL set EXPIRATIONDATE = null
            from @MEMBERSHIPCARDSTABLE TEMPTBL
                inner join dbo.MEMBERSHIPCARD on TEMPTBL.ID = MEMBERSHIPCARD.ID
            where MEMBERSHIPCARD.EXPIRATIONDATE is null

            -- update the items that exist in the XML table and the db

            update dbo.[MEMBERSHIPCARD]
            set
                [MEMBERSHIPCARD].[NAMEONCARD] = temp.[NAMEONCARD],
                [MEMBERSHIPCARD].[EXPIRATIONDATE] = temp.[EXPIRATIONDATE],
                [MEMBERSHIPCARD].[MEMBERID] = temp.[MEMBERID],
                [MEMBERSHIPCARD].CHANGEDBYID = @CHANGEAGENTID,
                [MEMBERSHIPCARD].DATECHANGED = @CURRENTDATE
            from dbo.[MEMBERSHIPCARD] inner join @MEMBERSHIPCARDSTABLE as [temp] on [MEMBERSHIPCARD].ID = [temp].ID
            where
                ([MEMBERSHIPCARD].[NAMEONCARD] <> temp.[NAMEONCARD]) or
                ([MEMBERSHIPCARD].[NAMEONCARD] is null and temp.[NAMEONCARD] is not null) or
                ([MEMBERSHIPCARD].[NAMEONCARD] is not null and temp.[NAMEONCARD] is null) or
                ([MEMBERSHIPCARD].[EXPIRATIONDATE] <> temp.[EXPIRATIONDATE]) or
                ([MEMBERSHIPCARD].[EXPIRATIONDATE] is null and temp.[EXPIRATIONDATE] is not null) or
                ([MEMBERSHIPCARD].[EXPIRATIONDATE] is not null and temp.[EXPIRATIONDATE] is null) or
                ([MEMBERSHIPCARD].[MEMBERID] <> temp.[MEMBERID]) or
                ([MEMBERSHIPCARD].[MEMBERID] is null and temp.[MEMBERID] is not null) or
                ([MEMBERSHIPCARD].[MEMBERID] is not null and temp.[MEMBERID] is null)

            -- insert new items

            insert into dbo.[MEMBERSHIPCARD] 
                ([ID], 
                [MEMBERID],
                [NAMEONCARD],
                [EXPIRATIONDATE],
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED)
            select [ID], 
                [MEMBERID],
                [NAMEONCARD],
                [EXPIRATIONDATE],
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @MEMBERSHIPCARDSTABLE as [temp]
            where not exists (select ID from dbo.[MEMBERSHIPCARD] as data where data.ID = [temp].ID)
        end

        --Save the GL distributions

        --Moved to end of procedure to make sure all records necessary for building the account string have been saved.

        if @POSTSTATUSCODE <> 2 and @PAYMENTCREATED = 1
        begin
            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

            -- save any benefit distributions

            exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
        end

        -- KevinHi: Reports require that the Pledge/Recurring Gift ID only be outputted when we never made a payment.

        if @PLEDGECREATED = 1 and @RENEWALREVENUETYPE = 2
        begin
            set @ID = @PLEDGEID
        end

        if @RECURRINGGIFTCREATED = 1 and @RENEWALREVENUETYPE = 2
        begin
            set @ID = @RECURRINGGIFTID
        end

        if @ISONEOFF = 1
        begin
            set @ID=@EXISTINGMEMBERSHIPID
        end

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0