USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTDUPLICATEMATCH

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@LOOKUPID	nvarchar(50)	INOUT
@FIRSTNAME	nvarchar(50)	INOUT
@MIDDLENAME	nvarchar(50)	INOUT
@LASTNAME	nvarchar(100)	INOUT
@SUFFIXCODEID	uniqueidentifier	INOUT
@TITLECODEID	uniqueidentifier	INOUT
@MAIDENNAME	nvarchar(100)	INOUT
@NICKNAME	nvarchar(50)	INOUT
@GENDERCODE	tinyint	INOUT
@BIRTHDATE	UDT_FUZZYDATE	INOUT
@ADDRESSID	uniqueidentifier	INOUT
@ADDRESSTYPECODEID	uniqueidentifier	INOUT
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT
@ADDRESS_CITY	nvarchar(50)	INOUT
@ADDRESS_STATEID	uniqueidentifier	INOUT
@ADDRESS_POSTCODE	nvarchar(12)	INOUT
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT
@PHONEID	uniqueidentifier	INOUT
@PHONETYPECODEID	uniqueidentifier	INOUT
@PHONENUMBER	nvarchar(100)	INOUT
@EMAILID	uniqueidentifier	INOUT
@EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT
@CREATEDON	datetime	INOUT
@DATECHANGED	datetime	INOUT
@ADDRESSES	xml	INOUT
@PHONES	xml	INOUT
@EMAILS	xml	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSELOOKUPID	nvarchar(50)	INOUT
@SPOUSESTARTDATE	datetime	INOUT
@SPOUSEENDDATE	datetime	INOUT
@HOUSEHOLDNAME	nvarchar(154)	INOUT
@HOUSEHOLDLOOKUPID	nvarchar(50)	INOUT
@BUSINESSNAME	nvarchar(154)	INOUT
@BUSINESSLOOKUPID	nvarchar(50)	INOUT
@CONSTITUENTACTION	tinyint	INOUT
@ADDRESSACTION	tinyint	INOUT
@EMAILACTION	tinyint	INOUT
@PHONEACTION	tinyint	INOUT
@ISORGANIZATION	bit	INOUT
@INDUSTRYCODEID	uniqueidentifier	INOUT
@NUMEMPLOYEES	int	INOUT
@NUMSUBSIDIARIES	int	INOUT
@DECEASED	bit	INOUT
@DECEASEDDATE	UDT_FUZZYDATE	INOUT
@GIVESANONYMOUSLY	bit	INOUT
@MARITALSTATUSCODEID	uniqueidentifier	INOUT
@WEBADDRESS	UDT_WEBADDRESS	INOUT
@ADDRESSHISTORICALSTARTDATE	date	INOUT
@ADDRESSHISTORICALENDDATE	date	INOUT
@ADDRESSDONOTMAIL	bit	INOUT
@ADDRESSDONOTMAILREASONCODEID	uniqueidentifier	INOUT
@ADDRESSSTARTDATE	UDT_MONTHDAY	INOUT
@ADDRESSENDDATE	UDT_MONTHDAY	INOUT
@ADDRESSDPC	nvarchar(max)	INOUT
@ADDRESSCART	nvarchar(max)	INOUT
@ADDRESSLOT	nvarchar(5)	INOUT
@ADDRESSINFOSOURCECODEID	uniqueidentifier	INOUT
@ADDRESSINFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ADDRESSCOUNTYCODEID	uniqueidentifier	INOUT
@ADDRESSREGIONCODEID	uniqueidentifier	INOUT
@ADDRESSCONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESSSTATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESSSTATESENATEDISTRICTCODEID	uniqueidentifier	INOUT
@ADDRESSLOCALPRECINCTCODEID	uniqueidentifier	INOUT
@ADDRESSCERTIFICATIONDATA	int	INOUT
@ADDRESSLASTVALIDATIONATTEMPTDATE	date	INOUT
@ADDRESSOMITFROMVALIDATION	bit	INOUT
@ADDRESSVALIDATIONMESSAGE	nvarchar(200)	INOUT
@PHONEDONOTCALL	bit	INOUT
@PHONESTARTTIME	UDT_HOURMINUTE	INOUT
@PHONEENDTIME	UDT_HOURMINUTE	INOUT
@PHONEINFOSOURCECODEID	uniqueidentifier	INOUT
@PHONECOUNTRYID	uniqueidentifier	INOUT
@PHONESTARTDATE	date	INOUT
@PHONEENDDATE	date	INOUT
@PHONESEASONALSTARTDATE	UDT_MONTHDAY	INOUT
@PHONESEASONALENDDATE	UDT_MONTHDAY	INOUT
@EMAILADDRESSDONOTEMAIL	bit	INOUT
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	INOUT
@EMAILADDRESSSTARTDATE	date	INOUT
@EMAILADDRESSENDDATE	date	INOUT
@DEFAULTCOUNTRYID	uniqueidentifier	INOUT
@CONSTITUENCIES	xml	INOUT
@GENDERCODEID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTDUPLICATEMATCH (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @LOOKUPID nvarchar(50) = null output,
    @FIRSTNAME nvarchar(50) = null output,
    @MIDDLENAME nvarchar(50) = null output,
    @LASTNAME nvarchar(100) = null output, -- also used as OsRGANIZATIONNAME
    @SUFFIXCODEID uniqueidentifier = null output,
    @TITLECODEID uniqueidentifier = null output,
    @MAIDENNAME nvarchar(100) = null output,
    @NICKNAME nvarchar(50) = null output,
    @GENDERCODE tinyint = null output,
    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
    @ADDRESSID uniqueidentifier = null output,
    @ADDRESSTYPECODEID uniqueidentifier = null output,
    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
    @ADDRESS_CITY nvarchar(50) = null output,
    @ADDRESS_STATEID uniqueidentifier = null output,
    @ADDRESS_POSTCODE nvarchar(12) = null output,
    @ADDRESS_COUNTRYID uniqueidentifier = null output,
    @PHONEID uniqueidentifier = null output,
    @PHONETYPECODEID uniqueidentifier = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILID uniqueidentifier = null output,
    @EMAILADDRESSTYPECODEID uniqueidentifier = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @CREATEDON datetime = null output,
    @DATECHANGED datetime = null output,
    @ADDRESSES xml = null output,
    @PHONES xml = null output,
    @EMAILS xml = null output,
    @SPOUSENAME nvarchar(154) = null output,
    @SPOUSELOOKUPID nvarchar(50) = null output,
    @SPOUSESTARTDATE datetime = null output,
    @SPOUSEENDDATE datetime = null output,
    @HOUSEHOLDNAME nvarchar(154) = null output,
    @HOUSEHOLDLOOKUPID nvarchar(50) = null output,
    @BUSINESSNAME nvarchar(154) = null output,
    @BUSINESSLOOKUPID nvarchar(50) = null output,
    @CONSTITUENTACTION tinyint = null output,
    @ADDRESSACTION tinyint = null output,
    @EMAILACTION tinyint = null output,
    @PHONEACTION tinyint = null output,
    @ISORGANIZATION bit = null output,
    @INDUSTRYCODEID uniqueidentifier = null output,
    @NUMEMPLOYEES int = null output,
    @NUMSUBSIDIARIES int = null output,
    @DECEASED bit = null output,
    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
    @GIVESANONYMOUSLY bit = null output,
    @MARITALSTATUSCODEID uniqueidentifier = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @ADDRESSHISTORICALSTARTDATE date = null output,
    @ADDRESSHISTORICALENDDATE date = null output,
    @ADDRESSDONOTMAIL bit = null output,
    @ADDRESSDONOTMAILREASONCODEID uniqueidentifier = null output,
    @ADDRESSSTARTDATE dbo.UDT_MONTHDAY = null output,
    @ADDRESSENDDATE dbo.UDT_MONTHDAY = null output,
    @ADDRESSDPC nvarchar(max) = null output,
    @ADDRESSCART nvarchar(max) = null output,
    @ADDRESSLOT nvarchar(5) = null output,
    @ADDRESSINFOSOURCECODEID uniqueidentifier = null output,
    @ADDRESSINFOSOURCECOMMENTS nvarchar(256) = null output,
    @ADDRESSCOUNTYCODEID uniqueidentifier = null output,
    @ADDRESSREGIONCODEID uniqueidentifier = null output,
    @ADDRESSCONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESSSTATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESSSTATESENATEDISTRICTCODEID uniqueidentifier = null output,
    @ADDRESSLOCALPRECINCTCODEID uniqueidentifier = null output,
    @ADDRESSCERTIFICATIONDATA int = null output,
    @ADDRESSLASTVALIDATIONATTEMPTDATE date = null output,
    @ADDRESSOMITFROMVALIDATION bit = null output,
    @ADDRESSVALIDATIONMESSAGE nvarchar(200) = null output,
    @PHONEDONOTCALL bit = null output,
    @PHONESTARTTIME dbo.UDT_HOURMINUTE = null output,
    @PHONEENDTIME dbo.UDT_HOURMINUTE = null output,
    @PHONEINFOSOURCECODEID uniqueidentifier = null output,
    @PHONECOUNTRYID uniqueidentifier = null output,
    @PHONESTARTDATE date = null output,
    @PHONEENDDATE date = null output,
    @PHONESEASONALSTARTDATE dbo.UDT_MONTHDAY = null output,
    @PHONESEASONALENDDATE dbo.UDT_MONTHDAY = null output,
    @EMAILADDRESSDONOTEMAIL bit = null output,
    @EMAILADDRESSINFOSOURCECODEID uniqueidentifier = null output,
    @EMAILADDRESSSTARTDATE date = null output,
    @EMAILADDRESSENDDATE date = null output,
    @DEFAULTCOUNTRYID uniqueidentifier = null output,
    @CONSTITUENCIES xml = null output,
  @GENDERCODEID uniqueidentifier = null output
    )
