USP_EDITLOAD_BATCHCONSTITUENTUPDATEBATCHROW

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

set @DATALOADED = 0;
set @TSLONG = 0;

declare @USERDEFINEDCONSTITUENCIES xml;
declare @SYSTEMDEFINEDCONSTITUENCIES xml;

select
  @DATALOADED = 1,
  @TSLONG = BATCHCONSTITUENTUPDATE.[TSLONG],
  @SEQUENCE = BATCHCONSTITUENTUPDATE.[SEQUENCE],
  @PRIMARYRECORDID = BATCHCONSTITUENTUPDATE.[PRIMARYRECORDID],
  @CONSTITUENTTYPECODE = BATCHCONSTITUENTUPDATE.[CONSTITUENTTYPECODE],
  @BIRTHDATE = BATCHCONSTITUENTUPDATE.[BIRTHDATE],
  @FIRSTNAME = BATCHCONSTITUENTUPDATE.[FIRSTNAME],
  @GENDERCODE = BATCHCONSTITUENTUPDATE.[GENDERCODE],
  @GIVESANONYMOUSLY = BATCHCONSTITUENTUPDATE.[GIVESANONYMOUSLY],
  @KEYNAME = BATCHCONSTITUENTUPDATE.[KEYNAME],
  @LOOKUP_ID = BATCHCONSTITUENTUPDATE.[LOOKUP_ID],
  @MAIDENNAME = BATCHCONSTITUENTUPDATE.[MAIDENNAME],
  @MIDDLENAME = BATCHCONSTITUENTUPDATE.[MIDDLENAME],
  @NICKNAME = BATCHCONSTITUENTUPDATE.[NICKNAME],
  @SUFFIXCODEID = BATCHCONSTITUENTUPDATE.[SUFFIXCODEID],
  @TITLECODEID = BATCHCONSTITUENTUPDATE.[TITLECODEID],
  @WEBADDRESS = BATCHCONSTITUENTUPDATE.[WEBADDRESS],
  @ALTERNATELOOKUPIDS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETALTERNATELOOKUPIDS_TOITEMLISTXML(@ID),
  @SECURITYATTRIBUTES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_TOITEMLISTXML(@ID),
  @DECEASED = BATCHCONSTITUENTUPDATE.[DECEASED],
  @DECEASEDDATE = BATCHCONSTITUENTUPDATE.[DECEASEDDATE],
  @CONSTITUENT_SITES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_TOITEMLISTXML(@ID),
  @INTERESTS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETINTERESTS_TOITEMLISTXML(@ID),
  @PROSPECTMANAGERFUNDRAISERID =BATCHCONSTITUENTUPDATE.[PROSPECTMANAGERFUNDRAISERID],
  @ADDRESSES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_TOITEMLISTXML(@ID),
  @PHONES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETPHONES_WITHDATES_TOITEMLISTXML(@ID),
  @EMAILADDRESSES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_WITHDATES_2_TOITEMLISTXML(@ID),

  -- Used to build @CONSTITUENCIES

  @USERDEFINEDCONSTITUENCIES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_TOITEMLISTXML(@ID),
  @SYSTEMDEFINEDCONSTITUENCIES = dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_SYSTEM_TOITEMLISTXML(@ID),

  @GROUPTYPEID = BATCHCONSTITUENTUPDATE.[GROUPTYPEID],
  @GROUPDESCRIPTION = BATCHCONSTITUENTUPDATE.[GROUPDESCRIPTION],
  @GROUPSTARTDATE = BATCHCONSTITUENTUPDATE.[GROUPSTARTDATE],
  @ORG_INDUSTRYCODEID = BATCHCONSTITUENTUPDATE.[ORG_INDUSTRYCODEID],
  @ORG_NUMEMPLOYEES = BATCHCONSTITUENTUPDATE.[ORG_NUMEMPLOYEES],
  @ORG_NUMSUBSIDIARIES = BATCHCONSTITUENTUPDATE.[ORG_NUMSUBSIDIARIES],
  @ORG_PARENTCORPID = BATCHCONSTITUENTUPDATE.[ORG_PARENTCORPID],
  @MARITALSTATUSCODEID = BATCHCONSTITUENTUPDATE.[MARITALSTATUSCODEID],
  @SPOUSE_ID = case when BATCHCONSTITUENTUPDATE.[SPOUSE_ID] is null then SPOUSE.ID else BATCHCONSTITUENTUPDATE.[SPOUSE_ID] end,
  @SPOUSE_TITLECODEID = case when (BATCHCONSTITUENTUPDATE.[SPOUSE_TITLECODEID] is null) then SPOUSE.TITLECODEID else BATCHCONSTITUENTUPDATE.[SPOUSE_TITLECODEID] end,
  @SPOUSE_FIRSTNAME = BATCHCONSTITUENTUPDATE.[SPOUSE_FIRSTNAME],
  @SPOUSE_NICKNAME = BATCHCONSTITUENTUPDATE.[SPOUSE_NICKNAME],
  @SPOUSE_MIDDLENAME = BATCHCONSTITUENTUPDATE.[SPOUSE_MIDDLENAME],
  @SPOUSE_MAIDENNAME = BATCHCONSTITUENTUPDATE.[SPOUSE_MAIDENNAME],
  @SPOUSE_LASTNAME =case when (BATCHCONSTITUENTUPDATE.[SPOUSE_LASTNAME]='' or BATCHCONSTITUENTUPDATE.[SPOUSE_LASTNAME] is null) then SPOUSE.KEYNAME else BATCHCONSTITUENTUPDATE.[SPOUSE_LASTNAME] end,
  @SPOUSE_SUFFIXCODEID = case when (BATCHCONSTITUENTUPDATE.[SPOUSE_SUFFIXCODEID] is null) then SPOUSE.SUFFIXCODEID else BATCHCONSTITUENTUPDATE.[SPOUSE_SUFFIXCODEID] end,
  @SPOUSE_BIRTHDATE =  case when (BATCHCONSTITUENTUPDATE.[SPOUSE_BIRTHDATE] is null) then SPOUSE.BIRTHDATE else BATCHCONSTITUENTUPDATE.[SPOUSE_BIRTHDATE] end,
  @SPOUSE_GENDERCODE = case when (BATCHCONSTITUENTUPDATE.[SPOUSE_GENDERCODE] is null) then SPOUSE.GENDERCODE else BATCHCONSTITUENTUPDATE.[SPOUSE_GENDERCODE] end,
  @SPOUSE_LOOKUPID =  case when (BATCHCONSTITUENTUPDATE.[SPOUSE_LOOKUPID]='' or BATCHCONSTITUENTUPDATE.[SPOUSE_LOOKUPID] is null) then SPOUSE.LOOKUPID else BATCHCONSTITUENTUPDATE.[SPOUSE_LOOKUPID] end,
  @SPOUSE_RELATIONSHIPTYPECODEID = case when BATCHCONSTITUENTUPDATE.[SPOUSE_RELATIONSHIPTYPECODEID] is null then RELATIONSHIP.RELATIONSHIPTYPECODEID else BATCHCONSTITUENTUPDATE.[SPOUSE_RELATIONSHIPTYPECODEID] end,
  @SPOUSE_RECIPROCALTYPECODEID = case when BATCHCONSTITUENTUPDATE.[SPOUSE_RECIPROCALTYPECODEID] is null then RELATIONSHIP.RECIPROCALTYPECODEID else BATCHCONSTITUENTUPDATE.[SPOUSE_RECIPROCALTYPECODEID] end,
  @BUSINESS_ID = BATCHCONSTITUENTUPDATE.[BUSINESS_ID],
  @BUSINESS_ADDRESSBLOCK = BATCHCONSTITUENTUPDATE.[BUSINESS_ADDRESSBLOCK],
  @BUSINESS_ADDRESSTYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_ADDRESSTYPECODEID],
  @BUSINESS_CART = BATCHCONSTITUENTUPDATE.[BUSINESS_CART],
  @BUSINESS_CITY = BATCHCONSTITUENTUPDATE.[BUSINESS_CITY],
  @BUSINESS_COUNTRYID = BATCHCONSTITUENTUPDATE.[BUSINESS_COUNTRYID],
  @BUSINESS_DONOTMAIL = coalesce(BATCHCONSTITUENTUPDATE.[BUSINESS_DONOTMAIL], '0'),
  @BUSINESS_DONOTMAILREASONCODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_DONOTMAILREASONCODEID],
  @BUSINESS_DPC = BATCHCONSTITUENTUPDATE.[BUSINESS_DPC],
  @BUSINESS_EMAILADDRESS = BATCHCONSTITUENTUPDATE.[BUSINESS_EMAILADDRESS],
  @BUSINESS_EMAILADDRESSTYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_EMAILADDRESSTYPECODEID],
  @BUSINESS_INDUSTRYCODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_INDUSTRYCODEID],
  @BUSINESS_LOOKUPID = BATCHCONSTITUENTUPDATE.[BUSINESS_LOOKUPID],
  @BUSINESS_LOT = BATCHCONSTITUENTUPDATE.[BUSINESS_LOT],
  @BUSINESS_NAME = BATCHCONSTITUENTUPDATE.[BUSINESS_NAME],
  @BUSINESS_NUMEMPLOYEES = BATCHCONSTITUENTUPDATE.[BUSINESS_NUMEMPLOYEES],
  @BUSINESS_NUMSUBSIDIARIES = BATCHCONSTITUENTUPDATE.[BUSINESS_NUMSUBSIDIARIES],
  @BUSINESS_PARENTCORPID = BATCHCONSTITUENTUPDATE.[BUSINESS_PARENTCORPID],
  @BUSINESS_PHONENUMBER = BATCHCONSTITUENTUPDATE.[BUSINESS_PHONENUMBER],
  @BUSINESS_PHONETYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_PHONETYPECODEID],
  @BUSINESS_RELATIONSHIPTYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_RELATIONSHIPTYPECODEID],
  @BUSINESS_RECIPROCALTYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_RECIPROCALTYPECODEID],
  @BUSINESS_STATEID = BATCHCONSTITUENTUPDATE.[BUSINESS_STATEID],
  @BUSINESS_WEBADDRESS = BATCHCONSTITUENTUPDATE.[BUSINESS_WEBADDRESS],
  @BUSINESS_POSTCODE = BATCHCONSTITUENTUPDATE.[BUSINESS_POSTCODE],
  @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST = BATCHCONSTITUENTUPDATE.[SPOUSE_RELATIONSHIPTYPECODEID],
  @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST = BATCHCONSTITUENTUPDATE.[SPOUSE_RECIPROCALTYPECODEID],
  @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = BATCHCONSTITUENTUPDATE.[BUSINESS_RELATIONSHIPTYPECODEID],
  @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = BATCHCONSTITUENTUPDATE.[BUSINESS_RECIPROCALTYPECODEID],
  @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = BATCHCONSTITUENTUPDATE.[BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS],
  @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = BATCHCONSTITUENTUPDATE.[BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR],
  @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = BATCHCONSTITUENTUPDATE.[BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS],
  @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = BATCHCONSTITUENTUPDATE.[BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR],
  @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_PRIMARYRECOGNITIONTYPECODEID],
  @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = BATCHCONSTITUENTUPDATE.[BUSINESS_RECIPROCALRECOGNITIONTYPECODEID],
  @BUSINESS_EMAILADDRESSSTARTDATE = BATCHCONSTITUENTUPDATE.[BUSINESS_EMAILADDRESSSTARTDATE],
  @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = BATCHCONSTITUENTUPDATE.[SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS],
  @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = BATCHCONSTITUENTUPDATE.[SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR],
  @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = BATCHCONSTITUENTUPDATE.[SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS],
  @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = BATCHCONSTITUENTUPDATE.[SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR],
  @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = BATCHCONSTITUENTUPDATE.[SPOUSE_PRIMARYRECOGNITIONTYPECODEID],
  @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = BATCHCONSTITUENTUPDATE.[SPOUSE_RECIPROCALRECOGNITIONTYPECODEID],
  @SPOUSE_ALTERNATELOOKUPIDS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETSPOUSEALTERNATELOOKUPIDS_TOITEMLISTXML(@ID),
  @REMOVESPOUSE = BATCHCONSTITUENTUPDATE.[REMOVESPOUSE],
  @DUPLICATERECORDID = BATCHCONSTITUENTUPDATE.[ID],
  @SOCIALMEDIAACCOUNTS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID),
  @DOMANUALREVIEWFORAUTOMATCH = BATCHCONSTITUENTUPDATE.DOMANUALREVIEWFORAUTOMATCH,
  @RELATIONSHIPS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETRELATIONSHIPS_TOITEMLISTXML(@ID),
  @NAMECODE = BATCHCONSTITUENTUPDATE.NAMECODE,
  @SIMILARADDRESSCODE = BATCHCONSTITUENTUPDATE.SIMILARADDRESSCODE,
  @UNSIMILARADDRESSCODE = BATCHCONSTITUENTUPDATE.UNSIMILARADDRESSCODE,
  @NEWADDRESSENDDATECODE = BATCHCONSTITUENTUPDATE.NEWADDRESSENDDATECODE,
  @NEWADDRESSPRIMARYCODE = BATCHCONSTITUENTUPDATE.NEWADDRESSPRIMARYCODE,
  @BIRTHDATERULECODE = BATCHCONSTITUENTUPDATE.BIRTHDATERULECODE,
  @DIFFERENTPHONECODE = BATCHCONSTITUENTUPDATE.DIFFERENTPHONECODE,
  @NEWPHONEENDDATECODE = BATCHCONSTITUENTUPDATE.NEWPHONEENDDATECODE,
  @NEWPHONEPRIMARYCODE = BATCHCONSTITUENTUPDATE.NEWPHONEPRIMARYCODE,
  @DIFFERENTEMAILCODE = BATCHCONSTITUENTUPDATE.DIFFERENTEMAILCODE,
  @NEWEMAILENDDATECODE = BATCHCONSTITUENTUPDATE.NEWEMAILENDDATECODE,
  @NEWEMAILPRIMARYCODE = BATCHCONSTITUENTUPDATE.NEWEMAILPRIMARYCODE,
  @USEGLOBALSETTINGS = BATCHCONSTITUENTUPDATE.USEGLOBALSETTINGS,
  @CREATEHISTORICALNAMECODE = BATCHCONSTITUENTUPDATE.CREATEHISTORICALNAMECODE,
  @NAMEFORMATS = dbo.UFN_BATCHCONSTITUENTUPDATE_GETNAMEFORMATS_TOITEMLISTXML(@ID),
  @REQUESTSNOEMAIL = BATCHCONSTITUENTUPDATE.REQUESTSNOEMAIL,
  @ROWFROMBATCHUI = BATCHCONSTITUENTUPDATE.ROWFROMBATCHUI,
  @ORIGINAL_KEYNAME = BATCHCONSTITUENTUPDATE.ORIGINAL_KEYNAME,
  @ORIGINAL_FIRSTNAME = BATCHCONSTITUENTUPDATE.ORIGINAL_FIRSTNAME,
  @SOLICITCODES = dbo.UFN_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_TOITEMLISTXML(@ID),
  @GENDERCODEID  = BATCHCONSTITUENTUPDATE.GENDERCODEID,
  @SPOUSE_GENDERCODEID  = BATCHCONSTITUENTUPDATE.SPOUSE_GENDERCODEID
