USP_ADD_BATCHCONSTITUENTUPDATEBATCHROW

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

declare @CURRENTDATE datetime;

if @ID is null
    set @ID = newid();

-- Check to see if this batch row is being added as part of an exception batch.

-- Used when populating ROWORIGINCODE in contact information collection fields.

declare @ISEXCEPTIONBATCH bit = 0;
select @ISEXCEPTIONBATCH = case when ORIGINATINGBATCHID is null then 0 else 1 end from dbo.BATCH where ID = @BATCHID;

-- set for use on duplicate check

set @DUPLICATERECORDID = @ID;

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

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

if @CONSTITUENT_SITEID is not null and @CONSTITUENT_SITES is null
    set @CONSTITUENT_SITES = 
    (
        select @CONSTITUENT_SITEID SITEID
        for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
    )

if LEN(@SPOUSE_IMPORTLOOKUPID) > 0
begin
    set @SPOUSE_LOOKUPID = @SPOUSE_IMPORTLOOKUPID;

    if @SPOUSE_ID is null
    begin
        with LOOKUPIDS_CTE as (
            select top(2)
                C.ID ID
            from
                dbo.CONSTITUENT C
            where
                LOOKUPID = @SPOUSE_IMPORTLOOKUPID
            union all
            select top(2)
                C.ID
            from
                dbo.ALTERNATELOOKUPID AL
            inner join
                dbo.CONSTITUENT C on C.ID = AL.CONSTITUENTID
            left join
                dbo.ALTERNATELOOKUPIDTYPECODE ALTC on ALTC.ID = AL.ALTERNATELOOKUPIDTYPECODEID
            where
                AL.ALTERNATELOOKUPID = @SPOUSE_IMPORTLOOKUPID
        )
        select
                distinct @SPOUSE_ID = ID
        from
                LOOKUPIDS_CTE

        if @@ROWCOUNT > 1
            raiserror('BBERR_MULTIPLESPOUSESWITHLOOKUPID: More than one constituent exists with the supplied spouse lookup ID.', 13, 1);
    end
end

-- BBNT\RobertDi 5/6/2013 Bug 267901

-- When importing, be sure we don't accidentally blank out the gender.


--If not matched, bring constituent type forward.  It will default to 0 if none.

--If matched determine existing type.  Doesn't matter what is in file.  Type of existing one cannot be changed.

--Combine with selection for @GENDERCODE to optimize

if @ROWFROMBATCHUI = 0 
begin
        if @PRIMARYRECORDID is not null
             select @GENDERCODE = 
             case coalesce(@GENDERCODE,0)
                        when 0 THEN C.GENDERCODE
                        ELSE @GENDERCODE
                end,
                @GENDERCODEID = case when C.GENDERCODEID is null or (C.GENDERCODEID = (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0)) and @GENDERCODEID is not null) 
                                                then @GENDERCODEID 
                                                when C.GENDERCODEID is not null and @GENDERCODEID is not null and @GENDERCODEID <> (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0))
                                                then @GENDERCODEID
                                                else C.GENDERCODEID end,
                @CONSTITUENTTYPECODE = 
             case
                        when C.ISORGANIZATION = 1 then 1
                        when (C.ISGROUP = 1 and G.GROUPTYPECODE = 0) then 2
                        when (C.ISGROUP = 1 and G.GROUPTYPECODE = 1) then 3
                        else 0
end
             from dbo.CONSTITUENT C 
             left join dbo.GROUPDATA G on G.ID = C.ID
             where C.ID = @PRIMARYRECORDID;

         if @SPOUSE_ID is null
                select @SPOUSE_ID = (select RECIPROCALCONSTITUENTID from dbo.CONSTITUENT 
                inner join dbo.RELATIONSHIP  on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                where CONSTITUENT.ID = @PRIMARYRECORDID and RELATIONSHIP.ISSPOUSE = 1)

         if coalesce(@SPOUSE_GENDERCODE, 0) = 0
    select @SPOUSE_GENDERCODE = CONSTITUENT.GENDERCODE
    from dbo.CONSTITUENT where CONSTITUENT.ID = @SPOUSE_ID;

    select @SPOUSE_GENDERCODEID = case when GENDERCODEID is null or (GENDERCODEID = (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0)) and @SPOUSE_GENDERCODEID is not null) 
                                                                then @SPOUSE_GENDERCODEID 
                                                                when GENDERCODEID is not null and @SPOUSE_GENDERCODEID is not null and @SPOUSE_GENDERCODEID <> (select dbo.UFN_GENDERCODEDEFAULTMAPPING_GETGENDERCODEID(0))
                                                                then @SPOUSE_GENDERCODEID
                                                                else GENDERCODEID end
    from dbo.CONSTITUENT where CONSTITUENT.ID = @SPOUSE_ID;