as
set nocount on;
set @DATALOADED = 0;
set @TSLONG = 0;

declare @ISGROUP bit;

--CONSTIT FIELDS
select @DATALOADED = 1,
    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
    @ISGROUP = CONSTITUENT.ISGROUP,
    @FIRSTNAME = CONSTITUENT.FIRSTNAME,
    @MIDDLENAME = CONSTITUENT.MIDDLENAME,
    @LASTNAME = case CONSTITUENT.ISORGANIZATION when 1 then CONSTITUENT.KEYNAMEPREFIX + case CONSTITUENT.KEYNAMEPREFIX when '' then '' else '\' end + CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAME end,
    @SUFFIXCODEID = CONSTITUENT.SUFFIXCODEID,
    @TITLECODEID = CONSTITUENT.TITLECODEID,
    @MAIDENNAME = CONSTITUENT.MAIDENNAME,
    @NICKNAME = CONSTITUENT.NICKNAME,
    @GENDERCODE = CONSTITUENT.GENDERCODE,
    @BIRTHDATE = CONSTITUENT.BIRTHDATE,
    @LOOKUPID = CONSTITUENT.LOOKUPID,
    @CREATEDON = CONSTITUENT.DATEADDED,
    @DATECHANGED = CONSTITUENT.DATECHANGED,
    @TSLONG = CONSTITUENT.TSLONG,
    @INDUSTRYCODEID = ORGANIZATIONDATA.INDUSTRYCODEID,
    @NUMEMPLOYEES = coalesce(ORGANIZATIONDATA.NUMEMPLOYEES, 0),
    @NUMSUBSIDIARIES = coalesce(ORGANIZATIONDATA.NUMSUBSIDIARIES, 0),
    @MARITALSTATUSCODEID = MARITALSTATUSCODEID,
    @DECEASED = dbo.UFN_CONSTITUENT_ISDECEASED(@ID),
    @DECEASEDDATE = (
        select DECEASEDDATE
        from dbo.DECEASEDCONSTITUENT
        where ID = @ID
        ),
    @WEBADDRESS = WEBADDRESS,
    @GIVESANONYMOUSLY = GIVESANONYMOUSLY,
    @GENDERCODEID = CONSTITUENT.GENDERCODEID
