USP_EDITSAVE_BATCHCONSTITUENTUPDATEBATCHROW_4

  CREATE procedure dbo.USP_EDITSAVE_BATCHCONSTITUENTUPDATEBATCHROW_4(  
  @ID uniqueidentifier,  
  @CHANGEAGENTID uniqueidentifier,  
  @SEQUENCE int,  
  @PRIMARYRECORDID uniqueidentifier,   
  @CONSTITUENTTYPECODE tinyint,  
  @BIRTHDATE dbo.UDT_FUZZYDATE,  
  @FIRSTNAME nvarchar(50),  
  @GENDERCODE tinyint,  
  @GIVESANONYMOUSLY bit,  
  @KEYNAME nvarchar(100),  
  @LOOKUP_ID nvarchar(100),  
  @MAIDENNAME nvarchar(100),  
  @MIDDLENAME nvarchar(50),  
  @NICKNAME nvarchar(50),  
  @SUFFIXCODEID uniqueidentifier,  
  @TITLECODEID uniqueidentifier,  
  @WEBADDRESS dbo.UDT_WEBADDRESS,  
  @ALTERNATELOOKUPIDS xml,  
  @SECURITYATTRIBUTES xml,  
  @DECEASED bit,  
  @DECEASEDDATE dbo.UDT_FUZZYDATE,  
  @CONSTITUENT_SITEID uniqueidentifier,  
  @INTERESTS xml,  
  @PROSPECTMANAGERFUNDRAISERID uniqueidentifier,  
  @ADDRESSES xml,  
  @PHONES xml,  
  @EMAILADDRESSES xml,  
  @CONSTITUENCIES xml,  
  @GROUPTYPEID uniqueidentifier,  
  @GROUPDESCRIPTION nvarchar(300),  
  @GROUPSTARTDATE datetime,  
  @ORG_INDUSTRYCODEID uniqueidentifier,  
  @ORG_NUMEMPLOYEES int,  
  @ORG_NUMSUBSIDIARIES int,  
  @ORG_PARENTCORPID uniqueidentifier,  
  @MARITALSTATUSCODEID uniqueidentifier,  
  @SPOUSE_ID uniqueidentifier,  
  @SPOUSE_TITLECODEID uniqueidentifier,  
  @SPOUSE_FIRSTNAME nvarchar(50),  
  @SPOUSE_NICKNAME nvarchar(50),  
  @SPOUSE_MIDDLENAME nvarchar(50),  
  @SPOUSE_MAIDENNAME nvarchar(100),  
  @SPOUSE_LASTNAME nvarchar(100),  
  @SPOUSE_SUFFIXCODEID uniqueidentifier,  
  @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,  
  @SPOUSE_GENDERCODE tinyint,  
  @SPOUSE_LOOKUPID nvarchar(100),  
  @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,  
  @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,  
  @BUSINESS_ID 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_EMAILADDRESS dbo.UDT_EMAILADDRESS,  
  @BUSINESS_EMAILADDRESSTYPECODEID uniqueidentifier,  
  @BUSINESS_INDUSTRYCODEID uniqueidentifier,  
  @BUSINESS_LOOKUPID nvarchar(100),  
  @BUSINESS_LOT nvarchar(5),  
  @BUSINESS_NAME nvarchar(100),  
  @BUSINESS_NUMEMPLOYEES int,  
  @BUSINESS_NUMSUBSIDIARIES int,  
  @BUSINESS_PARENTCORPID uniqueidentifier,  
  @BUSINESS_PHONENUMBER nvarchar(100),  
  @BUSINESS_PHONE_COUNTRYID uniqueidentifier,  
  @BUSINESS_PHONETYPECODEID uniqueidentifier,  
  @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier,  
  @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier,  
  @BUSINESS_STATEID uniqueidentifier,  
  @BUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS,   
  @BUSINESS_POSTCODE nvarchar(12),  
  @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier,  
  @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier,  
  @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier,  
  @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier,  
  @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,  
  @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),  
  @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,  
  @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),  
  @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,  
  @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,  
  @BUSINESS_EMAILADDRESSSTARTDATE date,  
  @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,  
  @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),  
  @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,  
  @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),  
  @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,  
  @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,  
  @CONSTITUENT_SITES xml,  
  @SPOUSE_ALTERNATELOOKUPIDS xml,  
  @SPOUSE_IMPORTLOOKUPID nvarchar(100),  
  @ROWFROMBATCHUI bit,   
  @BBNCTRANID int,   
  @PAGEID int,   
  @PAGENAME nvarchar(100),   
  @BBNCUSERID int,   
  @REMOVESPOUSE bit,   
  @DUPLICATERECORDID uniqueidentifier,  
  @SOCIALMEDIAACCOUNTS xml,  
  @RELATIONSHIPS xml,  
  @NETCOMMUNITYTRANSACTIONPROCESSORID uniqueidentifier,  
  @BBNCID int,  
  @NAMECODE tinyint = 1,  
  @SIMILARADDRESSCODE tinyint = 3,  
  @UNSIMILARADDRESSCODE tinyint = 3,  
  @NEWADDRESSENDDATECODE tinyint = 0,  
  @NEWADDRESSPRIMARYCODE tinyint = 1,  
  @BIRTHDATERULECODE tinyint = 1,  
  @DIFFERENTPHONECODE tinyint = 3 ,  
  @NEWPHONEENDDATECODE tinyint = 0 ,  
  @NEWPHONEPRIMARYCODE tinyint = 1 ,  
  @DIFFERENTEMAILCODE tinyint = 3 ,  
  @NEWEMAILENDDATECODE tinyint = 0 ,  
  @NEWEMAILPRIMARYCODE tinyint = 1 ,  
  @USEGLOBALSETTINGS bit = 1,  
  @CREATEHISTORICALNAMECODE tinyint = 1,  
  @NAMEFORMATS xml,  
  @REQUESTSNOEMAIL bit = 0,  
  @ORIGINAL_KEYNAME nvarchar(100),  
  @ORIGINAL_FIRSTNAME nvarchar(50),  
  @SOLICITCODES xml,  
  @GENDERCODEID uniqueidentifier,  
  @SPOUSE_GENDERCODEID uniqueidentifier  
) as  
set nocount on;  