end

--bug fix for ALTRU 305484

if @SIMILARADDRESSCODE is null
    set @SIMILARADDRESSCODE = 3
if @UNSIMILARADDRESSCODE is null
    set @UNSIMILARADDRESSCODE = 3
if @NEWADDRESSENDDATECODE is null
    set @NEWADDRESSENDDATECODE = 0
if @NEWADDRESSPRIMARYCODE is null
    set @NEWADDRESSPRIMARYCODE = 1
if @BIRTHDATERULECODE is null
    set @BIRTHDATERULECODE = 1
if @DIFFERENTPHONECODE is null
    set @DIFFERENTPHONECODE = 3
if @NEWPHONEENDDATECODE is null
    set @NEWPHONEENDDATECODE = 0
if @NEWPHONEPRIMARYCODE is null
    set @NEWPHONEPRIMARYCODE = 1
if @DIFFERENTEMAILCODE is null
    set @DIFFERENTEMAILCODE = 3
if @NEWEMAILENDDATECODE is null
    set @NEWEMAILENDDATECODE = 0
if @NEWEMAILPRIMARYCODE is null
    set @NEWEMAILPRIMARYCODE = 1
if @USEGLOBALSETTINGS is null
    set @USEGLOBALSETTINGS = 1
if @NAMECODE is null
    set @NAMECODE = 1
if @CREATEHISTORICALNAMECODE is null
    set @CREATEHISTORICALNAMECODE = 1

set @CURRENTDATE = getdate();

-- If primary record is provided during import, we need to check the existing values 

-- against the imported values to determine if capitalization should be preserved.

if @ROWFROMBATCHUI = 0 and @PRIMARYRECORDID is not null
begin
    declare @CURRENT_FIRSTNAME nvarchar(50)
    declare @CURRENT_KEYNAME nvarchar(100)

    select
        @CURRENT_FIRSTNAME = FIRSTNAME,
        @CURRENT_KEYNAME = KEYNAME
    from
        dbo.CONSTITUENT
    where
        ID = @PRIMARYRECORDID

    -- If the original names match those in the database case-sensitive

    -- then we should store the original values

    if (@CURRENT_KEYNAME = @ORIGINAL_KEYNAME COLLATE Latin1_General_CS_AS) set @KEYNAME = @CURRENT_KEYNAME
    if (@CURRENT_FIRSTNAME = @ORIGINAL_FIRSTNAME COLLATE Latin1_General_CS_AS) set @FIRSTNAME = @CURRENT_FIRSTNAME
end

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

-- Default in the ID of any existing alternate lookup IDs

set @ALTERNATELOOKUPIDS = 
(
    select
        INPUTALTERNATELOOKUPID.ALTERNATELOOKUPID,
        INPUTALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID,
        INPUTALTERNATELOOKUPID.ID,
        coalesce(INPUTALTERNATELOOKUPID.ORIGINALALTERNATELOOKUPID, ALTERNATELOOKUPID.ID) as ORIGINALALTERNATELOOKUPID
    from dbo.UFN_BATCHCONSTITUENTUPDATE_GETALTERNATELOOKUPIDS_FROMITEMLISTXML(@ALTERNATELOOKUPIDS) as INPUTALTERNATELOOKUPID
    left join dbo.ALTERNATELOOKUPID on
        ALTERNATELOOKUPID.ALTERNATELOOKUPID = INPUTALTERNATELOOKUPID.ALTERNATELOOKUPID and
        ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID = INPUTALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID and
        ALTERNATELOOKUPID.CONSTITUENTID = @PRIMARYRECORDID
    for xml raw('ITEM'),type,elements,root('ALTERNATELOOKUPIDS'),BINARY BASE64
)

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

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

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

insert into dbo.BATCHCONSTITUENTUPDATEINTERESTS (
    BATCHCONSTITUENTUPDATEID,
    CONSTITUENTINTERESTCODEID,
    NOTE,
    CONSTITUENTINTERESTID,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
)
select 
    @ID,
    CONSTITUENTINTERESTCODEID,
    coalesce(NOTE, ''),
    CONSTITUENTINTERESTID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
