USP_REVENUEBATCH_EDITCONSTITUENTFROMXML_1

create procedure dbo.USP_REVENUEBATCH_EDITCONSTITUENTFROMXML_1(
    @NEWCONSTITUENT xml,
    @CONSTITUENTID uniqueidentifier,
    @BATCHID uniqueidentifier, 
    @BATCHROWID uniqueidentifier, 
    @CHANGEAGENTID uniqueidentifier, 
    @CURRENTAPPUSERID uniqueidentifier,
    @NAMECODE tinyint = 1,
    @SIMILARADDRESSCODE tinyint = 3,
    @UNSIMILARADDRESSCODE tinyint = 3,
    @NEWADDRESSPRIMARYCODE tinyint = 1,
    @BIRTHDATERULECODE tinyint = 1,
    @DIFFERENTPHONECODE tinyint = 3,
    @NEWPHONEPRIMARYCODE tinyint = 1,
    @DIFFERENTEMAILCODE tinyint = 3,
    @NEWEMAILPRIMARYCODE tinyint = 1      
    )
      as
      begin                
        declare @ISORGANIZATION bit
        declare @ISGROUP bit
        declare @ISHOUSEHOLD bit
        declare @LASTNAME nvarchar(100)
        declare @FIRSTNAME nvarchar(50)
        declare @MIDDLENAME nvarchar(50)
        declare @MAIDENNAME nvarchar(100)
        declare @NICKNAME nvarchar(50)
        declare @TITLECODEID uniqueidentifier
        declare @SUFFIXCODEID uniqueidentifier
        declare @GENDERCODE tinyint
        declare @BIRTHDATE dbo.UDT_FUZZYDATE
        declare @ADDRESS_ADDRESSTYPECODEID uniqueidentifier
        declare @ADDRESS_DONOTMAIL bit
        declare @ADDRESS_COUNTRYID uniqueidentifier
        declare @ADDRESS_STATEID uniqueidentifier
        declare @ADDRESS_ADDRESSBLOCK nvarchar(150)
        declare @ADDRESS_CITY nvarchar(50)
        declare @ADDRESS_POSTCODE nvarchar(12)
        declare @PHONE_PHONETYPECODEID uniqueidentifier
        declare @PHONE_NUMBER nvarchar(100)
        declare @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier
        declare @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS
        declare @MARITALSTATUSCODEID uniqueidentifier
        declare @WEBADDRESS dbo.UDT_WEBADDRESS
        declare @INDUSTRYCODEID uniqueidentifier
        declare @NUMEMPLOYEES int
        declare @NUMSUBSIDIARIES int
        declare @PARENTCORPID uniqueidentifier
        declare @SPOUSEID uniqueidentifier
        declare @EXISTINGSPOUSE bit
        declare @SPOUSE_LASTNAME nvarchar(100)
        declare @SPOUSE_FIRSTNAME nvarchar(50)
        declare @SPOUSE_MIDDLENAME nvarchar(50)
        declare @SPOUSE_MAIDENNAME nvarchar(100)
        declare @SPOUSE_NICKNAME nvarchar(50)
        declare @SPOUSE_TITLECODEID uniqueidentifier
        declare @SPOUSE_SUFFIXCODEID uniqueidentifier
        declare @SPOUSE_GENDERCODE tinyint
        declare @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE
        declare @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier
        declare @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier
        declare @SPOUSE_ISSPOUSE bit
        declare @COPYPRIMARYINFORMATION bit
        declare @COPYPRIMARYINFORMATIONFORHOUSEHOLD bit
        declare @SPOUSE_STARTDATE datetime
        declare @BUSINESSID uniqueidentifier
        declare @EXISTINGBUSINESS bit
        declare @BUSINESS_NAME nvarchar(100)
        declare @BUSINESS_ADDRESSTYPECODEID uniqueidentifier
        declare @BUSINESS_COUNTRYID uniqueidentifier
        declare @BUSINESS_STATEID uniqueidentifier
        declare @BUSINESS_ADDRESSBLOCK nvarchar(150)
        declare @BUSINESS_CITY nvarchar(50)
        declare @BUSINESS_POSTCODE nvarchar(12)
        declare @BUSINESS_DONOTMAIL bit
        declare @BUSINESS_PHONETYPECODEID uniqueidentifier
        declare @BUSINESS_NUMBER nvarchar(100)
        declare @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier
        declare @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier
        declare @BUSINESS_STARTDATE datetime
        declare @ISCONTACT bit
        declare @ISPRIMARYCONTACT bit
        declare @CONTACTTYPECODEID uniqueidentifier
        declare @POSITION nvarchar(50)
        declare @ISMATCHINGGIFTRELATIONSHIP bit
        declare @GIVESANONYMOUSLY bit
        declare @GROUP_DESCRIPTION nvarchar(300)
        declare @GROUP_PRIMARYCONTACT uniqueidentifier
        declare @GROUP_STARTDATE datetime
        declare @GROUP_GROUPTYPEID uniqueidentifier
        declare @HOUSEHOLD_PRIMARYCONTACTID uniqueidentifier
        declare @HOUSEHOLD_PRIMARYCONTACT_KEYNAME nvarchar(100)
        declare @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME nvarchar(50)
        declare @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME nvarchar(50)
        declare @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID uniqueidentifier
        declare @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID uniqueidentifier
        declare @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT bit
        declare @HOUSEHOLD_SECONDMEMBERID uniqueidentifier
        declare @HOUSEHOLD_SECONDMEMBER_KEYNAME nvarchar(100)
        declare @HOUSEHOLD_SECONDMEMBER_FIRSTNAME nvarchar(50)
        declare @HOUSEHOLD_SECONDMEMBER_MIDDLENAME nvarchar(50)
        declare @HOUSEHOLD_SECONDMEMBER_TITLECODEID uniqueidentifier
        declare @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID uniqueidentifier
        declare @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT bit
        declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID uniqueidentifier
        declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID uniqueidentifier
        declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE datetime
        declare @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE bit
        declare @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
        declare @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
        declare @BUSINESS_PRIMARYRELATIONSHIPEXISTS bit
        declare @BUSINESS_RECIPROCALRELATIONSHIPEXISTS bit
        declare @BUSINESS_PRIMARYMATCHFACTOR decimal(5,2)
        declare @BUSINESS_RECIPROCALMATCHFACTOR decimal(5,2)
        declare @SITEID uniqueidentifier
        declare @CONSTITUENTSECURITYGROUPID uniqueidentifier
        declare @ADDRESSID uniqueidentifier
        declare @EMAILADDRESSID uniqueidentifier
        declare @PHONEID uniqueidentifier
        declare @CONSTITUENCYCODEID uniqueidentifier
        declare @INFOSOURCECODEID uniqueidentifier
        declare @SPOUSE_CLASSOF dbo.UDT_YEAR
        declare @NAMEFORMATS xml
        declare @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier
        declare @ORIGINAL_KEYNAME nvarchar(100)
        declare @ORIGINAL_FIRSTNAME nvarchar(50)
        declare @SPOUSE_LOOKUPID nvarchar(100)
        declare @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit
        declare @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2)
        declare @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier
        declare @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit
        declare @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) 
        declare @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
        declare @GENDERCODEID uniqueidentifier = null
        declare @SPOUSE_GENDERCODEID uniqueidentifier = null

        SELECT top 1                                             
          @ISORGANIZATION=coalesce(T.c.value('(ISORGANIZATION)[1]','bit'), 0),                                        
          @ISGROUP=coalesce(T.c.value('(ISGROUP)[1]','bit'), 0),                                        
          @ISHOUSEHOLD=coalesce(T.c.value('(ISHOUSEHOLD)[1]','bit'), 0),
          @LASTNAME=coalesce(T.c.value('(LASTNAME)[1]','nvarchar(100)'), ''),
          @FIRSTNAME=coalesce(T.c.value('(FIRSTNAME)[1]','nvarchar(50)'), ''),
          @MIDDLENAME=coalesce(T.c.value('(MIDDLENAME)[1]','nvarchar(50)'), ''),
          @MAIDENNAME=coalesce(T.c.value('(MAIDENNAME)[1]','nvarchar(100)'), ''),
          @NICKNAME=coalesce(T.c.value('(NICKNAME)[1]','nvarchar(50)'), ''),
          @TITLECODEID=T.c.value('(TITLECODEID)[1]','uniqueidentifier'),
          @SUFFIXCODEID=T.c.value('(SUFFIXCODEID)[1]','uniqueidentifier'),
          @GENDERCODE=0,
          @BIRTHDATE=coalesce(T.c.value('(BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000'),
          @ADDRESS_ADDRESSTYPECODEID=T.c.value('(ADDRESS_ADDRESSTYPECODEID)[1]','uniqueidentifier'),
          @ADDRESS_DONOTMAIL=coalesce(T.c.value('(ADDRESS_DONOTMAIL)[1]','bit'), 0),
          @ADDRESS_COUNTRYID=T.c.value('(ADDRESS_COUNTRYID)[1]','uniqueidentifier'),
          @ADDRESS_STATEID=T.c.value('(ADDRESS_STATEID)[1]','uniqueidentifier'),
          @ADDRESS_ADDRESSBLOCK=coalesce(T.c.value('(ADDRESS_ADDRESSBLOCK)[1]','nvarchar(150)'), ''),
          @ADDRESS_CITY=coalesce(T.c.value('(ADDRESS_CITY)[1]','nvarchar(50)'), ''),
          @ADDRESS_POSTCODE=coalesce(T.c.value('(ADDRESS_POSTCODE)[1]','nvarchar(12)'), ''),
          @PHONE_PHONETYPECODEID=T.c.value('(PHONE_PHONETYPECODEID)[1]','uniqueidentifier'),
          @PHONE_NUMBER=coalesce(T.c.value('(PHONE_NUMBER)[1]','nvarchar(100)'), ''),
          @EMAILADDRESS_EMAILADDRESSTYPECODEID=T.c.value('(EMAILADDRESS_EMAILADDRESSTYPECODEID)[1]','uniqueidentifier'),
          @EMAILADDRESS_EMAILADDRESS=coalesce(T.c.value('(EMAILADDRESS_EMAILADDRESS)[1]','dbo.UDT_EMAILADDRESS'), ''),
          @MARITALSTATUSCODEID=T.c.value('(MARITALSTATUSCODEID)[1]','uniqueidentifier'),
          @WEBADDRESS=coalesce(T.c.value('(WEBADDRESS)[1]','dbo.UDT_WEBADDRESS'), ''),
          @INDUSTRYCODEID=T.c.value('(INDUSTRYCODEID)[1]','uniqueidentifier'),
          @NUMEMPLOYEES=coalesce(T.c.value('(NUMEMPLOYEES)[1]','int'), 0),
          @NUMSUBSIDIARIES=coalesce(T.c.value('(NUMSUBSIDIARIES)[1]','int'), 0),
          @PARENTCORPID=T.c.value('(PARENTCORPID)[1]','uniqueidentifier'),
          @SPOUSEID=T.c.value('(SPOUSEID)[1]','uniqueidentifier'),
          @EXISTINGSPOUSE=coalesce(T.c.value('(EXISTINGSPOUSE)[1]','bit'), 0),
          @SPOUSE_LASTNAME=coalesce(T.c.value('(SPOUSE_LASTNAME)[1]','nvarchar(100)'), ''),
          @SPOUSE_FIRSTNAME=coalesce(T.c.value('(SPOUSE_FIRSTNAME)[1]','nvarchar(50)'), ''),
          @SPOUSE_MIDDLENAME=coalesce(T.c.value('(SPOUSE_MIDDLENAME)[1]','nvarchar(50)'), ''),
          @SPOUSE_MAIDENNAME=coalesce(T.c.value('(SPOUSE_MAIDENNAME)[1]','nvarchar(100)'), ''),
          @SPOUSE_NICKNAME=coalesce(T.c.value('(SPOUSE_NICKNAME)[1]','nvarchar(50)'), ''),
          @SPOUSE_TITLECODEID=T.c.value('(SPOUSE_TITLECODEID)[1]','uniqueidentifier'),
          @SPOUSE_SUFFIXCODEID=T.c.value('(SPOUSE_SUFFIXCODEID)[1]','uniqueidentifier'),
          @SPOUSE_GENDERCODE=0,
          @SPOUSE_BIRTHDATE=coalesce(T.c.value('(SPOUSE_BIRTHDATE)[1]','dbo.UDT_FUZZYDATE'), '00000000'),
          @SPOUSE_RECIPROCALTYPECODEID=coalesce(T.c.value('(SPOUSE_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
          @SPOUSE_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(SPOUSE_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
          @SPOUSE_ISSPOUSE=T.c.value('(SPOUSE_ISSPOUSE)[1]','bit'),
          @SPOUSE_CLASSOF = coalesce(T.c.value('(SPOUSE_CLASSOF)[1]', 'dbo.UDT_YEAR'),0),
          @COPYPRIMARYINFORMATION=coalesce(T.c.value('(COPYPRIMARYINFORMATION)[1]','bit'), 0),
          @COPYPRIMARYINFORMATIONFORHOUSEHOLD=T.c.value('(COPYPRIMARYINFORMATIONFORHOUSEHOLD)[1]','bit'),
          @SPOUSE_STARTDATE=T.c.value('(SPOUSE_STARTDATE)[1]','datetime'),
          @BUSINESSID=T.c.value('(BUSINESSID)[1]','uniqueidentifier'),
          @EXISTINGBUSINESS=coalesce(T.c.value('(EXISTINGBUSINESS)[1]','bit'), 0),
          @BUSINESS_NAME=coalesce(T.c.value('(BUSINESS_NAME)[1]','nvarchar(100)'), ''),
          @BUSINESS_ADDRESSTYPECODEID=T.c.value('(BUSINESS_ADDRESSTYPECODEID)[1]','uniqueidentifier'),
          @BUSINESS_COUNTRYID=T.c.value('(BUSINESS_COUNTRYID)[1]','uniqueidentifier'),
          @BUSINESS_STATEID=T.c.value('(BUSINESS_STATEID)[1]','uniqueidentifier'),
          @BUSINESS_ADDRESSBLOCK=coalesce(T.c.value('(BUSINESS_ADDRESSBLOCK)[1]','nvarchar(150)'), ''),
          @BUSINESS_CITY=coalesce(T.c.value('(BUSINESS_CITY)[1]','nvarchar(50)'), ''),
          @BUSINESS_POSTCODE=coalesce(T.c.value('(BUSINESS_POSTCODE)[1]','nvarchar(12)'), ''),
          @BUSINESS_DONOTMAIL=coalesce(T.c.value('(BUSINESS_DONOTMAIL)[1]','bit'), 0),
          @BUSINESS_PHONETYPECODEID=T.c.value('(BUSINESS_PHONETYPECODEID)[1]','uniqueidentifier'),
          @BUSINESS_NUMBER=coalesce(T.c.value('(BUSINESS_NUMBER)[1]','nvarchar(100)'), ''),
          @BUSINESS_RECIPROCALTYPECODEID=coalesce(T.c.value('(BUSINESS_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
          @BUSINESS_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(BUSINESS_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
          @BUSINESS_STARTDATE=T.c.value('(BUSINESS_STARTDATE)[1]','datetime'),
          @ISCONTACT=coalesce(T.c.value('(ISCONTACT)[1]','bit'), 0),
          @ISPRIMARYCONTACT=coalesce(T.c.value('(ISPRIMARYCONTACT)[1]','bit'), 0),
          @CONTACTTYPECODEID=T.c.value('(CONTACTTYPECODEID)[1]','uniqueidentifier'),
          @POSITION=coalesce(T.c.value('(POSITION)[1]','nvarchar(50)'), ''),
          @ISMATCHINGGIFTRELATIONSHIP=coalesce(T.c.value('(ISMATCHINGGIFTRELATIONSHIP)[1]','bit'), 0),
          --removing this field so we can get it from the USP_DATAFORMTEMPLATE_EDITLOAD_REVENUEBATCHCONSTITUENTINBATCH explicitly below

          --@GIVESANONYMOUSLY=coalesce(T.c.value('(GIVESANONYMOUSLY)[1]','bit'), 0),

          @GROUP_DESCRIPTION=coalesce(T.c.value('(GROUP_DESCRIPTION)[1]','nvarchar(300)'), ''),
          @GROUP_PRIMARYCONTACT=T.c.value('(GROUP_PRIMARYCONTACT)[1]','uniqueidentifier'),
          @GROUP_STARTDATE=T.c.value('(GROUP_STARTDATE)[1]','datetime'),
          @GROUP_GROUPTYPEID=T.c.value('(GROUP_GROUPTYPEID)[1]','uniqueidentifier'),
          @HOUSEHOLD_PRIMARYCONTACTID=T.c.value('(HOUSEHOLD_PRIMARYCONTACTID)[1]','uniqueidentifier'),
          @HOUSEHOLD_PRIMARYCONTACT_KEYNAME=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_KEYNAME)[1]','nvarchar(100)'),
          @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME)[1]','nvarchar(50)'), ''),
          @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME)[1]','nvarchar(50)'), ''),
          @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_TITLECODEID)[1]','uniqueidentifier'),
          @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID=T.c.value('(HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID)[1]','uniqueidentifier'),
          @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT=coalesce(T.c.value('(HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT)[1]','bit'), 0),
          @HOUSEHOLD_SECONDMEMBERID=T.c.value('(HOUSEHOLD_SECONDMEMBERID)[1]','uniqueidentifier'),
          @HOUSEHOLD_SECONDMEMBER_KEYNAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_KEYNAME)[1]','nvarchar(100)'), ''),
          @HOUSEHOLD_SECONDMEMBER_FIRSTNAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_FIRSTNAME)[1]','nvarchar(50)'), ''),
          @HOUSEHOLD_SECONDMEMBER_MIDDLENAME=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_MIDDLENAME)[1]','nvarchar(50)'), ''),
          @HOUSEHOLD_SECONDMEMBER_TITLECODEID=T.c.value('(HOUSEHOLD_SECONDMEMBER_TITLECODEID)[1]','uniqueidentifier'),
          @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID=T.c.value('(HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID)[1]','uniqueidentifier'),
          @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT)[1]','bit'), 0),
          @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID)[1]','uniqueidentifier'),T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
          @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID)[1]','uniqueidentifier'),T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEIDSIMPLELIST)[1]','uniqueidentifier')),
          @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE=T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE)[1]','datetime'),
          @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE=coalesce(T.c.value('(HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE)[1]','bit'), 0),
          @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = T.c.value('(BUSINESS_PRIMARYRECOGNITIONTYPECODEID)[1]','uniqueidentifier'),
          @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = T.c.value('(BUSINESS_RECIPROCALRECOGNITIONTYPECODEID)[1]','uniqueidentifier'),
          @BUSINESS_PRIMARYRELATIONSHIPEXISTS = coalesce(T.c.value('(BUSINESS_PRIMARYRELATIONSHIPEXISTS)[1]','bit'), 0),
          @BUSINESS_RECIPROCALRELATIONSHIPEXISTS = coalesce(T.c.value('(BUSINESS_RECIPROCALRELATIONSHIPEXISTS)[1]','bit'), 0),
          @BUSINESS_PRIMARYMATCHFACTOR = coalesce(T.c.value('(BUSINESS_PRIMARYMATCHFACTOR)[1]','decimal(5,2)'), 100),
          @BUSINESS_RECIPROCALMATCHFACTOR = coalesce(T.c.value('(BUSINESS_RECIPROCALMATCHFACTOR)[1]','decimal(5,2)'), 100),
          @SITEID = T.c.value('(SITEID)[1]','uniqueidentifier'),
          @CONSTITUENTSECURITYGROUPID = T.c.value('(CONSTITUENTSECURITYGROUPID)[1]','uniqueidentifier'),
          @ADDRESSID = T.c.value('(ADDRESSID)[1]','uniqueidentifier'),
          @EMAILADDRESSID = T.c.value('(EMAILADDRESSID)[1]','uniqueidentifier'),
          @PHONEID = T.c.value('(PHONEID)[1]','uniqueidentifier'),
          @CONSTITUENCYCODEID = T.c.value('(CONSTITUENCYCODEID)[1]','uniqueidentifier'),
          @INFOSOURCECODEID = T.c.value('(INFOSOURCECODEID)[1]','uniqueidentifier'),
          @ADDRESS_DONOTMAILREASONCODEID=T.c.value('(ADDRESS_DONOTMAILREASONCODEID)[1]','uniqueidentifier'),
          @ORIGINAL_KEYNAME = coalesce(T.c.value('(ORIGINAL_LASTNAME)[1]','nvarchar(100)'),''),
          @ORIGINAL_FIRSTNAME = coalesce(T.c.value('(ORIGINAL_FIRSTNAME)[1]','nvarchar(50)'),''),
          @GENDERCODEID=T.c.value('(GENDERCODE)[1]','uniqueidentifier'),
          @SPOUSE_GENDERCODEID=T.c.value('(SPOUSE_GENDERCODE)[1]','uniqueidentifier')
        FROM @NEWCONSTITUENT.nodes('/NEWCONSTITUENT/ITEM') T(c)

        select @NAMEFORMATS = @NEWCONSTITUENT.query('/NEWCONSTITUENT/ITEM/NAMEFORMATS')


        if (@WEBADDRESS is not null) and len(@WEBADDRESS) > 0           
          begin
            declare @WEBADDRESSVALID as bit;
            select @WEBADDRESSVALID = dbo.UFN_VALIDWEBADDRESS(@WEBADDRESS);
            if @WEBADDRESSVALID = 0
              begin
                select @WEBADDRESSVALID = dbo.UFN_VALIDWEBADDRESS('http://' + @WEBADDRESS)
                if @WEBADDRESSVALID = 1
                  set @WEBADDRESS = 'http://' + @WEBADDRESS;
              end
          end

        --JamesWill 2009-09-02 WI52852 Default the country if any part of an address is specified but the country is null

        if (@ADDRESS_STATEID is not null or len(@ADDRESS_ADDRESSBLOCK) > 0 or len(@ADDRESS_CITY) > 0 or len(@ADDRESS_POSTCODE) > 0)
          if @ADDRESS_COUNTRYID is null
            select @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT();

        if (@BUSINESS_STATEID is not null or len(@BUSINESS_ADDRESSBLOCK) > 0 or len(@BUSINESS_CITY) > 0 or len(@BUSINESS_POSTCODE) > 0)
          if @BUSINESS_COUNTRYID is null
            select @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT();

            -- the form ID is a concatenated nvarchar using the new constituent ID for the load            

        declare @EDITFORMID nvarchar(110) = cast(@CONSTITUENTID as nvarchar(36))+'|'+cast(@BATCHID as nvarchar(36))+'|'+cast(@BATCHROWID as nvarchar(36))

        -- Bug 603933: Explicitly load these values as historically done by USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENTINBATCH call

        exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUEBATCHCONSTITUENTINBATCH
          @EDITFORMID,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID,
          @SPOUSEID = @SPOUSEID output,
          @GROUP_GIVESANONYMOUSLY = @GIVESANONYMOUSLY output,
          @SPOUSE_LOOKUPID = @SPOUSE_LOOKUPID output,
          @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
          @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR output,
          @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = @SPOUSE_PRIMARYRECOGNITIONTYPECODEID output,
          @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
          @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR output,
          @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID output,
          @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_PRIMARYRELATIONSHIPEXISTS output,
          @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = @BUSINESS_PRIMARYMATCHFACTOR output,
          @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = @BUSINESS_PRIMARYRECOGNITIONTYPECODEID output,
          @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_RECIPROCALRELATIONSHIPEXISTS output,
          @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = @BUSINESS_RECIPROCALMATCHFACTOR output,
          @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID output;

        exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBATCHCONSTITUENTINBATCH_5 @EDITFORMID, @CHANGEAGENTID, @LASTNAME, @LASTNAME, @FIRSTNAME, @MIDDLENAME, @MAIDENNAME, @NICKNAME, 
          @TITLECODEID, @SUFFIXCODEID, @GENDERCODE, @BIRTHDATE, @ADDRESS_ADDRESSTYPECODEID, @ADDRESS_DONOTMAIL, @ADDRESS_DONOTMAILREASONCODEID, @ADDRESS_COUNTRYID, @ADDRESS_STATEID, 
          @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_POSTCODE, null, null, null, null, null, null, null,null , null, null, @PHONE_PHONETYPECODEID, @PHONE_NUMBER, 
          @EMAILADDRESS_EMAILADDRESSTYPECODEID, @EMAILADDRESS_EMAILADDRESS, @MARITALSTATUSCODEID, @ADDRESS_ADDRESSTYPECODEID, @ADDRESS_DONOTMAIL, null, @ADDRESS_COUNTRYID, 
          @ADDRESS_STATEID, @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_POSTCODE,  null, null, null, null, null, null, null, null, null, @PHONE_PHONETYPECODEID, @PHONE_NUMBER, 
          @EMAILADDRESS_EMAILADDRESSTYPECODEID, @EMAILADDRESS_EMAILADDRESS, @WEBADDRESS, @INDUSTRYCODEID, @NUMEMPLOYEES, @NUMSUBSIDIARIES, @PARENTCORPID, null, 
          null, @SPOUSEID, @EXISTINGSPOUSE, @SPOUSE_LASTNAME, @SPOUSE_FIRSTNAME, @SPOUSE_MIDDLENAME, @SPOUSE_MAIDENNAME, @SPOUSE_NICKNAME, @SPOUSE_TITLECODEID, @SPOUSE_SUFFIXCODEID, 
          @SPOUSE_GENDERCODE, @SPOUSE_BIRTHDATE, @SPOUSE_RECIPROCALTYPECODEID, @SPOUSE_RELATIONSHIPTYPECODEID, @COPYPRIMARYINFORMATION, @SPOUSE_STARTDATE, null, null, null, 
          @BUSINESSID, @EXISTINGBUSINESS, @BUSINESS_NAME, @BUSINESS_ADDRESSTYPECODEID, @BUSINESS_COUNTRYID, @BUSINESS_STATEID, @BUSINESS_ADDRESSBLOCK, @BUSINESS_CITY, @BUSINESS_POSTCODE, 
          @BUSINESS_DONOTMAIL, null, null, null, null, null, null, null, null , null, null, @BUSINESS_PHONETYPECODEID, @BUSINESS_NUMBER, @BUSINESS_RECIPROCALTYPECODEID, 
          @BUSINESS_RELATIONSHIPTYPECODEID, @BUSINESS_STARTDATE, @ISCONTACT, @ISPRIMARYCONTACT, @CONTACTTYPECODEID, @POSITION, @ISMATCHINGGIFTRELATIONSHIP, @CURRENTAPPUSERID, @LASTNAME, 
          @GROUP_DESCRIPTION, @GIVESANONYMOUSLY, @ADDRESS_ADDRESSTYPECODEID, @ADDRESS_COUNTRYID, @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_STATEID, null, null, null, null, null, 
          null, null , null, null, null, @PHONE_PHONETYPECODEID, @PHONE_NUMBER, @EMAILADDRESS_EMAILADDRESSTYPECODEID, @EMAILADDRESS_EMAILADDRESS, @WEBADDRESS, @ADDRESS_DONOTMAIL, 
          null, null, @GROUP_GROUPTYPEID, @GROUP_PRIMARYCONTACT, null, null, @GROUP_STARTDATE, @HOUSEHOLD_PRIMARYCONTACTID, @HOUSEHOLD_PRIMARYCONTACT_KEYNAME, @HOUSEHOLD_PRIMARYCONTACT_FIRSTNAME, 
          @HOUSEHOLD_PRIMARYCONTACT_MIDDLENAME, @HOUSEHOLD_PRIMARYCONTACT_TITLECODEID, @HOUSEHOLD_PRIMARYCONTACT_SUFFIXCODEID, @HOUSEHOLD_PRIMARYCONTACT_COPYHOUSEHOLDCONTACT, @HOUSEHOLD_SECONDMEMBERID, 
          @HOUSEHOLD_SECONDMEMBER_KEYNAME, @HOUSEHOLD_SECONDMEMBER_FIRSTNAME, @HOUSEHOLD_SECONDMEMBER_MIDDLENAME, @HOUSEHOLD_SECONDMEMBER_TITLECODEID, @HOUSEHOLD_SECONDMEMBER_SUFFIXCODEID, 
          @HOUSEHOLD_SECONDMEMBER_COPYHOUSEHOLDCONTACT,  @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RECIPROCALTYPECODEID, @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_RELATIONSHIPTYPECODEID, 
          @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_STARTDATE, @HOUSEHOLD_SECONDMEMBER_RELATIONSHIP_ISSPOUSE, null, null, null, null, null, @ADDRESSID, @PHONEID, @EMAILADDRESSID, @SITEID,
          @CONSTITUENTSECURITYGROUPID, @CONSTITUENCYCODEID, @INFOSOURCECODEID, @NAMEFORMATS, null, @SPOUSE_LOOKUPID, @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
          @SPOUSE_PRIMARYRECOGNITIONTYPECODEID, @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR, @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
          @BUSINESS_PRIMARYRELATIONSHIPEXISTS, @BUSINESS_PRIMARYMATCHFACTOR, @BUSINESS_PRIMARYRECOGNITIONTYPECODEID, @BUSINESS_RECIPROCALRELATIONSHIPEXISTS, @BUSINESS_RECIPROCALMATCHFACTOR,
          @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID, @ORIGINAL_KEYNAME, @ORIGINAL_FIRSTNAME, @GENDERCODEID, @SPOUSE_GENDERCODEID;


        declare @DOMANUALREVIEWFORAUTOMATCH bit = 0
        exec dbo.USP_BATCHCONSTITUENTAPPLYRULES_FORIMPORT 
          @BATCHROWID, 
          @CONSTITUENTID, 
          @CHANGEAGENTID, 
          @SIMILARADDRESSCODE, 
          @UNSIMILARADDRESSCODE, 
          @NEWADDRESSPRIMARYCODE, 
          @DOMANUALREVIEWFORAUTOMATCH output,
          @DIFFERENTPHONECODE,
          @NEWPHONEPRIMARYCODE,
          @DIFFERENTEMAILCODE,
          @NEWEMAILPRIMARYCODE,
          0;

        -- Check for 'generate manual review' exception and don't apply the rules

        if @DOMANUALREVIEWFORAUTOMATCH = 1 
        begin
          if exists ( select 'x' from dbo.BATCHREVENUE where ID = @BATCHROWID)
          begin
            insert into dbo.BATCHREVENUEBATCHSYSTEMMESSAGES(BATCHREVENUEID, MESSAGETEXT, MESSAGETYPECODE, SEVERITYCODE, ORIGINCODE,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@BATCHROWID, 'Data in batch is different from data for this constituent. Click here to review.', 1, 0, 1, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate())
          end

          if exists (select 'x' from dbo.BATCHMEMBERSHIPDUES where ID = @BATCHROWID)
          begin
            insert into dbo.BATCHMEMBERSHIPDUESBATCHSYSTEMMESSAGES(BATCHMEMBERSHIPDUESID, MESSAGETEXT, MESSAGETYPECODE, SEVERITYCODE, ORIGINCODE,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@BATCHROWID, 'Data in batch is different from data for this constituent. Click here to review.', 1, 0, 1, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate())
          end
        end

      end