if (@ORIGINAL_KEYNAME is null or @ORIGINAL_FIRSTNAME is null)
    begin
        exec dbo.USP_EDITLOAD_BATCHCONSTITUENTUPDATEBATCHROW  
          @ID = @ID,  
          @ORIGINAL_KEYNAME = @ORIGINAL_KEYNAME output,  
        @ORIGINAL_FIRSTNAME = @ORIGINAL_FIRSTNAME output; 
    end

declare @CURRENTDATE datetime;  

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

set @CURRENTDATE = getdate();  

if coalesce(@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0) = 0  
begin  
  set @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = 100;  
  set @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = null;  
end  

if coalesce(@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0) = 0  
begin  
  set @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = 100;  
  set @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = null;  
end  

if coalesce(@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0) = 0  
begin  
  set @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = 100;  
  set @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = null;  
end  

if coalesce(@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0) = 0  
begin  
  set @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = 100;  
  set @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = null;  
end  

begin try  
update dbo.BATCHCONSTITUENTUPDATE  
set [SEQUENCE] = coalesce(@SEQUENCE, SEQUENCE),  
  [CHANGEDBYID] = @CHANGEAGENTID,  
  [DATECHANGED] = @CURRENTDATE,  
  [PRIMARYRECORDID] = @PRIMARYRECORDID,  
  [CONSTITUENTTYPECODE] = coalesce(@CONSTITUENTTYPECODE, '0'),  
  [BIRTHDATE] = @BIRTHDATE,  
  [FIRSTNAME] = @FIRSTNAME,  
  [GENDERCODE] = coalesce(@GENDERCODE, '0'),  
  [GENDERCODEID] = @GENDERCODEID,  
  [GIVESANONYMOUSLY] = @GIVESANONYMOUSLY,  
  [KEYNAME] = @KEYNAME,  
  [LOOKUP_ID] = coalesce(@LOOKUP_ID, LOOKUP_ID),  
  [MAIDENNAME] = @MAIDENNAME,  
  [MIDDLENAME] = @MIDDLENAME,  
  [NICKNAME] = @NICKNAME,  
  [SUFFIXCODEID] = @SUFFIXCODEID,  
  [TITLECODEID] = @TITLECODEID,  
  [WEBADDRESS] = @WEBADDRESS,  
  [DECEASED] = coalesce(@DECEASED, DECEASED),  
  [DECEASEDDATE] = coalesce(@DECEASEDDATE, DECEASEDDATE),  
  [CONSTITUENT_SITEID] = @CONSTITUENT_SITEID,  
  [PROSPECTMANAGERFUNDRAISERID] = @PROSPECTMANAGERFUNDRAISERID,  
  [GROUPTYPEID] = @GROUPTYPEID,  
  [GROUPDESCRIPTION] = @GROUPDESCRIPTION,  
  [GROUPSTARTDATE] = @GROUPSTARTDATE,  
  [ORG_INDUSTRYCODEID] = @ORG_INDUSTRYCODEID,  
  [ORG_NUMEMPLOYEES] = coalesce(@ORG_NUMEMPLOYEES, '0'),  
  [ORG_NUMSUBSIDIARIES] = coalesce(@ORG_NUMSUBSIDIARIES, '0'),  
  [ORG_PARENTCORPID] = @ORG_PARENTCORPID,  
  [MARITALSTATUSCODEID] = @MARITALSTATUSCODEID,  
  [SPOUSE_ID] = @SPOUSE_ID,  
  [SPOUSE_TITLECODEID] = @SPOUSE_TITLECODEID,  
  [SPOUSE_FIRSTNAME] = coalesce(@SPOUSE_FIRSTNAME, SPOUSE_FIRSTNAME),  
  [SPOUSE_NICKNAME] = coalesce(@SPOUSE_NICKNAME, SPOUSE_NICKNAME),  
  [SPOUSE_MIDDLENAME] = coalesce(@SPOUSE_MIDDLENAME, SPOUSE_MIDDLENAME),  
  [SPOUSE_MAIDENNAME] = coalesce(@SPOUSE_MAIDENNAME, SPOUSE_MAIDENNAME),  
  [SPOUSE_LASTNAME] = coalesce(@SPOUSE_LASTNAME, SPOUSE_LASTNAME),  
  [SPOUSE_SUFFIXCODEID] = @SPOUSE_SUFFIXCODEID,  
  [SPOUSE_BIRTHDATE] = coalesce(@SPOUSE_BIRTHDATE, SPOUSE_BIRTHDATE),  
  [SPOUSE_GENDERCODE] = coalesce(@SPOUSE_GENDERCODE, '0'),  
  [SPOUSE_GENDERCODEID] = @SPOUSE_GENDERCODEID,  
  [SPOUSE_LOOKUPID] = coalesce(@SPOUSE_LOOKUPID, SPOUSE_LOOKUPID),  
  [SPOUSE_RELATIONSHIPTYPECODEID] = @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST,  
  [SPOUSE_RECIPROCALTYPECODEID] = @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST,  
  [BUSINESS_ID] = @BUSINESS_ID,  
  [BUSINESS_ADDRESSBLOCK] = coalesce(@BUSINESS_ADDRESSBLOCK, BUSINESS_ADDRESSBLOCK),  
  [BUSINESS_ADDRESSTYPECODEID] = @BUSINESS_ADDRESSTYPECODEID,  
  [BUSINESS_CART] = coalesce(@BUSINESS_CART, BUSINESS_CART),  
  [BUSINESS_CITY] = coalesce(@BUSINESS_CITY, BUSINESS_CITY),  
  [BUSINESS_COUNTRYID] = @BUSINESS_COUNTRYID,  
  [BUSINESS_DONOTMAIL] = coalesce(@BUSINESS_DONOTMAIL, BUSINESS_DONOTMAIL),  
  [BUSINESS_DONOTMAILREASONCODEID] = @BUSINESS_DONOTMAILREASONCODEID,  
  [BUSINESS_DPC] = coalesce(@BUSINESS_DPC, BUSINESS_DPC),  
  [BUSINESS_EMAILADDRESS] = coalesce(@BUSINESS_EMAILADDRESS, BUSINESS_EMAILADDRESS),  
  [BUSINESS_EMAILADDRESSTYPECODEID] = @BUSINESS_EMAILADDRESSTYPECODEID,  
  [BUSINESS_INDUSTRYCODEID] = @BUSINESS_INDUSTRYCODEID,  
  [BUSINESS_LOOKUPID] = coalesce(@BUSINESS_LOOKUPID, BUSINESS_LOOKUPID),  
  [BUSINESS_LOT] = coalesce(@BUSINESS_LOT, BUSINESS_LOT),  
  [BUSINESS_NAME] = coalesce(@BUSINESS_NAME, BUSINESS_NAME),  
  [BUSINESS_NUMEMPLOYEES] = coalesce(@BUSINESS_NUMEMPLOYEES, '0'),  
  [BUSINESS_NUMSUBSIDIARIES] = coalesce(@BUSINESS_NUMSUBSIDIARIES, '0'),  
  [BUSINESS_PARENTCORPID] = @BUSINESS_PARENTCORPID,  
  [BUSINESS_PHONENUMBER] = coalesce(@BUSINESS_PHONENUMBER, BUSINESS_PHONENUMBER),  
  [BUSINESS_PHONE_COUNTRYID] = @BUSINESS_PHONE_COUNTRYID,  
  [BUSINESS_PHONETYPECODEID] = @BUSINESS_PHONETYPECODEID,  
  [BUSINESS_RELATIONSHIPTYPECODEID] = @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST,  
  [BUSINESS_RECIPROCALTYPECODEID] = @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST,  
  [BUSINESS_STATEID] = @BUSINESS_STATEID,  
  [BUSINESS_WEBADDRESS] = coalesce(@BUSINESS_WEBADDRESS, BUSINESS_WEBADDRESS),  
  [BUSINESS_POSTCODE] = coalesce(@BUSINESS_POSTCODE, BUSINESS_POSTCODE),  
  [BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS] = coalesce(@BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0),  
  [BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR] = coalesce(@BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR, 100),  
  [BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS] = coalesce(@BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0),  
  [BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR] = coalesce(@BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR, 100),  
  [BUSINESS_PRIMARYRECOGNITIONTYPECODEID] = @BUSINESS_PRIMARYRECOGNITIONTYPECODEID,  
  [BUSINESS_RECIPROCALRECOGNITIONTYPECODEID] = @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,  
  [BUSINESS_EMAILADDRESSSTARTDATE] = @BUSINESS_EMAILADDRESSSTARTDATE,  
  [SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS] = coalesce(@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0),  
  [SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR] = coalesce(@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR, 100),  
  [SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS] = coalesce(@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0),  
  [SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR] = coalesce(@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR, 100),  
  [SPOUSE_PRIMARYRECOGNITIONTYPECODEID] = @SPOUSE_PRIMARYRECOGNITIONTYPECODEID,  
  [SPOUSE_RECIPROCALRECOGNITIONTYPECODEID] = @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,  
  [REMOVESPOUSE] = coalesce(@REMOVESPOUSE, REMOVESPOUSE),  
  NAMECODE = @NAMECODE,  
  SIMILARADDRESSCODE = @SIMILARADDRESSCODE,  
  UNSIMILARADDRESSCODE = @UNSIMILARADDRESSCODE,  
  NEWADDRESSENDDATECODE = @NEWADDRESSENDDATECODE,  
  NEWADDRESSPRIMARYCODE = @NEWADDRESSPRIMARYCODE,        
  BIRTHDATERULECODE = @BIRTHDATERULECODE,  
  DIFFERENTPHONECODE = @DIFFERENTPHONECODE,  
  NEWPHONEENDDATECODE = @NEWPHONEENDDATECODE,  
  NEWPHONEPRIMARYCODE = @NEWPHONEPRIMARYCODE,  
  DIFFERENTEMAILCODE = @DIFFERENTEMAILCODE,  
  NEWEMAILENDDATECODE = @NEWEMAILENDDATECODE,  
  NEWEMAILPRIMARYCODE = @NEWEMAILPRIMARYCODE,  
  USEGLOBALSETTINGS = @USEGLOBALSETTINGS,  
  CREATEHISTORICALNAMECODE= @CREATEHISTORICALNAMECODE,  
  REQUESTSNOEMAIL = @REQUESTSNOEMAIL,  
  ROWFROMBATCHUI = coalesce(@ROWFROMBATCHUI, ROWFROMBATCHUI),  
  ORIGINAL_KEYNAME = coalesce(@ORIGINAL_KEYNAME,''),
  ORIGINAL_FIRSTNAME = coalesce(@ORIGINAL_FIRSTNAME  ,'')