from
    dbo.UFN_BATCHCONSTITUENTUPDATE_GETINTERESTS_FROMITEMLISTXML(@INTERESTS)

insert into dbo.BATCHCONSTITUENTUPDATEADDRESSES (
    ID,
    BATCHCONSTITUENTUPDATEID,
    ADDRESSID,
    UPDATEHOUSEHOLD,
    ADDRESSBLOCK,
    ENDDATE,
    STARTDATE,
    HISTORICALSTARTDATE,
    HISTORICALENDDATE,
    ADDRESSTYPECODEID,
    CART,
    CERTIFICATIONDATA,
    CITY,
    CONGRESSIONALDISTRICTCODEID,
    COUNTRYID,
    COUNTYCODEID,
    DONOTMAIL,
    DONOTMAILREASONCODEID,
    DPC,
    INFOSOURCECODEID,
    INFOSOURCECOMMENTS,
    ISPRIMARY,
    LASTVALIDATIONATTEMPTDATE,
    LOCALPRECINCTCODEID,
    LOT,
    OMITFROMVALIDATION,
    REGIONCODEID,
    STATEID,
    STATEHOUSEDISTRICTCODEID,
    STATESENATEDISTRICTCODEID,
    VALIDATIONMESSAGE,
    POSTCODE,
    SEQUENCE,
    ROWORIGINCODE,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED
)
select
    (select case when ID = '00000000-0000-0000-0000-000000000000' then newid() else coalesce(ID, newid()) end),
    @ID,
    ADDRESSID,
    coalesce(UPDATEHOUSEHOLD, 0),
    coalesce(ADDRESSBLOCK, ''),
    coalesce(ENDDATE, '0000'),
    coalesce(STARTDATE, '0000'),
    HISTORICALSTARTDATE,
    HISTORICALENDDATE,
    ADDRESSTYPECODEID,
    coalesce(CART, ''),
    coalesce(CERTIFICATIONDATA, '0'),
    coalesce(CITY, ''),
    CONGRESSIONALDISTRICTCODEID,
    COUNTRYID,
    COUNTYCODEID,
    coalesce(DONOTMAIL, 0),
    DONOTMAILREASONCODEID,
    coalesce(DPC, ''),
    INFOSOURCECODEID,
    coalesce(INFOSOURCECOMMENTS, ''),
    coalesce(ISPRIMARY, ''),
    LASTVALIDATIONATTEMPTDATE,
    LOCALPRECINCTCODEID,
    coalesce(LOT, ''),
    coalesce(OMITFROMVALIDATION, 0),
    REGIONCODEID,
    STATEID,
    STATEHOUSEDISTRICTCODEID,
    STATESENATEDISTRICTCODEID,
    coalesce(VALIDATIONMESSAGE, ''),
    coalesce(POSTCODE, ''),
    coalesce(SEQUENCE, 0),
    case when @ISEXCEPTIONBATCH = 1 then coalesce(ROWORIGINCODE, 0) else 1 end, -- Row in the collection field XML at time of batch save

    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE
from
    dbo.UFN_BATCHCONSTITUENTUPDATE_GETADDRESSES_WITHDATES_FROMITEMLISTXML(@ADDRESSES) as ADDRESSFROMXML;


insert into dbo.BATCHCONSTITUENTUPDATEPHONES (
    ID,
    BATCHCONSTITUENTUPDATEID,
    PHONETYPECODEID,
    NUMBER,
    ISPRIMARY,
    DONOTCALL,
    UPDATEHOUSEHOLD,
    SEQUENCE,
    COUNTRYID, 
    SEASONALSTARTDATE,
    SEASONALENDDATE,
    STARTTIME,
    ENDTIME,
    STARTDATE,
    ENDDATE,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED,
    INFOSOURCECODEID, 
    PHONEID,
    ROWORIGINCODE,
    DONOTTEXT,
    INFOSOURCECOMMENTS
)
select 
    (select case when ID = '00000000-0000-0000-0000-000000000000' then newid() else coalesce(ID, newid()) end),
    @ID,
    PHONETYPECODEID,
    coalesce(NUMBER, ''),
    coalesce(ISPRIMARY, 0),
    coalesce(DONOTCALL, 0),
    coalesce(UPDATEHOUSEHOLD, 0),
    coalesce(SEQUENCE, 0),
    COUNTRYID,
    coalesce(SEASONALSTARTDATE,'0000'),
    coalesce(SEASONALENDDATE,'0000'),
    coalesce(STARTTIME,'0000'),
    coalesce(ENDTIME,'0000'),
    STARTDATE,
    ENDDATE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    INFOSOURCECODEID,
    PHONEID,
    case when @ISEXCEPTIONBATCH = 1 then coalesce(ROWORIGINCODE, 0) else 1 end ,-- Row in the collection field XML at time of batch save

    coalesce(DONOTTEXT, cast(0 as bit)),
    ISNULL(INFOSOURCECOMMENTS,'')