from dbo.CONSTITUENT
left join dbo.ORGANIZATIONDATA on dbo.CONSTITUENT.ID = dbo.ORGANIZATIONDATA.ID
where CONSTITUENT.ID = @ID;

--EMAIL
declare @EMAILTABLE table (
    EMAILADDRESS dbo.UDT_EMAILADDRESS,
    EMAILADDRESSID uniqueidentifier,
    EMAILADDRESSTYPECODEID uniqueidentifier,
    ISPRIMARY bit,
    STARTDATE datetime,
    ENDDATE datetime,
    INFOSOURCECODEID uniqueidentifier,
    DONOTEMAIL bit,
    LASTUPDATED date
    );

insert into @EMAILTABLE (
    EMAILADDRESS,
    EMAILADDRESSID,
    EMAILADDRESSTYPECODEID,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    INFOSOURCECODEID,
    DONOTEMAIL,
    LASTUPDATED
    )
select EMAILADDRESS,
    ID,
    EMAILADDRESSTYPECODEID,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    INFOSOURCECODEID,
    DONOTEMAIL,
    DATECHANGED
from dbo.EMAILADDRESS
where EMAILADDRESS.CONSTITUENTID = @ID;

select @EMAILID = EMAILADDRESSID,
    @EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODEID,
    @EMAILADDRESS = EMAILADDRESS,
    @EMAILADDRESSSTARTDATE = STARTDATE,
    @EMAILADDRESSENDDATE = ENDDATE,
    @EMAILADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
    @EMAILADDRESSDONOTEMAIL = DONOTEMAIL