where  
  ID = @ID  

update dbo.BATCHCONSTITUENTUPDATEBBNCINFO  
set BBNCTRANID =@BBNCTRANID,  
  PAGEID =@PAGEID,  
  PAGENAME =@PAGENAME,  
  BBNCUSERID= @BBNCUSERID,  
  NETCOMMUNITYTRANSACTIONPROCESSORID = @NETCOMMUNITYTRANSACTIONPROCESSORID,  
  BBNCID = @BBNCID  
where   
  BATCHCONSTITUENTUPDATEID = @ID  

declare @NEWPRIMARYADDRESSID uniqueidentifier;  
select  
  @NEWPRIMARYADDRESSID = T.c.value('(ID)[1]','uniqueidentifier')   
from  
  @ADDRESSES.nodes('/ADDRESSES/ITEM') T(c)  
where  
  T.c.value('(ISPRIMARY)[1]','bit') = 1;  

--Updating ISPRIMARY to false to avoid problems with the trigger clearing ISPRIMARY for all rows  

update dbo.BATCHCONSTITUENTUPDATEADDRESSES set  
  ISPRIMARY = 0,  
  CHANGEDBYID = @CHANGEAGENTID,  
  DATECHANGED = @CURRENTDATE  
where  
  BATCHCONSTITUENTUPDATEADDRESSES.BATCHCONSTITUENTUPDATEID = @ID  
  -- Don't execute the update if ISPRIMARY already has the value we want  

  and BATCHCONSTITUENTUPDATEADDRESSES.ISPRIMARY = 1  
  -- Only execute the update if the primary address is changing  

  and BATCHCONSTITUENTUPDATEADDRESSES.ID <> @NEWPRIMARYADDRESSID;  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETALTERNATELOOKUPIDS_UPDATEFROMXML @ID, @ALTERNATELOOKUPIDS, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETSPOUSEALTERNATELOOKUPIDS_UPDATEFROMXML @ID, @SPOUSE_ALTERNATELOOKUPIDS, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_UPDATEFROMXML @ID, @SECURITYATTRIBUTES, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETSITES_UPDATEFROMXML @ID, @CONSTITUENT_SITES, @CHANGEAGENTID;  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETINTERESTS_UPDATEFROMXML @ID, @INTERESTS, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_UPDATEFROMXML @ID, @ADDRESSES, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETPHONES_WITHDATES_UPDATEFROMXML @ID, @PHONES, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_WITHDATES_2_UPDATEFROMXML @ID, @EMAILADDRESSES, @CHANGEAGENTID  

