USP_DATAFORMTEMPLATE_ADD_BATCHMEMBERSHIPDUESBATCHROWCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHMEMBERSHIPDUESBATCHROWCOMMIT
(
  @ID uniqueidentifier = null output,
  @VALIDATEONLY bit = 0,
  @BATCHNUMBER nvarchar(100),
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CHANGEAGENTID uniqueidentifier = null,
  @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,
  @REVENUETYPECODE tinyint = 0,
  @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) = '',
  @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) = '',
  @REFERENCENUMBER nvarchar(20) = '',
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @PAYMENTMETHODCODE tinyint = 1,

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

  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @REFERENCE nvarchar(255) = '',
  @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 = 0,
  @LETTERCODEID uniqueidentifier = null,
  @TRIBUTEID uniqueidentifier = null,
  --@NEWEVENTREGISTRATION bit = 0,
  @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 = 0,
  @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,
  @CURRENTBATCHROWID uniqueidentifier = null,
  @BBNCTRANID int = 0,
  @ORIGINPAGEID int = 0,
  @ORIGINPAGE nvarchar(100) = '',

  @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;

  declare @CURRENTDATE datetime = getdate();

  declare @RECURRINGPROGRAMTYPECODE tinyint = 1;
  declare @MEMBERSHIPPROGRAMTYPECODE tinyint;

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

  begin try
    /*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 @REVENUETYPECODE = 3
    set @MEMBERSHIPTRANSACTIONAMOUNT = @PAYOTHERAMOUNT

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

    --  Write Validation logic here.
    if @PDACCOUNTSYSTEMID is null
      set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

    --Set currency parameters for backwards compatibility
    if @TRANSACTIONCURRENCYID is null
      set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    -- 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
      )
      raiserror('TRANSACTIONCURRENCYINVALIDFORACCOUNTSYSTEM',13,1)

    if exists (
      select * from dbo.MEMBERSHIPTRANSACTION
      where
        MEMBERSHIPID = @EXISTINGMEMBERSHIPID and
        cast(TRANSACTIONDATE as date) > @DATE
      )
      raiserror('Transaction date cannot be earlier than the last transaction of the membership.', 13,1);

    declare @BATCHID uniqueidentifier;
    select
      @BATCHID = [BATCH].[ID]
    from dbo.[BATCH]
    inner join dbo.[BATCHMEMBERSHIPDUES] on [BATCH].[ID] = [BATCHMEMBERSHIPDUES].[BATCHID]
    where [BATCHMEMBERSHIPDUES].[ID] = @CURRENTBATCHROWID;

    if @BBNCTRANID > 0
    begin
      declare @TEMPSOURCECODE nvarchar(50) = @SOURCECODE;
      declare @TEMPMAILINGID uniqueidentifier = @EFFORTID;
      declare @TEMPAPPEALID uniqueidentifier = @APPEALID;
      declare @TEMPCONSTITUENTID uniqueidentifier = @BILLTOCONSTITUENTID;

      exec dbo.[USP_REVENUEBATCH_GETMARKETINGDATA]
        @FINDERNUMBER = @FINDERNUMBER,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @SOURCECODE = @TEMPSOURCECODE output,
        @MAILINGID = @TEMPMAILINGID output,
        @APPEALID = @TEMPAPPEALID output,
        @CONSTITUENTID = @TEMPCONSTITUENTID output,
        @BATCHID = @BATCHID,
        @IMPORT = @IMPORT;

      if @TEMPCONSTITUENTID <> @BILLTOCONSTITUENTID
        set @FINDERNUMBER = 0;
    end

    /* Validate all marketing data and dependent fields */
    exec dbo.[USP_REVENUEBATCH_VALIDATEMARKETINGDATA]
      @FINDERNUMBER = @FINDERNUMBER,
      @SOURCECODE = @SOURCECODE,
      @MAILINGID = @EFFORTID,
      @APPEALID = @APPEALID,
      @CONSTITUENTID = @BILLTOCONSTITUENTID,
      @BATCHID = @BATCHID;

    if @PAYMENTMETHODCODE = 10 and @OTHERPAYMENTMETHODCODEID is null --Other
    begin
      raiserror('BBERR_OTHERPAYMENTMETHOD', 13, 1);
    end

    declare @CONSTITUENTID uniqueidentifier;

    declare @BILLTOCONSTITUENTEXISTS bit = 0
    if exists (select ID from dbo.CONSTITUENT where ID = @BILLTOCONSTITUENTID)
      set @BILLTOCONSTITUENTEXISTS = 1

    declare @MEMBERSHIPRECIPIENTEXISTS bit = 0
    if exists (select ID from dbo.CONSTITUENT where ID = @MEMBERSHIPRECIPIENT)
      set @MEMBERSHIPRECIPIENTEXISTS = 1

    declare @BATCHOWNERID uniqueidentifier
    select
      @BATCHOWNERID = APPUSERID
    from dbo.BATCH
    inner join dbo.BATCHMEMBERSHIPDUES on BATCH.ID = BATCHMEMBERSHIPDUES.BATCHID
    where BATCHMEMBERSHIPDUES.ID = @CURRENTBATCHROWID

    -- Validate constituent fields if the constituent hasn't been created yet
    if @BILLTOCONSTITUENTEXISTS = 0
      exec dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT
        @REVENUEBATCHCONSTITUENTID = @BILLTOCONSTITUENTID,
        @ISDONOR = 1,
        @BATCHROWID = @CURRENTBATCHROWID,
        @BATCHOWNERID = @BATCHOWNERID

    if @MEMBERSHIPRECIPIENTEXISTS = 0 and @MEMBERSHIPRECIPIENT is not null
      exec dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT
        @REVENUEBATCHCONSTITUENTID = @MEMBERSHIPRECIPIENT,
        @ISDONOR = 1,
        @BATCHROWID = @CURRENTBATCHROWID,
        @BATCHOWNERID = @BATCHOWNERID

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

    if @VALIDATEONLY = 1
      begin
        --When validating the fields below could be updated by automatch, we have to re-pull them from the row since the values sent in by the validate could be out of date
        if @CURRENTBATCHROWID is not null
        begin
        select 
            @MEMBERSHIPTRANSACTIONTYPECODE = MEMBERSHIPTRANSACTIONTYPECODE,
            @MEMBERSHIPEXPIRESONDATE = MEMBERSHIPEXPIRESONDATE,
            @EXISTINGMEMBERSHIPID = EXISTINGMEMBERSHIPID 
          from dbo.BATCHMEMBERSHIPDUES 
          where BATCHMEMBERSHIPDUES.ID = @CURRENTBATCHROWID
        end
    end

    if @BBNCTRANID > 0
    begin
      if @EXISTINGMEMBERSHIPID is not null
      begin
        -- Adjust benefits
        declare @REMOVEBENEFITID uniqueidentifier
        declare benefits cursor fast_forward for
        select BENEFITID
        from dbo.MEMBERSHIPLEVELBENEFIT
        where MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID and FREQUENCYCODE = 1

        open benefits
        fetch next from benefits into @REMOVEBENEFITID

        while @@FETCH_STATUS = 0
        begin
          if @BENEFITS is not null
            set @BENEFITS.modify('delete /BENEFITS/ITEM[upper-case(BENEFITID[1]) = upper-case(sql:variable("@REMOVEBENEFITID"))]');

          if @PERCENTAGEBENEFITS is not null
            set @PERCENTAGEBENEFITS.modify('delete /PERCENTAGEBENEFITS/ITEM[upper-case(BENEFITID[1]) = upper-case(sql:variable("@REMOVEBENEFITID"))]');

          fetch next from benefits into @REMOVEBENEFITID
        end

        close benefits
        deallocate benefits
      end
    end

    if @DIRECTDEBITISREJECTED is null
      set @DIRECTDEBITISREJECTED = 0;

    if @DIRECTDEBITISREJECTED = 1
      raiserror('BBERR_DIRECTDEBIT_REJECTED', 13, 1);

        -- Adding or renewing or upgrading
        if (@MEMBERSHIPTRANSACTIONTYPECODE = 0 or @MEMBERSHIPTRANSACTIONTYPECODE = 1 or @MEMBERSHIPTRANSACTIONTYPECODE = 3)
    begin

      declare @COMMITONLYCHECK bit = ~@VALIDATEONLY

      exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_ADDRENEWPROGRAM 
        @CURRENTAPPUSERID,
        null,
        null,
        @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,
        @COMMITONLYCHECK 


      -- Creating an installment plan
      if @REVENUETYPECODE > 0 and @MEMBERSHIPPROGRAMTYPECODE <> @RECURRINGPROGRAMTYPECODE
        exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_PLEDGE @DATE, @MEMBERSHIPPLEDGEAMOUNT, @PLEDGEFREQUENCYCODE, @PLEDGESTARTDATE, @PLEDGENUMBEROFINSTALLMENTS, @INSTALLMENTS;

    end
    else
      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

         declare @PLEDGEID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETPLEDGE(@EXISTINGMEMBERSHIPID);
         update dbo.REVENUESCHEDULE
            set ISPENDING = 0,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE
         from dbo.REVENUESCHEDULE
         where REVENUESCHEDULE.ID = @PLEDGEID;



      -- only check if there is actually a payment.
      -- We don't care if you're only creating a installment plan/recurring...if you include an additional donation or addon(recurring gift only) you could still be charging a card.
      if @PAYMENTMETHODCODE = 2 and  @TOTALAMOUNT > 0
      begin
        if @VALIDATEONLY = 0
        begin
            --If attempting to pay by credit card  with the intent to process (token is not null), ensure an authorization code exists.
            --This prevents a batch of credit card payments from being committed without the process running. 
            if @CREDITCARDTOKEN is not null and len(@AUTHORIZATIONCODE) = 0
            begin
              raiserror('BBERR_CREDITCARD_NOTPROCESSED', 13, 1);
            end
        end

        --If REQUIRECREDITCARDPROCESSING is true and we don't have a token then the only way we want this batch to commit is if the user enters an authorization code.
        if @CREDITCARDTOKEN is null and len(@AUTHORIZATIONCODE) = 0 and @REQUIRECREDITCARDPROCESSING = 1 
        begin
            raiserror('BBERR_CREDITCARD_MISSINGTOKEN', 13, 1);
        end
      end

    if @VALIDATEONLY = 1
      exec dbo.USP_MEMBERSHIPDUESBATCH_VALIDATE_SOLICITCODES @SOLICITCODES;

    if @VALIDATEONLY = 0
    begin

      -- Temporarily de-XML some collections
      declare @TEMP_EXISTINGMEMBERS table
      (
        CONSTITUENTID uniqueidentifier,
        RELATIONTOPRIMARY nvarchar(100),
        LOOKUPID nvarchar(50)
      )
      insert into @TEMP_EXISTINGMEMBERS
      select
        T.c.value('(CONSTITUENTID)[1]','uniqueidentifier'),
        T.c.value('(RELATIONTOPRIMARY)[1]','nvarchar(100)'),
        T.c.value('(LOOKUPID)[1]','nvarchar(50)')
      from @EXISTINGMEMBERS.nodes('/EXISTINGMEMBERS/ITEM') T(c)

      declare @TEMP_MEMBERSHIPCARDS table
      (
        CONSTITUENTID uniqueidentifier,
        NAMEONCARD nvarchar(100),
        EXPIRATIONDATE date
      )
      insert into @TEMP_MEMBERSHIPCARDS
      select
        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)

      declare @IDMAPPING xml = null
      declare @BATCHCONSTITID uniqueidentifier
      declare @RECOGNITIONSTOPASS xml

      if @BILLTOCONSTITUENTEXISTS = 0
      begin
        set @BATCHCONSTITID = @BILLTOCONSTITUENTID;

        if exists (select 1 from dbo.UFN_REVENUEBATCH_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONS) for xml raw('ITEM'), type, elements, root('RECOGNITIONS'), binary base64)
          set @RECOGNITIONSTOPASS = @RECOGNITIONS
        else
          set @RECOGNITIONSTOPASS = null

        -- Add new constituent
        exec dbo.USP_REVENUEBATCH_CONSTITUENT_ADD
          @BILLTOCONSTITUENTID output,
          @CHANGEAGENTID,
          @BATCHCONSTITID,
          @CONSTITUENTACCOUNTID,
          @CONSTITUENTACCOUNTID output,
          @CURRENTRECOGNITIONS = @RECOGNITIONSTOPASS,
          @UPDATEDRECOGNITIONS = @RECOGNITIONS output,
          @UPDATEDAPPLICATIONRECOGNITIONS = @RECOGNITIONS output,
          @IDMAPPING = @IDMAPPING output;

        if @IDMAPPING is not null
        begin
          update @TEMP_EXISTINGMEMBERS
          set CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
          from @IDMAPPING.nodes('/IDMAPPING/ITEM') T(c)
          where CONSTITUENTID = T.c.value('(REVENUEBATCHCONSTITUENTID)[1]','uniqueidentifier')

          update @TEMP_MEMBERSHIPCARDS
          set CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
          from @IDMAPPING.nodes('/IDMAPPING/ITEM') T(c)
          where CONSTITUENTID = T.c.value('(REVENUEBATCHCONSTITUENTID)[1]','uniqueidentifier')
        end

        if @FINDERNUMBER > 0
          exec dbo.USP_REVENUEBATCH_CONSTITUENTAPPEAL_ADD
            @CONSTITUENTID,
            @CHANGEAGENTID,
            @BATCHCONSTITID,
            @FINDERNUMBER;

        --delete the temporary batch version of the constituent
        exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE
          @BATCHREVENUECONSTITUENTID = @BATCHCONSTITID,
          @CHANGEAGENTID = @CHANGEAGENTID
      end
      else
      begin
        -- Apply edits to an existing constituent
        if exists (select 'x' from dbo.BATCHCONSTITUENTUPDATE where ID = @CURRENTBATCHROWID)
        begin
          declare
            @CONSTITUENTTYPECODE tinyint,
            @LASTNAME nvarchar(100),
            @ORGANIZATIONNAME nvarchar(100),
            @FIRSTNAME nvarchar(50),
            @MIDDLENAME nvarchar(50),
            @MAIDENNAME nvarchar(100),
            @NICKNAME nvarchar(50),
            @TITLECODEID uniqueidentifier,
            @SUFFIXCODEID uniqueidentifier,
            @GENDERCODE tinyint,
            @BIRTHDATE dbo.UDT_FUZZYDATE,
            @GIVESANONYMOUSLY bit,
            @WEBADDRESS dbo.UDT_WEBADDRESS,
            @ADDRESSES xml,
            @PHONES xml,
            @EMAILADDRESSES xml,
            @GROUP_DESCRIPTION nvarchar(300),
            @GROUP_GROUPTYPEID uniqueidentifier,
            @GROUP_STARTDATE datetime,
            @INDUSTRYCODEID uniqueidentifier,
            @NUMEMPLOYEES int,
            @NUMSUBSIDIARIES int,
            @PARENTCORPID uniqueidentifier,
            @MARITALSTATUSCODEID uniqueidentifier,
            @LOOKUPID nvarchar(100),
            @ALTERNATELOOKUPIDS xml,
            @SECURITYATTRIBUTES xml,
            @DECEASED bit,
            @DECEASEDDATE dbo.UDT_FUZZYDATE,
            @INTERESTS xml,
            @PROSPECTMANAGERFUNDRAISERID uniqueidentifier,
            @CONSTITUENCIES xml,
            @CONSTITUENT_SITES xml,
            -- BBIS fields 
            @BBISCONSTITUENT_SITES xml,
            @BBISSECURITYATTRIBUTES xml,
            @BBISCONSTITUENCIES xml,
            --spouse and business fields
            @SPOUSEID uniqueidentifier,
            @SPOUSE_LASTNAME nvarchar(100),
            @SPOUSE_FIRSTNAME nvarchar(50),
            @SPOUSE_MIDDLENAME nvarchar(50),
            @SPOUSE_MAIDENNAME nvarchar(100),
            @SPOUSE_NICKNAME nvarchar(50),
            @SPOUSE_TITLECODEID uniqueidentifier,
            @SPOUSE_SUFFIXCODEID uniqueidentifier,
            @SPOUSE_GENDERCODE tinyint,
            @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
            @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,
            @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
            @BUSINESSID uniqueidentifier,
            @BUSINESS_ADDRESSBLOCK nvarchar(150),
            @BUSINESS_ADDRESSTYPECODEID uniqueidentifier,
            @BUSINESS_CART nvarchar(10),
            @BUSINESS_CITY nvarchar(50),
            @BUSINESS_COUNTRYID uniqueidentifier,
            @BUSINESS_DONOTMAIL bit,
            @BUSINESS_DONOTMAILREASONCODEID uniqueidentifier,
            @BUSINESS_DPC nvarchar(8),            
            @BUSINESS_LOT nvarchar(5),
            @BUSINESS_NAME nvarchar(100),           
            @BUSINESS_NUMBER nvarchar(100),
            @BUSINESS_PHONETYPECODEID uniqueidentifier,
            @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier,
            @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier,
            @BUSINESS_STATEID uniqueidentifier,
            @BUSINESS_POSTCODE nvarchar(12),
            @REMOVESPOUSE bit,
            @SPOUSE_LOOKUPID nvarchar(100),
            @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
            @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
            @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
            @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
            @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
            @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
            @REQUESTSNOEMAIL bit,
            @GENDERCODEID uniqueidentifier,
            @SPOUSE_GENDERCODEID uniqueidentifier

          exec dbo.USP_EDITLOAD_BATCHCONSTITUENTUPDATEBATCHROW 
            @CURRENTBATCHROWID,
            null, --@DATALOADED output,
            null, --@TSLONG output,
            null, --@SEQUENCE
            null, --@PRIMARYRECORDID 
            @CONSTITUENTTYPECODE output, 
            @BIRTHDATE output,
            @FIRSTNAME output, 
            @GENDERCODE output, 
            @GIVESANONYMOUSLY output, 
            @LASTNAME output, 
            null, --@LOOKUP_ID
            @MAIDENNAME output, 
            @MIDDLENAME output,
            @NICKNAME output, 
            @SUFFIXCODEID output, 
            @TITLECODEID output, 
            @WEBADDRESS output, 
            null, --@ALTERNATELOOKUPIDS
            @BBISSECURITYATTRIBUTES output,
            null, --@DECEASED
            null, --@DECEASEDDATE
            null, --@CONSTITUENT_SITEID
            null, --@INTERESTS
            null, --@PROSPECTMANAGERFUNDRAISERID
            @ADDRESSES output,
            @PHONES output, 
            @EMAILADDRESSES output, 
            @BBISCONSTITUENCIES output, 
            @GROUP_GROUPTYPEID output, 
            @GROUP_DESCRIPTION output, 
            @GROUP_STARTDATE output,
            @INDUSTRYCODEID output, 
            @NUMEMPLOYEES output, 
            @NUMSUBSIDIARIES output, 
            @PARENTCORPID output, 
            @MARITALSTATUSCODEID output,
            @SPOUSEID output,
            @SPOUSE_TITLECODEID output,
            @SPOUSE_FIRSTNAME output,
            @SPOUSE_NICKNAME output,
            @SPOUSE_MIDDLENAME output,
            @SPOUSE_MAIDENNAME output,
            @SPOUSE_LASTNAME output,
            @SPOUSE_SUFFIXCODEID output,
            @SPOUSE_BIRTHDATE output,
            @SPOUSE_GENDERCODE output,
            @SPOUSE_LOOKUPID output,
            @SPOUSE_RELATIONSHIPTYPECODEID output,
            @SPOUSE_RECIPROCALTYPECODEID output,
            @BUSINESSID output,
            @BUSINESS_ADDRESSBLOCK output,
            @BUSINESS_ADDRESSTYPECODEID output,
            @BUSINESS_CART output,
            @BUSINESS_CITY output,
            @BUSINESS_COUNTRYID output,
            @BUSINESS_DONOTMAIL output,
            @BUSINESS_DONOTMAILREASONCODEID output,
            @BUSINESS_DPC output,
            null, --@BUSINESS_EMAILADDRESS
            null, --@BUSINESS_EMAILADDRESSTYPECODEID
            null, --@BUSINESS_INDUSTRYCODEID
            null, --@BUSINESS_LOOKUPID
            @BUSINESS_LOT output,
            @BUSINESS_NAME output,
            null, --@BUSINESS_NUMEMPLOYEES
            null, --@BUSINESS_NUMSUBSIDIARIES
            null, --@BUSINESS_PARENTCORPID
            @BUSINESS_NUMBER output,
            null, --@BUSINESS_PHONE_COUNTRYID
            @BUSINESS_PHONETYPECODEID output,
            @BUSINESS_RELATIONSHIPTYPECODEID output,
            @BUSINESS_RECIPROCALTYPECODEID output,
            @BUSINESS_STATEID output,
            null, --@BUSINESS_WEBADDRESS
            @BUSINESS_POSTCODE output,
            null, --@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST
            null, --@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST
            null, --@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST
            null, --@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST
            null, --@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS
            null, --@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR
            null, --@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS
            null, --@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR
            null, --@BUSINESS_PRIMARYRECOGNITIONTYPECODEID
            null, --@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID
            null, --@BUSINESS_EMAILADDRESSSTARTDATE
            null, --@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS
            null, --@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR
            null, --@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS
            null, --@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR
            null, --@SPOUSE_PRIMARYRECOGNITIONTYPECODEID
            null, --@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID
            @BBISCONSTITUENT_SITES output, 
            null, --@SPOUSE_ALTERNATELOOKUPIDS
            null, --@SPOUSE_IMPORTLOOKUPID
            null, --@ROWFROMBATCHUI
            null, --@BBNCTRANID
            null, --@PAGEID
            null, --@PAGENAME
            null, --@BBNCUSERID
            @REMOVESPOUSE output,
            null, --@DUPLICATERECORDID
            null, --@SOCIALMEDIAACCOUNTS
            null, --@DOMANUALREVIEWFORAUTOMATCH
            null, --@RELATIONSHIPS
            null, --@NETCOMMUNITYTRANSACTIONPROCESSORID
            null, --@BBNCID
            null, --@NAMECODE
            null, --@SIMILARADDRESSCODE
            null, --@UNSIMILARADDRESSCODE 
            null, --@NEWADDRESSENDDATECODE 
            null, --@NEWADDRESSPRIMARYCODE 
            null, --@BIRTHDATERULECODE 
            null, --@DIFFERENTPHONECODE 
            null, --@NEWPHONEENDDATECODE 
            null, --@NEWPHONEPRIMARYCODE 
            null, --@DIFFERENTEMAILCODE 
            null, --@NEWEMAILENDDATECODE 
            null, --@NEWEMAILPRIMARYCODE 
            null, --@USEGLOBALSETTINGS
            null, --@CREATEHISTORICALNAMECODE
            null, --@NAMEFORMATS
            null, --@SUBMITTEDLOOKUPID
            null, --@SUBMITTEDCLASSYEAR
            null, --@SUBMITTEDEDUCATIONALINSTITUTION
            @REQUESTSNOEMAIL output,
            null, --@ORIGINAL_KEYNAME
            null, --@ORIGINAL_FIRSTNAME
            @SOLICITCODES output,
            @GENDERCODEID output,
            @SPOUSE_GENDERCODEID output;

          -- Pull in fields not handled by the ERB constituent edit form so they don't get overwritten by CUB commit.
          exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTUPDATEBATCHTEMPLATE
            @CONSTITUENTID, 
            @LOOKUPID = @LOOKUPID output,
            @ALTERNATELOOKUPIDS = @ALTERNATELOOKUPIDS output,
            @SECURITYATTRIBUTES = @SECURITYATTRIBUTES output,
            @DECEASED = @DECEASED output,
            @DECEASEDDATE = @DECEASEDDATE output,
            @INTERESTS = @INTERESTS output,
            @PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID output,
            @CONSTITUENCIES = @CONSTITUENCIES output,
            @CONSTITUENT_SITES = @CONSTITUENT_SITES output,
            @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS =@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
            @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR=@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR output,
            @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS=@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
            @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR=@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR output,
            @SPOUSE_PRIMARYRECOGNITIONTYPECODEID=@SPOUSE_PRIMARYRECOGNITIONTYPECODEID output,
            @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID=@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID output


        -- add the bbis values to the constituency, sited and security attributes collections 
        set @CONSTITUENT_SITES = (select * from 
            (select SITEID                   
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES) 
            union 
            select SITEID
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@BBISCONSTITUENT_SITES)                   
            where SITEID not in (select SITEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES))) A
          for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
          )  

        set @CONSTITUENT_SITES = (select * from 
            (select SITEID                   
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES) 
            union 
            select SITEID
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@BBISCONSTITUENT_SITES)                   
            where SITEID not in (select SITEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES))) A
          for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
          )  

        set @SECURITYATTRIBUTES = (select * from 
            (select CONSTIT_SECURITY_ATTRIBUTEID                   
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_FROMITEMLISTXML(@SECURITYATTRIBUTES) 
            union 
            select CONSTIT_SECURITY_ATTRIBUTEID
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_FROMITEMLISTXML(@BBISSECURITYATTRIBUTES)                   
            where CONSTIT_SECURITY_ATTRIBUTEID not in (select CONSTIT_SECURITY_ATTRIBUTEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_FROMITEMLISTXML(@SECURITYATTRIBUTES))) A
          for xml raw('ITEM'),type,elements,root('SECURITYATTRIBUTES'),BINARY BASE64
          )  

        set @CONSTITUENCIES = (select * from 
            (select CONSTITUENCYCODEID,
                    DATEFROM,
                    DATETO,                 
                    ORIGINALCONSTITUENCYID
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) 
            union 
            select CONSTITUENCYCODEID,
                    DATEFROM,
                    DATETO,                 
                    ORIGINALCONSTITUENCYID
            from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@BBISCONSTITUENCIES)                   
            where CONSTITUENCYCODEID not in (select CONSTITUENCYCODEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES))) A
          for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64
          )

          exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHTEMPLATE_15
            @ID = @CONSTITUENTID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @VALIDATEONLY = @VALIDATEONLY,
            @CONSTITUENTTYPECODE = @CONSTITUENTTYPECODE,
            @BIRTHDATE = @BIRTHDATE,
            @FIRSTNAME = @FIRSTNAME,
            @GENDERCODE = @GENDERCODE,
            @GIVESANONYMOUSLY = @GIVESANONYMOUSLY,
            @KEYNAME = @LASTNAME,
            @LOOKUPID = @LOOKUPID,
            @MAIDENNAME = @MAIDENNAME,
            @MIDDLENAME = @MIDDLENAME,
            @NICKNAME = @NICKNAME,
            @SUFFIXCODEID = @SUFFIXCODEID,
            @TITLECODEID = @TITLECODEID,
            @WEBADDRESS = @WEBADDRESS,
            @ALTERNATELOOKUPIDS = @ALTERNATELOOKUPIDS,
            @SECURITYATTRIBUTES = @SECURITYATTRIBUTES,
            @DECEASED = @DECEASED,
            @DECEASEDDATE = @DECEASEDDATE,
            @CONSTITUENT_SITEID = null,
            @INTERESTS = @INTERESTS,
            @PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
            @ADDRESSES = @ADDRESSES,
            @EMAILADDRESSES = @EMAILADDRESSES,
            @PHONES = @PHONES,
            @CONSTITUENCIES = @CONSTITUENCIES,
            @GROUPTYPEID = @GROUP_GROUPTYPEID,
            @GROUPDESCRIPTION = @GROUP_DESCRIPTION,
            @GROUPSTARTDATE = @GROUP_STARTDATE,
            @ORG_INDUSTRYCODEID = @INDUSTRYCODEID,
            @ORG_NUMEMPLOYEES = @NUMEMPLOYEES,
            @ORG_NUMSUBSIDIARIES = @NUMSUBSIDIARIES,
            @ORG_PARENTCORPID = @PARENTCORPID,
            @MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
            @SPOUSE_ID = @SPOUSEID,
            @SPOUSE_BIRTHDATE = @SPOUSE_BIRTHDATE,
            @SPOUSE_FIRSTNAME = @SPOUSE_FIRSTNAME,
            @SPOUSE_GENDERCODE = @SPOUSE_GENDERCODE,
            @SPOUSE_LASTNAME = @SPOUSE_LASTNAME,
            @SPOUSE_LOOKUPID = @SPOUSE_LOOKUPID,
            @SPOUSE_MAIDENNAME = @SPOUSE_MAIDENNAME,
            @SPOUSE_MIDDLENAME = @SPOUSE_MIDDLENAME,
            @SPOUSE_NICKNAME = @SPOUSE_NICKNAME,
            @SPOUSE_SUFFIXCODEID = @SPOUSE_SUFFIXCODEID,
            @SPOUSE_TITLECODEID = @SPOUSE_TITLECODEID,
            @SPOUSE_RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID,
            @SPOUSE_RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID,
            @BUSINESS_ADDRESSBLOCK = @BUSINESS_ADDRESSBLOCK,
            @BUSINESS_ADDRESSTYPECODEID = @BUSINESS_ADDRESSTYPECODEID,
            @BUSINESS_CART = @BUSINESS_CART,
            @BUSINESS_CITY = @BUSINESS_CITY,
            @BUSINESS_COUNTRYID = @BUSINESS_COUNTRYID,
            @BUSINESS_DONOTMAIL = @BUSINESS_DONOTMAIL,
            @BUSINESS_DONOTMAILREASONCODEID = @BUSINESS_DONOTMAILREASONCODEID,
            @BUSINESS_DPC = @BUSINESS_DPC,
            @BUSINESS_EMAILADDRESS = null,
            @BUSINESS_EMAILADDRESSTYPECODEID = null,
            @BUSINESS_INDUSTRYCODEID = null,
            @BUSINESS_LOOKUPID = null,
            @BUSINESS_LOT = @BUSINESS_LOT,
            @BUSINESS_ID = @BUSINESSID,
            @BUSINESS_NAME = @BUSINESS_NAME,
            @BUSINESS_NUMEMPLOYEES = null,
            @BUSINESS_NUMSUBSIDIARIES = null,
            @BUSINESS_PARENTCORPID = null,
            @BUSINESS_PHONENUMBER = @BUSINESS_NUMBER,
            @BUSINESS_PHONE_COUNTRYID = null,
            @BUSINESS_PHONETYPECODEID = @BUSINESS_PHONETYPECODEID,
            @BUSINESS_RELATIONSHIPTYPECODEID = @BUSINESS_RELATIONSHIPTYPECODEID,
            @BUSINESS_RECIPROCALTYPECODEID = @BUSINESS_RECIPROCALTYPECODEID,
            @BUSINESS_STATEID = @BUSINESS_STATEID,
            @BUSINESS_WEBADDRESS = null,
            @BUSINESS_POSTCODE = @BUSINESS_POSTCODE,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST = @SPOUSE_RELATIONSHIPTYPECODEID,
            @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST = @SPOUSE_RECIPROCALTYPECODEID,
            @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = @BUSINESS_RELATIONSHIPTYPECODEID,
            @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = @BUSINESS_RECIPROCALTYPECODEID,
            @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = null,
            @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = null,
            @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = null,
            @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = null,
            @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = null,
            @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = null,
            @BUSINESS_EMAILADDRESSSTARTDATE = null,
            @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
            @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
            @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
            @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR,
            @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = @SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
            @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
            @CONSTITUENT_SITES = @CONSTITUENT_SITES,
            @BATCHOWNERID = @BATCHOWNERID,
            @BATCHROWID = null,
            @SPOUSE_ALTERNATELOOKUPIDS = null,
            @SPOUSE_IMPORTLOOKUPID = null,
            @ROWFROMBATCHUI = null,
            @BBNCTRANID = null,
            @REMOVESPOUSE = @REMOVESPOUSE,
            @SOCIALMEDIAACCOUNTS = null,
            @RELATIONSHIPS = null,
            @NETCOMMUNITYTRANSACTIONPROCESSORID = null,
            @NEWADDRESSENDDATECODE = @NEWADDRESSENDDATECODE,
            @CREATEHISTORICALNAMECODE = @CREATEHISTORICALNAMECODE,
            @NEWPHONEENDDATECODE = @NEWPHONEENDDATECODE,
            @NEWEMAILENDDATECODE = @NEWEMAILENDDATECODE,
            @NAMEFORMATS = null,
            @REQUESTSNOEMAIL = @REQUESTSNOEMAIL,
            @SOLICITCODES = @SOLICITCODES,
            @GENDERCODEID = @GENDERCODEID,
            @SPOUSE_GENDERCODEID = @SPOUSE_GENDERCODEID

        end
      end      

      if (
          select
            count(ID)
          from
            dbo.CONSTITUENTACCOUNT
          where
            CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID
         ) = 0
      begin
          exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
              @BATCHREVENUECONSTITUENTID = @BILLTOCONSTITUENTID, 
              @CONSTITUENTID = @BILLTOCONSTITUENTID, 
              @CHANGEAGENTID = @CHANGEAGENTID,
              @BATCHREVENUECONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output;
      end

      if @MEMBERSHIPRECIPIENTEXISTS = 0 and @MEMBERSHIPRECIPIENT is not null
      begin
        set @BATCHCONSTITID = @MEMBERSHIPRECIPIENT;

        if exists (select 1 from dbo.UFN_REVENUEBATCH_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONS) for xml raw('ITEM'), type, elements, root('RECOGNITIONS'), binary base64)
          set @RECOGNITIONSTOPASS = @RECOGNITIONS
        else
          set @RECOGNITIONSTOPASS = null

        -- Add new constituent
        exec dbo.USP_REVENUEBATCH_CONSTITUENT_ADD
          @MEMBERSHIPRECIPIENT output,
          @CHANGEAGENTID,
          @BATCHCONSTITID,
          @CONSTITUENTACCOUNTID,
          @CONSTITUENTACCOUNTID output,
          @CURRENTRECOGNITIONS = @RECOGNITIONSTOPASS,
          @UPDATEDRECOGNITIONS = @RECOGNITIONS output,
          @UPDATEDAPPLICATIONRECOGNITIONS = @RECOGNITIONS output,
          @IDMAPPING = @IDMAPPING output;

        if @IDMAPPING is not null
        begin
          update @TEMP_EXISTINGMEMBERS
          set CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
          from @IDMAPPING.nodes('/IDMAPPING/ITEM') T(c)
          where CONSTITUENTID = T.c.value('(REVENUEBATCHCONSTITUENTID)[1]','uniqueidentifier')

          update @TEMP_MEMBERSHIPCARDS
          set CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
          from @IDMAPPING.nodes('/IDMAPPING/ITEM') T(c)
          where CONSTITUENTID = T.c.value('(REVENUEBATCHCONSTITUENTID)[1]','uniqueidentifier')
        end

        if @FINDERNUMBER > 0
          exec dbo.USP_REVENUEBATCH_CONSTITUENTAPPEAL_ADD
            @MEMBERSHIPRECIPIENT,
            @CHANGEAGENTID,
            @BATCHCONSTITID,
            @FINDERNUMBER;

        --delete the temporary batch version of the constituent
          exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE
            @BATCHREVENUECONSTITUENTID = @BATCHCONSTITID,
            @CHANGEAGENTID = @CHANGEAGENTID
      end

      declare @NEWMEMBERCONSTITUENTID uniqueidentifier

      declare members cursor fast_forward
      for
        select
          CONSTITUENTID
        from
          @TEMP_EXISTINGMEMBERS
        where
          CONSTITUENTID is not null and
          CONSTITUENTID <> @BILLTOCONSTITUENTID

      open members
      fetch next from members into @BATCHCONSTITID
      while @@FETCH_STATUS = 0
      begin

        set @NEWMEMBERCONSTITUENTID = null

        if not exists (select ID from dbo.CONSTITUENT where ID = @BATCHCONSTITID)
        begin
          exec dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT
            @REVENUEBATCHCONSTITUENTID = @BATCHCONSTITID,
            @ISDONOR = 1,
            @BATCHROWID = @CURRENTBATCHROWID,
            @BATCHOWNERID = @BATCHOWNERID

          if exists (select 1 from dbo.UFN_REVENUEBATCH_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONS) for xml raw('ITEM'), type, elements, root('RECOGNITIONS'), binary base64)
            set @RECOGNITIONSTOPASS = @RECOGNITIONS
          else
            set @RECOGNITIONSTOPASS = null

          set @IDMAPPING = null

          -- Add new constituent
          exec dbo.USP_REVENUEBATCH_CONSTITUENT_ADD
            @NEWMEMBERCONSTITUENTID output,
            @CHANGEAGENTID,
            @BATCHCONSTITID,
            @CONSTITUENTACCOUNTID,
            @CONSTITUENTACCOUNTID output,
            @CURRENTRECOGNITIONS = @RECOGNITIONSTOPASS,
            @UPDATEDRECOGNITIONS = @RECOGNITIONS output,
            @UPDATEDAPPLICATIONRECOGNITIONS = @RECOGNITIONS output,
            @IDMAPPING = @IDMAPPING output;

          if @IDMAPPING is not null
          begin
            update @TEMP_EXISTINGMEMBERS
            set CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
            from @IDMAPPING.nodes('/IDMAPPING/ITEM') T(c)
            where CONSTITUENTID = T.c.value('(REVENUEBATCHCONSTITUENTID)[1]','uniqueidentifier')

            update @TEMP_MEMBERSHIPCARDS
            set CONSTITUENTID = T.c.value('(CONSTITUENTID)[1]','uniqueidentifier')
            from @IDMAPPING.nodes('/IDMAPPING/ITEM') T(c)
            where CONSTITUENTID = T.c.value('(REVENUEBATCHCONSTITUENTID)[1]','uniqueidentifier')
          end

          --delete the temporary batch version of the constituent
          exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE
            @BATCHREVENUECONSTITUENTID = @BATCHCONSTITID,
            @CHANGEAGENTID = @CHANGEAGENTID
        end

        fetch next from members into @BATCHCONSTITID
      end
      close members
      deallocate members

      -- Update xml collections
      set @EXISTINGMEMBERS =
      (
        select CONSTITUENTID, RELATIONTOPRIMARY, LOOKUPID
        from @TEMP_EXISTINGMEMBERS
        for xml raw('ITEM'), type, elements, root('EXISTINGMEMBERS'), BINARY BASE64
      )

      set @MEMBERSHIPCARDS =
      (
        select CONSTITUENTID, NAMEONCARD, EXPIRATIONDATE
        from @TEMP_MEMBERSHIPCARDS
        for xml raw('ITEM'), type, elements, root('MEMBERSHIPCARDS'), BINARY BASE64
      )

      exec dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPDUES 
        @ID output, 
        @CURRENTAPPUSERID, 
        @CHANGEAGENTID, 
        @TRANSACTIONCURRENCYID,
        @BASECURRENCYID, 
        @BASEEXCHANGERATEID, 
        @EXCHANGERATE, 
        @PDACCOUNTSYSTEMID, 
        @POSTSTATUSCODE,
        @POSTDATE, 
        @DUESTYPECODE, 
        @MEMBERSHIPRECIPIENT, 
        @RENEWALRECIPIENT,
        @BILLTOCONSTITUENTID, 
        @EFFORTID, 
        @FINDERNUMBER, 
        @APPEALID, 
        @CHANNELCODEID, 
        @DATE, 
        @MEMBERSHIPTRANSACTIONTYPECODE,
        @REVENUETYPECODE,
        0,        -- @PAYREVENUETYPE which is never used in the save proc
        @PAYADDITIONALTONEXTINSTALLMENT, 
        @PAYADDITIONALMONEYAMOUNT,
        @MEMBERSHIPPROGRAMID, 
        @MEMBERSHIPLEVELID, 
        @MEMBERSHIPLEVELTERMID, 
        @MEMBERSHIPEXPIRESONDATE,
        @MEMBERSHIPTRANSACTIONAMOUNT, 
    @MEMBERSHIPRECOGNITION, 
        @CONTRIBUTORYDESIGNATIONID, 
        @EXISTINGMEMBERSHIPID, 
        @MEMBERSHIPPLEDGEAMOUNT,
        @EXISTINGMEMBERS, 
        @EXISTINGCHILDREN, 
        @MEMBERSHIPCARDS, 
        @USEDISCOUNTRADIO, 
        @DISCOUNTTYPE, 
        @PROMOTIONCODE,
        @APPLIEDDISCOUNTID, 
        @MEMBERSHIPPROGRAMADDON, 
        @ADDDONATION, 
        @DONATIONAMOUNT, 
        @GIVENANONYMOUSLY,
        @DONATIONOPPORTUNITYID, 
        @DONATIONDESIGNATIONID,
        '',       -- @DONATIONCAMPAIGNSLIST,
        '',       -- @DONATIONSOLICITORSLIST,
        '',       -- @DONATIONRECOGNITIONSLIST,
        @CAMPAIGNS, 
        @SOLICITORS, 
        @DONATIONCATEGORYCODEID, 
        @RECOGNITIONS,
        @DECLINESGIFTAID, 
        @PLEDGEFREQUENCYCODE, 
        @PLEDGENUMBEROFINSTALLMENTS, 
        @PLEDGESTARTDATE, 
        @INSTALLMENTS,
        @SENDPLEDGEREMINDER, 
        @CHECKDATE, 
        @CHECKNUMBER, 
        @REFERENCENUMBER, 
        @REFERENCEDATE, 
        @PAYMENTMETHODCODE, 
        @DIRECTDEBITRESULTCODE,
        @CONSTITUENTACCOUNTID, 
        @REFERENCE, 
        @AUTOMATICALLYRENEWMEMBERSHIP, 
        @CREDITCARDNUMBER, 
        @CREDITCARDTOKEN,
        @CARDHOLDERNAME, 
        @EXPIRESON,
        0,        -- @AUTHORIZECREDITCARD,
        @AUTHORIZATIONCODE,
        '',       -- @CSC
        @CREDITTYPECODEID,
        null,     -- @MERCHANTACCOUNTID
        @TRANSACTIONID, --CREDITCARDTRANSACTIONID
        @AUTOPAY, 
        @DONOTACKNOWLEDGE, 
        @TAXDEDUCTIBLEAMOUNT, 
        @LETTERCODEID, 
        @TRIBUTEID,
        0,        -- @NEWEVENTREGISTRATION,
        @TOTALAMOUNT, 
        @DONOTRECEIPT, 
        @BENEFITS, 
        @PERCENTAGEBENEFITS, 
        @COMMENTS, 
        @BATCHNUMBER,
        @NUMBEROFCHILDREN,
        @OTHERPAYMENTMETHODCODEID,
        @SOURCECODE, 
        0,   -- Temporary workaround for children not being implemented in 2012 Q1
        @MEMBERSHIPDECLINESGIFTAID, 
        @DDISOURCECODEID, 
        @DDISOURCEDATE, 
        @VENDORID, 
        @MEMBERSHIPLEVELTYPECODEID, 
        @BBNCTRANID, 
        @ORIGINPAGE, 
        @ORIGINPAGEID,
        @SEPAMANDATEID,
        @CURRENTBATCHROWID,
        @GLREVENUECATEGORYMAPPINGID,
        @SOLICITCODES;

        if @BBNCTRANID > 0
          exec spTransactions_MarkMembershipAsProcessed @BBNCTRANID;
    end

  end try

  begin catch
    if CURSOR_STATUS('global','members') >= -1
    begin
      close members
      deallocate members
    end

    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end