from @EMAILTABLE
where ISPRIMARY = 1;

set @EMAILS = (
        select EMAILADDRESSID,
            EMAILADDRESSTYPECODEID,
            EMAILADDRESS,
            STARTDATE,
            ENDDATE,
            ISPRIMARY,
            LASTUPDATED
        from @EMAILTABLE
        order by ISPRIMARY desc
        for xml raw('ITEM'),
            type,
            elements,
            root('EMAILS'),
            binary BASE64
        );

--PHONE    
declare @PHONETABLE table (
    PHONENUMBER nvarchar(100),
    PHONEID uniqueidentifier,
    PHONETYPECODEID uniqueidentifier,
    ISPRIMARY bit,
    STARTDATE datetime,
    ENDDATE datetime,
    DONOTCALL bit,
    COUNTRYID uniqueidentifier,
    STARTTIME dbo.UDT_HOURMINUTE,
    ENDTIME dbo.UDT_HOURMINUTE,
    SEASONALSTARTDATE dbo.UDT_MONTHDAY,
    SEASONALENDDATE dbo.UDT_MONTHDAY,
    INFOSOURCECODEID uniqueidentifier,
    LASTUPDATED date
    );

insert into @PHONETABLE (
    PHONENUMBER,
    PHONEID,
    PHONETYPECODEID,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    DONOTCALL,
    COUNTRYID,
    STARTTIME,
    ENDTIME,
    SEASONALSTARTDATE,
    SEASONALENDDATE,
    INFOSOURCECODEID,
    LASTUPDATED
    )
select PHONE.NUMBER,
    PHONE.ID,
    PHONE.PHONETYPECODEID,
    PHONE.ISPRIMARY,
    PHONE.STARTDATE,
    PHONE.ENDDATE,
    DONOTCALL,
    COUNTRYID,
    STARTTIME,
    ENDTIME,
    SEASONALSTARTDATE,
    SEASONALENDDATE,
    INFOSOURCECODEID,
    DATECHANGED
from dbo.PHONE
where PHONE.CONSTITUENTID = @ID;

-- get primary phone
select @PHONEID = PHONEID,
    @PHONETYPECODEID = PHONETYPECODEID,
    @PHONENUMBER = PHONENUMBER,
    @PHONEDONOTCALL = DONOTCALL,
    @PHONECOUNTRYID = COUNTRYID,
    @PHONESEASONALSTARTDATE = SEASONALSTARTDATE,
    @PHONESEASONALENDDATE = SEASONALENDDATE,
    @PHONESTARTDATE = STARTDATE,
    @PHONEENDDATE = ENDDATE,
    @PHONESTARTTIME = STARTTIME,
    @PHONEENDTIME = ENDTIME,
    @PHONEINFOSOURCECODEID = INFOSOURCECODEID
from @PHONETABLE
where ISPRIMARY = 1;

set @PHONES = (
        select PHONEID,
            PHONETYPECODEID,
            PHONENUMBER,
            STARTDATE,
            ENDDATE,
            ISPRIMARY,
            LASTUPDATED
        from @PHONETABLE
        order by ISPRIMARY desc
        for xml raw('ITEM'),
            type,
            elements,
            root('PHONES'),
            binary BASE64
        );