exec dbo.USP_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_UPDATEFROMXML @ID, @SOLICITCODES, @CHANGEAGENTID  

declare @USERDEFINEDCONSTITUENCY xml;  
set @USERDEFINEDCONSTITUENCY =   
  (  
    select  
      CONSTITUENCYFROMXML.ID,  
      CONSTITUENCYFROMXML.CONSTITUENCYCODEID,  
      CONSTITUENCYFROMXML.DATEFROM,  
      CONSTITUENCYFROMXML.DATETO,  
      case  
        -- Check for existence in dbo.CONSTITUENCY.  It's possible the original row was a system defined constituency and so the ORIGINALCONSTITUENCYID  

        -- doesn't exist in the CONSTITUENCY table.  In that case, it needs to be cleared since it will cause a foreign key violation when inserting into   

        -- BATCHCONSTITUENTUPDATECONSTITUENCIES.  The system defined constituencies don't need this check since there isn't a FK for ORIGINALCONSTITUENCYID  

        -- on the table they are stored in.  

        when CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID is not null and not exists (select 1 from dbo.CONSTITUENCY where ID = CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID) then null  
        else CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID  
      end ORIGINALCONSTITUENCYID  
    from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) as CONSTITUENCYFROMXML  
    inner join dbo.CONSTITUENCYDEFINITION on CONSTITUENCYFROMXML.CONSTITUENCYCODEID = CONSTITUENCYDEFINITION.ID  
    where  
      CONSTITUENCYDEFINITION.ISSYSTEM = 0  
    for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64  
  );  
