USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTBATCHROW_6

The load procedure used by the edit dataform template "Constituent Batch Row Edit Form 6"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ISORGANIZATION	tinyint	INOUT	Constituent type
@KEYNAME	nvarchar(100)	INOUT	Last/org/group/household name
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@MAIDENNAME	nvarchar(100)	INOUT	Maiden name
@NICKNAME	nvarchar(50)	INOUT	Nickname
@TITLECODEID	uniqueidentifier	INOUT	Title
@SUFFIXCODEID	uniqueidentifier	INOUT	Suffix
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@GENDERCODE	tinyint	INOUT	Gender
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Web site address
@INDUSTRYCODEID	uniqueidentifier	INOUT	Industry
@PARENTCORPID	uniqueidentifier	INOUT	Parent org
@NUMEMPLOYEES	int	INOUT	No. of employees
@NUMSUBSIDIARIES	int	INOUT	No. of subsidiary orgs
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT	Address type
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT	Country
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@ADDRESS_CITY	nvarchar(50)	INOUT	City
@ADDRESS_STATEID	uniqueidentifier	INOUT	State
@ADDRESS_POSTCODE	nvarchar(12)	INOUT	ZIP
@ADDRESS_DONOTMAIL	bit	INOUT	Do not send mail to this address
@PHONES	xml	INOUT	Phones
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@CUSTOMIDENTIFIER	nvarchar(100)	INOUT	Lookup ID
@SEQUENCE	int	INOUT	Sequence
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@ADDRESS_CART	nvarchar(10)	INOUT	CART
@ADDRESS_DPC	nvarchar(8)	INOUT	DPC
@ADDRESS_LOT	nvarchar(5)	INOUT	LOT
@ADDRESS_STARTDATE	UDT_MONTHDAY	INOUT	Address start date
@ADDRESS_ENDDATE	UDT_MONTHDAY	INOUT	Address end date
@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID	uniqueidentifier	INOUT	Addressee type
@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID	uniqueidentifier	INOUT	Addressee name format
@PRIMARYSALUTATION_NAMEFORMATTYPECODEID	uniqueidentifier	INOUT	Salutation type
@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID	uniqueidentifier	INOUT	Salutation name format
@ISDECEASED	bit	INOUT	Is deceased
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	Deceased date
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID	uniqueidentifier	INOUT	Educational institution
@EDUCATIONALHISTORY_ISPRIMARYRECORD	bit	INOUT	Educational is primary
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE	tinyint	INOUT	Educational status
@EDUCATIONALHISTORY_CLASSOF	UDT_YEAR	INOUT	Class year
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID	uniqueidentifier	INOUT	Educational degree
@EDUCATIONALHISTORY_STARTDATE	UDT_FUZZYDATE	INOUT	Educational start date
@SPOUSEID	uniqueidentifier	INOUT	Spouse
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Spouse relationship type
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Spouse reciprocal type
@SPOUSE_KEYNAME	nvarchar(100)	INOUT	Spouse last name
@SPOUSE_FIRSTNAME	nvarchar(50)	INOUT	Spouse first name
@SPOUSE_MIDDLENAME	nvarchar(50)	INOUT	Spouse middle name
@SPOUSE_MAIDENNAME	nvarchar(100)	INOUT	Spouse maiden name
@SPOUSE_NICKNAME	nvarchar(50)	INOUT	Spouse nickname
@SPOUSE_TITLECODEID	uniqueidentifier	INOUT	Spouse title
@SPOUSE_SUFFIXCODEID	uniqueidentifier	INOUT	Spouse suffix
@SPOUSE_GENDERCODE	tinyint	INOUT	Spouse gender
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	INOUT	Spouse birth date
@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT	Spouse email address type
@SPOUSE_EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Spouse email address
@PRIMARYBUSINESSID	uniqueidentifier	INOUT	Business
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Business relationship type
@PRIMARYBUSINESS_RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Business reciprocal type
@PRIMARYBUSINESS_KEYNAME	nvarchar(100)	INOUT	Business name
@PRIMARYBUSINESS_PHONE_PHONETYPECODEID	uniqueidentifier	INOUT	Business phone type
@PRIMARYBUSINESS_PHONE_NUMBER	nvarchar(100)	INOUT	Business phone number
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	INOUT	Business email type
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Business email address
@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	INOUT	Business address type
@PRIMARYBUSINESS_ADDRESS_COUNTRYID	uniqueidentifier	INOUT	Business country
@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Business address
@PRIMARYBUSINESS_ADDRESS_CITY	nvarchar(50)	INOUT	Business city
@PRIMARYBUSINESS_ADDRESS_STATEID	uniqueidentifier	INOUT	Business state
@PRIMARYBUSINESS_ADDRESS_POSTCODE	nvarchar(12)	INOUT	Business ZIP
@PRIMARYBUSINESS_ADDRESS_CART	nvarchar(10)	INOUT	Business CART
@PRIMARYBUSINESS_ADDRESS_DPC	nvarchar(8)	INOUT	Business DPC
@PRIMARYBUSINESS_ADDRESS_LOT	nvarchar(5)	INOUT	Business LOT
@PRIMARYBUSINESS_ADDRESS_DONOTMAIL	bit	INOUT	Business do not mail
@PRIMARYBUSINESS_INDUSTRYCODEID	uniqueidentifier	INOUT	Business industry
@PRIMARYBUSINESS_NUMEMPLOYEES	int	INOUT	Business no. of employees
@PRIMARYBUSINESS_NUMSUBSIDIARIES	int	INOUT	Business no. of subsidiary orgs
@PRIMARYBUSINESS_WEBADDRESS	UDT_WEBADDRESS	INOUT	Business web site address
@PRIMARYBUSINESS_PARENTCORPID	uniqueidentifier	INOUT	Business parent org
@CONSTITUENCY	xml	INOUT	Constituencies
@INTEREST	xml	INOUT	Interests
@SPOUSE_COPY_ADDRESS	bit	INOUT	Spouse copy address
@SPOUSE_COPY_PHONENUMBER	bit	INOUT	Spouse copy phone number
@SPOUSE_COPY_EMAILADDRESS	bit	INOUT	Spouse copy email address
@CONSTIT_SECURITY_ATTRIBUTEID	uniqueidentifier	INOUT	Constituent security attribute
@ADDRESS_COUNTYCODEID	uniqueidentifier	INOUT	County
@ADDRESS_OMITFROMVALIDATION	bit	INOUT	Omit from validation
@ADDRESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT	Congressional district
@ADDRESS_STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT	State house district
@ADDRESS_STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT	State senate district
@ADDRESS_LOCALPRECINCTCODEID	uniqueidentifier	INOUT	Local precinct
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	INOUT	Info source
@ADDRESS_REGIONCODEID	uniqueidentifier	INOUT	Region
@ADDRESS_LASTVALIDATIONATTEMPTDATE	datetime	INOUT	Last validation attempt date
@ADDRESS_VALIDATIONMESSAGE	nvarchar(200)	INOUT	Validation message
@ADDRESS_CERTIFICATIONDATA	int	INOUT	Certification data
@GROUPTYPEID	uniqueidentifier	INOUT	Group type
@GROUPSTARTDATE	datetime	INOUT	Group start date
@GROUPDESCRIPTION	nvarchar(300)	INOUT	Group description
@GROUPMEMBERS	xml	INOUT	Group members
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Do not mail reason
@PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Business do not mail reason
@EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID	uniqueidentifier	INOUT	Academic catalog program
@EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID	uniqueidentifier	INOUT	Educational program
@EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID	uniqueidentifier	INOUT	Academic catalog degree
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID	uniqueidentifier	INOUT	Awarded
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR	UDT_YEAR	INOUT	Preferred class year
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID	uniqueidentifier	INOUT	Source
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE	UDT_FUZZYDATE	INOUT	Source date
@EDUCATIONALHISTORY_COMMENT	nvarchar(500)	INOUT	Comment
@AFFILIATEDADDITIONALINFORMATION	xml	INOUT	Academic catalog additional information
@UNAFFILIATEDADDITIONALINFORMATION	xml	INOUT	Educational additional information
@EDUCATIONALINVOLVEMENT	xml	INOUT	Involvement
@ALTERNATELOOKUPIDS	xml	INOUT	Alternate lookup IDs
@EDUCATIONALHISTORY_DATEGRADUATED	UDT_FUZZYDATE	INOUT	Date graduated
@EDUCATIONALHISTORY_DATELEFT	UDT_FUZZYDATE	INOUT	Date left
@EDUCATIONALHISTORY_LEVELCODEID	uniqueidentifier	INOUT	Level
@EDUCATIONALHISTORY_REASONCODEID	uniqueidentifier	INOUT	Reason
@ISPRIMARYORGANIZATION	bit	INOUT	Is primary org
@PRIMARYBUSINESS_ISPRIMARYORGANIZATION	bit	INOUT	Business is primary org

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTBATCHROW_6
                    (
                            @ID uniqueidentifier,
                            @DATALOADED bit = 0 output,                            
                            @ISORGANIZATION tinyint = null output,
                            @KEYNAME nvarchar(100) = null output,
                            @FIRSTNAME nvarchar(50) = null output,
                            @MIDDLENAME nvarchar(50) = null output,
                            @MAIDENNAME nvarchar(100) = null output,
                            @NICKNAME nvarchar(50) = null output,
                            @TITLECODEID uniqueidentifier = null output,
                            @SUFFIXCODEID uniqueidentifier = null output,
                            @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                            @GENDERCODE tinyint = null output,
                            @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                            @INDUSTRYCODEID uniqueidentifier = null output,
                            @PARENTCORPID uniqueidentifier = null output,
                            @NUMEMPLOYEES int = null output,
                            @NUMSUBSIDIARIES int = null output,
                            @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null output,
                            @ADDRESS_COUNTRYID 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_DONOTMAIL bit = null output,
                            @PHONES xml = null output,
                            @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
                            @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                            @CUSTOMIDENTIFIER nvarchar(100) = null output,
                            @SEQUENCE int = null output,
                            @TSLONG bigint = 0 output,
                            @ADDRESS_CART nvarchar(10) = null output,
                            @ADDRESS_DPC nvarchar(8) = null output,
                            @ADDRESS_LOT nvarchar(5) = null output,
                            @ADDRESS_STARTDATE dbo.UDT_MONTHDAY = null output,
                            @ADDRESS_ENDDATE dbo.UDT_MONTHDAY = null output,
                            @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID uniqueidentifier = null output,
                            @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID uniqueidentifier = null output,
                            @PRIMARYSALUTATION_NAMEFORMATTYPECODEID uniqueidentifier = null output,
                            @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID uniqueidentifier = null output,
                            @ISDECEASED bit = null output,
                            @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
                            @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_ISPRIMARYRECORD bit = null output,
                            @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE tinyint = null output,
                            @EDUCATIONALHISTORY_CLASSOF dbo.UDT_YEAR = null output,
                            @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_STARTDATE dbo.UDT_FUZZYDATE = null output,
                            @SPOUSEID uniqueidentifier = null output,
                            @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
                            @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null output,
                            @SPOUSE_KEYNAME nvarchar(100) = null output,
                            @SPOUSE_FIRSTNAME nvarchar(50) = null output,
                            @SPOUSE_MIDDLENAME nvarchar(50) = null output,
                            @SPOUSE_MAIDENNAME nvarchar(100) = null output,
                            @SPOUSE_NICKNAME nvarchar(50) = null output,
                            @SPOUSE_TITLECODEID uniqueidentifier = null output,
                            @SPOUSE_SUFFIXCODEID uniqueidentifier = null output,
                            @SPOUSE_GENDERCODE tinyint = null output,
                            @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                            @SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
                            @SPOUSE_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                            @PRIMARYBUSINESSID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_KEYNAME nvarchar(100) = null output,
                            @PRIMARYBUSINESS_PHONE_PHONETYPECODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_PHONE_NUMBER nvarchar(100) = null output,
                            @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
                            @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_ADDRESS_COUNTRYID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
                            @PRIMARYBUSINESS_ADDRESS_CITY nvarchar(50) = null output,
                            @PRIMARYBUSINESS_ADDRESS_STATEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_ADDRESS_POSTCODE nvarchar(12) = null output,
                            @PRIMARYBUSINESS_ADDRESS_CART nvarchar(10) = null output,
                            @PRIMARYBUSINESS_ADDRESS_DPC nvarchar(8) = null output,
                            @PRIMARYBUSINESS_ADDRESS_LOT nvarchar(5) = null output,
                            @PRIMARYBUSINESS_ADDRESS_DONOTMAIL bit = null output,
                            @PRIMARYBUSINESS_INDUSTRYCODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_NUMEMPLOYEES int = null output,
                            @PRIMARYBUSINESS_NUMSUBSIDIARIES int = null output,
                            @PRIMARYBUSINESS_WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                            @PRIMARYBUSINESS_PARENTCORPID uniqueidentifier = null output,
                            @CONSTITUENCY xml = null output,
                            @INTEREST xml = null output,
                            @SPOUSE_COPY_ADDRESS bit = null output,
                            @SPOUSE_COPY_PHONENUMBER bit = null output,
                            @SPOUSE_COPY_EMAILADDRESS bit = null output,
                            @CONSTIT_SECURITY_ATTRIBUTEID uniqueidentifier = null output,
                            @ADDRESS_COUNTYCODEID uniqueidentifier = null output,
                            @ADDRESS_OMITFROMVALIDATION bit = null output,
                            @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
                            @ADDRESS_STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
                            @ADDRESS_STATESENATEDISTRICTCODEID uniqueidentifier = null output,
                            @ADDRESS_LOCALPRECINCTCODEID uniqueidentifier = null output,
                            @ADDRESS_INFOSOURCECODEID uniqueidentifier = null output,
                            @ADDRESS_REGIONCODEID uniqueidentifier = null output,
                            @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null output,
                            @ADDRESS_VALIDATIONMESSAGE nvarchar(200) = null output,
                            @ADDRESS_CERTIFICATIONDATA int = null output,
                            @GROUPTYPEID uniqueidentifier = null output,
                            @GROUPSTARTDATE datetime = null output,
                            @GROUPDESCRIPTION nvarchar(300) = null output,
                            @GROUPMEMBERS xml = null output,
                            @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
                            @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_PREFERREDCLASSYEAR dbo.UDT_YEAR = null output,
                            @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = null output,
                            @EDUCATIONALHISTORY_COMMENT nvarchar(500) = null output,
                            @AFFILIATEDADDITIONALINFORMATION xml = null output,
                            @UNAFFILIATEDADDITIONALINFORMATION xml = null output,
                            @EDUCATIONALINVOLVEMENT xml = null output,
                            @ALTERNATELOOKUPIDS xml = null output,
                            @EDUCATIONALHISTORY_DATEGRADUATED dbo.UDT_FUZZYDATE = null output,
                            @EDUCATIONALHISTORY_DATELEFT dbo.UDT_FUZZYDATE = null output,
                            @EDUCATIONALHISTORY_LEVELCODEID uniqueidentifier = null output,
                            @EDUCATIONALHISTORY_REASONCODEID uniqueidentifier = null output,
                            @ISPRIMARYORGANIZATION bit = null output,
                            @PRIMARYBUSINESS_ISPRIMARYORGANIZATION bit = null output
                        ) as
                            set nocount on;

                            declare @USERDEFINEDCONSTITUENCY xml;
                            declare @SYSTEMDEFINEDCONSTITUENCY xml;

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

                            select
                                @DATALOADED = 1,                            
                                --@ISORGANIZATION = [ISORGANIZATION],

                                @ISORGANIZATION = case 
                                    when (ISORGANIZATION = 0 and ISGROUP = 0) then 0
                                    when (ISORGANIZATION = 1) then 1
                                    when (ISGROUP = 1 and GROUPTYPECODE = 0) then 2
                                    when (ISGROUP = 1 and GROUPTYPECODE = 1) then 3
                                  end,
                                @KEYNAME = [KEYNAME],
                                @FIRSTNAME = [FIRSTNAME],
                                @MIDDLENAME = [MIDDLENAME],
                                @MAIDENNAME = [MAIDENNAME],
                                @NICKNAME = [NICKNAME],
                                @TITLECODEID = [TITLECODEID],
                                @SUFFIXCODEID = [SUFFIXCODEID],
                                @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],
                                @PHONES = dbo.UFN_CONSTITUENTBATCH_GETPHONES_TOITEMLISTXML(ID),
                                @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_RELATIONSHIPTYPECODEID],
                                @SPOUSE_RECIPROCALTYPECODEID = [SPOUSE_RECIPROCALTYPECODEID],
                                @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_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_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_INDUSTRYCODEID = [PRIMARYBUSINESS_INDUSTRYCODEID],
                                @PRIMARYBUSINESS_NUMEMPLOYEES = [PRIMARYBUSINESS_NUMEMPLOYEES],
                                @PRIMARYBUSINESS_NUMSUBSIDIARIES = [PRIMARYBUSINESS_NUMSUBSIDIARIES],
                                @PRIMARYBUSINESS_WEBADDRESS = [PRIMARYBUSINESS_WEBADDRESS],
                                @PRIMARYBUSINESS_PARENTCORPID = [PRIMARYBUSINESS_PARENTCORPID],

                                -- Used to build @CONSTITUENCY

                                @USERDEFINEDCONSTITUENCY = dbo.UFN_CONSTITUENTBATCH_GETCONSTITUENCIES_TOITEMLISTXML(@ID),
                                @SYSTEMDEFINEDCONSTITUENCY = dbo.UFN_CONSTITUENTBATCH_GETCONSTITUENCIES_SYSTEM_TOITEMLISTXML(@ID),

                                @INTEREST = dbo.UFN_CONSTITUENTBATCH_GETINTERESTS_TOITEMLISTXML(ID),
                                @GROUPMEMBERS = dbo.UFN_CONSTITUENTBATCH_GETGROUPMEMBERS_TOITEMLISTXML(ID),
                                @GROUPTYPEID = [GROUPTYPEID],
                                @GROUPSTARTDATE = [GROUPSTARTDATE],
                                @GROUPDESCRIPTION = [GROUPDESCRIPTION],
                                @CONSTIT_SECURITY_ATTRIBUTEID = CONSTIT_SECURITY_ATTRIBUTEID,
                                @CUSTOMIDENTIFIER = [CUSTOMID],
                                @SEQUENCE = [SEQUENCE],
                                @TSLONG = [TSLONG],
                                @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,
                                @AFFILIATEDADDITIONALINFORMATION = dbo.UFN_CONSTITUENTBATCH_GETEDUCATIONAFFILIATEDADDITIONALINFORMATION_TOITEMLISTXML(ID),
                                @UNAFFILIATEDADDITIONALINFORMATION = dbo.UFN_CONSTITUENTBATCH_GETEDUCATIONUNAFFILIATEDADDITIONALINFORMATION_TOITEMLISTXML(ID),
                                @EDUCATIONALINVOLVEMENT = dbo.UFN_CONSTITUENTBATCH_GETEDUCATIONALINVOLVEMENT_TOITEMLISTXML(ID),
                                @ALTERNATELOOKUPIDS = dbo.UFN_CONSTITUENTBATCH_GETBATCHALTERNATELOOKUPIDS_TOITEMLISTXML(ID),
                                @EDUCATIONALINVOLVEMENT = dbo.UFN_CONSTITUENTBATCH_GETEDUCATIONALINVOLVEMENT_TOITEMLISTXML(ID),
                                @EDUCATIONALHISTORY_DATEGRADUATED = EDUCATIONALHISTORY_DATEGRADUATED,
                                @EDUCATIONALHISTORY_DATELEFT = EDUCATIONALHISTORY_DATELEFT,
                                @EDUCATIONALHISTORY_LEVELCODEID = EDUCATIONALHISTORY_LEVELCODEID,
                                @EDUCATIONALHISTORY_REASONCODEID = EDUCATIONALHISTORY_REASONCODEID,
                                @ISPRIMARYORGANIZATION = [ISPRIMARYORGANIZATION],
                                @PRIMARYBUSINESS_ISPRIMARYORGANIZATION = [PRIMARYBUSINESS_ISPRIMARYORGANIZATION]
                            from
                                dbo.BATCHCONSTITUENT
                            where
                                BATCHCONSTITUENT.ID = @ID;

                            set @CONSTITUENCY =
                                (
                                    select
                                        CONSTITUENCY.c.query('.')
                                    from
                                    (
                                        select
                                            USERDEFINEDCONSTITUENCY.c.query('.')
                                        from
                                            @USERDEFINEDCONSTITUENCY.nodes('/CONSTITUENCY/ITEM') as USERDEFINEDCONSTITUENCY(c)

                                        union all

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

                                );

                            return 0;