--ADDRESS
declare @ADDRESSTABLE table (
    ADDRESSID uniqueidentifier,
    ADDRESSTYPECODEID uniqueidentifier,
    ADDRESSBLOCK nvarchar(150),
    CITY nvarchar(50),
    STATEID uniqueidentifier,
    POSTCODE nvarchar(12),
    ISPRIMARY bit,
    STARTDATE dbo.UDT_MONTHDAY,
    ENDDATE dbo.UDT_MONTHDAY,
    COUNTRYID uniqueidentifier,
    HISTORICALSTARTDATE date,
    HISTORICALENDDATE date,
    DONOTMAIL bit,
    DONOTMAILREASONCODEID uniqueidentifier,
    DPC nvarchar(max),
    CART nvarchar(max),
    LOT nvarchar(5),
    INFOSOURCECODEID uniqueidentifier,
    INFOSOURCECOMMENTS nvarchar(256),
    COUNTYCODEID uniqueidentifier,
    REGIONCODEID uniqueidentifier,
    CONGRESSIONALDISTRICTCODEID uniqueidentifier,
    STATEHOUSEDISTRICTCODEID uniqueidentifier,
    STATESENATEDISTRICTCODEID uniqueidentifier,
    LOCALPRECINCTCODEID uniqueidentifier,
    CERTIFICATIONDATA int,
    LASTVALIDATIONATTEMPTDATE date,
    OMITFROMVALIDATION bit,
    VALIDATIONMESSAGE nvarchar(200),
    LASTUPDATED date
    );

insert into @ADDRESSTABLE (
    ADDRESSID,
    ADDRESSTYPECODEID,
    ADDRESSBLOCK,
    CITY,
    STATEID,
    POSTCODE,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    COUNTRYID,
    HISTORICALSTARTDATE,
    HISTORICALENDDATE,
    DONOTMAIL,
    DONOTMAILREASONCODEID,
    DPC,
    CART,
    LOT,
    INFOSOURCECODEID,
    INFOSOURCECOMMENTS,
    COUNTYCODEID,
    REGIONCODEID,
    CONGRESSIONALDISTRICTCODEID,
    STATEHOUSEDISTRICTCODEID,
    STATESENATEDISTRICTCODEID,
    LOCALPRECINCTCODEID,
    CERTIFICATIONDATA,
    LASTVALIDATIONATTEMPTDATE,
    OMITFROMVALIDATION,
    VALIDATIONMESSAGE,
    LASTUPDATED
    )
select ADDRESS.ID,
    ADDRESS.ADDRESSTYPECODEID,
    ADDRESS.ADDRESSBLOCK,
    ADDRESS.CITY,
    ADDRESS.STATEID,
    ADDRESS.POSTCODE,
    ISPRIMARY,
    STARTDATE,
    ENDDATE,
    COUNTRYID,
    HISTORICALSTARTDATE,
    HISTORICALENDDATE,
    DONOTMAIL,
    DONOTMAILREASONCODEID,
    DPC,
    CART,
    LOT,
    INFOSOURCECODEID,
    INFOSOURCECOMMENTS,
    COUNTYCODEID,
    REGIONCODEID,
    CONGRESSIONALDISTRICTCODEID,
    STATEHOUSEDISTRICTCODEID,
    STATESENATEDISTRICTCODEID,
    LOCALPRECINCTCODEID,
    CERTIFICATIONDATA,
    LASTVALIDATIONATTEMPTDATE,
    OMITFROMVALIDATION,
    VALIDATIONMESSAGE,
    ADDRESS.DATECHANGED
from dbo.ADDRESS
left join dbo.ADDRESSVALIDATIONUPDATE on ADDRESSVALIDATIONUPDATE.ID = ADDRESS.ID
where ADDRESS.CONSTITUENTID = @ID;

