USP_DATAFORMTEMPLATE_ADD_CONSTITUENTBATCHROW3

The save procedure used by the add dataform template "Constituent Batch Row Add Form 3".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@BATCHID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@ISORGANIZATION	tinyint	IN	Constituent type
@KEYNAME	nvarchar(100)	IN	Last/org/group/household name
@FIRSTNAME	nvarchar(50)	IN	First name
@MIDDLENAME	nvarchar(50)	IN	Middle name
@MAIDENNAME	nvarchar(100)	IN	Maiden name
@NICKNAME	nvarchar(50)	IN	Nickname
@TITLECODEID	uniqueidentifier	IN	Title
@SUFFIXCODEID	uniqueidentifier	IN	Suffix
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@GENDERCODE	tinyint	IN	Gender
@WEBADDRESS	UDT_WEBADDRESS	IN	Web site address
@INDUSTRYCODEID	uniqueidentifier	IN	Industry
@PARENTCORPID	uniqueidentifier	IN	Parent org
@NUMEMPLOYEES	int	IN	No. of employees
@NUMSUBSIDIARIES	int	IN	No. of subsidiary orgs
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@ADDRESS_COUNTRYID	uniqueidentifier	IN	Country
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@ADDRESS_CITY	nvarchar(50)	IN	City
@ADDRESS_STATEID	uniqueidentifier	IN	State
@ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP
@ADDRESS_DONOTMAIL	bit	IN	Do not send mail to this address
@PHONES	xml	IN	Phones
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@CUSTOMIDENTIFIER	nvarchar(100)	IN	Lookup ID
@SEQUENCE	int	IN	Sequence
@ADDRESS_CART	nvarchar(10)	IN	CART
@ADDRESS_DPC	nvarchar(8)	IN	DPC
@ADDRESS_LOT	nvarchar(5)	IN	LOT
@ADDRESS_STARTDATE	UDT_MONTHDAY	IN	Seasonal start date
@ADDRESS_ENDDATE	UDT_MONTHDAY	IN	Seasonal end date
@PRIMARYADDRESSEE_NAMEFORMATTYPECODEID	uniqueidentifier	IN	Addressee type
@PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID	uniqueidentifier	IN	Addressee name format
@PRIMARYSALUTATION_NAMEFORMATTYPECODEID	uniqueidentifier	IN	Salutation type
@PRIMARYSALUTATION_NAMEFORMATFUNCTIONID	uniqueidentifier	IN	Salutation name format
@ISDECEASED	bit	IN	Is deceased
@DECEASEDDATE	UDT_FUZZYDATE	IN	Deceased date
@EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID	uniqueidentifier	IN	Educational institution
@EDUCATIONALHISTORY_ISPRIMARYRECORD	bit	IN	Educational is primary
@EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE	tinyint	IN	Educational status
@EDUCATIONALHISTORY_CLASSOF	UDT_YEAR	IN	Class of
@EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID	uniqueidentifier	IN	Educational degree
@EDUCATIONALHISTORY_STARTDATE	UDT_FUZZYDATE	IN	Educational date from
@SPOUSEID	uniqueidentifier	IN	Spouse
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Spouse relationship type
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	IN	Spouse reciprocal type
@SPOUSE_KEYNAME	nvarchar(100)	IN	Spouse last name
@SPOUSE_FIRSTNAME	nvarchar(50)	IN	Spouse first name
@SPOUSE_MIDDLENAME	nvarchar(50)	IN	Spouse middle name
@SPOUSE_MAIDENNAME	nvarchar(100)	IN	Spouse maiden name
@SPOUSE_NICKNAME	nvarchar(50)	IN	Spouse nickname
@SPOUSE_TITLECODEID	uniqueidentifier	IN	Spouse title
@SPOUSE_SUFFIXCODEID	uniqueidentifier	IN	Spouse suffix
@SPOUSE_GENDERCODE	tinyint	IN	Spouse gender
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	IN	Spouse birth date
@SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Spouse email address type
@SPOUSE_EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Spouse email address
@PRIMARYBUSINESSID	uniqueidentifier	IN	Business
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Business relationship type
@PRIMARYBUSINESS_RECIPROCALTYPECODEID	uniqueidentifier	IN	Business reciprocal type
@PRIMARYBUSINESS_KEYNAME	nvarchar(100)	IN	Business name
@PRIMARYBUSINESS_PHONE_PHONETYPECODEID	uniqueidentifier	IN	Business phone type
@PRIMARYBUSINESS_PHONE_NUMBER	nvarchar(100)	IN	Business phone number
@PRIMARYBUSINESS_PHONE_COUNTRYID	uniqueidentifier	IN	Business phone country
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Business email type
@PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Business email address
@PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN	Business address type
@PRIMARYBUSINESS_ADDRESS_COUNTRYID	uniqueidentifier	IN	Business country
@PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Business address
@PRIMARYBUSINESS_ADDRESS_CITY	nvarchar(50)	IN	Business city
@PRIMARYBUSINESS_ADDRESS_STATEID	uniqueidentifier	IN	Business state
@PRIMARYBUSINESS_ADDRESS_POSTCODE	nvarchar(12)	IN	Business ZIP
@PRIMARYBUSINESS_ADDRESS_CART	nvarchar(10)	IN	Business CART
@PRIMARYBUSINESS_ADDRESS_DPC	nvarchar(8)	IN	Business DPC
@PRIMARYBUSINESS_ADDRESS_LOT	nvarchar(5)	IN	Business LOT
@PRIMARYBUSINESS_ADDRESS_DONOTMAIL	bit	IN	Business do not mail
@PRIMARYBUSINESS_INDUSTRYCODEID	uniqueidentifier	IN	Business industry
@PRIMARYBUSINESS_NUMEMPLOYEES	int	IN	Business no. of employees
@PRIMARYBUSINESS_NUMSUBSIDIARIES	int	IN	Business no. of subsidiary orgs
@PRIMARYBUSINESS_WEBADDRESS	UDT_WEBADDRESS	IN	Business web site address
@PRIMARYBUSINESS_PARENTCORPID	uniqueidentifier	IN	Business parent org
@CONSTITUENCY	xml	IN	Constituencies
@INTEREST	xml	IN	Interests
@SPOUSE_COPY_ADDRESS	bit	IN	Spouse copy address
@SPOUSE_COPY_PHONENUMBER	bit	IN	Spouse copy phone number
@SPOUSE_COPY_EMAILADDRESS	bit	IN	Spouse copy email address
@CONSTIT_SECURITY_ATTRIBUTEID	uniqueidentifier	IN	Constituent security attribute
@ADDRESS_COUNTYCODEID	uniqueidentifier	IN	County
@ADDRESS_OMITFROMVALIDATION	bit	IN	Omit from validation
@ADDRESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN	Congressional district
@ADDRESS_STATEHOUSEDISTRICTCODEID	uniqueidentifier	IN	State house district
@ADDRESS_STATESENATEDISTRICTCODEID	uniqueidentifier	IN	State senate district
@ADDRESS_LOCALPRECINCTCODEID	uniqueidentifier	IN	Local precinct
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	IN	Constituent information source
@ADDRESS_REGIONCODEID	uniqueidentifier	IN	Region
@ADDRESS_LASTVALIDATIONATTEMPTDATE	datetime	IN	Last validation attempt date
@ADDRESS_VALIDATIONMESSAGE	nvarchar(200)	IN	Validation message
@ADDRESS_CERTIFICATIONDATA	int	IN	Certification data
@GROUPTYPEID	uniqueidentifier	IN	Group type
@GROUPSTARTDATE	datetime	IN	Group consolidation date
@GROUPDESCRIPTION	nvarchar(300)	IN	Group description
@GROUPMEMBERS	xml	IN	Group members
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Do not mail reason
@PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Business do not mail reason
@EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID	uniqueidentifier	IN	Academic catalog program
@EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID	uniqueidentifier	IN	Educational program
@EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID	uniqueidentifier	IN	Academic catalog degree
@EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID	uniqueidentifier	IN	Honor awarded
@EDUCATIONALHISTORY_PREFERREDCLASSYEAR	UDT_YEAR	IN	Preferred class of
@EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID	uniqueidentifier	IN	Information source
@EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE	UDT_FUZZYDATE	IN	Source date
@EDUCATIONALHISTORY_COMMENT	nvarchar(500)	IN	Comments
@AFFILIATEDADDITIONALINFORMATION	xml	IN	Academic catalog additional information
@UNAFFILIATEDADDITIONALINFORMATION	xml	IN	Educational additional information
@EDUCATIONALINVOLVEMENT	xml	IN	Involvement
@ALTERNATELOOKUPIDS	xml	IN	Alternate lookup IDs
@EDUCATIONALHISTORY_DATEGRADUATED	UDT_FUZZYDATE	IN	Date graduated
@EDUCATIONALHISTORY_DATELEFT	UDT_FUZZYDATE	IN	Date left
@EDUCATIONALHISTORY_LEVELCODEID	uniqueidentifier	IN	Level
@EDUCATIONALHISTORY_REASONCODEID	uniqueidentifier	IN	Reason
@ISPRIMARYORGANIZATION	bit	IN	Is primary org
@PRIMARYBUSINESS_ISPRIMARYORGANIZATION	bit	IN	Business is primary org
@CONSTITUENT_SITEID	uniqueidentifier	IN	Constituent site
@TAXDECLARATIONS	xml	IN	Declarations
@ADDRESS_HISTORICALSTARTDATE	date	IN	Address start date
@PRIMARYMEMBER_COPYCONTACTINFO	bit	IN	Use primary member contact info
@PRIMARYBUSINESS_JOBTITLE	nvarchar(100)	IN	Business job title
@PRIMARYBUSINESS_JOBCATEGORYCODEID	uniqueidentifier	IN	Business job category
@PRIMARYBUSINESS_CAREERLEVELCODEID	uniqueidentifier	IN	Business career level
@PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID	uniqueidentifier	IN	Business information source
@TITLE2CODEID	uniqueidentifier	IN	Title 2
@SUFFIX2CODEID	uniqueidentifier	IN	Suffix 2
@SPOUSE_TITLE2CODEID	uniqueidentifier	IN	Spouse title 2
@SPOUSE_SUFFIX2CODEID	uniqueidentifier	IN	Spouse suffix 2
@SOLICITCODES	xml	IN	Solicit codes
@PRIMARYBUSINESS_JOBDIVISION	nvarchar(100)	IN	Business job division
@PRIMARYBUSINESS_JOBDEPARTMENT	nvarchar(100)	IN	Business job department
@PRIMARYBUSINESS_JOBSCHEDULECODEID	uniqueidentifier	IN	Business job schedule
@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST	uniqueidentifier	IN	Spouse relationship type
@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST	uniqueidentifier	IN	Spouse reciprocal type
@PRIMARYBUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST	uniqueidentifier	IN	Business relationship type
@PRIMARYBUSINESS_RECIPROCALTYPECODEIDSIMPLELIST	uniqueidentifier	IN	Business reciprocal type
@ETHNICITYCODEID	uniqueidentifier	IN	Ethnicity
@RELIGIONCODEID	uniqueidentifier	IN	Religion
@TARGETCODEID	uniqueidentifier	IN	Target
@INCOMECODEID	uniqueidentifier	IN	Income
@BIRTHPLACE	nvarchar(50)	IN	Birthplace
@PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply recognition from business
@PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition from business match percent
@PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply recognition to business
@PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition to business match percent
@PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition from business type
@PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition to business type
@EMAILADDRESS_STARTDATE	date	IN	Email address start date
@SPOUSE_EMAILADDRESS_STARTDATE	date	IN	Spouse email address start date
@PRIMARYBUSINESS_EMAILADDRESS_STARTDATE	date	IN	Business email address start date
@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply recognition from spouse
@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition from spouse match percent
@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply recognition to spouse
@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition to spouse match percent
@SPOUSE_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition from spouse type
@SPOUSE_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition to spouse type
@PRIMARYBUSINESS_STARTDATE	date	IN
@SPOUSE_STARTDATE	date	IN
@SOCIALMEDIAACCOUNTS	xml	IN
@EMAILADDRESS_INFOSOURCECODEID	uniqueidentifier	IN
@EMAILADDRESS_INFOSOURCECOMMENTS	nvarchar(500)	IN
@GENDERCODEID	uniqueidentifier	IN
@SPOUSE_GENDERCODEID	uniqueidentifier	IN

