USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTBATCHROW_20

create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTBATCHROW_20
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @ISORGANIZATION tinyint,
    @KEYNAME nvarchar(100),
    @FIRSTNAME nvarchar(50),
    @MIDDLENAME nvarchar(50),
    @MAIDENNAME nvarchar(100),
    @NICKNAME nvarchar(50),
    @TITLECODEID uniqueidentifier,
    @SUFFIXCODEID uniqueidentifier,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @GENDERCODE tinyint,
    @WEBADDRESS dbo.UDT_WEBADDRESS,
    @INDUSTRYCODEID uniqueidentifier,
    @PARENTCORPID uniqueidentifier,
    @NUMEMPLOYEES int,
    @NUMSUBSIDIARIES int,
    @ADDRESS_ADDRESSTYPECODEID uniqueidentifier,
    @ADDRESS_COUNTRYID uniqueidentifier,
    @ADDRESS_ADDRESSBLOCK nvarchar(150),
    @ADDRESS_CITY nvarchar(50),
    @ADDRESS_STATEID uniqueidentifier,
    @ADDRESS_POSTCODE nvarchar(12),
    @ADDRESS_DONOTMAIL bit,
    @PHONES xml,
    @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
    @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @CUSTOMIDENTIFIER nvarchar(100),
    @SEQUENCE int,
    @ADDRESS_CART nvarchar(10),
    @ADDRESS_DPC nvarchar(8),
    @ADDRESS_LOT nvarchar(5),
    @ADDRESS_STARTDATE dbo.UDT_MONTHDAY,
    @ADDRESS_ENDDATE dbo.UDT_MONTHDAY,
    @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID uniqueidentifier,
    @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID uniqueidentifier,
    @PRIMARYSALUTATION_NAMEFORMATTYPECODEID uniqueidentifier,
    @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID uniqueidentifier,
    @ISDECEASED bit,
    @DECEASEDDATE dbo.UDT_FUZZYDATE,
    @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID uniqueidentifier,
    @EDUCATIONALHISTORY_ISPRIMARYRECORD bit,
    @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE tinyint,
    @EDUCATIONALHISTORY_CLASSOF dbo.UDT_YEAR,
    @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID uniqueidentifier,
    @EDUCATIONALHISTORY_STARTDATE dbo.UDT_FUZZYDATE,
    @SPOUSEID uniqueidentifier,
    @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
    @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,
    @SPOUSE_KEYNAME 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_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
    @SPOUSE_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @PRIMARYBUSINESSID uniqueidentifier,
    @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_RECIPROCALTYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_KEYNAME nvarchar(100),
    @PRIMARYBUSINESS_PHONE_PHONETYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_PHONE_NUMBER nvarchar(100),
    @PRIMARYBUSINESS_PHONE_COUNTRYID uniqueidentifier,
    @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_ADDRESS_COUNTRYID uniqueidentifier,
    @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK nvarchar(150),
    @PRIMARYBUSINESS_ADDRESS_CITY nvarchar(50),
    @PRIMARYBUSINESS_ADDRESS_STATEID uniqueidentifier,
    @PRIMARYBUSINESS_ADDRESS_POSTCODE nvarchar(12),
    @PRIMARYBUSINESS_ADDRESS_CART nvarchar(10),
    @PRIMARYBUSINESS_ADDRESS_DPC nvarchar(8),
    @PRIMARYBUSINESS_ADDRESS_LOT nvarchar(5),
    @PRIMARYBUSINESS_ADDRESS_DONOTMAIL bit,
    @PRIMARYBUSINESS_INDUSTRYCODEID uniqueidentifier,
    @PRIMARYBUSINESS_NUMEMPLOYEES int,
    @PRIMARYBUSINESS_NUMSUBSIDIARIES int,
    @PRIMARYBUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS,
    @PRIMARYBUSINESS_PARENTCORPID uniqueidentifier,
    @CONSTITUENCY xml,
    @INTEREST xml,
    @SPOUSE_COPY_ADDRESS bit,
    @SPOUSE_COPY_PHONENUMBER bit,
    @SPOUSE_COPY_EMAILADDRESS bit,
    @CONSTIT_SECURITY_ATTRIBUTEID uniqueidentifier,
    @ADDRESS_COUNTYCODEID uniqueidentifier,
    @ADDRESS_OMITFROMVALIDATION bit,
    @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier,
    @ADDRESS_STATEHOUSEDISTRICTCODEID uniqueidentifier,
    @ADDRESS_STATESENATEDISTRICTCODEID uniqueidentifier,
    @ADDRESS_LOCALPRECINCTCODEID uniqueidentifier,
    @ADDRESS_INFOSOURCECODEID uniqueidentifier,
    @ADDRESS_REGIONCODEID uniqueidentifier,
    @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime,
    @ADDRESS_VALIDATIONMESSAGE nvarchar(200),
    @ADDRESS_CERTIFICATIONDATA int,
    @GROUPTYPEID uniqueidentifier,
    @GROUPSTARTDATE datetime,
    @GROUPDESCRIPTION nvarchar(300),
    @GROUPMEMBERS xml,
    @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier,
    @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID uniqueidentifier,
    @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID uniqueidentifier,
    @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID uniqueidentifier,
    @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID uniqueidentifier,
    @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID uniqueidentifier,
    @EDUCATIONALHISTORY_PREFERREDCLASSYEAR dbo.UDT_YEAR,
    @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID uniqueidentifier,
    @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE,
    @EDUCATIONALHISTORY_COMMENT nvarchar(500),
    @AFFILIATEDADDITIONALINFORMATION xml,
    @UNAFFILIATEDADDITIONALINFORMATION xml,
    @EDUCATIONALINVOLVEMENT xml,
    @ALTERNATELOOKUPIDS xml ,
    @EDUCATIONALHISTORY_DATEGRADUATED dbo.UDT_FUZZYDATE,
    @EDUCATIONALHISTORY_DATELEFT dbo.UDT_FUZZYDATE,
    @EDUCATIONALHISTORY_LEVELCODEID uniqueidentifier,
    @EDUCATIONALHISTORY_REASONCODEID uniqueidentifier,
    @ISPRIMARYORGANIZATION bit,
    @PRIMARYBUSINESS_ISPRIMARYORGANIZATION bit,
    @CONSTITUENT_SITEID uniqueidentifier,
    @TAXDECLARATIONS xml,
    @ADDRESS_HISTORICALSTARTDATE date,
    @PRIMARYMEMBER_COPYCONTACTINFO bit,
    @PRIMARYBUSINESS_JOBTITLE nvarchar(100),
    @PRIMARYBUSINESS_JOBCATEGORYCODEID uniqueidentifier,
    @PRIMARYBUSINESS_CAREERLEVELCODEID uniqueidentifier,
    @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID uniqueidentifier,
    @TITLE2CODEID uniqueidentifier,
    @SUFFIX2CODEID uniqueidentifier,
    @SPOUSE_TITLE2CODEID uniqueidentifier,
    @SPOUSE_SUFFIX2CODEID uniqueidentifier,
    @SOLICITCODES xml,
    @PRIMARYBUSINESS_JOBDIVISION nvarchar(100),
    @PRIMARYBUSINESS_JOBDEPARTMENT nvarchar(100),
    @PRIMARYBUSINESS_JOBSCHEDULECODEID uniqueidentifier,
    @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier,
    @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier,
    @PRIMARYBUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST uniqueidentifier,
    @PRIMARYBUSINESS_RECIPROCALTYPECODEIDSIMPLELIST uniqueidentifier,
    @ETHNICITYCODEID uniqueidentifier,
    @RELIGIONCODEID uniqueidentifier,
    @TARGETCODEID uniqueidentifier,
    @INCOMECODEID uniqueidentifier,
    @BIRTHPLACE nvarchar(50),
    @PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
    @PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
    @PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
    @PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
    @PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
    @EMAILADDRESS_STARTDATE date,
    @SPOUSE_EMAILADDRESS_STARTDATE date,
    @PRIMARYBUSINESS_EMAILADDRESS_STARTDATE date,
    @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
    @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
    @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
    @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
    @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
    @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
    @PRIMARYBUSINESS_STARTDATE date,
    @SPOUSE_STARTDATE date,
    @SOCIALMEDIAACCOUNTS xml,
    @EMAILADDRESS_INFOSOURCECODEID uniqueidentifier,