exec dbo.USP_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_UPDATEFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;  

declare @SYSTEMDEFINEDCONSTITUENCY xml;  
set @SYSTEMDEFINEDCONSTITUENCY =  
  (  
    select  
      CONSTITUENCYFROMXML.ID,  
      CONSTITUENCYFROMXML.CONSTITUENCYCODEID,  
      CONSTITUENCYFROMXML.DATEFROM,  
      CONSTITUENCYFROMXML.DATETO,  
      CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID  
    from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) as CONSTITUENCYFROMXML  
    inner join dbo.CONSTITUENCYDEFINITION on CONSTITUENCYFROMXML.CONSTITUENCYCODEID = CONSTITUENCYDEFINITION.ID  
   where  
      CONSTITUENCYDEFINITION.ISSYSTEM = 1  
    for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64  
  );  
exec dbo.USP_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_SYSTEM_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETSOCIALMEDIAACCOUNTS_UPDATEFROMXML @ID, @SOCIALMEDIAACCOUNTS, @CHANGEAGENTID  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETRELATIONSHIPS_UPDATEFROMXML @ID, @RELATIONSHIPS, @CHANGEAGENTID;  

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETNAMEFORMATS_UPDATEFROMXML @ID, @NAMEFORMATS, @CHANGEAGENTID;  

-- set for use on duplicate check  

set @DUPLICATERECORDID = @ID;  

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

return 0;