USP_DATAFORMTEMPLATE_ADD_BATCHMEMBERSHIPDUESBATCHROW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHMEMBERSHIPDUESBATCHROW
(
    @ID uniqueidentifier = null output,
    @BATCHID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier,
    @SEQUENCE int,
    @BILLTOCONSTITUENTID 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,
    @DUESTYPECODE tinyint = 0,
    @MEMBERSHIPRECIPIENT uniqueidentifier = null,
    @RENEWALRECIPIENT tinyint = 0,
    @EFFORTID uniqueidentifier = null,
    @FINDERNUMBER nvarchar(19) = null,
    @APPEALID uniqueidentifier = null,
    @CHANNELCODEID uniqueidentifier = null,
    @DATE date = null,
    @MEMBERSHIPTRANSACTIONTYPECODE tinyint = 0,         --0 = Add, 1 = Renew, 2 = Pay, 3 = Upgrade

    @REVENUETYPECODE tinyint = 0,                       -- 0 = Pay in full, 1 = Pay first/next installment, 2 = Pledge, 3 = Other Amount

    @PAYADDITIONALTONEXTINSTALLMENT bit = 0,
    @PAYADDITIONALMONEYAMOUNT money = 0,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
    @MEMBERSHIPLEVELID uniqueidentifier = null,
    @MEMBERSHIPLEVELTERMID uniqueidentifier = null,
    @MEMBERSHIPEXPIRESONDATE date = null,
    @MEMBERSHIPAMOUNT money = null,
    @MEMBERSHIPTRANSACTIONAMOUNT money = null,
    @EXISTINGMEMBERSHIPID uniqueidentifier = null,
    @MEMBERSHIPPLEDGEAMOUNT money = 0,
    @CONTRIBUTORYDESIGNATIONID uniqueidentifier = null,
    @USEDISCOUNTRADIO tinyint = 0,
    @DISCOUNTTYPE uniqueidentifier = null,
    @PROMOTIONCODE nvarchar(50) = null,
    @APPLIEDDISCOUNTID uniqueidentifier = null,

    @ADDDONATION bit = 0,
    @DONATIONAMOUNT money = 0,
    @GIVENANONYMOUSLY bit = 0,
    @DONATIONOPPORTUNITYID uniqueidentifier = null,
    @DONATIONDESIGNATIONID uniqueidentifier = null,
    @DONATIONCATEGORYCODEID uniqueidentifier = null,
    @DECLINESGIFTAID bit = 0,

    @PLEDGEFREQUENCYCODE tinyint = 5,
    @PLEDGENUMBEROFINSTALLMENTS int = 1,
    @PLEDGESTARTDATE datetime = null,
    @AUTOPAY bit = 0,
    @SENDPLEDGEREMINDER bit = 1,

    @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
    @CHECKNUMBER nvarchar(20) = null,
    @REFERENCENUMBER nvarchar(20) = null,
    @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
    @PAYMENTMETHODCODE tinyint = 1,

    @DIRECTDEBITRESULTCODE nvarchar(10) = null,
    @DIRECTDEBITISREJECTED bit = 0,

    @CONSTITUENTACCOUNTID uniqueidentifier = null,
    @REFERENCE nvarchar(255) = null,
    @AUTOMATICALLYRENEWMEMBERSHIP bit = 0,
    @CREDITCARDID uniqueidentifier = null,
    @CREDITCARDNUMBER nvarchar(20) = '',
    @CREDITCARDTOKEN uniqueidentifier = null,
    @CARDHOLDERNAME nvarchar(255) = '',
    @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
    @AUTHORIZATIONCODE nvarchar(20) = '',
    @REJECTIONMESSAGE nvarchar(500) = '',
    @CREDITTYPECODEID uniqueidentifier = null,
    @TRANSACTIONID uniqueidentifier = null,
    @PARTIALCREDITCARDNUMBER nvarchar(4) = '',
    @DONOTACKNOWLEDGE bit = 0,
    @TAXDEDUCTIBLEAMOUNT money = null,
    @LETTERCODEID uniqueidentifier = null,
    @TRIBUTEID uniqueidentifier = null,
    @DONOTRECEIPT bit = 0,
    @COMMENTS nvarchar(255) = '' ,
    @TOTALAMOUNT money = 0,
    @EXISTINGMEMBERS xml = null,
    @EXISTINGCHILDREN xml = null,
    @MEMBERSHIPCARDS xml = null,
    @MEMBERSHIPRECOGNITION xml = null,
    @MEMBERSHIPPROGRAMADDON xml = null,
    @IMPORTADDON xml = null,

    @CAMPAIGNS xml = null,
    @SOLICITORS xml = null,
    @RECOGNITIONS xml = null,

    @BENEFITS xml = null,
    @PERCENTAGEBENEFITS xml = null,

    @INSTALLMENTS xml = null,
    @IMPORT bit = 1,
    @NUMBEROFCHILDREN smallint = 0,        -- Temporary workaround for children not being implemented in 2012 Q1

    @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
    @SOURCECODE nvarchar(50) = '',
    @MEMBERSHIPDECLINESGIFTAID bit = 0,
    @DDISOURCECODEID uniqueidentifier = null,
    @DDISOURCEDATE date = null,
    @VENDORID nvarchar(50) = '',
    @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null,
    @CREDITCARDATTEMPTCOUNT tinyint = 0,
    @NEWCONSTITUENT xml = null,
    @BBNCTRANID int = 0,
    @ORIGINPAGEID int = 0,
    @ORIGINPAGE nvarchar(100) = '',
    @BBNCID int = 0,
    @BBISPROCESSORID uniqueidentifier = null,
    @NAMECODE tinyint = 1,
    @SIMILARADDRESSCODE tinyint = 3,
    @UNSIMILARADDRESSCODE tinyint = 3,
    @NEWADDRESSENDDATECODE tinyint = 0,
    @NEWADDRESSPRIMARYCODE tinyint = 1,
    @BIRTHDATERULECODE  tinyint = 0,
    @DIFFERENTPHONECODE tinyint = 3,
    @NEWPHONEENDDATECODE tinyint = 0,
    @NEWPHONEPRIMARYCODE tinyint = 1,
    @DIFFERENTEMAILCODE tinyint = 3,
    @NEWEMAILENDDATECODE tinyint = 0,
    @NEWEMAILPRIMARYCODE tinyint = 1,
    @USEGLOBALSETTINGS bit = 1,  
    @CREATEHISTORICALNAMECODE tinyint = 1,
    @SEPAMANDATEID uniqueidentifier = null,
    @ADDSEPAMANDATE bit = 0,
    @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = '',
    @SEPAMANDATESIGNATUREDATE date = null,
    @SEPAMANDATETYPECODE tinyint = 0,
    @REQUIRECREDITCARDPROCESSING bit = 0,
    @PAYOTHERAMOUNT money = null,
    @ISGENERATEDPAYMENT bit = 0,
    @GLREVENUECATEGORYMAPPINGID uniqueidentifier = null,
    @SOLICITCODES xml = null
)
as
begin
    set nocount on;

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

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

    if @MEMBERSHIPTRANSACTIONTYPECODE = 3
        select @EXISTINGMEMBERSHIPID = ID, @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID from dbo.MEMBERSHIP where ID = @MEMBERSHIPPROGRAMID

    declare @CURRENTDATE datetime = getdate();

    --Passing transaction guid for reconciliation as BBIS sends BBISTransID

    if @TRANSACTIONID is null
        select @TRANSACTIONID = TransactionGUID from [dbo].[MembershipTransactions]
            where ID = @BBNCTRANID;

    --If we're using the 'Pay other amount' option, clear additional amount

    if @REVENUETYPECODE = 3
    begin
        set @PAYADDITIONALTONEXTINSTALLMENT = 0
        set @PAYADDITIONALMONEYAMOUNT = 0
    end

    begin try
    if @REVENUETYPECODE = 3 and @PAYOTHERAMOUNT <= 0
        raiserror('BBERR_INVALIDPAYOTHERAMOUNT', 13, 1);

        /*Because of the way this batch in particular works, the findernumber parameter is a string instead of bigint, so we need to do some extra validation and casting.*/
        if len(isnull(@FINDERNUMBER, '')) = 0
            set @FINDERNUMBER = 0;

        if len(@FINDERNUMBER) > 0 and isNumeric(@FINDERNUMBER) = 0
            raiserror('BBERR_INVALIDFINDERNUMBER', 13, 1);

        declare @MARKETINGCONSTITUENTID uniqueidentifier = @BILLTOCONSTITUENTID;

        /* Lookup and set all possible marketing data (via output params) from the data that was specified. */
        exec dbo.[USP_REVENUEBATCH_GETMARKETINGDATA]
            @FINDERNUMBER = @FINDERNUMBER,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @SOURCECODE = @SOURCECODE output,
            @MAILINGID = @EFFORTID output,
            @APPEALID = @APPEALID output,
            @CONSTITUENTID = @MARKETINGCONSTITUENTID output,
            @BATCHID = @BATCHID,
            @IMPORT = @IMPORT;

        if @BBNCTRANID = 0
            set @BILLTOCONSTITUENTID = @MARKETINGCONSTITUENTID;

        if @NEWCONSTITUENT.exist('NEWCONSTITUENT/ITEM') = 1 and not exists (select ID from dbo.CONSTITUENT where ID = @BILLTOCONSTITUENTID)
            begin
                declare @NEWCONSTITID uniqueidentifier;
                exec USP_REVENUEBATCH_ADDNEWCONSTITUENTFROMXML_1 @NEWCONSTITUENT, @CHANGEAGENTID, @NEWCONSTITID output, @BBNCTRANID;
                set @BILLTOCONSTITUENTID = @NEWCONSTITID;
            end


        declare @CONSTITUENTID uniqueidentifier;

        select @CONSTITUENTID = 
                    case @DUESTYPECODE 
                        when 0 then @BILLTOCONSTITUENTID 
                        else @MEMBERSHIPRECIPIENT 
                    end;

        --  Write Validation logic here.

        if @PDACCOUNTSYSTEMID is null
            set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

        if @IMPORT = 0 and @ADDDONATION = 0 and @SOLICITORS is not null
            begin
                set @SOLICITORS = null;
                set @DONATIONDESIGNATIONID = null;
                set @DONATIONCATEGORYCODEID = null;
            end

         if @GIVENANONYMOUSLY is null
            set @GIVENANONYMOUSLY = 0;

        --Set currency parameters for backwards compatibility

        if @BASECURRENCYID is null
            begin
                declare @CURRENCYSETID uniqueidentifier;

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

                select 
                    @BASECURRENCYID = CURRENCY.ID
                from dbo.CURRENCYSET
                inner join dbo.CURRENCY 
                    on CURRENCY.ID = CURRENCYSET.BASECURRENCYID
                where CURRENCYSET.ID = coalesce(@CURRENCYSETID, dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET());
            end

        if @TRANSACTIONCURRENCYID is null
            set @TRANSACTIONCURRENCYID = @BASECURRENCYID;

        -- 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
            ) and @BBNCTRANID = 0
            raiserror('TRANSACTIONCURRENCYINVALIDFORACCOUNTSYSTEM',13,1)

        -- If the system has set that households can't be donors, verify that constituent isn't a household

        if dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS() = 0 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
            raiserror('HOUSEHOLDSCANNOTBEDONORS', 13, 1);

        -- if the group type can't be a donor, raise an error

        if exists(select GD.ID from dbo.GROUPDATA GD inner join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID where GD.ID = @CONSTITUENTID and GT.CANBEDONOR = 0)
            raiserror('GROUPCANNOTBEDONOR', 13, 1);

        -- Handle any blank fields

        if @MEMBERSHIPTRANSACTIONTYPECODE is null
            set @MEMBERSHIPTRANSACTIONTYPECODE = 0;

        if @DUESTYPECODE is null
            set @DUESTYPECODE = 0;

        if @REVENUETYPECODE is null
            set @REVENUETYPECODE = 0;

        if @RENEWALRECIPIENT is null
            set @RENEWALRECIPIENT = 0;

        if @USEDISCOUNTRADIO is null
            set @USEDISCOUNTRADIO = 0;

        if @PLEDGEFREQUENCYCODE is null
            set @PLEDGEFREQUENCYCODe = 5;

        if @PLEDGESTARTDATE is null
            set @PLEDGESTARTDATE = @DATE;

        if @PLEDGENUMBEROFINSTALLMENTS is null and @PLEDGEFREQUENCYCODE = 5
            set @PLEDGENUMBEROFINSTALLMENTS = 1;

        if @POSTSTATUSCODE is null
            set @POSTSTATUSCODE = 1

        if @CREDITCARDATTEMPTCOUNT is null
            set @CREDITCARDATTEMPTCOUNT = 0;
        if @CARDHOLDERNAME is null
            set @CARDHOLDERNAME = '';
        if @CREDITCARDNUMBER is null
            set @CREDITCARDNUMBER = '';
        if @AUTHORIZATIONCODE is null
            set @AUTHORIZATIONCODE = '';
        if @EXPIRESON is null
            set @EXPIRESON = '00000000';

        if @SIMILARADDRESSCODE is null
            set @SIMILARADDRESSCODE = 3
        if @UNSIMILARADDRESSCODE is null
            set @UNSIMILARADDRESSCODE = 3
        if @NEWADDRESSENDDATECODE is null
            set @NEWADDRESSENDDATECODE = 0
        if @NEWADDRESSPRIMARYCODE is null
            set @NEWADDRESSPRIMARYCODE = 1
        if @BIRTHDATERULECODE is null
            set @BIRTHDATERULECODE = 0
        if @DIFFERENTPHONECODE is null
            set @DIFFERENTPHONECODE = 3
        if @NEWPHONEENDDATECODE is null
            set @NEWPHONEENDDATECODE = 0
        if @NEWPHONEPRIMARYCODE is null
            set @NEWPHONEPRIMARYCODE = 1
        if @DIFFERENTEMAILCODE is null
            set @DIFFERENTEMAILCODE = 3
        if @NEWEMAILENDDATECODE is null
            set @NEWEMAILENDDATECODE = 0
        if @NEWEMAILPRIMARYCODE is null
            set @NEWEMAILPRIMARYCODE = 1
        if @USEGLOBALSETTINGS is null
            set @USEGLOBALSETTINGS = 1
        if @NAMECODE is null
            set @NAMECODE = 1
        if @CREATEHISTORICALNAMECODE is null
            set @CREATEHISTORICALNAMECODE = 1
        if @ADDSEPAMANDATE is null
            set @ADDSEPAMANDATE = 0;
        if @REQUIRECREDITCARDPROCESSING is null
            set @REQUIRECREDITCARDPROCESSING = 0;
        if @ISGENERATEDPAYMENT is null
            set @ISGENERATEDPAYMENT = 0;

        declare @RECURRINGPROGRAMTYPECODE int = 1
        declare @MEMBERSHIPPROGRAMTYPECODE tinyint;
        declare @DEDUCTIBILITYCODE tinyint;
        declare @DUESTREATEDASCONTRIBUTION bit;
        declare @HASCONTRIBUTIONDESIGNATIONS bit;
        declare @MEMBERSHIPTERM int;  -- 0 = Pay in full, 1 = Monthly, 2 = Quarterly, 3 = Yearly


        select
            @MEMBERSHIPPROGRAMTYPECODE = PROGRAMTYPECODE,
            @DEDUCTIBILITYCODE = DEDUCTIBILITYCODE,
            @DUESTREATEDASCONTRIBUTION = DUESTREATEDASCONTRIBUTION
        from dbo.MEMBERSHIPPROGRAM
        where ID = @MEMBERSHIPPROGRAMID;

        select @MEMBERSHIPTERM=LIFETIMEPAYMENTOPTIONCODE from dbo.MEMBERSHIPLEVELTERM where ID=@MEMBERSHIPLEVELTERMID;
        if @BBNCTRANID > 0
        begin
            --Type always be pay in full when @MEMBERSHIPPROGRAMTYPECODE = lifetime membership(2 represent lifetime membership).

            if @MEMBERSHIPPROGRAMTYPECODE = 2  and @MEMBERSHIPTERM =0 and @MEMBERSHIPAMOUNT=0
            begin
                set @REVENUETYPECODE = 0;    --pay in full

            end
            else if @MEMBERSHIPPROGRAMTYPECODE = 2
            begin
                set @REVENUETYPECODE = 1;    --pay_installment

            end
        end

        declare @LEVELRECEIPTAMOUNT money;
        declare @OBTAINLEVELCODE tinyint;

        select
            @LEVELRECEIPTAMOUNT = RECEIPTAMOUNT,
            @OBTAINLEVELCODE = OBTAINLEVELCODE,
            @HASCONTRIBUTIONDESIGNATIONS = 
                case
                    when exists(select ID from dbo.MEMBERSHIPLEVELDESIGNATION where MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID) then 1
                    else 0
                end
        from dbo.MEMBERSHIPLEVEL
        where ID = @MEMBERSHIPLEVELID;

        if @IMPORT = 1
            begin

                --Always set Autopay = 1 when importing, this will store the CC/DD information on the InstallmentPlan/Recurring gift if it is specified.

                if @PAYMENTMETHODCODE = 2 or @PAYMENTMETHODCODE = 3
                begin
                    set @AUTOPAY = 1;
                end

                --Ensure that the Membership Level Type chosen is listed as associated with the level

                if @MEMBERSHIPLEVELTYPECODEID is not null
                begin
                    if (not exists (select * from dbo.MEMBERSHIPLEVELTYPE 
                                    where MEMBERSHIPLEVELTYPE.LEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID
                                    and MEMBERSHIPLEVELTYPE.LEVELID = @MEMBERSHIPLEVELID))
                    begin
                         raiserror('BBERR_MEMBERSHIPLEVELTYPEINVALID', 13, 1);
                    end
                end

                -- Renewing or paying an existing membership

                if @MEMBERSHIPTRANSACTIONTYPECODE > 0 and @EXISTINGMEMBERSHIPID is null
                    begin
                        if object_id('tempdb..#GROUPID') is not null  
                        drop table #GROUPID;  

                        create table #GROUPID  
                        (  
                         ID uniqueidentifier  
                        );  
                        insert into #GROUPID  
                         select ID  
                         from dbo.UFN_CONSTITUENT_GETGROUPS(@CONSTITUENTID)  
                         where dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1; 
                        with ConstituentMemberships_CTE as (
                            select 
                                MEMBERSHIP.ID,
                                case 
                                    when MEMBER.CONSTITUENTID = @CONSTITUENTID then
                                        case
                                            when MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                                                and MEMBERSHIP.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                                                and MEMBERSHIP.MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID then 1
                                            when MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                                                and MEMBERSHIP.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID then 2
                                            when MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID then 3
                                        end
                                    else
                                        case
                                            when MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                                                and MEMBERSHIP.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                                                and MEMBERSHIP.MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID then 4
                                            when MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                                                and MEMBERSHIP.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID then 5
                                            when MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID then 6
                                        end
                                end MATCHCODE
                            from dbo.MEMBERSHIP
                            inner join dbo.MEMBER
                                on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                            where MEMBERSHIP.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                                and (@MEMBERSHIPTRANSACTIONTYPECODE = 1 or @MEMBERSHIPTRANSACTIONTYPECODE = 3    -- Renewing, level and term can change

                                    or (
                                        MEMBERSHIP.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                                        and MEMBERSHIP.MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID
                                    )
                                )
                                and MEMBER.CONSTITUENTID = @CONSTITUENTID
                                or MEMBER.CONSTITUENTID in (
                                    select ID
                                    from dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID, '3550edaf-780c-48c8-9700-f478eeac9e51', 0)
                                    -- Check security against Constituent Membership View Form

                                ) 
                or MEMBER.CONSTITUENTID in (select ID from #GROUPID)
                        )
                        select top 1
                            @EXISTINGMEMBERSHIPID = ID
                        from ConstituentMemberships_CTE
                        order by MATCHCODE;

                        if @EXISTINGMEMBERSHIPID is null
                            raiserror('BBERR_PAYRENEWMEMBERSHIPDNE', 13, 1);

                    end

                if @DONATIONAMOUNT > 0
                    begin
                        set @ADDDONATION = 1;

                        declare @NUMBERRECOGNITIONS integer = 0;

                        select 
                            @NUMBERRECOGNITIONS = count(T.c.value('CONSTITUENTID [1]', 'uniqueidentifier'))
                        from @RECOGNITIONS.nodes('/RECOGNITIONS/ITEM') T(c);

                        if @NUMBERRECOGNITIONS = 0
                            set @RECOGNITIONS = (
                                select
                                    RECOGNITIONS.CONSTITUENTID,
                                    coalesce(NF.NAME, BATCHREVENUECONSTITUENT.NAME) as NAME,
                                    RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
                                    getDate() as EFFECTIVEDATE,
                                    RECOGNITIONS.AMOUNT
                                from dbo.UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS(@GIVENANONYMOUSLY, @BILLTOCONSTITUENTID, @DONATIONAMOUNT, @DATE, null) as RECOGNITIONS
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RECOGNITIONS.CONSTITUENTID) NF
                                left join dbo.BATCHREVENUECONSTITUENT 
                                    on RECOGNITIONS.CONSTITUENTID = BATCHREVENUECONSTITUENT.ID 
                                        and BATCHREVENUECONSTITUENT.EXISTINGCONSTITUENTID is null
                                for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),BINARY BASE64
                            );

                    end

            end

        if @ADDDONATION = 1 and @DONATIONDESIGNATIONID is null
            raiserror('BBERR_DONATIONDESIGNATIONID_REQUIRED', 13, 1);

        if @OBTAINLEVELCODE = 1 and (select WHEREISREVENUETRACKEDCODE from dbo.MEMBERSHIPPROGRAM where ID = @MEMBERSHIPPROGRAMID) = 0 and @CONTRIBUTORYDESIGNATIONID is null
            raiserror('BBERR_CONTRIBUTORYDESIGNATIONID_REQUIRED', 13, 1);

        declare @PROMOTIONAMOUNT money = 0;
        declare @TOTALADDONAMOUNT money = 0;

        -- Adding or renewing

        if (@MEMBERSHIPTRANSACTIONTYPECODE = 0 or @MEMBERSHIPTRANSACTIONTYPECODE = 1 or @MEMBERSHIPTRANSACTIONTYPECODE = 3)
            begin
                if @IMPORT = 1
                    begin
                        if (@MEMBERSHIPAMOUNT is null or @MEMBERSHIPAMOUNT = 0 or @BBNCTRANID > 0) and @DUESTYPECODE in (0,1) -- Not comping membership

                            select
                                @MEMBERSHIPAMOUNT = AMOUNT
                            from dbo.MEMBERSHIPLEVELTERM
                            where ID = @MEMBERSHIPLEVELTERMID
                                and LEVELID = @MEMBERSHIPLEVELID;

                        declare @EXPIRATIONDATECALCULATED bit = 0;

                        if @MEMBERSHIPEXPIRESONDATE is null and @MEMBERSHIPPROGRAMTYPECODE = 0        -- Recurring and Lifetime do not have expiration dates.

                            begin
                                set @EXPIRATIONDATECALCULATED = 1
                                declare @ACTIONCODE tinyint = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@EXISTINGMEMBERSHIPID, @MEMBERSHIPLEVELID, @DATE);

                                if @ACTIONCODE = 0 or @ACTIONCODE = 5 --join/rejoin

                                    set @MEMBERSHIPEXPIRESONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @DATE);
                                else
                                    begin
                                        declare @CURRENTSTATUS tinyint;
                                        declare @CURRENTEXPIRATIONDATE datetime;

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

                                        if @CURRENTSTATUS = 2 --pending membership

                                            set @MEMBERSHIPEXPIRESONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @DATE);
                                        else
                                            set @MEMBERSHIPEXPIRESONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION_2(@CURRENTEXPIRATIONDATE, @MEMBERSHIPLEVELTERMID);

                                    end
                            end

                        if @DISCOUNTTYPE is not null or len(coalesce(@PROMOTIONCODE, '')) > 0
                            begin
                                declare @PROMOTIONTYPECODE tinyint = 0;
                                declare @DISCOUNTCALCULATIONTYPECODE tinyint = 0;
                                declare @DISCOUNTAMOUNT money = 0;
                                declare @DISCOUNTPERCENT decimal = 0;

                                declare @EXTENSIONCALCULATIONTYPECODE tinyint = 0;
                                declare @EXTENSIONVALUE integer = 0;

                                if @DISCOUNTTYPE is not null
                                    begin
                                        select
                                            @APPLIEDDISCOUNTID = MEMBERSHIPPROMO.ID,
                                            @PROMOTIONTYPECODE = MEMBERSHIPPROMO.PROMOTIONTYPECODE,
                                            @DISCOUNTCALCULATIONTYPECODE = MEMBERSHIPPROMO.DISCOUNTCALCULATIONTYPECODE,
                                            @EXTENSIONCALCULATIONTYPECODE = MEMBERSHIPPROMO.EXTENSIONCALCULATIONTYPECODE,
                                            @DISCOUNTAMOUNT = MEMBERSHIPPROMO.AMOUNT,
                                            @DISCOUNTPERCENT = MEMBERSHIPPROMO.[PERCENT]
                                        from dbo.MEMBERSHIPPROMO
                                        inner join dbo.MEMBERSHIPPROMOAVAILABILITY 
                                            on MEMBERSHIPPROMO.ID = MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROMOID
                                        where MEMBERSHIPPROMO.ID = @DISCOUNTTYPE
                                            and MEMBERSHIPPROMOAVAILABILITY.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;

                                        if @APPLIEDDISCOUNTID is null
                                            raiserror('BBERR_INVALIDPROMOTION', 13, 1);
                                    end
                                else
                                    begin
                                        select
                                            @APPLIEDDISCOUNTID = MEMBERSHIPPROMO.ID,
                                            @PROMOTIONTYPECODE = PROMOTIONTYPECODE,
                                            @DISCOUNTCALCULATIONTYPECODE = DISCOUNTCALCULATIONTYPECODE,
                                            @EXTENSIONCALCULATIONTYPECODE = EXTENSIONCALCULATIONTYPECODE,
                                            @DISCOUNTAMOUNT = AMOUNT,
                                            @DISCOUNTPERCENT = [PERCENT]
                                        from dbo.MEMBERSHIPPROMO
                                        inner join dbo.MEMBERSHIPPROMOCODE
                                            on MEMBERSHIPPROMO.ID = MEMBERSHIPPROMOCODE.MEMBERSHIPPROMOID
                                        where MEMBERSHIPPROMOCODE.PROMOTIONALCODE = @PROMOTIONCODE
                                            and (MEMBERSHIPPROMOCODE.VALIDFROM is null or MEMBERSHIPPROMOCODE.VALIDFROM <= dbo.UFN_DATE_GETEARLIESTTIME(@DATE))
                                            and (MEMBERSHIPPROMOCODE.VALIDTO is null or MEMBERSHIPPROMOCODE.VALIDTO >= dbo.UFN_DATE_GETLATESTTIME(@DATE));

                                        if @APPLIEDDISCOUNTID is null
                                            raiserror('BBERR_INVALIDPROMOCODE', 13, 1);

                                    end

                                set @USEDISCOUNTRADIO = 1;

                                if @PROMOTIONTYPECODE = 0
                                    begin
                                        -- Discount

                                        if @DISCOUNTCALCULATIONTYPECODE = 0
                                            set @PROMOTIONAMOUNT = @DISCOUNTAMOUNT
                                        else
                                            set @PROMOTIONAMOUNT = @MEMBERSHIPAMOUNT * (@DISCOUNTPERCENT / 100)
                                    end
                                else
                                    begin
                                        -- Extension

                                        if @EXTENSIONCALCULATIONTYPECODE = 0
                                            -- Days

                                            set @MEMBERSHIPEXPIRESONDATE = dateadd(dd, @EXTENSIONVALUE, @MEMBERSHIPEXPIRESONDATE);
                                        else
                                            -- Months

                                            set @MEMBERSHIPEXPIRESONDATE = dateadd(mm, @EXTENSIONVALUE, @MEMBERSHIPEXPIRESONDATE);

                                    end

                            end        -- if discount or promo code


                        -- set expiration date on membership cards

                        if @MEMBERSHIPCARDS is not null and  @EXPIRATIONDATECALCULATED = 1
                            set @MEMBERSHIPCARDS.modify('replace value of (/MEMBERSHIPCARDS/ITEM/@EXPIRATIONDATE)[1] with sql:variable("@MEMBERSHIPEXPIRESONDATE")');

                        set @MEMBERSHIPTRANSACTIONAMOUNT = @MEMBERSHIPAMOUNT - @PROMOTIONAMOUNT;

                        if @MEMBERSHIPTRANSACTIONAMOUNT < 0
                            set @MEMBERSHIPTRANSACTIONAMOUNT = 0;

                        if (select T.c.value('(ADDONID)[1]', 'uniqueidentifier')
                                from @MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM[1]') T(c))
                                is not null
                            raiserror('BBERR_USEIMPORTADDONS', 13, 1);

                        -- We have a different collection for imported add-ons (for usability). Here we fill the real collection.

                        set @MEMBERSHIPPROGRAMADDON = (
                            select
                                1 as APPLY,
                                MPA.ADDONID,
                                ADDON.ADDONTYPECODE as ADDONTYPE,
                                case
                                    when MPA.BASECURRENCYID = @TRANSACTIONCURRENCYID then MPA.PRICE
                                    else dbo.UFN_CURRENCY_CONVERT(MPA.PRICE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(MPA.BASECURRENCYID, @TRANSACTIONCURRENCYID, @DATE, 1, null))
                                end as PRICE,
                                T.c.value('(NUMBEROFADDONS)[1]', 'smallint') as NUMBEROFADDONS,
                                MPA.MULTIPLEALLOWED,
                                MPA.TRANSACTIONCURRENCYID
                            from @IMPORTADDON.nodes('/IMPORTADDON/ITEM') T(c)
                            inner join dbo.MEMBERSHIPPROGRAMADDON MPA
                                on MPA.ADDONID = T.c.value('(ADDONID)[1]','uniqueidentifier')
                            inner join dbo.ADDON
                                on ADDON.ID = MPA.ADDONID
                            where MPA.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPPROGRAMADDON'),BINARY BASE64
                        );

                        set @TOTALADDONAMOUNT = (
                            select coalesce(sum(T.c.value('(PRICE)[1]','money') * (case when T.c.value('(MULTIPLEALLOWED)[1]','bit') = 1 then T.c.value('(NUMBEROFADDONS)[1]','smallint') else 1 end)), 0)
                            from @MEMBERSHIPPROGRAMADDON.nodes('/MEMBERSHIPPROGRAMADDON/ITEM') T(c)
                        );

                        set @MEMBERSHIPTRANSACTIONAMOUNT += @TOTALADDONAMOUNT;

                        -- Set up Benefits


                        if (select T.c.value('(BENEFITID)[1]', 'uniqueidentifier')
                                from @BENEFITS.nodes('/BENEFITS/ITEM[1]') T(c))
                                is null
                            begin

                                declare @TERMAMOUNT money = (select AMOUNT from dbo.MEMBERSHIPLEVELTERM where MEMBERSHIPLEVELTERM.ID = @MEMBERSHIPLEVELTERMID)

                                set @BENEFITS = (
                                    select
                                        newid() as ID
                                        , MEMBERSHIPLEVELBENEFIT.BENEFITID
                                        , MEMBERSHIPLEVELBENEFIT.QUANTITY
                                        , MEMBERSHIPLEVELBENEFIT.UNITVALUE
                                        , MEMBERSHIPLEVELBENEFIT.SEQUENCE
                                        , MEMBERSHIPLEVELBENEFIT.DETAILS
                                        , 0 as PERCENTAPPLICABLEAMOUNT
                                    from dbo.MEMBERSHIPLEVELBENEFIT
                                    inner join dbo.BENEFIT on MEMBERSHIPLEVELBENEFIT.BENEFITID = BENEFIT.ID
                                    where MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                                    and MEMBERSHIPLEVELBENEFIT.USEPERCENT = 0
                                    order by MEMBERSHIPLEVELBENEFIT.SEQUENCE
                                    for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64
                                )
                            end
                        if (select T.c.value('(BENEFITID)[1]', 'uniqueidentifier')
                                from @PERCENTAGEBENEFITS.nodes('/PERCENTAGEBENEFITS/ITEM[1]') T(c))
                                is null
                            begin
                                declare @VALUEBENEFITTOTAL money = (select coalesce(sum(MEMBERSHIPLEVELBENEFIT.QUANTITY * MEMBERSHIPLEVELBENEFIT.UNITVALUE),0)
                                                                    from dbo.MEMBERSHIPLEVELBENEFIT
                                                                    where MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                                                                    and MEMBERSHIPLEVELBENEFIT.USEPERCENT = 0)

                                set @PERCENTAGEBENEFITS = (select
                                        newid()
                                        , MEMBERSHIPLEVELBENEFIT.BENEFITID
                                        , MEMBERSHIPLEVELBENEFIT.QUANTITY
                                        , MEMBERSHIPLEVELBENEFIT.VALUEPERCENT
                                        , MEMBERSHIPLEVELBENEFIT.SEQUENCE
                                        , MEMBERSHIPLEVELBENEFIT.DETAILS
                                        , case when @TERMAMOUNT-@VALUEBENEFITTOTAL < 0 then 0
                                            else @TERMAMOUNT-@VALUEBENEFITTOTAL
                                            end as PERCENTAPPLICABLEAMOUNT
                                    from dbo.MEMBERSHIPLEVELBENEFIT
                                    inner join dbo.BENEFIT on MEMBERSHIPLEVELBENEFIT.BENEFITID = BENEFIT.ID
                                    where MEMBERSHIPLEVELBENEFIT.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                                    and MEMBERSHIPLEVELBENEFIT.USEPERCENT = 1
                                    order by MEMBERSHIPLEVELBENEFIT.SEQUENCE
                                    for xml raw('ITEM'),type,elements,root('PERCENTAGEBENEFITS'),BINARY BASE64
                                 )
                            end

                    end        -- if @IMPORT


                exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_ADDRENEWPROGRAM
                            @CURRENTAPPUSERID,
                            @ID,
                            @BATCHID,
                            @MEMBERSHIPTRANSACTIONTYPECODE,
                            @DATE,
                            @MEMBERSHIPEXPIRESONDATE,
                            @MEMBERSHIPPROGRAMID,
                            @MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID,
                            @CONSTITUENTID,
                            @REVENUETYPECODE,
                            @EXISTINGMEMBERSHIPID,
                            @MEMBERSHIPPROGRAMADDON,
                            @EXISTINGMEMBERS,
                            @MEMBERSHIPCARDS,
                            @EXISTINGCHILDREN,
                            @NUMBEROFCHILDREN,        -- Temporary workaround for children not being implemented in 2012 Q1

                            @IMPORTADDON,
                            @IMPORT;

                -- Creating a pledge or recurring gift

                if @REVENUETYPECODE > 0
                    begin
                        if @IMPORT = 1 or @BBNCTRANID > 0
                            begin

                                if @BBNCTRANID > 0
                                begin
                                    select @MEMBERSHIPPLEDGEAMOUNT = case when MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS = 0 then  MEMBERSHIPLEVELTERM.AMOUNT
                                                                                                     else MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS * MEMBERSHIPLEVELTERM.AMOUNT
                                                                                                     end,
                                                 @PLEDGEFREQUENCYCODE = case MEMBERSHIPLEVELTERM.LIFETIMEPAYMENTOPTIONCODE when 0 then 5
                                                                                                                                                                                                     when 1 then 3
                                                                                                                                                                                                     when 2 then 2
                                                                                                                                                                                                     when 3 then 0
                                                                                                else 5
                                                                                                end,
                                                 @PLEDGENUMBEROFINSTALLMENTS = case MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS when 0 then 1
                                                                                                             else MEMBERSHIPLEVELTERM.LIFETIMENUMBEROFPAYMENTS
                                                                                                             end
                                    from dbo.MEMBERSHIPLEVELTERM
                                    where MEMBERSHIPLEVELTERM.ID = @MEMBERSHIPLEVELTERMID
                                end
                                else
                                begin
                                    set @MEMBERSHIPPLEDGEAMOUNT = isnull(@MEMBERSHIPAMOUNT, 0) - @PROMOTIONAMOUNT;
                                    if @MEMBERSHIPPLEDGEAMOUNT < 0
                                            set @MEMBERSHIPPLEDGEAMOUNT = 0;
                                    set @MEMBERSHIPPLEDGEAMOUNT += isnull(@TOTALADDONAMOUNT, 0)
                                end

                                if @MEMBERSHIPPLEDGEAMOUNT < 0
                                    set @MEMBERSHIPPLEDGEAMOUNT = 0;

                                if @PLEDGEFREQUENCYCODE <> 4
                                            begin
                                                declare @TOTALBENEFITS xml;
                                                set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITSFORBATCH(@BENEFITS, @PERCENTAGEBENEFITS);

                                                set @INSTALLMENTS = dbo.UFN_REVENUEBATCH_GENERATEINSTALLMENTS2(@MEMBERSHIPPLEDGEAMOUNT, @PLEDGEFREQUENCYCODE, @PLEDGESTARTDATE, @PLEDGENUMBEROFINSTALLMENTS, @TRANSACTIONCURRENCYID, @TOTALBENEFITS);
                                            end
                                -- Paying first installment for Lifetime or Annual program (not recurring program)

                                if @MEMBERSHIPPROGRAMTYPECODE <> 1 and @REVENUETYPECODE = 1
                                    begin
                                        select top 1
                                            @MEMBERSHIPTRANSACTIONAMOUNT = T.c.value('AMOUNT [1]', 'money')
                                        from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
                                        order by T.c.value('SEQUENCE [1]', 'int');

                                        set @TOTALAMOUNT = @MEMBERSHIPTRANSACTIONAMOUNT;

                                        -- TODO This should be calculated based on the pledge installments and amount paid...

                                        set @TAXDEDUCTIBLEAMOUNT = @MEMBERSHIPTRANSACTIONAMOUNT;
                                    end
                                else if @MEMBERSHIPPROGRAMTYPECODE <> @RECURRINGPROGRAMTYPECODE and @REVENUETYPECODE = 2
                                    begin
                                        -- Pledge

                                        set @MEMBERSHIPTRANSACTIONAMOUNT = 0;
                                        set @TOTALAMOUNT = 0;
                                    end  

                            end

                        --Validate that pledge is valid

                        if @MEMBERSHIPPROGRAMTYPECODE <> @RECURRINGPROGRAMTYPECODE
                            exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_PLEDGE @DATE, @MEMBERSHIPPLEDGEAMOUNT, @PLEDGEFREQUENCYCODE, @PLEDGESTARTDATE, @PLEDGENUMBEROFINSTALLMENTS, @INSTALLMENTS;
                    end
                else
                    -- Paying in full

                    if @IMPORT = 1
                        begin
                            set @TAXDEDUCTIBLEAMOUNT = 
                                    case @DEDUCTIBILITYCODE
                                            when 0 then @MEMBERSHIPAMOUNT - @PROMOTIONAMOUNT
                                            when 1 then @LEVELRECEIPTAMOUNT - @PROMOTIONAMOUNT
                                            when 2 then 0
                                    end;

                            if @TAXDEDUCTIBLEAMOUNT < 0
                                set @TAXDEDUCTIBLEAMOUNT = 0;

                            set @TOTALAMOUNT = @MEMBERSHIPAMOUNT - @PROMOTIONAMOUNT;
                            if @TOTALAMOUNT < 0
                                set @TOTALAMOUNT = 0;
                            set @TOTALAMOUNT += @TOTALADDONAMOUNT;

                            -- Default Membership recognitions

                            -- Dues based level with Contributory portion

                            declare @NUMBERMEMBERSHIPRECOGNITIONS integer = 0;

                            select 
                                @NUMBERMEMBERSHIPRECOGNITIONS = count(T.c.value('CONSTITUENTID [1]', 'uniqueidentifier'))
                            from @MEMBERSHIPRECOGNITION.nodes('/MEMBERSHIPRECOGNITION/ITEM') T(c);

                            if @NUMBERMEMBERSHIPRECOGNITIONS = 0 and @OBTAINLEVELCODE = 0 and @DUESTREATEDASCONTRIBUTION = 1 and @HASCONTRIBUTIONDESIGNATIONS = 1
                                begin
                                    declare @BASETAXDEDUCTIBLEAMOUNT money = @TAXDEDUCTIBLEAMOUNT;

                                    if @TRANSACTIONCURRENCYID <> @BASECURRENCYID
                                        set @BASETAXDEDUCTIBLEAMOUNT = @BASETAXDEDUCTIBLEAMOUNT * @EXCHANGERATE;

                                    declare @DESIGNATIONSTABLE table(DESIGNATIONID uniqueidentifier, AMOUNT money);

                                    insert into @DESIGNATIONSTABLE(DESIGNATIONID, AMOUNT)
                                        select 
                                            MEMBERSHIPLEVELDESIGNATION.DESIGNATIONID, 
                                            case MEMBERSHIPLEVELDESIGNATION.[PERCENT]
                                                when 0 then 0
                                                else (MEMBERSHIPLEVELDESIGNATION.[PERCENT] / 100) * @BASETAXDEDUCTIBLEAMOUNT
                                            end as AMOUNT
                                        from dbo.MEMBERSHIPLEVELDESIGNATION
                                        where MEMBERSHIPLEVELDESIGNATION.MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID;

                                    set @MEMBERSHIPRECOGNITION = (
                                        select 
                                            RECOGNITIONS.CONSTITUENTID,
                                            RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
                                            DESIGNATIONS.DESIGNATIONID,
                                            @DATE as EFFECTIVEDATE,
                                            DESIGNATIONS.AMOUNT,
                                            @TRANSACTIONCURRENCYID as APPLICATIONCURRENCYID
                                        from @DESIGNATIONSTABLE DESIGNATIONS
                                        inner join dbo.DESIGNATION 
                                            on DESIGNATIONS.DESIGNATIONID = DESIGNATION.ID
                                        outer apply dbo.UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_2(@GIVENANONYMOUSLY, @BILLTOCONSTITUENTID, DESIGNATIONS.AMOUNT, @DATE, null, default, default) as RECOGNITIONS
                                        left outer join dbo.REVENUERECOGNITIONTYPECODE 
                                            on RECOGNITIONS.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
                                        for xml raw('ITEM'),type,elements,root('MEMBERSHIPRECOGNITION'),BINARY BASE64
                                    );

                                end
                        end        -- if @IMPORT


            end
        else
            begin
                -- Paying for an existing pledged membership

                if @IMPORT = 1 
                    begin
                        declare @PLEDGEID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETPLEDGE(@EXISTINGMEMBERSHIPID);
                        declare @RECURRINGGIFTID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(@EXISTINGMEMBERSHIPID)
                        declare @INSTALLMENTID uniqueidentifier = dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(@PLEDGEID, null);

                        --When importing and paying for a pledged or recurring membership, we don't want to change the number of children even if it is sent.

                        set  @NUMBEROFCHILDREN = (select NUMBEROFCHILDREN from dbo.MEMBERSHIP where ID = @EXISTINGMEMBERSHIPID);

                        if @PLEDGEID is not null
                            begin
                                if @REVENUETYPECODE = 1 --Pay next

                                    set @MEMBERSHIPAMOUNT = dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(@INSTALLMENTID);
                                else if @REVENUETYPECODE = 3 --Pay other

                                    set @MEMBERSHIPAMOUNT = @PAYOTHERAMOUNT;
                                else --Pay in full

                                    set @MEMBERSHIPAMOUNT = dbo.UFN_PLEDGE_GETBALANCE(@PLEDGEID);

                                set @MEMBERSHIPTRANSACTIONAMOUNT = @MEMBERSHIPAMOUNT;

                                set @TOTALAMOUNT = @MEMBERSHIPAMOUNT +
                                                            case @PAYADDITIONALTONEXTINSTALLMENT
                                                                when 1 then coalesce(@PAYADDITIONALMONEYAMOUNT, 0)
                                                                else 0
                                                            end;

                                -- If the program is set to 'No, nothing is tax deductible then it should be zero.

                                declare @TAXDEDUCTIBLETYPE tinyint = (select MEMBERSHIPPROGRAM.DEDUCTIBILITYCODE from dbo.MEMBERSHIPPROGRAM where ID = @MEMBERSHIPPROGRAMID);
                                if @TAXDEDUCTIBLETYPE = 2
                                begin
                                    set @TAXDEDUCTIBLEAMOUNT = 0;
                                end
                                else if @TAXDEDUCTIBLEAMOUNT is null
                                begin
                                    set @TAXDEDUCTIBLEAMOUNT = @MEMBERSHIPAMOUNT;
                                end;

                                update dbo.REVENUESCHEDULE set 
                                    REVENUESCHEDULE.ISPENDING = 1,
                                    REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
                                    REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
                                where REVENUESCHEDULE.ID = @PLEDGEID;

                                select @PLEDGEFREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE, 
                                             @PLEDGENUMBEROFINSTALLMENTS = REVENUESCHEDULE.NUMBEROFINSTALLMENTS,
                                             @PLEDGESTARTDATE = REVENUESCHEDULE.STARTDATE
                                from dbo.REVENUESCHEDULE
                                where REVENUESCHEDULE.ID = @PLEDGEID;

                            end

                        if @RECURRINGGIFTID is not null
                            begin
                                if @MEMBERSHIPAMOUNT = 0
                                    select @MEMBERSHIPAMOUNT = NEXTINSTALLMENT.BALANCE
                                    from dbo.FINANCIALTRANSACTION
                                    cross apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(FINANCIALTRANSACTION.ID,null) NEXTINSTALLMENT
                                    where FINANCIALTRANSACTION.ID = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(@EXISTINGMEMBERSHIPID)

                                if @MEMBERSHIPAMOUNT is null or @MEMBERSHIPAMOUNT = 0
                                    select
                                        @MEMBERSHIPAMOUNT = AMOUNT
                                    from dbo.MEMBERSHIPLEVELTERM
                                    where ID = @MEMBERSHIPLEVELTERMID
                                    and LEVELID = @MEMBERSHIPLEVELID;

                                set @MEMBERSHIPTRANSACTIONAMOUNT = @MEMBERSHIPAMOUNT;
                                set @TOTALAMOUNT = @MEMBERSHIPAMOUNT + case @PAYADDITIONALTONEXTINSTALLMENT
                                                                                                                 when 1 then coalesce(@PAYADDITIONALMONEYAMOUNT, 0)
                                                                                                                 else 0
                                                                                                             end;
                            end

                    end

                exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_PAYPLEDGEDMEMBERSHIP
                    @MEMBERSHIPTRANSACTIONTYPECODE,
                    @DATE,
                    @MEMBERSHIPEXPIRESONDATE,
                    @MEMBERSHIPPROGRAMID, 
                    @MEMBERSHIPLEVELID,
                    @MEMBERSHIPLEVELTERMID,
                    @CONSTITUENTID,
                    @REVENUETYPECODE,
                    @EXISTINGMEMBERSHIPID,
                    @MEMBERSHIPPROGRAMADDON,
                    @EXISTINGMEMBERS,
                    @MEMBERSHIPCARDS,
                    @EXISTINGCHILDREN,
                    @NUMBEROFCHILDREN;        -- Temporary workaround for children not being implemented in 2012 Q1

            end

        if @IMPORT = 1 and @ADDDONATION = 1
            begin
                set @TOTALAMOUNT = @TOTALAMOUNT + coalesce(@DONATIONAMOUNT, 0);
                set @TAXDEDUCTIBLEAMOUNT = @TAXDEDUCTIBLEAMOUNT + coalesce(@DONATIONAMOUNT, 0);
            end

        -- Use the partial card number if set.  This field is used for import

        -- and doesn't have any CC processing code run for it.

        if coalesce(@PARTIALCREDITCARDNUMBER, '') <> ''
            set @CREDITCARDNUMBER = @PARTIALCREDITCARDNUMBER

        -- Validate that if one credit card field is entered, the required fields are set.

        -- Server code should generally have validated this already unless the code is being

        -- added through import.

        if @PAYMENTMETHODCODE = 2     
        begin
            if len(coalesce(@CREDITCARDNUMBER, '')) > 4
            begin
                if coalesce(@CARDHOLDERNAME, '') = ''
                    raiserror('BBERR_CARDHOLDERREQUIRED', 13, 1)

                if coalesce(@EXPIRESON, '00000000') = '00000000'
                    raiserror('BBERR_EXPIRESONREQUIRED', 13, 1)
            end      
        end

        if @PAYMENTMETHODCODE = 2
            begin
                exec dbo.USP_CREDITCARD_SAVE
                    @ID = @CREDITCARDID output,
                    @CREDITCARDTOKEN = @CREDITCARDTOKEN,
                    @CARDHOLDERNAME = @CARDHOLDERNAME,
                    @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
                    @CREDITTYPECODEID = @CREDITTYPECODEID,
                    @EXPIRESON = @EXPIRESON,
                    @CHANGEAGENTID = @CHANGEAGENTID,
                    @CURRENTDATE = @CURRENTDATE;
            end

        if @EXCHANGERATE is null
            set @EXCHANGERATE = 0;

        exec dbo.USP_REVENUEBATCH_SEPAMANDATE_VALIDATEORCREATE
            @SEPAMANDATEID output,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @ADDSEPAMANDATE,
            @SEPAMANDATECUSTOMIDENTIFIER,
            @SEPAMANDATESIGNATUREDATE,
            @SEPAMANDATETYPECODE,
            @PAYMENTMETHODCODE,
            @CONSTITUENTACCOUNTID,
            null, --FINANCIALINSTITUTIONID

            @TRANSACTIONCURRENCYID;

        insert into dbo.BATCHMEMBERSHIPDUES
            (
                ID,
                BATCHID,
                SEQUENCE,
                BILLTOCONSTITUENTID,
                DUESTYPECODE,
                MEMBERSHIPRECIPIENTID,
                RENEWALRECIPIENTCODE,
                FINDERNUMBER,
                APPEALID,
                EFFORTID,
                CHANNELCODEID,
                DATE,
                MEMBERSHIPTRANSACTIONTYPECODE,
                REVENUETYPECODE,
                PAYADDITIONALTONEXTINSTALLMENT,
                PAYADDITIONALMONEYAMOUNT,
                MEMBERSHIPPROGRAMID,
                MEMBERSHIPLEVELID,
                MEMBERSHIPLEVELTERMID,
                MEMBERSHIPEXPIRESONDATE,
                MEMBERSHIPAMOUNT,
                MEMBERSHIPTRANSACTIONAMOUNT,
                EXISTINGMEMBERSHIPID,
                MEMBERSHIPPLEDGEAMOUNT,
                CONTRIBUTORYDESIGNATIONID,
                USEDISCOUNT,
                MEMBERSHIPPROMOID,
                PROMOTIONCODE,
                APPLIEDDISCOUNTID,
                ADDDONATION,
                DONATIONAMOUNT,
                DONATIONGIVENANONYMOUSLY,
                DONATIONOPPORTUNITYID,
                DONATIONSINGLEDESIGNATIONID,
                DONATIONCATEGORYCODEID,
                DONATIONDECLINESGIFTAID,
                PLEDGEFREQUENCYCODE,
                PLEDGENUMBEROFINSTALLMENTS,
                PLEDGESTARTDATE,
                AUTOPAY,
                SENDPLEDGEREMINDER,
                CHECKDATE,
                CHECKNUMBER,
                REFERENCENUMBER,
                REFERENCEDATE,

                DIRECTDEBITRESULTCODE,
                DIRECTDEBITISREJECTED,

                REFERENCE,
                CONSTITUENTACCOUNTID,
                PAYMENTMETHODCODE,
                AUTOMATICALLYRENEWMEMBERSHIP,

                CREDITCARDID,
                AUTHORIZATIONCODE,
                REJECTIONMESSAGE,
                TRANSACTIONID,

                DONOTACKNOWLEDGE,
                LETTERCODEID,
                TAXDEDUCTIBLEAMOUNT,
                TRIBUTEID,
                DONOTRECEIPT,
                COMMENTS,
                TOTALAMOUNT,

                PDACCOUNTSYSTEMID,
                BASECURRENCYID,
                TRANSACTIONCURRENCYID,
                BASEEXCHANGERATEID,
                EXCHANGERATE,

                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
                NUMBEROFCHILDREN,
                OTHERPAYMENTMETHODCODEID,
                POSTSTATUSCODE,
                POSTDATE,
                SOURCECODE,
                MEMBERSHIPDECLINESGIFTAID,
                DDISOURCECODEID,
                DDISOURCEDATE,
                VENDORID,
                MEMBERSHIPLEVELTYPECODEID,
                CREDITCARDATTEMPTCOUNT,
                NAMECODE, 
                SIMILARADDRESSCODE,
                UNSIMILARADDRESSCODE, 
                NEWADDRESSENDDATECODE, 
                NEWADDRESSPRIMARYCODE, 
                BIRTHDATERULECODE, 
                DIFFERENTPHONECODE,
                NEWPHONEENDDATECODE, 
                NEWPHONEPRIMARYCODE, 
                DIFFERENTEMAILCODE, 
                NEWEMAILENDDATECODE, 
                NEWEMAILPRIMARYCODE, 
                USEGLOBALSETTINGS, 
                CREATEHISTORICALNAMECODE,
                SEPAMANDATEID,
                REQUIRECREDITCARDPROCESSING,
                ISGENERATEDPAYMENT,
                GLREVENUECATEGORYMAPPINGID
            )
            values 
            (
                @ID,
                @BATCHID,
                @SEQUENCE,
                @BILLTOCONSTITUENTID,

                @DUESTYPECODE,                     -- Paying, giving, comping

                @MEMBERSHIPRECIPIENT,
                @RENEWALRECIPIENT,
                @FINDERNUMBER,
                @APPEALID,
                @EFFORTID,
                @CHANNELCODEID,
                @DATE,
                @MEMBERSHIPTRANSACTIONTYPECODE,
                @REVENUETYPECODE,
                @PAYADDITIONALTONEXTINSTALLMENT,
                @PAYADDITIONALMONEYAMOUNT,
                @MEMBERSHIPPROGRAMID,
                @MEMBERSHIPLEVELID,
                @MEMBERSHIPLEVELTERMID,
                @MEMBERSHIPEXPIRESONDATE,
                @MEMBERSHIPAMOUNT,
                @MEMBERSHIPTRANSACTIONAMOUNT,
                @EXISTINGMEMBERSHIPID,
                @MEMBERSHIPPLEDGEAMOUNT,
                @CONTRIBUTORYDESIGNATIONID,
                cast(@USEDISCOUNTRADIO as bit),
                @DISCOUNTTYPE,                     -- MembershipPromoID

                @PROMOTIONCODE,
                @APPLIEDDISCOUNTID,
                @ADDDONATION,
                @DONATIONAMOUNT,
                @GIVENANONYMOUSLY,
                @DONATIONOPPORTUNITYID,
                @DONATIONDESIGNATIONID,            -- SingleDesignationID

                @DONATIONCATEGORYCODEID,
                @DECLINESGIFTAID,
                @PLEDGEFREQUENCYCODE,
                @PLEDGENUMBEROFINSTALLMENTS,
                @PLEDGESTARTDATE,
                @AUTOPAY,
                @SENDPLEDGEREMINDER,
                @CHECKDATE,
                @CHECKNUMBER,
                @REFERENCENUMBER,
                @REFERENCEDATE,

                @DIRECTDEBITRESULTCODE,
                @DIRECTDEBITISREJECTED,

                @REFERENCE,
                @CONSTITUENTACCOUNTID,
                @PAYMENTMETHODCODE,
                @AUTOMATICALLYRENEWMEMBERSHIP,

                @CREDITCARDID,
                @AUTHORIZATIONCODE,
                @REJECTIONMESSAGE,
                @TRANSACTIONID,

                @DONOTACKNOWLEDGE,
                @LETTERCODEID,
                @TAXDEDUCTIBLEAMOUNT,
                @TRIBUTEID,
                @DONOTRECEIPT,
                @COMMENTS,
                @TOTALAMOUNT,

                @PDACCOUNTSYSTEMID,
                @BASECURRENCYID,
                @TRANSACTIONCURRENCYID,
                @BASEEXCHANGERATEID,
                @EXCHANGERATE,

                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                @NUMBEROFCHILDREN,
                @OTHERPAYMENTMETHODCODEID,
                @POSTSTATUSCODE,
                @POSTDATE,
                @SOURCECODE,
                @MEMBERSHIPDECLINESGIFTAID,
                @DDISOURCECODEID,
                @DDISOURCEDATE,
                isnull(@VENDORID, ''),
                @MEMBERSHIPLEVELTYPECODEID,
                @CREDITCARDATTEMPTCOUNT,
                @NAMECODE, 
                @SIMILARADDRESSCODE, 
                @UNSIMILARADDRESSCODE, 
                @NEWADDRESSENDDATECODE, 
                @NEWADDRESSPRIMARYCODE, 
                @BIRTHDATERULECODE, 
                @DIFFERENTPHONECODE,
                @NEWPHONEENDDATECODE, 
                @NEWPHONEPRIMARYCODE, 
                @DIFFERENTEMAILCODE, 
                @NEWEMAILENDDATECODE, 
                @NEWEMAILPRIMARYCODE, 
                @USEGLOBALSETTINGS, 
                @CREATEHISTORICALNAMECODE,
                @SEPAMANDATEID,
                @REQUIRECREDITCARDPROCESSING,
                @ISGENERATEDPAYMENT,
                @GLREVENUECATEGORYMAPPINGID
            );

        if @BBNCTRANID > 0 
        begin
                insert into dbo.BBNCDOWNLOADEDTRANSACTION ([ID])
                values (@BBNCID);

                insert into dbo.BATCHMEMBERSHIPDUESBBNCINFO
                (
                    BATCHMEMBERSHIPDUESID, BBNCTRANID, PAGEID, PAGENAME, BBNCID, NETCOMMUNITYTRANSACTIONPROCESSORID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                values
                (
                    @ID, @BBNCTRANID, @ORIGINPAGEID, @ORIGINPAGE, @BBNCID, @BBISPROCESSORID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                )

                delete from dbo.BBNCUNSUCCESSFULDOWNLOADTRANSACTION where ID = @BBNCID;
        end

--  if @DONATIONDESIGNATIONID is null

--    exec dbo.USP_MEMBERSHIPDUESBATCH_GETDONATIONSPLITS_ADDFROMXML @ID, @DONATIONSPLITS, @CHANGEAGENTID, @CURRENTDATE; 


        -- Insert into child tables

        if @BENEFITS is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETBENEFITS_ADDFROMXML @ID, @BENEFITS, @CHANGEAGENTID, @CURRENTDATE;

        if @PERCENTAGEBENEFITS is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETPERCENTAGEBENEFITS_ADDFROMXML @ID, @PERCENTAGEBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

        if @EXISTINGMEMBERS is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERS_UPDATEFROMXML @ID, @EXISTINGMEMBERS, @CHANGEAGENTID, @CURRENTDATE;

        if @EXISTINGCHILDREN is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETCHILDREN_UPDATEFROMXML @ID, @EXISTINGCHILDREN, @CHANGEAGENTID, @CURRENTDATE;

        if @MEMBERSHIPCARDS is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERSHIPCARDS_ADDFROMXML @ID, @MEMBERSHIPCARDS, @CHANGEAGENTID, @CURRENTDATE;

        if @MEMBERSHIPPROGRAMADDON is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERSHIPPROGRAMADDONS_UPDATEFROMXML @ID, @MEMBERSHIPPROGRAMADDON, @CHANGEAGENTID, @CURRENTDATE;

        if @MEMBERSHIPRECOGNITION is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETMEMBERSHIPRECOGNITIONS_ADDFROMXML @ID, @MEMBERSHIPRECOGNITION, @CHANGEAGENTID, @CURRENTDATE;

        -- TODO: Should I only do this is paying with pledge...?

        --       Or will this be null if not paying with pledge...?

        if @INSTALLMENTS is not null
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETPLEDGEINSTALLMENTS_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;

        if @SOLICITCODES is not null
        begin
            exec dbo.USP_MEMBERSHIPDUESBATCH_GETSOLICITCODES_ADDFROMXML_DEFAULTID @ID, @SOLICITCODES, @CHANGEAGENTID, @CURRENTDATE;
        end

        -- add solicit codes for matched constituent

        if @IMPORT = 1 or @BBNCTRANID > 0
        begin
            -- When importing, load the constituent's existing solicit codes first

            insert into [dbo].[BATCHMEMBERSHIPDUESSOLICITCODE]
            (
                [ID],
                [BATCHMEMBERSHIPDUESID],
                [SOLICITCODEID],
                [CONSTITUENTSOLICITCODEID],
                [STARTDATE],
                [ENDDATE],
                [COMMENTS],
                [SEQUENCE],
                [CONSENTPREFERENCECODE],
                [SOURCECODEID],
                [SOURCEFILEPATH],
                [PRIVACYPOLICYFILEPATH],
                [SUPPORTINGINFORMATION],
                [CONSENTSTATEMENT],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
            )
            select
                newid() ID,
                @ID [BATCHMEMBERSHIPDUESID],
                CONSTITUENTSOLICITCODE.[SOLICITCODEID],
                CONSTITUENTSOLICITCODE.[ID] [CONSTITUENTSOLICITCODEID],
                CONSTITUENTSOLICITCODE.[STARTDATE],
                CONSTITUENTSOLICITCODE.[ENDDATE],
                CONSTITUENTSOLICITCODE.[COMMENTS],
                CONSTITUENTSOLICITCODE.[SEQUENCE],
                CONSTITUENTSOLICITCODE.[CONSENTPREFERENCECODE],
                CONSTITUENTSOLICITCODE.[SOURCECODEID],
                CONSTITUENTSOLICITCODE.[SOURCEFILEPATH],
                CONSTITUENTSOLICITCODE.[PRIVACYPOLICYFILEPATH],
                CONSTITUENTSOLICITCODE.[SUPPORTINGINFORMATION],
                CONSTITUENTSOLICITCODE.[CONSENTSTATEMENT],
                CONSTITUENTSOLICITCODE.[ADDEDBYID],
                CONSTITUENTSOLICITCODE.[CHANGEDBYID],
                CONSTITUENTSOLICITCODE.[DATEADDED],
                CONSTITUENTSOLICITCODE.[DATECHANGED]
            from dbo.CONSTITUENTSOLICITCODE
                left join BATCHMEMBERSHIPDUESSOLICITCODE on CONSTITUENTSOLICITCODE.ID = BATCHMEMBERSHIPDUESSOLICITCODE.CONSTITUENTSOLICITCODEID and BATCHMEMBERSHIPDUESID = @ID
            where
                CONSTITUENTSOLICITCODE.[CONSTITUENTID] = @BILLTOCONSTITUENTID and 
                BATCHMEMBERSHIPDUESSOLICITCODE.ID is null;
        end

        select @SOLICITCODES = dbo.UFN_MEMBERSHIPDUESBATCH_GETSOLICITCODES_TOITEMLISTXML(@ID);

        if @SOLICITCODES is not null
        begin
            -- address the auto end date issues for consent based solicit codes

            exec dbo.USP_MEMBERSHIPDUESBATCH_ADJUSTSOLICITCODEDATERANGES @BILLTOCONSTITUENTID, @SOLICITCODES, @CHANGEAGENTID;
        end

        if @ADDDONATION = 1
            begin
                if @CAMPAIGNS is not null
                    exec dbo.USP_MEMBERSHIPDUESBATCH_GETDONATIONCAMPAIGNS_ADDFROMXML @ID, @CAMPAIGNS, @CHANGEAGENTID, @CURRENTDATE;

                if @RECOGNITIONS is not null
                    exec dbo.USP_MEMBERSHIPDUESBATCH_GETDONATIONRECOGNITIONS_ADDFROMXML @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

                if @SOLICITORS is not null
                    exec dbo.USP_MEMBERSHIPDUESBATCH_GETDONATIONSOLICITORS_ADDFROMXML @ID, @SOLICITORS, @CHANGEAGENTID, @CURRENTDATE;

            end

            --this is a constituent update coming from import or BBIS - business rules are applied as well

        if @NEWCONSTITUENT.exist('NEWCONSTITUENT/ITEM') = 1 and exists (select ID from dbo.CONSTITUENT where ID = @BILLTOCONSTITUENTID)          
            begin
                exec USP_REVENUEBATCH_EDITCONSTITUENTFROMXML_1
                            @NEWCONSTITUENT, 
                            @BILLTOCONSTITUENTID, 
                            @BATCHID, 
                            @ID, 
                            @CHANGEAGENTID, 
                            @CURRENTAPPUSERID, 
                            @NAMECODE,
                            @SIMILARADDRESSCODE,
                            @UNSIMILARADDRESSCODE,
                            @NEWADDRESSPRIMARYCODE,
                            @BIRTHDATERULECODE,
                            @DIFFERENTPHONECODE,
                            @NEWPHONEPRIMARYCODE,
                            @DIFFERENTEMAILCODE,
                            @NEWEMAILPRIMARYCODE;              

                    if exists (select ID from dbo.BATCHREVENUECONSTITUENT where ID = @BILLTOCONSTITUENTID)
                    --constituent was resolved by DUPLICATE check

                    --delete the temporary batch version of the constituent

                    exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID = @BILLTOCONSTITUENTID, @CHANGEAGENTID = @CHANGEAGENTID              
            end              

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end