from
    dbo.UFN_BATCHCONSTITUENTUPDATE_GETPHONES_WITHDATES_FROMITEMLISTXML(@PHONES)

insert into dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES (
    ID,
    BATCHCONSTITUENTUPDATEID,
    EMAILADDRESSTYPECODEID,
    EMAILADDRESS,
    ISPRIMARY,
    DONOTEMAIL,
    UPDATEHOUSEHOLD,
    SEQUENCE,
    STARTDATE,
    ENDDATE,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED,
    INFOSOURCECODEID,
    EMAILADDRESSID,
    ROWORIGINCODE,
    DONOTEMAILREASONCODEID,
    INFOSOURCECOMMENTS
)
select 
    (select case when ID = '00000000-0000-0000-0000-000000000000' then newid() else coalesce(ID, newid()) end),
    @ID,
    EMAILADDRESSTYPECODEID,
    coalesce(EMAILADDRESS, ''),
    coalesce(ISPRIMARY, 0),
    coalesce(DONOTEMAIL, 0),
    coalesce(UPDATEHOUSEHOLD, 0),
    coalesce(SEQUENCE, 0),
    STARTDATE,
    ENDDATE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    INFOSOURCECODEID,
    EMAILADDRESSID,
    case when @ISEXCEPTIONBATCH = 1 then coalesce(ROWORIGINCODE, 0) else 1 end, -- Row in the collection field XML at time of batch save

    DONOTEMAILREASONCODEID,
    INFOSOURCECOMMENTS
from
    dbo.UFN_BATCHCONSTITUENTUPDATE_GETEMAILADDRESSES_WITHDATES_2_FROMITEMLISTXML(@EMAILADDRESSES)


IF ISNULL(@BBNCUSERID, 0) <> 0
BEGIN
    insert into dbo.BBNCDOWNLOADEDTRANSACTION 
    (
        [ID]
    )
    values 
    (
        @BBNCID
    );

    insert into dbo.BATCHCONSTITUENTUPDATEBBNCINFO (
        BATCHCONSTITUENTUPDATEID,
        BBNCTRANID,
        BBNCID,
        PAGEID,
        PAGENAME,
        BBNCUSERID,
        NETCOMMUNITYTRANSACTIONPROCESSORID,
        SUBMITTEDLOOKUPID,
        SUBMITTEDCLASSYEAR,
        SUBMITTEDEDUCATIONALINSTITUTION,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select 
        @ID,
        @BBNCTRANID,
        @BBNCID,
        @PAGEID,
        @PAGENAME,
        @BBNCUSERID,
        @NETCOMMUNITYTRANSACTIONPROCESSORID,
        coalesce(@SUBMITTEDLOOKUPID , ''),
        coalesce(@SUBMITTEDCLASSYEAR , ''),
        coalesce(@SUBMITTEDEDUCATIONALINSTITUTION , ''),
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE

    delete from dbo.BBNCUNSUCCESSFULDOWNLOADTRANSACTION
        where ID = @BBNCID;
END

--Bug 198040 - AdamBu - 11/14/12 - When importing, constituencies now are always additive.

if @ROWFROMBATCHUI = 0
begin
    declare @CONSTITUENCYTABLE table(
        CONSTITUENCYCODEID uniqueidentifier,
        DATEFROM datetime,
        DATETO datetime,
        ORIGINALCONSTITUENCYID uniqueidentifier
    );
    insert into @CONSTITUENCYTABLE
    select
        CONSTITUENCYFROMXML.CONSTITUENCYCODEID,
        CONSTITUENCYFROMXML.DATEFROM,
        CONSTITUENCYFROMXML.DATETO,
        CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID
    from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) CONSTITUENCYFROMXML

    union

    select
        CONSTITUENCYFROMDATABASE.CONSTITUENCYCODEID,
        CONSTITUENCYFROMDATABASE.DATEFROM,
        CONSTITUENCYFROMDATABASE.DATETO,
        CONSTITUENCYFROMDATABASE.ORIGINALCONSTITUENCYID
    from dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_FORUPDATEBATCH(@PRIMARYRECORDID) CONSTITUENCYFROMDATABASE;

    set @CONSTITUENCIES = (
        select
            CONSTITUENCYCODEID,
            DATEFROM,
            DATETO,
            ORIGINALCONSTITUENCYID
        from @CONSTITUENCYTABLE
        for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64            
    );