Definition

Copy



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

                        declare @CURRENTDATE datetime = getdate();

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

                        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_CERTIFICATIONDATA is null
                            set @ADDRESS_CERTIFICATIONDATA = 0

                        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);

                            insert into    dbo.BATCHCONSTITUENT
                            (
                                [ID],                            
                                [BATCHID],
                                [ISORGANIZATION],
                                [KEYNAME],
                                [FIRSTNAME],
                                [MIDDLENAME],
                                [MAIDENNAME],
                                [NICKNAME],
                                [TITLECODEID],
                                [TITLE2CODEID],
                                [SUFFIXCODEID],
                                [SUFFIX2CODEID],
                                [BIRTHDATE],
                                [GENDERCODE],
                                [WEBADDRESS],
                                [INDUSTRYCODEID],
                                [PARENTCORPID],
                                [NUMEMPLOYEES],
                                [NUMSUBSIDIARIES],
                                [ADDRESS_ADDRESSTYPECODEID],
                                [ADDRESS_COUNTRYID],
                                [ADDRESS_ADDRESSBLOCK],
                                [ADDRESS_CITY],
                                [ADDRESS_STATEID],
                                [ADDRESS_POSTCODE],
                                [ADDRESS_DONOTMAIL],
                                [EMAILADDRESS_EMAILADDRESSTYPECODEID],
                                [EMAILADDRESS_EMAILADDRESS],
                                [ADDRESS_CART],
                                [ADDRESS_DPC],
                                [ADDRESS_LOT],
                                [ADDRESS_STARTDATE],
                                [ADDRESS_ENDDATE],
                                [PRIMARYADDRESSEE_NAMEFORMATTYPECODEID],
                                [PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID],
                                [PRIMARYSALUTATION_NAMEFORMATTYPECODEID],
                                [PRIMARYSALUTATION_NAMEFORMATFUNCTIONID],
                                [ISDECEASED],
                                [DECEASEDDATE],
                                [EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID],
                                [EDUCATIONALHISTORY_ISPRIMARYRECORD],
                                [EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE],
                                [EDUCATIONALHISTORY_CLASSOF],
                                [EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID],
                                [EDUCATIONALHISTORY_STARTDATE],
                                [SPOUSEID],
                                [SPOUSE_RELATIONSHIPTYPECODEID],
                                [SPOUSE_RECIPROCALTYPECODEID],
                                [SPOUSE_KEYNAME],
                                [SPOUSE_FIRSTNAME],
                                [SPOUSE_MIDDLENAME],
                                [SPOUSE_MAIDENNAME],
                                [SPOUSE_NICKNAME],
                                [SPOUSE_TITLECODEID],
                                [SPOUSE_TITLE2CODEID],
                                [SPOUSE_SUFFIXCODEID],
                                [SPOUSE_SUFFIX2CODEID],
                                [SPOUSE_GENDERCODE],
                                [SPOUSE_BIRTHDATE],
                                [SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID],
                                [SPOUSE_EMAILADDRESS_EMAILADDRESS],
                                [SPOUSE_COPY_ADDRESS],
                                [SPOUSE_COPY_PHONENUMBER],
                                [SPOUSE_COPY_EMAILADDRESS],
                                [PRIMARYBUSINESSID],
                                [PRIMARYBUSINESS_RELATIONSHIPTYPECODEID],
                                [PRIMARYBUSINESS_RECIPROCALTYPECODEID],
                                [PRIMARYBUSINESS_KEYNAME],
                                [PRIMARYBUSINESS_PHONE_PHONETYPECODEID],
                                [PRIMARYBUSINESS_PHONE_NUMBER],
                                [PRIMARYBUSINESS_PHONE_COUNTRYID],
                                [PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID],
                                [PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS],
                                [PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID],
                                [PRIMARYBUSINESS_ADDRESS_COUNTRYID],
                                [PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK],
                                [PRIMARYBUSINESS_ADDRESS_CITY],
                                [PRIMARYBUSINESS_ADDRESS_STATEID],
                                [PRIMARYBUSINESS_ADDRESS_POSTCODE],
                                [PRIMARYBUSINESS_ADDRESS_CART],
                                [PRIMARYBUSINESS_ADDRESS_DPC],
                                [PRIMARYBUSINESS_ADDRESS_LOT],
                                [PRIMARYBUSINESS_ADDRESS_DONOTMAIL],
                                [PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID],
                                [PRIMARYBUSINESS_INDUSTRYCODEID],
                                [PRIMARYBUSINESS_NUMEMPLOYEES],
                                [PRIMARYBUSINESS_NUMSUBSIDIARIES],
                                [PRIMARYBUSINESS_WEBADDRESS],
                                [PRIMARYBUSINESS_PARENTCORPID],
                                [CUSTOMID],
                                [CONSTIT_SECURITY_ATTRIBUTEID],
                                [ISGROUP],
                                [GROUPTYPECODE],
                                [GROUPTYPEID],
                                [GROUPSTARTDATE],
                                [GROUPDESCRIPTION],
                                [SEQUENCE],
                                [ADDEDBYID],
                                [CHANGEDBYID],
                                [DATEADDED],
                                [DATECHANGED],
                                [ADDRESS_COUNTYCODEID],
                                [ADDRESS_OMITFROMVALIDATION],
                                [ADDRESS_CONGRESSIONALDISTRICTCODEID],
                                [ADDRESS_STATEHOUSEDISTRICTCODEID],
                                [ADDRESS_STATESENATEDISTRICTCODEID],
                                [ADDRESS_LOCALPRECINCTCODEID],
                                [ADDRESS_INFOSOURCECODEID],
                                [ADDRESS_REGIONCODEID],
                                [ADDRESS_LASTVALIDATIONATTEMPTDATE],
                                [ADDRESS_VALIDATIONMESSAGE],
                                [ADDRESS_CERTIFICATIONDATA],
                                [ADDRESS_DONOTMAILREASONCODEID],
                                [PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID],
                                [EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID],
                                [EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID],
                                [EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID],
                                [EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID],
                                [EDUCATIONALHISTORY_PREFERREDCLASSYEAR],
                                [EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID],
                                [EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE],
                                [EDUCATIONALHISTORY_COMMENT],
                                [EDUCATIONALHISTORY_DATEGRADUATED],
                                [EDUCATIONALHISTORY_DATELEFT],
      [EDUCATIONALHISTORY_LEVELCODEID],
                                [EDUCATIONALHISTORY_REASONCODEID],
                                [ISPRIMARYORGANIZATION],
                                [PRIMARYBUSINESS_ISPRIMARYORGANIZATION],
                                [CONSTITUENT_SITEID],
                                [ADDRESS_HISTORICALSTARTDATE],
                                [PRIMARYMEMBER_COPYCONTACTINFO],
                                [PRIMARYBUSINESS_JOBTITLE],
                                [PRIMARYBUSINESS_JOBCATEGORYCODEID],
                                [PRIMARYBUSINESS_CAREERLEVELCODEID],
                                [PRIMARYBUSINESS_JOBDIVISION],
                                [PRIMARYBUSINESS_JOBDEPARTMENT],
                                [PRIMARYBUSINESS_JOBSCHEDULECODEID],
                                [ETHNICITYCODEID],
                                [RELIGIONCODEID],
                                [TARGETCODEID],
                                [INCOMECODEID],
                                [BIRTHPLACE],
                                [PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS],
                                [PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR],
                                [PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS],
                                [PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR],
                                [PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID],
                                [PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID],
                                [EMAILADDRESS_STARTDATE],
                                [SPOUSE_EMAILADDRESS_STARTDATE],
                                [PRIMARYBUSINESS_EMAILADDRESS_STARTDATE],
                                [SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS],
                                [SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR],
                                [SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS],
                                [SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR],
                                [SPOUSE_PRIMARYRECOGNITIONTYPECODEID],
                                [SPOUSE_RECIPROCALRECOGNITIONTYPECODEID],
                                [PRIMARYBUSINESS_STARTDATE],
                                [SPOUSE_STARTDATE],
                                [EMAILADDRESS_INFOSOURCECODEID],
                                [EMAILADDRESS_INFOSOURCECOMMENTS],
                                [GENDERCODEID],
                                [SPOUSE_GENDERCODEID]
                            )
                            values
                            (
                                @ID,                            
                                @BATCHID,
                                case when @ISORGANIZATION = 1 then 1 else 0 end,
                                @KEYNAME,
                                @FIRSTNAME,
                                @MIDDLENAME,
                                @MAIDENNAME,
                                @NICKNAME,
                                @TITLECODEID,
                                @TITLE2CODEID,
                                @SUFFIXCODEID,
                                @SUFFIX2CODEID,
                                @BIRTHDATE,
                                @GENDERCODE,
                                @WEBADDRESS,
                                @INDUSTRYCODEID,
                                @PARENTCORPID,
                                @NUMEMPLOYEES,
                                @NUMSUBSIDIARIES,
                                @ADDRESS_ADDRESSTYPECODEID,
                                @ADDRESS_COUNTRYID,
                                @ADDRESS_ADDRESSBLOCK,
                                @ADDRESS_CITY,
                                @ADDRESS_STATEID,
                                @ADDRESS_POSTCODE,
                                @ADDRESS_DONOTMAIL,
                @EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                @EMAILADDRESS_EMAILADDRESS,
                                @ADDRESS_CART,
                                @ADDRESS_DPC,
                                @ADDRESS_LOT,
                                @ADDRESS_STARTDATE,
                                @ADDRESS_ENDDATE,
                                @PRIMARYADDRESSEE_NAMEFORMATTYPECODEID,
                                @PRIMARYADDRESSEE_NAMEFORMATFUNCTIONID,
                                @PRIMARYSALUTATION_NAMEFORMATTYPECODEID,
                                @PRIMARYSALUTATION_NAMEFORMATFUNCTIONID,
                                @ISDECEASED,
                                @DECEASEDDATE,
                                @EDUCATIONALHISTORY_EDUCATIONALINSTITUTIONID,
                                @EDUCATIONALHISTORY_ISPRIMARYRECORD,
                                @EDUCATIONALHISTORY_CONSTITUENCYSTATUSCODE,
                                @EDUCATIONALHISTORY_CLASSOF,
                                @EDUCATIONALHISTORY_EDUCATIONALDEGREECODEID,
                                @EDUCATIONALHISTORY_STARTDATE,
                                @SPOUSEID,
                                coalesce(@SPOUSE_RELATIONSHIPTYPECODEID,@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST),
                                coalesce(@SPOUSE_RECIPROCALTYPECODEID,@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST),
                                @SPOUSE_KEYNAME,
                                @SPOUSE_FIRSTNAME,
                                @SPOUSE_MIDDLENAME,
                                @SPOUSE_MAIDENNAME,
                                @SPOUSE_NICKNAME,
                                @SPOUSE_TITLECODEID,
                                @SPOUSE_TITLE2CODEID,
                                @SPOUSE_SUFFIXCODEID,
                                @SPOUSE_SUFFIX2CODEID,
                                @SPOUSE_GENDERCODE,
                                @SPOUSE_BIRTHDATE,
                                @SPOUSE_EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                @SPOUSE_EMAILADDRESS_EMAILADDRESS,
                                @SPOUSE_COPY_ADDRESS,
                                @SPOUSE_COPY_PHONENUMBER,
                                @SPOUSE_COPY_EMAILADDRESS,
                                @PRIMARYBUSINESSID,
                                coalesce(@PRIMARYBUSINESS_RELATIONSHIPTYPECODEID,@PRIMARYBUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST),
                                coalesce(@PRIMARYBUSINESS_RECIPROCALTYPECODEID,@PRIMARYBUSINESS_RECIPROCALTYPECODEIDSIMPLELIST),
                                @PRIMARYBUSINESS_KEYNAME,
                                @PRIMARYBUSINESS_PHONE_PHONETYPECODEID,
                                @PRIMARYBUSINESS_PHONE_NUMBER,
                                @PRIMARYBUSINESS_PHONE_COUNTRYID,
                                @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESSTYPECODEID,
                                @PRIMARYBUSINESS_EMAILADDRESS_EMAILADDRESS,
                                @PRIMARYBUSINESS_ADDRESS_ADDRESSTYPECODEID,
                                @PRIMARYBUSINESS_ADDRESS_COUNTRYID,
                                @PRIMARYBUSINESS_ADDRESS_ADDRESSBLOCK,
                                @PRIMARYBUSINESS_ADDRESS_CITY,
                                @PRIMARYBUSINESS_ADDRESS_STATEID,
                                @PRIMARYBUSINESS_ADDRESS_POSTCODE,
                                @PRIMARYBUSINESS_ADDRESS_CART,
                                @PRIMARYBUSINESS_ADDRESS_DPC,
                                @PRIMARYBUSINESS_ADDRESS_LOT,
                                @PRIMARYBUSINESS_ADDRESS_DONOTMAIL,
                                @PRIMARYBUSINESS_ADDRESS_INFOSOURCECODEID,
                                @PRIMARYBUSINESS_INDUSTRYCODEID,
                                @PRIMARYBUSINESS_NUMEMPLOYEES,
              @PRIMARYBUSINESS_NUMSUBSIDIARIES,
                                @PRIMARYBUSINESS_WEBADDRESS,
                                @PRIMARYBUSINESS_PARENTCORPID,
                                @CUSTOMIDENTIFIER,
                                @CONSTIT_SECURITY_ATTRIBUTEID,
                                -- ISORGANIZATION = {0 => Individual, 1 => Organization, 2 => Household, 3 => Custom group}

                                case when (@ISORGANIZATION = 2 or @ISORGANIZATION = 3) then 1 else 0 end, -- ISGROUP

                                case 
                                  when @ISORGANIZATION = 2 then 0
                                  when @ISORGANIZATION = 3 then 1
                                  else 0
                                end, -- GROUPTYPECODE

                                @GROUPTYPEID,
                                @GROUPSTARTDATE,
                                @GROUPDESCRIPTION,
                                @SEQUENCE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE,
                                @ADDRESS_COUNTYCODEID,
                                @ADDRESS_OMITFROMVALIDATION,
                                @ADDRESS_CONGRESSIONALDISTRICTCODEID,
                                @ADDRESS_STATEHOUSEDISTRICTCODEID,
                                @ADDRESS_STATESENATEDISTRICTCODEID,
                                @ADDRESS_LOCALPRECINCTCODEID,
                                @ADDRESS_INFOSOURCECODEID,
                                @ADDRESS_REGIONCODEID,
                                @ADDRESS_LASTVALIDATIONATTEMPTDATE,
                                @ADDRESS_VALIDATIONMESSAGE,
                                @ADDRESS_CERTIFICATIONDATA,
                                @ADDRESS_DONOTMAILREASONCODEID,
                                @PRIMARYBUSINESS_ADDRESS_DONOTMAILREASONCODEID,
                                @EDUCATIONALHISTORY_ACADEMICCATALOGPROGRAMID,
                                @EDUCATIONALHISTORY_EDUCATIONALPROGRAMCODEID,
                                @EDUCATIONALHISTORY_ACADEMICCATALOGDEGREEID,
                                @EDUCATIONALHISTORY_EDUCATIONALAWARDCODEID,
                                @EDUCATIONALHISTORY_PREFERREDCLASSYEAR,
                                @EDUCATIONALHISTORY_EDUCATIONALSOURCECODEID,
                                @EDUCATIONALHISTORY_EDUCATIONALSOURCEDATE,
                                @EDUCATIONALHISTORY_COMMENT,
                                @EDUCATIONALHISTORY_DATEGRADUATED,
                                @EDUCATIONALHISTORY_DATELEFT,
                                @EDUCATIONALHISTORY_LEVELCODEID,
                                @EDUCATIONALHISTORY_REASONCODEID,
                                @ISPRIMARYORGANIZATION,
                                @PRIMARYBUSINESS_ISPRIMARYORGANIZATION,
                                @CONSTITUENT_SITEID,
                                @ADDRESS_HISTORICALSTARTDATE,
                                @PRIMARYMEMBER_COPYCONTACTINFO,
                                @PRIMARYBUSINESS_JOBTITLE,
                                @PRIMARYBUSINESS_JOBCATEGORYCODEID,
                                @PRIMARYBUSINESS_CAREERLEVELCODEID,
                                @PRIMARYBUSINESS_JOBDIVISION,
                                @PRIMARYBUSINESS_JOBDEPARTMENT,
                                @PRIMARYBUSINESS_JOBSCHEDULECODEID,
                                @ETHNICITYCODEID,
                                @RELIGIONCODEID,
                                @TARGETCODEID,
                                @INCOMECODEID,
                                @BIRTHPLACE,
                                coalesce(@PRIMARYBUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0),
                                coalesce(@PRIMARYBUSINESS_PRIMARYSOFTCREDITMATCHFACTOR, 100),
                                coalesce(@PRIMARYBUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0),
                                coalesce(@PRIMARYBUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR, 100),
                                @PRIMARYBUSINESS_PRIMARYRECOGNITIONTYPECODEID,
                                @PRIMARYBUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
                                @EMAILADDRESS_STARTDATE,
                                @SPOUSE_EMAILADDRESS_STARTDATE,
                                @PRIMARYBUSINESS_EMAILADDRESS_STARTDATE,
                                coalesce(@SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, 0),
                                coalesce(@SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR, 100),
                                coalesce(@SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 0),
                                coalesce(@SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR, 100),
                                @SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
                                @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
                                @PRIMARYBUSINESS_STARTDATE,
                                @SPOUSE_STARTDATE,
                                @EMAILADDRESS_INFOSOURCECODEID,
                                @EMAILADDRESS_INFOSOURCECOMMENTS,
                                @GENDERCODEID,
                                @SPOUSE_GENDERCODEID
                            );

                            exec dbo.USP_CONSTITUENTBATCH_GETPHONESWITHSEASONAL_ADDFROMXML @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_ADDFROMXML @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_ADDFROMXML @ID, @SYSTEMDEFINEDCONSTITUENCY, @CHANGEAGENTID;

                            exec dbo.USP_CONSTITUENTBATCH_GETINTERESTS_ADDFROMXML @ID, @INTEREST, @CHANGEAGENTID;
                            exec dbo.USP_CONSTITUENTBATCH_GETBATCHALTERNATELOOKUPIDS_ADDFROMXML @ID, @ALTERNATELOOKUPIDS, @CHANGEAGENTID                            
                            exec dbo.USP_CONSTITUENTBATCH_GETGROUPMEMBERS_ADDFROMXML @ID, @GROUPMEMBERS, @CHANGEAGENTID;                            
              exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
                            exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONUNAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
                            exec dbo.USP_CONSTITUENTBATCH_GETEDUCATIONALINVOLVEMENT_ADDFROMXML @ID, @EDUCATIONALINVOLVEMENT, @CHANGEAGENTID;
                            exec dbo.USP_CONSTITUENTBATCH_GETTAXDECLARATIONS_ADDFROMXML @ID, @TAXDECLARATIONS, @CHANGEAGENTID;
                            exec dbo.USP_CONSTITUENTBATCH_GETSOLICITCODES_ADDFROMXML_DEFAULTID @ID, @SOLICITCODES, @CHANGEAGENTID;
                            exec dbo.USP_CONSTITUENTBATCH_GETSOCIALMEDIAACCOUNTS_ADDFROMXML @ID, @SOCIALMEDIAACCOUNTS, @CHANGEAGENTID;
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;