@EMAILADDRESS_INFOSOURCECOMMENTS nvarchar(500),
    @GENDERCODEID uniqueidentifier,
    @SPOUSE_GENDERCODEID uniqueidentifier
) as
    set nocount on;

    declare @CURRENTDATE datetime = getdate();

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

    if @BIRTHDATE is null
      set @BIRTHDATE = '00000000';

    if @GENDERCODE is null
        set @GENDERCODE = 0;

    if @NUMEMPLOYEES is null
        set @NUMEMPLOYEES = 0;

    if @NUMSUBSIDIARIES is null
        set @NUMSUBSIDIARIES = 0;

    if @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE is null
        set @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE = 0;

    if @EDUCATIONALHISTORY_CLASSOF is null
        set @EDUCATIONALHISTORY_CLASSOF = 0;

    if @SPOUSE_GENDERCODE is null
        set @SPOUSE_GENDERCODE = 0;

    if @PRIMARYBUSINESS_NUMEMPLOYEES is null
        set @PRIMARYBUSINESS_NUMEMPLOYEES = 0;

    if @PRIMARYBUSINESS_NUMSUBSIDIARIES is null
        set @PRIMARYBUSINESS_NUMSUBSIDIARIES = 0;

    if ((@ADDRESS_ADDRESSTYPECODEID is not null) or (@ADDRESS_STATEID is not null) or
            (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '') or (coalesce(@ADDRESS_CITY,'') <> '') or
            (coalesce(@ADDRESS_POSTCODE,'') <> '')) and @ADDRESS_COUNTRYID is null
        exec @ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

    if ((@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID is not null) or (@PRIMARYBUSINESS_ADDRESS_STATEID is not null) or
            (coalesce(@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,'') <> '') or (coalesce(@PRIMARYBUSINESS_ADDRESS_CITY,'') <> '') or
            (coalesce(@PRIMARYBUSINESS_ADDRESS_POSTCODE,'') <> '')) and @PRIMARYBUSINESS_ADDRESS_COUNTRYID is null
        exec @PRIMARYBUSINESS_ADDRESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

    if @ADDRESS_DONOTMAIL = 0
        set @ADDRESS_DONOTMAILREASONCODEID = null

    if @PRIMARYBUSINESS_ADDRESS_DONOTMAIL = 0
        set @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID = null

    if @EDUCATIONALHISTORY_COMMENT is null
        set @EDUCATIONALHISTORY_COMMENT = '';

    if @EDUCATIONALHISTORY_PREFERREDCLASSYEAR is null
        set @EDUCATIONALHISTORY_PREFERREDCLASSYEAR = 0;

    if @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE is null
        set @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE = '00000000';

    if @EDUCATIONALHISTORY_DATEGRADUATED is null
        set @EDUCATIONALHISTORY_DATEGRADUATED = '00000000';

    if @EDUCATIONALHISTORY_DATELEFT is null
        set @EDUCATIONALHISTORY_DATELEFT = '00000000';

    if @ISPRIMARYORGANIZATION is null
        set @ISPRIMARYORGANIZATION = 0;

    if @PRIMARYBUSINESS_ISPRIMARYORGANIZATION is null
        set @PRIMARYBUSINESS_ISPRIMARYORGANIZATION = 0;                            

    if @PRIMARYMEMBER_COPYCONTACTINFO is null
        set @PRIMARYMEMBER_COPYCONTACTINFO = 0;

    if @PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0
    begin
      set @PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = 100;
      set @PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID = null;
    end

    if @PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0
    begin
      set @PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = 100;
      set @PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID = null;
    end

    if @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0
    begin
      set @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = 100;
      set @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = null;
    end

    if @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0
    begin
      set @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = 100;
      set @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = null;
    end

    begin try                                
        if @ADDRESS_ENDDATE != '0000' and @ADDRESS_STARTDATE = '0000'
            raiserror('An Address must have a valid seasonal start date if it has a end date.',13,1)
        if @ADDRESS_STARTDATE != '0000' and @ADDRESS_ENDDATE = '0000'
            raiserror('An Address must have a valid seasonal end date if it has a start date.',13,1)

        declare @INVALIDPHONESTARTDATE bit;

        if exists(
          select top 1 ID 
          from dbo.UFN_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_FROMITEMLISTXML(@PHONES)
          where STARTDATE is not null and STARTDATE > dbo.UFN_DATE_GETLATESTTIME(getdate()))
            set @INVALIDPHONESTARTDATE = 1;

        if @INVALIDPHONESTARTDATE = 1
            raiserror('BBERR_CONSTITUENTBATCH_INVALIDPHONESTARTDATE',13,1);

        if @EMAILADDRESS_STARTDATE > dbo.UFN_DATE_GETLATESTTIME(getdate())
          raiserror('BBERR_CONSTITUENTBATCH_INVALIDEMAILSTARTDATE',13,1);

        if @SPOUSE_EMAILADDRESS_STARTDATE > dbo.UFN_DATE_GETLATESTTIME(getdate())
          raiserror('BBERR_CONSTITUENTBATCH_INVALIDSPOUSEEMAILSTARTDATE',13,1);

        if @PRIMARYBUSINESS_EMAILADDRESS_STARTDATE > dbo.UFN_DATE_GETLATESTTIME(getdate())
          raiserror('BBERR_CONSTITUENTBATCH_INVALIDBUSINESSEMAILSTARTDATE',13,1);

        update
            dbo.BATCHCONSTITUENT 
        set
            [ISORGANIZATION] = case when @ISORGANIZATION = 1 then 1 else 0 end,
            [KEYNAME] = @KEYNAME,
            [FIRSTNAME] = @FIRSTNAME,
            [MIDDLENAME] = @MIDDLENAME,
            [MAIDENNAME] = @MAIDENNAME,
            [NICKNAME] = @NICKNAME,
            [TITLECODEID] = @TITLECODEID,
            [SUFFIXCODEID] = @SUFFIXCODEID,
            [TITLE2CODEID] = @TITLE2CODEID,
            [SUFFIX2CODEID] = @SUFFIX2CODEID,
            [BIRTHDATE] = @BIRTHDATE,
            [GENDERCODE] = @GENDERCODE,
            [WEBADDRESS] = @WEBADDRESS,
            [INDUSTRYCODEID] = @INDUSTRYCODEID,
            [PARENTCORPID] = @PARENTCORPID,
            [NUMEMPLOYEES] = @NUMEMPLOYEES,
            [NUMSUBSIDIARIES] = @NUMSUBSIDIARIES,
            [ADDRESS_ADDRESSTYPECODEID] = @ADDRESS_ADDRESSTYPECODEID,
            [ADDRESS_COUNTRYID] = @ADDRESS_COUNTRYID,
            [ADDRESS_ADDRESSBLOCK] = @ADDRESS_ADDRESSBLOCK,
            [ADDRESS_CITY] = @ADDRESS_CITY,
            [ADDRESS_STATEID] = @ADDRESS_STATEID,
            [ADDRESS_POSTCODE] = @ADDRESS_POSTCODE,
            [ADDRESS_DONOTMAIL] = @ADDRESS_DONOTMAIL,
            [EMAILADDRESS_EMAILADDRESSTYPECODEID] = @EMAILADDRESS_EMAILADDRESSTYPECODEID,
            [EMAILADDRESS_EMAILADDRESS] = @EMAILADDRESS_EMAILADDRESS,
            [ADDRESS_CART] = @ADDRESS_CART,
            [ADDRESS_DPC] = @ADDRESS_DPC,
            [ADDRESS_LOT] = @ADDRESS_LOT,
            [ADDRESS_STARTDATE] = @ADDRESS_STARTDATE,
            [ADDRESS_ENDDATE] = @ADDRESS_ENDDATE,
            [PRIMARYADDRESSEE_NAMEFORMATTYPECODEID] = @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID,
            [PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID] = @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID,
            [PRIMARYSALUTATION_NAMEFORMATTYPECODEID] = @PRIMARYSALUTATION_NAMEFORMATTYPECODEID,
            [PRIMARYSALUTATION_NAMEFORMATFUNCTIONID] = @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID,
            [ISDECEASED] = @ISDECEASED,
            [DECEASEDDATE] = @DECEASEDDATE,
            [EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID] = @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
            [EDUCATIONALHISTORY_ISPRIMARYRECORD] = @EDUCATIONALHISTORY_ISPRIMARYRECORD,
            [EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE] = @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
            [EDUCATIONALHISTORY_CLASSOF] = @EDUCATIONALHISTORY_CLASSOF,
            [EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID] = @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID,
            [EDUCATIONALHISTORY_STARTDATE] = @EDUCATIONALHISTORY_STARTDATE,
            [SPOUSEID] = @SPOUSEID,
            [SPOUSE_RELATIONSHIPTYPECODEID] = @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST,
            [SPOUSE_RECIPROCALTYPECODEID] = @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST,
            [SPOUSE_KEYNAME] = @SPOUSE_KEYNAME,
            [SPOUSE_FIRSTNAME] = @SPOUSE_FIRSTNAME,
            [SPOUSE_MIDDLENAME] = @SPOUSE_MIDDLENAME,
            [SPOUSE_MAIDENNAME] = @SPOUSE_MAIDENNAME,
            [SPOUSE_NICKNAME] = @SPOUSE_NICKNAME,
            [SPOUSE_TITLECODEID] = @SPOUSE_TITLECODEID,
            [SPOUSE_SUFFIXCODEID] = @SPOUSE_SUFFIXCODEID,
            [SPOUSE_TITLE2CODEID] = @SPOUSE_TITLE2CODEID,
            [SPOUSE_SUFFIX2CODEID] = @SPOUSE_SUFFIX2CODEID,
            [SPOUSE_GENDERCODE] = @SPOUSE_GENDERCODE,
            [SPOUSE_BIRTHDATE] = @SPOUSE_BIRTHDATE,
            [SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID] = @SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID,
            [SPOUSE_EMAILADDRESS_EMAILADDRESS] = @SPOUSE_EMAILADDRESS_EMAILADDRESS,
            [SPOUSE_COPY_ADDRESS] = @SPOUSE_COPY_ADDRESS,
            [SPOUSE_COPY_PHONENUMBER] = @SPOUSE_COPY_PHONENUMBER,
            [SPOUSE_COPY_EMAILADDRESS] = @SPOUSE_COPY_EMAILADDRESS,
            [PRIMARYBUSINESSID] = @PRIMARYBUSINESSID,
            [PRIMARYBUSINESS_RELATIONSHIPTYPECODEID] = @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID,
            [PRIMARYBUSINESS_RECIPROCALTYPECODEID] = @PRIMARYBUSINESS_RECIPROCALTYPECODEID,
            [PRIMARYBUSINESS_KEYNAME] = @PRIMARYBUSINESS_KEYNAME,
            [PRIMARYBUSINESS_PHONE_PHONETYPECODEID] = @PRIMARYBUSINESS_PHONE_PHONETYPECODEID,
            [PRIMARYBUSINESS_PHONE_NUMBER] = @PRIMARYBUSINESS_PHONE_NUMBER,
            [PRIMARYBUSINESS_PHONE_COUNTRYID] = @PRIMARYBUSINESS_PHONE_COUNTRYID,
            [PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID] = @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID,
            [PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS] = @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,
            [PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID] = @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID,
            [PRIMARYBUSINESS_ADDRESS_COUNTRYID] = @PRIMARYBUSINESS_ADDRESS_COUNTRYID,
            [PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK] = @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,
            [PRIMARYBUSINESS_ADDRESS_CITY] = @PRIMARYBUSINESS_ADDRESS_CITY,
            [PRIMARYBUSINESS_ADDRESS_STATEID] = @PRIMARYBUSINESS_ADDRESS_STATEID,
            [PRIMARYBUSINESS_ADDRESS_POSTCODE] = @PRIMARYBUSINESS_ADDRESS_POSTCODE,
            [PRIMARYBUSINESS_ADDRESS_CART] = @PRIMARYBUSINESS_ADDRESS_CART,
            [PRIMARYBUSINESS_ADDRESS_DPC] = @PRIMARYBUSINESS_ADDRESS_DPC,
            [PRIMARYBUSINESS_ADDRESS_LOT] = @PRIMARYBUSINESS_ADDRESS_LOT,
            [PRIMARYBUSINESS_ADDRESS_DONOTMAIL] = @PRIMARYBUSINESS_ADDRESS_DONOTMAIL,
            [PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID] = @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID,
            [PRIMARYBUSINESS_INDUSTRYCODEID] = @PRIMARYBUSINESS_INDUSTRYCODEID,
            [PRIMARYBUSINESS_NUMEMPLOYEES] = @PRIMARYBUSINESS_NUMEMPLOYEES,
            [PRIMARYBUSINESS_NUMSUBSIDIARIES] = @PRIMARYBUSINESS_NUMSUBSIDIARIES,
            [PRIMARYBUSINESS_WEBADDRESS] = @PRIMARYBUSINESS_WEBADDRESS,
            [PRIMARYBUSINESS_PARENTCORPID] = @PRIMARYBUSINESS_PARENTCORPID,
            [CONSTIT_SECURITY_ATTRIBUTEID] = @CONSTIT_SECURITY_ATTRIBUTEID,
            [CUSTOMID] = @CUSTOMIDENTIFIER,
            -- ISORGANIZATION = {0 => Individual, 1 => Organization, 2 => Household, 3 => Custom group}

            [ISGROUP] = case when (@ISORGANIZATION = 2 or @ISORGANIZATION = 3) then 1 else 0 end,
            [GROUPTYPECODE] = case 
              when @ISORGANIZATION = 2 then 0
              when @ISORGANIZATION = 3 then 1
              else 0
            end,
            [GROUPTYPEID] = @GROUPTYPEID,
            [GROUPSTARTDATE] = @GROUPSTARTDATE,
            [GROUPDESCRIPTION] = @GROUPDESCRIPTION,
            [SEQUENCE] = @SEQUENCE,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE,
            [ADDRESS_COUNTYCODEID] = @ADDRESS_COUNTYCODEID,
            [ADDRESS_OMITFROMVALIDATION] = @ADDRESS_OMITFROMVALIDATION,
            [ADDRESS_CONGRESSIONALDISTRICTCODEID] = @ADDRESS_CONGRESSIONALDISTRICTCODEID,
            [ADDRESS_STATEHOUSEDISTRICTCODEID] = @ADDRESS_STATEHOUSEDISTRICTCODEID,
            [ADDRESS_STATESENATEDISTRICTCODEID] = @ADDRESS_STATESENATEDISTRICTCODEID,
            [ADDRESS_LOCALPRECINCTCODEID] = @ADDRESS_LOCALPRECINCTCODEID,
            [ADDRESS_INFOSOURCECODEID] = @ADDRESS_INFOSOURCECODEID,
            [ADDRESS_REGIONCODEID] = @ADDRESS_REGIONCODEID,
            [ADDRESS_LASTVALIDATIONATTEMPTDATE] = @ADDRESS_LASTVALIDATIONATTEMPTDATE,
            [ADDRESS_VALIDATIONMESSAGE] = @ADDRESS_VALIDATIONMESSAGE,
            [ADDRESS_CERTIFICATIONDATA] = @ADDRESS_CERTIFICATIONDATA,
            [ADDRESS_DONOTMAILREASONCODEID] = @ADDRESS_DONOTMAILREASONCODEID,
            [PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID] = @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID,
            [EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID] = @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID,
            [EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID] = @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID,
            [EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID] = @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID,
            [EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID] = @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
            [EDUCATIONALHISTORY_PREFERREDCLASSYEAR] = @EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
            [EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID] = @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
            [EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE] = @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
            [EDUCATIONALHISTORY_COMMENT] = @EDUCATIONALHISTORY_COMMENT,
            [EDUCATIONALHISTORY_DATEGRADUATED] = @EDUCATIONALHISTORY_DATEGRADUATED,
            [EDUCATIONALHISTORY_DATELEFT] = @EDUCATIONALHISTORY_DATELEFT,
            [EDUCATIONALHISTORY_LEVELCODEID] = @EDUCATIONALHISTORY_LEVELCODEID,
            [EDUCATIONALHISTORY_REASONCODEID] = @EDUCATIONALHISTORY_REASONCODEID,
            [PRIMARYBUSINESS_ISPRIMARYORGANIZATION] = @PRIMARYBUSINESS_ISPRIMARYORGANIZATION,
            [ISPRIMARYORGANIZATION] = @ISPRIMARYORGANIZATION,
            [CONSTITUENT_SITEID] = @CONSTITUENT_SITEID,
            [ADDRESS_HISTORICALSTARTDATE] = @ADDRESS_HISTORICALSTARTDATE,
            [PRIMARYMEMBER_COPYCONTACTINFO] = @PRIMARYMEMBER_COPYCONTACTINFO,
            [PRIMARYBUSINESS_JOBTITLE] = @PRIMARYBUSINESS_JOBTITLE,
            [PRIMARYBUSINESS_JOBCATEGORYCODEID] = @PRIMARYBUSINESS_JOBCATEGORYCODEID,
            [PRIMARYBUSINESS_CAREERLEVELCODEID] = @PRIMARYBUSINESS_CAREERLEVELCODEID,
            [PRIMARYBUSINESS_JOBDIVISION] = @PRIMARYBUSINESS_JOBDIVISION,
            [PRIMARYBUSINESS_JOBDEPARTMENT] = @PRIMARYBUSINESS_JOBDEPARTMENT,
            [PRIMARYBUSINESS_JOBSCHEDULECODEID] = @PRIMARYBUSINESS_JOBSCHEDULECODEID,
            [ETHNICITYCODEID] = @ETHNICITYCODEID,
            [RELIGIONCODEID] = @RELIGIONCODEID,
            [TARGETCODEID] = @TARGETCODEID,
            [INCOMECODEID] = @INCOMECODEID,
            [BIRTHPLACE] = @BIRTHPLACE,
            [PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS] = coalesce(@PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0),
            [PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR] = coalesce(@PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR, 100),
            [PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS] = coalesce(@PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0),
            [PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR] = coalesce(@PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR, 100),
            [PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID] = @PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID,
            [PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID] = @PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
            [EMAILADDRESS_STARTDATE] = @EMAILADDRESS_STARTDATE,
            [SPOUSE_EMAILADDRESS_STARTDATE] = @SPOUSE_EMAILADDRESS_STARTDATE,
            [PRIMARYBUSINESS_EMAILADDRESS_STARTDATE] = @PRIMARYBUSINESS_EMAILADDRESS_STARTDATE,
            [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,
            [PRIMARYBUSINESS_STARTDATE] = @PRIMARYBUSINESS_STARTDATE,
            [SPOUSE_STARTDATE] = @SPOUSE_STARTDATE,
            [EMAILADDRESS_INFOSOURCECODEID] = @EMAILADDRESS_INFOSOURCECODEID,
            [EMAILADDRESS_INFOSOURCECOMMENTS] = @EMAILADDRESS_INFOSOURCECOMMENTS,
            [GENDERCODEID] = @GENDERCODEID,
            [SPOUSE_GENDERCODEID] = @SPOUSE_GENDERCODEID
        where
          ID = @ID;

        exec dbo.USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_UPDATEFROMXML @ID, @PHONES, @CHANGEAGENTID;

        declare @USERDEFINEDCONSTITUENCY xml;
        set @USERDEFINEDCONSTITUENCY = 
            (
                select
                    CONSTITUENCY.c.query('.')
                from
                    @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                    inner join dbo.CONSTITUENCYDEFINITION on CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = CONSTITUENCYDEFINITION.ID
                where
                    CONSTITUENCYDEFINITION.ISSYSTEM = 0
                for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
            );
        exec dbo.USP_CONSTITUENTBATCH_GETCONSTITUENCIES_UPDATEFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;

        declare @SYSTEMDEFINEDCONSTITUENCY xml;
        set @SYSTEMDEFINEDCONSTITUENCY =
            (
                select
                    CONSTITUENCY.c.query('.')
                from
                    @CONSTITUENCY.nodes('/CONSTITUENCY/ITEM') CONSTITUENCY(c)
                    inner join dbo.CONSTITUENCYDEFINITION on CONSTITUENCY.c.value('(CONSTITUENCYCODEID)[1]', 'uniqueidentifier') = CONSTITUENCYDEFINITION.ID
                where
                    CONSTITUENCYDEFINITION.ISSYSTEM = 1
                for xml raw(''),type,elements,root('CONSTITUENCY'),BINARY BASE64
            );
        exec dbo.USP_CONSTITUENTBATCH_GETCONSTITUENCIES_SYSTEM_UPDATEFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

        exec dbo.USP_CONSTITUENTBATCH_GETINTERESTS_UPDATEFROMXML @ID, @INTEREST, @CHANGEAGENTID;
        exec dbo.USP_CONSTITUENTBATCH_GETGROUPMEMBERS_UPDATEFROMXML @ID, @GROUPMEMBERS, @CHANGEAGENTID;

        -- This checks prevents the generated UPDATEFROMXML sproc from removing UNAFFILIATED information 

        -- since the fields share the same row in the database

        if @AFFILIATEDADDITIONALINFORMATION is not null or (@AFFILIATEDADDITIONALINFORMATION is null and @UNAFFILIATEDADDITIONALINFORMATION is null)
          exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONAFFILIATEDADDITIONALINFORMATION_UPDATEFROMXML @ID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;

        -- This checks prevents the generated UPDATEFROMXML sproc from removing AFFILIATED information         

        -- since the fields share the same row in the database                

        if @UNAFFILIATEDADDITIONALINFORMATION is not null or (@AFFILIATEDADDITIONALINFORMATION is null and @UNAFFILIATEDADDITIONALINFORMATION is null)
          exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONUNAFFILIATEDADDITIONALINFORMATION_UPDATEFROMXML @ID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;

        exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONALINVOLVEMENT_UPDATEFROMXML @ID, @EDUCATIONALINVOLVEMENT, @CHANGEAGENTID;
        exec dbo.USP_CONSTITUENTBATCH_GETBATCHALTERNATELOOKUPIDS_UPDATEFROMXML @ID, @ALTERNATELOOKUPIDS, @CHANGEAGENTID;
        exec dbo.USP_CONSTITUENTBATCH_GETTAXDECLARATIONS_UPDATEFROMXML @ID, @TAXDECLARATIONS, @CHANGEAGENTID;
        exec dbo.USP_CONSTITUENTBATCH_GETSOLICITCODES_UPDATEFROMXML @ID, @SOLICITCODES, @CHANGEAGENTID;
        exec dbo.USP_CONSTITUENTBATCH_GETSOCIALMEDIAACCOUNTS_UPDATEFROMXML @ID, @SOCIALMEDIAACCOUNTS, @CHANGEAGENTID;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;