end

declare @USERDEFINEDCONSTITUENCY xml;
set @USERDEFINEDCONSTITUENCY = 
(
    select
        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
            when CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID is not null then CONSTITUENCYFROMXML.ORIGINALCONSTITUENCYID            
            else null
        end ORIGINALCONSTITUENCYID
    from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) as CONSTITUENCYFROMXML
        left join dbo.CONSTITUENCYDEFINITION
        on CONSTITUENCYFROMXML.CONSTITUENCYCODEID = CONSTITUENCYDEFINITION.ID
        and CONSTITUENCYDEFINITION.ISSYSTEM = 1
    where
                CONSTITUENCYDEFINITION.ID is null
    for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64
);
exec dbo.USP_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_ADDFROMXML @ID, @USERDEFINEDCONSTITUENCY, @CHANGEAGENTID;

declare @SYSTEMDEFINEDCONSTITUENCY xml;
set @SYSTEMDEFINEDCONSTITUENCY = 
(
    select
        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_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

-- Defaulting DATEFROM/DATETO if possible for constituency

if @PRIMARYRECORDID is not null
begin
    declare @ALLCONSTITUENCY xml = dbo.UFN_CONSTITUENTUPDATEBATCH_GETCONSTITUENCIES_TOITEMLISTXML(@ID);
    if @ALLCONSTITUENCY is not null
    begin
        exec USP_CONSTITUENTUPDATEBATCH_ADJUSTCONSTITUENCYDATERANGE @ID, @PRIMARYRECORDID, @ALLCONSTITUENCY, @CHANGEAGENTID;
    end
end

--spouse alternate lookup IDs

-- if importing IDs but no spouse was specified raise an error

if len(@SPOUSE_LASTNAME) = 0 and @SPOUSE_ID is null 
if exists(select ID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSPOUSEALTERNATELOOKUPIDS_FROMITEMLISTXML(@SPOUSE_ALTERNATELOOKUPIDS))
    raiserror('BBERR_NOSPOUSE_SPOUSEALTERNATE', 13, 1);

if @PRIMARYRECORDID is not null and @SPOUSE_ID is not null
begin
    if exists(select 1 from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @PRIMARYRECORDID and RECIPROCALCONSTITUENTID <> @SPOUSE_ID and ISSPOUSE = 1)
    begin
        raiserror('BBERR_INCORRECTSPOUSE', 13, 1);
    end
end

--Default in the ID of any existing alternate lookup IDs

set @SPOUSE_ALTERNATELOOKUPIDS = 
(
    select
        INPUTALTERNATELOOKUPID.ALTERNATELOOKUPID,
        INPUTALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID,
        INPUTALTERNATELOOKUPID.ID,
        coalesce(INPUTALTERNATELOOKUPID.ORIGINALALTERNATELOOKUPID, ALTERNATELOOKUPID.ID) as ORIGINALALTERNATELOOKUPID
    from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSPOUSEALTERNATELOOKUPIDS_FROMITEMLISTXML(@SPOUSE_ALTERNATELOOKUPIDS) as INPUTALTERNATELOOKUPID
    left join dbo.ALTERNATELOOKUPID on
        ALTERNATELOOKUPID.ALTERNATELOOKUPID = INPUTALTERNATELOOKUPID.ALTERNATELOOKUPID and
        ALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID = INPUTALTERNATELOOKUPID.ALTERNATELOOKUPIDTYPECODEID and
        ALTERNATELOOKUPID.CONSTITUENTID = @SPOUSE_ID
    for xml raw('ITEM'),type,elements,root('SPOUSE_ALTERNATELOOKUPIDS'),BINARY BASE64
)

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

exec dbo.USP_BATCHCONSTITUENTUPDATE_GETSOCIALMEDIAACCOUNTS_ADDFROMXML @ID, @SOCIALMEDIAACCOUNTS, @CHANGEAGENTID;

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

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

exec dbo.USP_CONSTITUENTUPDATEBATCH_GETSOLICITCODES_ADDFROMXML_DEFAULTID @ID, @SOLICITCODES, @CHANGEAGENTID;

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

return 0;