-- get primary address
select @ADDRESSID = ADDRESSID,
    @ADDRESSTYPECODEID = ADDRESSTYPECODEID,
    @ADDRESS_ADDRESSBLOCK = ADDRESSBLOCK,
    @ADDRESS_CITY = CITY,
    @ADDRESS_STATEID = STATEID,
    @ADDRESS_POSTCODE = POSTCODE,
    @ADDRESS_COUNTRYID = COUNTRYID,
    @ADDRESSHISTORICALSTARTDATE = HISTORICALSTARTDATE,
    @ADDRESSHISTORICALENDDATE = HISTORICALENDDATE,
    @ADDRESSDONOTMAIL = DONOTMAIL,
    @ADDRESSDONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
    @ADDRESSSTARTDATE = STARTDATE,
    @ADDRESSENDDATE = ENDDATE,
    @ADDRESSDPC = DPC,
    @ADDRESSCART = CART,
    @ADDRESSLOT = LOT,
    @ADDRESSINFOSOURCECODEID = INFOSOURCECODEID,
    @ADDRESSINFOSOURCECOMMENTS = INFOSOURCECOMMENTS,
    @ADDRESSCOUNTYCODEID = COUNTYCODEID,
    @ADDRESSREGIONCODEID = REGIONCODEID,
    @ADDRESSCONGRESSIONALDISTRICTCODEID = CONGRESSIONALDISTRICTCODEID,
    @ADDRESSSTATEHOUSEDISTRICTCODEID = STATEHOUSEDISTRICTCODEID,
    @ADDRESSSTATESENATEDISTRICTCODEID = STATESENATEDISTRICTCODEID,
    @ADDRESSLOCALPRECINCTCODEID = LOCALPRECINCTCODEID,
    @ADDRESSCERTIFICATIONDATA = CERTIFICATIONDATA,
    @ADDRESSLASTVALIDATIONATTEMPTDATE = LASTVALIDATIONATTEMPTDATE,
    @ADDRESSOMITFROMVALIDATION = OMITFROMVALIDATION,
    @ADDRESSVALIDATIONMESSAGE = VALIDATIONMESSAGE
from @ADDRESSTABLE
where ISPRIMARY = 1;

set @ADDRESSES = (
        select ADDRESSID,
            ADDRESSTYPECODEID,
            ADDRESSBLOCK,
            CITY,
            STATEID,
            POSTCODE,
            STARTDATE,
            ENDDATE,
            ISPRIMARY,
            COUNTRYID,
            dbo.UFN_BUILDFULLADDRESS(ADDRESSID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID) as FULLADDRESS,
            LASTUPDATED,
            HISTORICALSTARTDATE,
            HISTORICALENDDATE
        from @ADDRESSTABLE
        order by ISPRIMARY desc
        for xml raw('ITEM'),
            type,
            elements,
            root('ADDRESSES'),
            binary BASE64
        );

-- spouse information
select @SPOUSENAME = CONSTITUENT.name,
    @SPOUSELOOKUPID = CONSTITUENT.LOOKUPID,
    @SPOUSESTARTDATE = RELATIONSHIP.STARTDATE,
    @SPOUSEENDDATE = RELATIONSHIP.ENDDATE
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and RELATIONSHIP.ISSPOUSE = 1;

-- household information
if @ISORGANIZATION = 0 and @ISGROUP = 0
    select top (1) @HOUSEHOLDNAME = CG.name,
        @HOUSEHOLDLOOKUPID = CG.LOOKUPID
    from dbo.GROUPMEMBER as GM
    join dbo.CONSTITUENT as CG on GM.GROUPID = CG.ID
    join dbo.GROUPDATA as GD on GD.ID = CG.ID
    where GM.MEMBERID = @ID and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1 and GD.GROUPTYPECODE = 0;

-- get primary business
if @ISORGANIZATION = 0 and @ISGROUP = 0
    select @BUSINESSNAME = [ORG].KEYNAME,
        @BUSINESSLOOKUPID = [ORG].LOOKUPID
    from dbo.RELATIONSHIP
    inner join dbo.CONSTITUENT as [ORG] on [ORG].ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
    where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and RELATIONSHIP.ISPRIMARYBUSINESS = 1;

select @DEFAULTCOUNTRYID = DEFAULTCOUNTRYID
from dbo.INTERNATIONALIZATIONINFO

-- constituencies
select @CONSTITUENCIES = (
    select
        CONSTITUENCYCODEID as '@CONSTITUENCYCODEID',
        ORIGINALCONSTITUENCYID as '@ORIGINALCONSTITUENCYID',
        DATEFROM as '@DATEFROM',
        DATETO as '@DATETO'
    from dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_FORUPDATEBATCH(@ID)
    for xml path('ITEM'), type, elements, root('CONSTITUENCIES'), BINARY BASE64
)

return 0;