from
  dbo.BATCHCONSTITUENTUPDATE 
    left join dbo.RELATIONSHIP 
          on (RELATIONSHIP.RELATIONSHIPCONSTITUENTID = BATCHCONSTITUENTUPDATE.PRIMARYRECORDID) and RELATIONSHIP.ISSPOUSE = 1                            
    left join dbo.CONSTITUENT SPOUSE
          on (RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID)
where
  BATCHCONSTITUENTUPDATE.ID = @ID;

select         
  @BBNCTRANID= BBNCTRANID,
  @PAGEID= PAGEID,
  @PAGENAME= PAGENAME,
  @BBNCUSERID= BBNCUSERID,
  @NETCOMMUNITYTRANSACTIONPROCESSORID = NETCOMMUNITYTRANSACTIONPROCESSORID,
  @BBNCID = BBNCID,
  @SUBMITTEDLOOKUPID = SUBMITTEDLOOKUPID,
  @SUBMITTEDCLASSYEAR = SUBMITTEDCLASSYEAR,
  @SUBMITTEDEDUCATIONALINSTITUTION = SUBMITTEDEDUCATIONALINSTITUTION
from
  dbo.BATCHCONSTITUENTUPDATEBBNCINFO
where
  BATCHCONSTITUENTUPDATEID = @ID;

set @CONSTITUENCIES =
(
  select
    CONSTITUENCIES.c.query('.')
  from
  (
    select
      USERDEFINEDCONSTITUENCIES.c.query('.')
    from
      @USERDEFINEDCONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') as USERDEFINEDCONSTITUENCIES(c)

    union all

    select
      SYSTEMDEFINEDCONSTITUENCIES.c.query('.')
    from
      @SYSTEMDEFINEDCONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') as SYSTEMDEFINEDCONSTITUENCIES(c)
  ) CONSTITUENCIES(c)
  order by
    CONSTITUENCIES.c.value('(ITEM/SEQUENCE)[1]', 'int')
  for xml raw(''),type,elements,root('CONSTITUENCIES'),BINARY BASE64
);

return 0;